Sql 返回UPSERT中的旧行值

有什么问题吗？

你会问:

第一个查询和下面的查询有什么不同:

或者报告刚刚以并发安全的方式更新的最后一行版本的旧值.但第一个要贵一些.它会在更早的时候锁定现有行.因此，它持有锁的时间更长，可能会阻止对同一行的并发写入更长时间.它还执行另一条语句.最重要的是，它锁定行，即使不晚发生UPDATE次，这完全是一种浪费.

所以第二个问题比较好.除了两个缺点:

1. 它为每一列运行单独的SELECT.这似乎是必要的，因为RETURNING子句中的子查询表达式只能返回单个值.

2. 即使在INSERT之后，它也会运行这SELECT个查询.然后，这SELECT美元肯定会空手而归--以全额成本.

仅当更新时才返回旧行

不幸的是，与常规的UPDATE不同，UPSERT的UPDATE部分不允许在FROM子句中添加表.请参见:

• Return pre-UPDATE column values using SQL only

所以你的try 是有根据的.

此查询修复了两个缺陷(在Postgres 14和amp；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个

(为了向普通读者说明:RETURNING子句中的子查询对表的同一快照进行操作，并且尚未在同一语句中看到任何插入或更新的行.它看到的是旧的行版本.)

测试t.xmax <> 0过滤实际更新.参见:

• Detect if the row was updated or inserted个

我们可以通过返回单个(众所周知的)行类型和then de-composing it-101 SELECT ... 103来绕过所讨论的对子查询的限制.

另外，使用特殊的行变量EXCLUDED.它保存建议插入的行，并帮助避免重复拼写输入值.(细微差别:所有缺省值和触发器都已apply.)

这将返回整行.要仅返回选定的列，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中分解行类型.这是为了避免重复判断.参见:

• How to avoid multiple function evals with the (func()).* syntax in a query?

在任何情况下都要返回旧行

根据您的 comments ，如果没有发生更新，您甚至希望恢复原来的行.如果条件WHERE不为真，则不进行更新，RETURNING不返回任何行.这是SELECT、INSERT和UPDATE(UPSERT)的更微妙的组合.上面的查询并不能解决问题.您的CTE解决方案将会奏效.或如下所示的空更新:

• How to use RETURNING with ON CONFLICT in PostgreSQL?个

简单，但浪费--正如我在回答同样的问题时所解释的那样:

• How to use RETURNING with ON CONFLICT in PostgreSQL?个

将我们到目前为止学到的一切与以下内容结合起来:

• Is SELECT or INSERT in a function prone to race conditions?

为了达到single-row 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"参数.替换为您的实际(架构限定的)表名.显然，应根据您的实际表定义调整所有列名和类型.

应该是perfect solution路.将锁保持在最低限度(当只返回一个旧的、未更改的行时，根本不会写锁)，并处理在默认的READ COMMITTED隔离级别下并发写入可能产生的所有情况.如图所示，呼叫变得非常简单和简短.

可能的缺点:

• 创建表类型的依赖项.如果修改表列，则可能必须重新创建该函数.除非在DROP命令中加上CASCADE，否则无法删除表.
• 不适用于多行UPSERT.