这是PostgreSQL的通用MERGE / UPSERT功能安全吗？

发布时间：2020-12-13 15:52:27 所属栏目：百科来源：网络整理

导读：我创建了一个“合并”函数,它应该执行UPDATE或INSERT查询,具体取决于现有数据.此函数不是为每个表编写upsert-wrapper(如大多数可用示例中所示),而是采用整个SQL字符串.这两个SQL字符串都是由我们的应用程序自动生成的. 计划是这样调用函数： -- hypothetical

我创建了一个“合并”函数,它应该执行UPDATE或INSERT查询,具体取决于现有数据.此函数不是为每个表编写upsert-wrapper(如大多数可用示例中所示),而是采用整个SQL字符串.这两个SQL字符串都是由我们的应用程序自动生成的.

计划是这样调用函数：

-- hypothetical "settings" table,with a primary key of (user_id,setting):

SELECT merge(
    $$UPDATE settings SET value = 'x' WHERE user_id = 42 AND setting = 'foo'$$,$$INSERT INTO settings (user_id,setting,value) VALUES (42,'foo','x')$$
);

这是merge()函数的完整代码：

CREATE OR REPLACE FUNCTION merge (update_sql TEXT,insert_sql TEXT) RETURNS TEXT AS
$func$
DECLARE
    max_iterations INTEGER := 10;
    i INTEGER := 0;
    num_updated INTEGER;
BEGIN
    -- usually returns before re-entering the loop
    LOOP

        -- first try the update
        EXECUTE update_sql;
        GET DIAGNOSTICS num_updated = ROW_COUNT;
        IF num_updated > 0 THEN
            RETURN 'UPDATE';
        END IF;

        -- nothing was updated: try the insert,watching out for concurrent inserts
        BEGIN
            EXECUTE insert_sql;
            RETURN 'INSERT';
        EXCEPTION WHEN unique_violation THEN
            -- nop; just loop and try again from the top
        END;

        -- emergency brake
        i := i + 1;
        IF i >= max_iterations THEN
            RAISE EXCEPTION 'merge(): tried looping % times,giving up now.',i;
            EXIT;
        END IF;

    END LOOP;
END;
$func$
LANGUAGE plpgsql;

它似乎在我的测试中运行良好,但我不确定我是否没有错过任何关键的内容,特别是关于并发UPDATE / INSERT / DELETE查询,可以在不使用此函数的情况下发出.我忽略了什么重要的事吗？

我为此功能咨询的资源包括：

> UPDATE/INSERT example 40.2 in the PostgreSQL manual
> Why is UPSERT so complicated?
> SO: Insert,on duplicate update (postgresql)

(编辑：其中一个目标是避免锁定目标表.)

解决方法

您的问题的答案取决于您的应用程序将如何访问数据库的上下文.有很多方法可以解决这个问题,如 depesz’s post you cited by yourself中所讨论的那样.此外,您可能还想考虑使用可写CTE,请参阅 here.此外,[问题] Insert,on duplicate update in PostgreSQL?对您的决策过程进行了一些有趣的讨论.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!