POSTGRES - 具有多个连接的高效选择或插入

Question

tl;dr 我正在尝试找出最有效的方法来选择记录或插入它(如果它尚不存在，则可以处理多个并发连接(。

情况：我正在构建一个 Postgres 数据库(9.3.5、x64(，其中包含与客户相关的一大堆信息。该数据库具有一个包含"id"列(串行主键(和"system_id"列(VARCHAR(64((的"客户"表。id 列在其他表中用作外键以链接到客户。如果"system_id"列不为 null，则必须是唯一的。

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    system_id VARCHAR(64),
    name VARCHAR(256));

引用客户表中 id 的表示例：

CREATE TABLE tsrs (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    filename VARCHAR(256) NOT NULL,
    name VARCHAR(256),
    timestamp TIMESTAMP WITHOUT TIME ZONE);

我编写了一个 python 脚本，它使用多处理模块通过多个连接(来自不同进程(将数据推送到数据库中。

将数据推送到数据库时，每个进程需要做的第一件事是检查具有特定system_id的客户是否在客户表中。如果是，则缓存关联的 customer.id。如果表中尚未添加该行，则添加一个新行，并缓存生成的 customer.id。 我写了一个SQL函数来为我做这件事：

CREATE OR REPLACE FUNCTION get_or_insert_customer(p_system_id customers.system_id%TYPE, p_name customers.name%TYPE) RETURNS customers.id%TYPE AS $$
DECLARE
    v_id customers.id%TYPE;
BEGIN
    LOCK TABLE customers IN EXCLUSIVE MODE;
    SELECT id INTO v_id FROM customers WHERE system_id=p_system_id;
    IF v_id is NULL THEN
        INSERT INTO customers(system_id, name)
            VALUES(p_system_id,p_name)
            RETURNING id INTO v_id;
    END IF;
    RETURN v_id;
END;
$$ LANGUAGE plpgsql;

问题：表锁定是我能够防止并发进程将重复system_ids添加到表中的唯一方法。这并不理想，因为它此时有效地序列化了所有处理，并且基本上将给定数量的数据推送到数据库中所需的时间增加了一倍。

我想问一下是否有一种更有效/优雅的方式来实现"选择或插入"机制，而不会造成太大的减速？我怀疑没有，但认为值得一问，以防万一。

非常感谢您阅读本文。任何建议都非常感谢！

Answer 1

我设法将函数重写为普通 SQL，更改顺序(避免IF和潜在的竞争条件(

CREATE OR REPLACE FUNCTION get_or_insert_customer
        ( p_system_id customers.system_id%TYPE
        , p_name customers.name%TYPE
        )  RETURNS customers.id%TYPE AS $func$
    LOCK TABLE customers IN EXCLUSIVE MODE;
    INSERT INTO customers(system_id, name)
    SELECT p_system_id,p_name
     WHERE NOT EXISTS (SELECT 1 FROM customers WHERE system_id = p_system_id)
        ;
    SELECT id
        FROM customers WHERE system_id = p_system_id
        ;
$func$ LANGUAGE sql;