仅使用 SQL 返回更新前的列值 - PostgreSQL 版本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7923237/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Return pre-UPDATE Column Values Using SQL Only - PostgreSQL Version
提问by pythonlarry
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).
我想从更新的行中获取列的 OLD VALUE - 不使用触发器（也不是存储过程，也不是任何其他额外的非 SQL/查询实体）。
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.
如果我可以在子查询的末尾执行“FOR UPDATE ON my_table”，那就太好了（并解决了我的其他问题/问题）。但是，那是行不通的：不能有这个和“GROUP BY”（这是计算 trans_nbr 的 COUNT 所必需的）。然后我可以只使用那些 trans_nbr 并首先进行查询以获取（即将成为）以前的 processing_by 值。
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_tableis not visible outside the join; the RETURNINGclause is blind to it.
但这行不通；old_my_table在连接外不可见；该RETURNING条款对此视而不见。
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 meso long/hard to find this sort of solution, perhaps my embarrassment is worth it? At least this is on SO for posterity now... :>
更新：[擦掉脸上的鸡蛋] 好的，所以我在上面写的非通用代码中有一个错字“不起作用”；它确实......感谢下面的Erwin Brandstetter，他表示会这样做，我重新做了（经过一夜的睡眠，眼睛焕然一新，早餐吃一根香蕉）。既然我花了这么长时间/很难找到这种解决方案，也许我的尴尬值得吗？至少这对后代来说是这样的......：>
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 Flimzyin a comment on my other (linked above) question. (I was more specific than necessary. [In this instance.])
该COUNT（*）是从每一个建议Flimzy在我的其他（以上链接）的问题中留言。（我比必要的更具体。[在这种情况下。]）
Please see my other questionfor 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.
请参阅我的其他问题以正确实现并发甚至非阻塞版本；此查询仅显示如何从更新中获取旧值和新值，忽略错误/错误的并发位。
回答by Erwin Brandstetter
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.
可选
RETURNING子句导致UPDATE根据实际更新的每一行计算和返回值。FROM可以计算使用表的列和/或 中提到的其他表的列的任何表达式。使用表列的新（更新后）值。RETURNING列表的语法与 的输出列表的语法相同SELECT。
Emphasis mine. There is no way to access the old row in a RETURNINGclause. You can do that in a trigger or with a separate SELECTbeforethe UPDATE, wrapped in a transaction as @Flimzy and @wildplasser commented, or wrapped in a CTE as @MattDiPasquale posted.
强调我的。无法访问RETURNING子句中的旧行。您可以在触发器中或SELECT在 之前单独执行此操作UPDATE，如@Flimzy 和@wildplasser 所评论的那样包装在事务中，或者如@MattDiPasquale 发布的那样包装在CTE 中。
Solution
解决方案
However, what you are trying to achieve works perfectly fineif you join in another instance of the table in the FROMclause:
但是，如果您在子句中加入表的另一个实例，则您想要实现的效果非常好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;
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.
我用 8.4 到 9.6 的 PostgreSQL 版本对此进行了测试。
It's different for INSERT:
它是不同的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 SERIALIZABLEisolation level.
有几种方法可以避免并发写操作的竞争条件。简单、缓慢和确定（但昂贵）的方法是运行具有SERIALIZABLE隔离级别的事务。
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 onerow 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:
此相关问题下的更多解释、示例和链接：
回答by ma11hew28
You can use a SELECTsubquery.
您可以使用SELECT子查询。
Example: Updatea user's email RETURNINGthe old value.
示例：将用户的电子邮件更新为RETURNING旧值。
RETURNINGSubqueryUPDATE users SET email = '[email protected]' WHERE id = 1 RETURNING (SELECT email FROM users WHERE id = 1);PostgreSQL WITH Query (Common Table Expressions)
WITH u AS ( SELECT email FROM users WHERE id = 1 ) UPDATE users SET email = '[email protected]' WHERE id = 1 RETURNING (SELECT email FROM u);This has worked several times on my local database without fail, but I'm not sure if the
SELECTinWITHis guaranteed to consistently execute before theUPDATEsince "the sub-statements in WITH are executed concurrently with each other and with the main query."
RETURNING子查询UPDATE users SET email = '[email protected]' WHERE id = 1 RETURNING (SELECT email FROM users WHERE id = 1);WITH u AS ( SELECT email FROM users WHERE id = 1 ) UPDATE users SET email = '[email protected]' WHERE id = 1 RETURNING (SELECT email FROM u);这已经在我的本地数据库上运行了多次，没有失败，但我不确定
SELECTinWITH是否保证在UPDATE“WITH 中的子语句彼此并发执行并与主查询同时执行”之前始终执行。
回答by Erwin Brandstetter
The CTE variant as proposed by @MattDiPasqualeshould work too.
With the comfortable means of a CTE I would be more explicit, though:
@MattDiPasquale 提出的 CTE 变体也应该有效。
不过，使用 CTE 的舒适方式，我会更明确：
WITH sel AS (
SELECT tbl_id, name FROM tbl WHERE tbl_id = 3 -- assuming unique tbl_id
)
, upd AS (
UPDATE tbl SET name = 'New Guy' WHERE tbl_id = 3
RETURNING tbl_id, name
)
SELECT s.tbl_id AS old_id, s.name As old_name
, u.tbl_id, u.name
FROM sel s, upd u;
Without testing I claim this works: SELECTand UPDATEsee the same snapshot of the database. The SELECTis bound to return the old values (even if you place the CTE after the CTE with the UPDATE), while the UPDATEreturns the new values by definition. Voilá.
未经测试，我声称这有效：SELECT并UPDATE查看数据库的相同快照。TheSELECT必然返回旧值（即使您将 CTE 放在 CTE 之后并带有UPDATE），而UPDATE根据定义返回新值。瞧。
But it will be slower than my first answer.
但它会比我的第一个答案慢。
回答by Jasen
when faced with this dilemma I added junk columns to the table and then I copy the old values into the junk columns (which I then return) when I update the record. this bloats the table a bit but avoids the need for joins.
面对这种困境时，我将垃圾列添加到表中，然后在更新记录时将旧值复制到垃圾列中（然后返回）。这使表有点膨胀，但避免了连接的需要。
