仅使用SQL返回更新前的列值

一尘不染

仅使用SQL返回更新前的列值

sql

我想从已更新的行中获取列的OLD值-无需使用触发器（也无需使用存储过程，也无需使用任何其他额外的非-SQL / -query实体）。

我有这样的查询：

   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在对我的其他问题（在上面链接）的评论中提出的建议。

请参阅我的其他问题，以正确实现并发甚至是非阻塞版本；此查询仅显示如何从更新中获取旧值和新值，而忽略错误/错误的并发位。

阅读 194

2021-03-10

共1个答案

一尘不染

问题
手册说明：

可选RETURNING子句导致UPDATE基于实际更新的每一行计算并返回值。FROM可以计算使用表的列和/或中提到的其他表的列的任何表达式。使用表列的新（更新后）值。该RETURNING列表的语法与的输出列表的语法相同SELECT。

大胆强调我的。无法访问RETURNING子句中的旧行。您可以通过触发器来解决此限制，也可以使用@SELECT 之前的分隔UPDATE符来解决此限制，如@Flimzy和@wildplasser所注释，将其包装在事务中，或如@MattDiPasquale发布，将其包装在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版本进行了测试。

并发写入负载的解决方案
有多种方法可以避免在同一行上进行并发写入操作时出现竞争情况。（请注意，对不相关的行进行并发写入操作完全没有问题。）一种简单，缓慢且确定（但昂贵）的方法是以SERIALIZABLE隔离级别运行事务：

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 FOR UPDATE) 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稍后，条件可能会解决为一组不同的行。

2021-03-10