SELECT或INSERT函数是否易于出现竞争状况？

Question

我写了一个函数来为一个简单的博客引擎创建帖子：

CREATE FUNCTION CreatePost(VARCHAR, TEXT, VARCHAR[])
RETURNS INTEGER AS $$
    DECLARE
        InsertedPostId INTEGER;
        TagName VARCHAR;
    BEGIN
        INSERT INTO Posts (Title, Body)
        VALUES ($1, $2)
        RETURNING Id INTO InsertedPostId;

        FOREACH TagName IN ARRAY $3 LOOP
            DECLARE
                InsertedTagId INTEGER;
            BEGIN
                -- I am concerned about this part.
                BEGIN
                    INSERT INTO Tags (Name)
                    VALUES (TagName)
                    RETURNING Id INTO InsertedTagId;
                EXCEPTION WHEN UNIQUE_VIOLATION THEN
                    SELECT INTO InsertedTagId Id
                    FROM Tags
                    WHERE Name = TagName
                    FETCH FIRST ROW ONLY;
                END;

                INSERT INTO Taggings (PostId, TagId)
                VALUES (InsertedPostId, InsertedTagId);
            END;
        END LOOP;

        RETURN InsertedPostId;
    END;
$$ LANGUAGE 'plpgsql';

当多个用户同时删除标签并创建帖子时，是否容易出现竞争状况？
具体来说，交易（以及功能）是否可以防止此类竞争情况的发生？
我正在使用PostgreSQL 9.2.3。

Answer 1

它的经常性问题 _ SELECT 或INSERT下可能并发写入负载，涉及（但不同于）UPSERT（这是 INSERT
或UPDATE_）。

对于Postgres 9.5或更高版本

使用新的UPSERT实施INSERT ... ON CONFLICT .. DO UPDATE，我们可以大大简化。PL / pgSQL函数到INSERT或SELECT一个 _ 单个_ 行（标记）：

CREATE OR REPLACE FUNCTION f_tag_id(_tag text, OUT _tag_id int) AS
$func$
BEGIN
   SELECT tag_id  -- only if row existed before
   FROM   tag
   WHERE  tag = _tag
   INTO   _tag_id;

   IF NOT FOUND THEN
      INSERT INTO tag AS t (tag)
      VALUES (_tag)
      ON     CONFLICT (tag) DO NOTHING
      RETURNING t.tag_id
      INTO   _tag_id;
   END IF;
END
$func$ LANGUAGE plpgsql;

比赛条件仍然有一个很小的窗口。为了 _ 确保_ 您获得ID，请执行以下操作：

CREATE OR REPLACE FUNCTION f_tag_id(_tag text, OUT _tag_id int) AS
$func$
BEGIN
LOOP
   SELECT tag_id
   FROM   tag
   WHERE  tag = _tag
   INTO   _tag_id;

   EXIT WHEN FOUND;

   INSERT INTO tag AS t (tag)
   VALUES (_tag)
   ON     CONFLICT (tag) DO NOTHING
   RETURNING t.tag_id
   INTO   _tag_id;

   EXIT WHEN FOUND;
END LOOP;
END
$func$ LANGUAGE plpgsql;

这一直循环直到成功INSERT或失败为止SELECT。称呼：

SELECT f_tag_id('possibly_new_tag');

如果 同一事务中的 后续命令依赖于该行的存在，并且实际上其他事务可能同时更新或删除该行，则可以使用来锁定SELECT语句中的现有行FOR SHARE。
如果改为插入该行，则该行将一直锁定到事务结束为止。

如果大多数时间都插入了新行，则从开始开始INSERT使其更快。

有什么不对 这个 纯SQL的解决方案？

我以前也曾建议过此SQL函数：

CREATE OR REPLACE FUNCTION f_tag_id(_tag text, OUT _tag_id int) AS
$func$
   WITH ins AS (
      INSERT INTO tag AS t (tag)
      VALUES (_tag)
      ON     CONFLICT (tag) DO NOTHING
      RETURNING t.tag_id
      )
   SELECT tag_id FROM ins
   UNION  ALL
   SELECT tag_id FROM tag WHERE tag = _tag
   LIMIT  1
$func$ LANGUAGE sql;

这并不是完全错误，但是它无法解决漏洞，就像@FunctorSalad在他的补充答案中得出的那样。如果并发事务尝试同时执行相同的操作，则该函数可能会得出空结果。具有CTE的查询中的所有语句实际上都是在同一时间执行的。手册：

所有语句都使用相同的快照执行

如果并发事务稍早插入了相同的新标记，但尚未提交：

• 等待并发事务完成后，UPSERT部分变为空。（如果并发事务应回滚，它仍会插入新标签并返回新ID。）

• SELECT部分​​也空了，因为它基于相同的快照，在该快照中，（尚未提交的）并发事务中的新标记不可见。

