sql - PSQL "BEGIN TRANSACTION"语句错误

Question

我正在尝试在 PSQL 中运行以下查询 -

DO $$
  BEGIN TRANSACTION
  LOCK TABLE tags IN EXCLUSIVE MODE;
  IF (SELECT COUNT(*)
     FROM tags
     WHERE user_id = 1) > 3
  THEN
    INSERT INTO "tags" ("user_id", "friend_id", "status", "explanation", "score", "created_at", "updated_at")
    VALUES (1, 4, 'pending', '', 0.0, '2016-02-29 00:43:58.969313', '2016-02-29 00:43:58.969313')
    RETURNING "id";

    INSERT INTO "tags" ("user_id", "friend_id", "status", "explanation", "score", "created_at", "updated_at")
    VALUES (4, 1, 'pending', '', 0.0, '2016-02-29 00:43:58.969313', '2016-02-29 00:43:58.969313')
    RETURNING "id";
  ELSE
    ROLLBACK;
  END IF;

  COMMIT;
$$;

不幸的是，它一直出错 -

ERROR:  syntax error at or near "TRANSACTION"
LINE 2:   BEGIN TRANSACTION
                ^

而且我似乎无法弄清楚为什么它不喜欢 BEGIN TRANSACTION。我尝试添加 ; 并删除关键字 TRANSACTION。

Answer 1

在匿名代码块中，BEGIN 表示代码的开始，而不是 SQL 命令 BEGIN。请注意，函数(包括匿名代码块)在其自己的事务中运行，因此您通常不需要显式的附加事务。

在您的代码中根本不需要事务，因为您只是在ROLLBACK 之前SELECTing 数据。这应该工作得很好:

DO $$
DECLARE
  cnt integer;
BEGIN
  SELECT count(*) INTO cnt
  FROM tags
  WHERE user_id = 1;
  IF cnt > 3 THEN
    INSERT INTO "tags" ("user_id", "friend_id", "status", "explanation", "score", "created_at", "updated_at")
    VALUES (1, 4, 'pending', '', 0.0, '2016-02-29 00:43:58.969313', '2016-02-29 00:43:58.969313');

    INSERT INTO "tags" ("user_id", "friend_id", "status", "explanation", "score", "created_at", "updated_at")
    VALUES (4, 1, 'pending', '', 0.0, '2016-02-29 00:43:58.969313', '2016-02-29 00:43:58.969313');
  END IF;
END;
$$ LANGUAGE plpgsql;

您通常最好将并发解决方案留给 DBMS。在经常访问的表上使用 EXCLUSIVE LOCK 会降低整个系统的速度。如果您使用的是 PG 9.5，请查看 INSERT ... ON CONFLICT DO。在所有版本中，您还可以使用不太严格的锁定策略，例如 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE(在不锁定整个表的情况下已经非常严格)或咨询锁(侵入性小得多)。另请注意，插入具有保证唯一键的记录时不会发生并发冲突，例如由序列生成的键。