仅使用SQL返回UPDATE之前的列值-PostgreSQL版本 [英] Return pre-UPDATE Column Values Using SQL Only - PostgreSQL Version
问题描述
我有一个相关问题，但这是我的另一难题.
我想从已更新的行中获取列的旧值-无需使用触发器(也不要使用存储过程，也不要使用任何其他额外的非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;
如果我可以在子查询的末尾执行对my_table进行更新"，那将是神圣的(并解决我的其他问题).但是，这是行不通的:不能有它和"GROUP BY"(这对于弄清trans_nbr的COUNT是必需的).然后，我可以使用那些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 的一个建议，该建议是针对我的其他问题(上面链接)的评论. (我比必要的要具体.[在这种情况下.])
请参阅我的其他问题，以正确实现并发甚至是非阻塞版本；此查询仅显示如何从更新中获取旧值和新值，而忽略不良/错误的并发位.
问题
手册说明:
可选的
RETURNING子句使UPDATE进行计算并返回 基于实际更新的每一行的值.任何使用 表的列和/或FROM中提到的其他表的列，可以 被计算.该表的列的新(更新后)值是 使用.RETURNING列表的语法与SELECT的输出列表.
强调我的.在RETURNING子句中无法访问旧行.您可以在触发器中执行此操作，也可以在UPDATE之前使用单独的SELECT ，以@Flimzy和@wildplasser的注释将其包装在事务中，或者以@MattDiPasquale发布的方式包装在CTE中. /p>
解决方案
但是，如果您在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
我使用8.4至9.6的PostgreSQL版本对此进行了测试.
与INSERT不同:
处理并发写入负载
有多种方法可以避免在并发写入操作中出现争用情况.一种简单，缓慢且确定(但昂贵)的方法是以SERIALIZABLE隔离级别运行事务.
BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE ..;
COMMIT;
但是这可能太过分了.如果遇到序列化失败，则需要准备重复操作.
在要更新的 one 行上，显式锁定了更简单，更快速的方法(并发写入负载同样可靠):
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;
此相关问题下的更多说明，示例和链接:
I have a related question, but this is another part of MY puzzle.
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).
The query I have is like this:
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;
If I could do "FOR UPDATE ON my_table" at the end of the subquery, that'd be devine (and fix my other question/problem). But, that won't work: can't have this AND a "GROUP BY" (which is necessary for figuring out the COUNT of trans_nbr's). Then I could just take those trans_nbr's and do a query first to get the (soon-to-be-) former processing_by values.
I've tried doing like:
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
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 ...
UPDATE: [WIPES EGG OFF FACE] Okay, so I had a typo in the non-generic code of the above that I wrote "doesn't work"; it does... thanks to Erwin Brandstetter, below, who stated it would, I re-did it (after a night's sleep, refreshed eyes, and a banana for bfast). Since it took me so long/hard 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
The COUNT(*) is per a suggestion from Flimzy in a comment on my other (linked above) question. (I was more specific than necessary. [In this instance.])
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.
Problem
The optional
RETURNINGclause causesUPDATEto 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 inFROM, can be computed. The new (post-update) values of the table's columns are used. The syntax of theRETURNINGlist is identical to that of the output list ofSELECT.
Emphasis mine. There is no way to access the old row in a RETURNING clause. You can do that in a trigger or with a separate SELECT before the UPDATE, wrapped in a transaction as @Flimzy and @wildplasser commented, or wrapped in a CTE as @MattDiPasquale posted.
Solution
However, what you are trying to achieve works perfectly fine if you join in 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;
Returns:
old_id | old_name | tbl_id | name
--------+----------+--------+---------
3 | Old Guy | 23 | New Guy
I tested this with PostgreSQL versions from 8.4 to 9.6.
It's different for INSERT:
Dealing with concurrent write load
There are several ways to avoid race conditions with concurrent write operations. 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'd need to be prepared to repeat the operation if you get 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;
More explanation, examples and links under this related question:
这篇关于仅使用SQL返回UPDATE之前的列值-PostgreSQL版本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
