In Postgres 13, I have a table which gets updated frequently. However, the update query is rather complicated and uses the same values multiple times. So, using a CTE seems quite a logical thing to do.
A simplified example looks like this:
WITH my_cte AS ( SELECT my_id, CASE WHEN my_value1 > 100 THEN 50 ELSE 10 END AS my_addition FROM my_table WHERE my_id = $1 ) UPDATE my_table SET my_value1 = my_table.my_value1 + my_cte.my_addition, my_value2 = my_table.my_value2 + my_cte.my_addition FROM my_cte WHERE my_table.my_id = my_cte.my_id
Now I'm wondering: What would happen if between the
SELECT in the CTE and the
UPDATE, the table is updated by another query, changing
my_value1 on thus, the calculation of
my_addition were to become outdated and wrong when the
UPDATE happens. Can such a situation occur? Or does Postgres set an implicit lock automatically?
If Postgres does no magic here and I need to take care of it myself: Would it be sufficient to do
FOR UPDATE in the
SELECT of the CTE?
Sorry if I did not make myself clear here: It's not that I want to "see" those concurrent modifications, I want to prevent them i.e. once the calculation the
SELECT is done, no other queries might modify that very row till the
UPDATE is done.
In real life, what I mocked here by
CASE WHEN my_value1 > 100 THEN 50 ELSE 10 END is about 20 lines long and I need it at about 5 places in the
UPDATE. Since I'm a big fan of "Do not repeat yourself", I think a CTE is the way to go. Or is there a better way to avoid copy & pasting in an
UPDATE without a CTE?