Return pre-UPDATE column values in PostgreSQL without using triggers, functions or other "magic"
Posted
by
Python Larry
on Stack Overflow
See other posts from Stack Overflow
or by Python Larry
Published on 2011-10-27T22:34:05Z
Indexed on
2011/11/14
9:51 UTC
Read the original article
Hit count: 399
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 instance
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 instance
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 viewable outside of 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 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 instance
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.])
© Stack Overflow or respective owner