仅使用 SQL 返回更新前的列值 [英] Return pre-UPDATE column values using SQL only

问题描述

我发布了一个相关问题，但这是我拼图的另一部分.

I posted a related question, but this is another part of my puzzle.

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

I would like to get the OLD value of a column from a row that was UPDATEd - WITHOUT using triggers (nor stored procedures, nor any other extra, non -SQL/-query entities).

我有一个这样的查询:

   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 值.

If I could do FOR UPDATE ON my_table at the end of the subquery, that'd be divine (and fix my other question/problem). But that won't work: can't combine this with GROUP BY (which is necessary for figuring out the count). Then I could just take those trans_nbr's and do a query first to get the (soon-to-be-) former processing_by values.

我试过这样做:

   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 在 join 之外不可见；RETURNING 子句对此视而不见.

But that can't work; old_my_table is not visible outside the join; the RETURNING clause is blind to it.

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

I've long since lost count of all the attempts I've made; I have been researching this for literally hours.

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

If I could just find a bullet-proof way to lock the rows in my subquery - and ONLY those rows, and WHEN the subquery happens - all the concurrency issues I'm trying to avoid would disappear ...

更新:我在上面的非通用代码中有一个错字.在 Erwin Brandstetter 建议它应该可以工作后，我重试了.既然我花了这么长时间才找到这样的解决方案，也许我的尴尬是值得的?至少这对后代来说是这样的......:>

UPDATE: I had a typo in the non-generic code of the above. I retried after Erwin Brandstetter suggested it should work. Since it took me so long to find this sort of solution, perhaps my embarrassment is worth it? At least this is on SO for posterity now... :>

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

What I now have (that works) is like this:

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

The COUNT(*) is per a suggestion from Flimzy in a comment on my other (linked above) question.

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

Please see my other question for correctly implementing concurrency and even a non-blocking version; THIS query merely shows how to get the old and new values from an update, ignore the bad/wrong concurrency bits.

推荐答案

问题

手册解释:

可选的 RETURNING 子句导致 UPDATE 计算并返回基于实际更新的每一行的值.任何使用表的列，和/或 FROM 中提到的其他表的列，可以被计算.表列的 新(更新后)值是使用.RETURNING 列表的语法与SELECT 的输出列表.

The optional RETURNING clause causes UPDATE to compute and return value(s) based on each row actually updated. Any expression using the table's columns, and/or columns of other tables mentioned in FROM, can be computed. The new (post-update) values of the table's columns are used. The syntax of the RETURNING list is identical to that of the output list of SELECT.

粗体强调我的.无法访问 RETURNING 子句中的旧行.您可以使用触发器或单独的 SELECT before 来解决此限制，UPDATE 包装在事务中或包装在 CTE 中，如注释.

Bold emphasis mine. There is no way to access the old row in a RETURNING clause. You can work around this restriction with a trigger or a separate SELECT before the UPDATE wrapped in a transaction or wrapped in a CTE as was commented.

但是，如果您在 FROM 子句中加入表的另一个实例，则您要实现的效果很好:

However, what you are trying to achieve works perfectly fine if you join to another instance of the table in the FROM clause:

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 上，但这只是巧合.适用于任何条件.

The column(s) used to self-join must be UNIQUE NOT NULL. In the simple example, the WHERE condition is on the same column tbl_id, but that's just coincidence. Works for any conditions.

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

I tested this with PostgreSQL versions from 8.4 to 13.

INSERT 是不同的:

• 插入...从选择...返回id 映射

有多种方法可以避免对同一行进行并发写入操作的竞争条件.(请注意，对不相关行的并发写入操作完全没有问题.)简单、缓慢且确定(但昂贵)的方法是使用 SERIALIZABLE 隔离级别:

There are various ways to avoid race conditions with concurrent write operations on the same rows. (Note that concurrent write operations on unrelated rows are no problem at all.) The simple, slow and sure (but expensive) method is to run the transaction with SERIALIZABLE isolation level:

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

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

But that's probably overkill. And you need to be prepared to repeat the operation in case of a serialization failure.

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

Simpler and faster (and just as reliable with concurrent write load) is an explicit lock on the one row to be updated:

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 条件如何移动到子查询(同样，可以是 anything)，并且只有自联接(在 UNIQUE NOT NULL 上)code> column(s)) 保留在外部查询中.这保证只处理由内部 SELECT 锁定的行.WHERE 条件稍后可能会解析为一组不同的行.

Note how the WHERE condition moved to the subquery (again, can be anything), and only the self-join (on UNIQUE NOT NULL column(s)) remains in the outer query. This guarantees that only rows locked by the inner SELECT are processed. The WHERE conditions might resolve to a different set of rows a moment later.

见:

• 原子更新..在 Postgres 中选择

db<>fiddle 这里
_旧sqlfiddle

这篇关于仅使用 SQL 返回更新前的列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！