我们 什么也得不到 。不符合预期。这与朴素的逻辑（我被困在那里）是违反直觉的，但这就是Postgres的MVCC模型的工作原理-必须起作用。

因此，如果多个事务可以尝试同时插入同一标签，请不要使用此选项。 或 循环播放，直到您真正得到一行。在常见的工作负载中几乎不会触发该循环。

原始答案（Postgres 9.4或更早版本）

给定此表（略有简化）：

CREATE table tag (
  tag_id serial PRIMARY KEY
, tag    text   UNIQUE
);

…一种 实际上是100％安全的 功能，可以插入新标签/选择现有标签，看起来像这样。
为什么不100％？请参考手册中有关相关UPSERT示例的注释：

CREATE OR REPLACE FUNCTION f_tag_id(_tag text, OUT tag_id int) AS
$func$
BEGIN

LOOP
   BEGIN

   WITH sel AS (SELECT t.tag_id FROM tag t WHERE t.tag = _tag FOR SHARE)
      , ins AS (INSERT INTO tag(tag)
                SELECT _tag
                WHERE  NOT EXISTS (SELECT 1 FROM sel)  -- only if not found
                RETURNING tag.tag_id)  -- qualified so no conflict with param
   SELECT sel.tag_id FROM sel
   UNION  ALL
   SELECT ins.tag_id FROM ins
   INTO   tag_id;

   EXCEPTION WHEN UNIQUE_VIOLATION THEN     -- insert in concurrent session?
      RAISE NOTICE 'It actually happened!'; -- hardly ever happens
   END;

   EXIT WHEN tag_id IS NOT NULL;            -- else keep looping
END LOOP;

END
$func$ LANGUAGE plpgsql;

解释

• 尝试第SELECT 一个 。这样，您可以避免99.99％的时间处理异常 昂贵的 异常。

• 使用CTE可以最大程度地减少竞争条件下的（已经很小的）时隙。

• 一个查询中 的SELECT和之间的时间窗口非常小。如果您没有繁重的并发负载，或者您可以每年遇到一次异常，则可以忽略这种情况，而使用SQL语句，这会更快。INSERT __

• 不需要FETCH FIRST ROW ONLY（= LIMIT 1）。标签名称显然是UNIQUE。

• FOR SHARE 如果您通常没有并发DELETE或不在UPDATE表中，请在我的示例中删除tag。花费一点点性能。

• 切勿引用语言名称： ‘plpgsql’ 。plpgsql是一个 标识符 。报价可能会引起问题，并且仅允许向后兼容。

• 请勿使用非描述性的列名，例如id或name。当联接几个表时（ 这是 在关系数据库中 所做的事情 ），您最终会使用多个相同的名称，并且必须使用别名。

内置在您的功能中

使用此功能，您可以在很大程度上简化FOREACH LOOP为：

...
FOREACH TagName IN ARRAY $3
LOOP
   INSERT INTO taggings (PostId, TagId)
   VALUES   (InsertedPostId, f_tag_id(TagName));
END LOOP;
...

不过，作为带有以下命令的单个SQL语句，速度更快unnest()：

INSERT INTO taggings (PostId, TagId)
SELECT InsertedPostId, f_tag_id(tag)
FROM   unnest($3) tag;

替换整个循环。

替代解决方案

此变体基于UNION ALLwithLIMIT子句的行为：一旦找到足够的行，就永远不会执行其余的行
在此基础上，我们可以将外包给INSERT一个单独的函数。只有在那里，我们需要异常处理。和第一个解决方案一样安全。

CREATE OR REPLACE FUNCTION f_insert_tag(_tag text, OUT tag_id int)
  RETURNS int AS
$func$
BEGIN
INSERT INTO tag(tag) VALUES (_tag) RETURNING tag.tag_id INTO tag_id;

EXCEPTION WHEN UNIQUE_VIOLATION THEN  -- catch exception, NULL is returned
END
$func$ LANGUAGE plpgsql;

主要功能中使用的是：

CREATE OR REPLACE FUNCTION f_tag_id(_tag text, OUT _tag_id int) AS
$func$
BEGIN
   LOOP
      SELECT tag_id FROM tag WHERE tag = _tag
      UNION  ALL
      SELECT f_insert_tag(_tag)  -- only executed if tag not found
      LIMIT  1  -- not strictly necessary, just to be clear
      INTO   _tag_id;

      EXIT WHEN _tag_id IS NOT NULL;  -- else keep looping
   END LOOP;
END
$func$ LANGUAGE plpgsql;

• 如果大多数调用只需要它SELECT，这会便宜一些，因为很少输入INSERT包含EXCEPTION子句的更昂贵的块。查询也更简单。

• FOR SHARE在这里是不可能的（UNION查询中不允许）。

• LIMIT 1不需要（在第9.4页中进行了测试）。Postgres派生LIMIT 1自INTO _tag_id并执行直到找到第一行。