sql - 仅使用 SQL 返回 pre-UPDATE 列值

Question

I posted a related question ，但这是我困惑的另一部分。

我想从已更新的行中获取列的旧值 - 不使用触发器(也不使用存储过程，也不使用任何其他额外的非 SQL/查询实体)。

我有这样的查询:

   UPDATE my_table
      SET processing_by = our_id_info  -- unique to this worker
    WHERE trans_nbr IN (
                        SELECT trans_nbr
                          FROM my_table
                         GROUP BY trans_nbr
                        HAVING COUNT(trans_nbr) > 1
                         LIMIT our_limit_to_have_single_process_grab
                       )
RETURNING row_id;

如果我可以在子查询的末尾执行 FOR UPDATE ON my_table，那就太棒了(并解决了我的其他问题)。但这行不通:不能将其与 GROUP BY 结合使用(这是计算计数所必需的)。然后我可以只获取那些 trans_nbr 并首先进行查询以获取(即将成为)以前的 processing_by 值。

我试过这样做:

   UPDATE my_table
      SET processing_by = our_id_info -- unique to this worker
     FROM my_table old_my_table
     JOIN (
             SELECT trans_nbr
               FROM my_table
           GROUP BY trans_nbr
             HAVING COUNT(trans_nbr) > 1
              LIMIT our_limit_to_have_single_process_grab
          ) sub_my_table
       ON old_my_table.trans_nbr = sub_my_table.trans_nbr
    WHERE     my_table.trans_nbr = sub_my_table.trans_nbr
      AND my_table.processing_by = old_my_table.processing_by
RETURNING my_table.row_id, my_table.processing_by, old_my_table.processing_by

但这行不通； old_my_table 在连接外不可见； RETURNING 子句对此视而不见。

我已经忘记了我所做的所有尝试；我已经为此研究了几个小时。

如果我能找到一种万无一失的方法来锁定我的子查询中的行 - 并且只有那些行，并且当子查询发生时 - 我试图避免的所有并发问题都会消失......

---

更新:我在上面的非通用代码中有错字。在 Erwin Brandstetter 建议它应该工作后，我重试了。既然我花了这么长时间才找到这种解决方案，也许我的尴尬是值得的？至少现在这是为后代准备的……:>

我现在拥有的(有效的)是这样的:

   UPDATE my_table
      SET processing_by = our_id_info -- unique to this worker
     FROM my_table AS old_my_table
    WHERE trans_nbr IN (
                          SELECT trans_nbr
                            FROM my_table
                        GROUP BY trans_nbr
                          HAVING COUNT(*) > 1
                           LIMIT our_limit_to_have_single_process_grab
                       )
      AND my_table.row_id = old_my_table.row_id
RETURNING my_table.row_id, my_table.processing_by, old_my_table.processing_by AS old_processing_by

COUNT(*) 是根据 Flimzy 在对我的其他(上面链接的)问题的评论中提出的建议。

Please see my other question正确实现并发甚至非阻塞版本；此查询仅显示如何从更新中获取旧值和新值，忽略坏/错误的并发位。

Answer 1

问题

The manual explains :

The optional RETURNING clause causes UPDATE to compute and returnvalue(s) based on each row actually updated. Any expression using thetable's columns, and/or columns of other tables mentioned in FROM, canbe computed. The new (post-update) values of the table's columns areused. The syntax of the RETURNING list is identical to that of theoutput list of SELECT.

大胆强调我的。无法访问 RETURNING 中的旧行条款。您可以使用触发器或单独的 SELECT 解决此限制。 之前 UPDATE正如所评论的那样，包裹在交易中或包裹在 CTE 中。

但是，如果您加入 FROM 中表的另一个实例，您试图实现的工作得很好子句:

没有并发写入的解决方案

UPDATE tbl x
SET    tbl_id = 23
     , name = 'New Guy'
FROM   tbl y                -- using the FROM clause
WHERE  x.tbl_id = y.tbl_id  -- must be UNIQUE NOT NULL
AND    x.tbl_id = 3
RETURNING y.tbl_id AS old_id, y.name AS old_name
        , x.tbl_id          , x.name;

返回:

 old_id | old_name | tbl_id |  name
--------+----------+--------+---------
  3     | Old Guy  | 23     | New Guy

用于自连接的列必须是 UNIQUE NOT NULL .在这个简单的例子中，WHERE条件在同一列 tbl_id ，但这只是巧合。适用于任何条件。

我使用 8.4 到 13 的 PostgreSQL 版本对此进行了测试。

INSERT 不同:

• INSERT INTO ... FROM SELECT ... RETURNING id mappings

并发写入负载的解决方案

有多种方法可以避免在同一行上并发写入操作的竞争条件。 (请注意，对不相关行的并发写入操作根本没有问题。)简单、缓慢且确定(但昂贵)的方法是使用 SERIALIZABLE isolation level 运行事务。 :

BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE ... ;
COMMIT;

但这可能有点矫枉过正。并且您需要准备好在序列化失败的情况下重复该操作。

在要更新的一个行上显式锁定更简单、更快(并且与并发写入负载一样可靠):

UPDATE tbl x
SET    tbl_id = 24
     , name = 'New Gal'
FROM  (SELECT tbl_id, name FROM tbl WHERE tbl_id = 4 <b>FOR UPDATE</b>) y 
WHERE  x.tbl_id = y.tbl_id
RETURNING y.tbl_id AS old_id, y.name AS old_name
        , x.tbl_id          , x.name;

请注意 WHERE条件移动到子查询(同样，可以是任何)，只有自连接(在 UNIQUE NOT NULL 列)保留在外部查询中。这保证只有被内部 SELECT 锁定的行被处理。 WHERE稍后，条件可能会解析为一组不同的行。

参见:

• Atomic UPDATE .. SELECT in Postgres

db<> fiddle here
<子>旧sqlfiddle