我在Postgres 14中有以下表格：

CREATE TABLE mytable (
  id            text PRIMARY KEY
, top           bigint  NOT NULL
, top_timestamp bigint  NOT NULL
);

字符串
我想upsert top和top_timestamp，同时返回旧值（如果有的话）。如果行已经存在，我想只有当top的新值>旧值时才更新它。
解决方案还必须考虑并发写入。
对于upsert，到目前为止，我有这个：

INSERT INTO mytable
AS mt (id, top, top_timestamp)
VALUES ('some-id', 123, 999)
ON CONFLICT (id)
DO UPDATE SET top=123, top_timestamp=999
WHERE mt.top < 123

型
我意识到RETURNING只能引用新值，所以为了返回旧值，我有：

WITH old_values AS (
   SELECT top, top_timestamp FROM mytable
   WHERE id = 'some-id' FOR UPDATE
   )
, upd AS ($upsertSqlAbove)
SELECT top AS old_top, top_timestamp AS old_top_timestamp
FROM old_values;

型
这看起来是可行的，但是对于并发写来说是安全的吗？SELECT FOR UPDATE是否像我期望的那样工作？也就是说，它将锁定行，直到整个查询完成？
第一个查询和这个查询有什么区别：

INSERT INTO mytable
AS mt (id, top, top_timestamp)
VALUES ('some-id', 123, 999)
ON CONFLICT (id)
DO UPDATE SET top=123, top_timestamp=999
WHERE mt.top < 123
RETURNING (SELECT top FROM mytable WHERE id = 123) AS last_top, 
          (SELECT top_timestamp FROM mytable WHERE id = 123) AS last_top_timestamp

型
下面是一个fiddle测试。

什么问题？

你会问：
第一个查询和这个查询有什么区别：
这两种方法都是以并发安全的方式报告刚更新的最后一个行版本的旧值。但是第一种方法的开销更大。它会更早地锁定现有的行。因此，它会更长时间地持有锁，并可能更长时间地阻止对同一行的并发写入。它还会执行多一条语句。最重要的是，即使没有发生更晚的UPDATE，它也会锁定该行，这完全是浪费。
所以第二个查询更好。除了两个缺陷：
1.它为每一列运行一个单独的SELECT。这似乎是必要的，因为RETURNING子句中的子查询表达式只能返回一个值。
1.即使在INSERT之后，它也会运行这些SELECT查询。

仅更新后返回旧行

不幸的是，UPSERT的UPDATE部分不允许在FROM子句中添加额外的表-这与常规的UPDATE不同。请参阅：

仅使用SQL返回UPDATE前的列值

所以你的尝试是有根据的。
此查询修复了两个缺陷（在Postgres 14和16中测试）：

INSERT INTO tbl AS t (id, top, top_timestamp)
VALUES ('some-id', 123, 999)
ON     CONFLICT (id) DO UPDATE
SET    top = EXCLUDED.top                      -- !
     , top_timestamp = EXCLUDED.top_timestamp  -- !
WHERE  t.top < EXCLUDED.top                    -- !
RETURNING (SELECT t_old FROM tbl t_old
           WHERE  t_old.id = t.id
           AND    t.xmax <> 0                  -- actually an UPDATE!
          ).*                                  -- !!!

字符串
fiddle
(To为普通读者澄清一下：RETURNING子句中的子查询对表的同一快照进行操作，但不会看到同一语句中插入或更新的任何行。它看到的是旧的行版本。）
测试t.xmax <> 0过滤实际更新。请参阅：

检测行是否被更新或插入

我们可以通过返回一个（众所周知的）行类型，然后用**(SELECT ...).***将其分解，从而绕过讨论过的子查询限制。
另外，使用特殊的行变量EXCLUDED。它保存建议插入的行，并有助于避免重复拼写输入值。（细微的区别：所有默认值和触发器都已应用。）
这将返回整行。要仅返回选定的列，请在CTE中使用UPSERT，然后进行分解：

WITH ups AS (
   INSERT INTO tbl AS t (id, top, top_timestamp)
   VALUES ('some-id', 124, 1000)
   ON     CONFLICT (id) DO UPDATE
   SET    top = EXCLUDED.top
        , top_timestamp = EXCLUDED.top_timestamp
   WHERE  t.top < EXCLUDED.top
   RETURNING (SELECT t_old FROM tbl t_old
              WHERE  t_old.id = t.id
              AND    t.xmax <> 0
             ) AS t_old
   )
SELECT id, top , top_timestamp  -- just the columns you want
FROM  (SELECT (t_old).* FROM ups) sub;

型
注意我如何在子查询中分解行类型 once。这是为了避免重复计算。请参阅：

如何避免在查询中使用（func（））.* 语法进行多个函数求值？

任何情况下都返回旧行

根据您的评论，如果没有更新发生，您甚至希望返回旧行。如果WHERE条件不为真，则不会发生更新，RETURNING也不会返回任何行。这是SELECT的一个更棘手的组合，INSERT和UPDATE（UPSERT）.上面的查询不会削减它.您的CTE解决方案将工作.或者像这里使用的空更新：

如何在PostgreSQL中使用RETURNING和ON CONFLICT？

简单，但浪费-正如我对同一问题的回答所解释的那样：

如何在PostgreSQL中使用RETURNING和ON CONFLICT？

把我们到目前为止学到的合并和这个结合起来：

函数中的SELECT或SELECT是否容易出现竞态条件？

要实现此针对单行UPSERT的优化函数，请执行以下操作：

CREATE OR REPLACE FUNCTION f_upsert_tbl(_id text, _top bigint, _top_timestamp bigint, OUT old_row tbl)
  LANGUAGE plpgsql AS
$func$
BEGIN
   LOOP
      SELECT *
      FROM   tbl
      WHERE  id = _id
      AND    top >= _top   -- new value isn't bigger
      INTO   old_row;

      EXIT WHEN FOUND;

      INSERT INTO tbl AS t
             ( id,  top,  top_timestamp)
      VALUES (_id, _top, _top_timestamp)
      ON     CONFLICT (id) DO UPDATE
      SET    top = EXCLUDED.top
           , top_timestamp = EXCLUDED.top_timestamp
      WHERE  t.top < EXCLUDED.top                    -- new value is bigger
      RETURNING (SELECT t_old FROM tbl t_old
                 WHERE  t_old.id = t.id
                 AND    t.xmax <> 0                  -- actually was an UPDATE!
                ).*
      INTO   old_row;

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

型
电话：

SELECT * FROM f_upsert_tbl('some-id', 123, 999);

型
或者：

SELECT id, top FROM f_upsert_tbl('new-id', 3, 3);

型
fiddle的
OUT old_row tbl使用表的注册行类型作为“OUT”参数。替换为实际的（模式限定的）表名。显然，使所有列名和类型适应实际的表定义。
应该是完美的解决方案。将锁保持在最低限度（当只是返回一个旧的，未更改的行时，根本不写锁），并在默认的READ COMMITTED隔离级别中处理并发写入可能产生的每个角落情况。调用变得超级简单和简短，如演示所示。
可能的缺点：

创建表类型的依赖项。如果修改表列，则可能需要重新创建函数。除非将CASCADE添加到DROP命令中，否则无法删除表。
不适用于多行UPSERT。

postgresql 在UPSERT中返回旧行值

1条答案

什么问题？

仅更新后返回旧行

任何情况下都返回旧行

相关问题

热门标签

最新问答