8

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?

0

3 Answers 3

10

Postgres uses a multiversion model (Multiversion Concurrency Control, MVCC).

In default READ COMMITTED isolation level, each separate query effectively sees a snapshot of the database as of the instant the query begins to run. Subsequent queries - even within the same transaction - can see a different snapshot if concurrent transactions are committed in between. (Plus what has been done in the same transaction so far.)

However, as far as CTEs are concerned, all sub-statements in WITH are executed concurrently with the outer statement, they effectively see the same snapshot of the database. All of it is considered a single query for this purpose.

So, no, you don't need an explicit lock to stay consistent.

Encapsulating the logic in a function may be convenient for a number of reasons, but that has no effect whatsoever on concurrency. Aside: a CTE with a volatile function is never inlined. See:

A SELECT does not lock queried rows. Postgres allows concurrent UPDATES. But UPDATE locks target rows. Concurrent transactions trying to write also, have to wait until the locking transaction has finished.

If you want to forbid writes to rows (columns) that have only been selected from while your UPDATE is in progress, you may want to take locks anyway (or use a stricter isolation level). Maybe FOR UPDATE locks, or maybe a weaker lock. That depends on details and requirements you are expressly withholding / not giving in your question.

Also (though you did not ask for that), if multiple concurrent transactions may be writing to overlapping rows (more than one at a time), be sure to adhere to the same, consistent order of rows to avoid deadlocks. That typically requires ORDER BY before locking.

If columns contributing to that ORDER BY might be updated by concurrent transactions, you also need to add NOWAIT to be be sure. Locking happens after ORDER BY. If a concurrent transaction updates a row in between, Postgres waits till that transaction is finished. If committed, the row may now sort differently, and the lock would happen out of order, re-introducing the possibility for deadlocks. So NOWAIT is the only way to be sure in READ COMMITTED isolation level. Or use a stricter isolation level. REPEATABLE READ or SERIALIZABLE would raise a serialization failure in that case anyway.

3
  • Just on your last comment, would you ensure consistent ordering by adding ORDER BY and FOR NO KEY UPDATE to the CTE? In a similar vein, I've been wondering if using a CTE is necessary when updating using FROM (VALUES to prevent deadlocking dba.stackexchange.com/questions/322808/…
    – ChrisJ
    Feb 4 at 2:46
  • @ChrisJ: Adding NO KEY is just a weaker lock, that may make sense if your UPDATEs do not alter key columns. (Else Postgres will escalate to a FOR UPDATE lock eventually anyway). ORDER BY plus NOWAIT are essential to be absolutely safe from deadlocks. And yes, a CTE is typically necessary. See: dba.stackexchange.com/a/69497/3684 Feb 4 at 5:10
  • Thanks. I'm confused by the need for NOWAIT and have a follow up question dba.stackexchange.com/questions/323040/…
    – ChrisJ
    Feb 4 at 10:09
1

If you want to prevent concurrent statements from modifying the rows that the CTE selects before they get updated, you need to use SELECT ... FOR NO KEY UPDATE in the CTE.

4
  • Hm, this differs from the accepted answer. Let's imagine the above query in session A & another UPDATE in session B. Are you pointing to the fact that while OP's sel+upd queries are executed concurrently in A, if Postgres requires a seq scan, the lock will be applied gradually as it progresses through the dataset to update rows; thus creating a gap where an UPDATE in session B could update a row that session A has not yet gotten to? Then, if B's UPDATE still keeps the row returnable by the WHERE filter exp, session A would then 'incorrectly' overwrite the row - is that right? Oct 2, 2022 at 19:25
  • 1
    Locks are taken row by row, but the problem is something else. It is true that the SELECT and the UPDATE share the same snapshot, but if the UPDATE is blocked, it will re-evaluate the row and may find it changed, that is, different from the version in the snapshot it shares with the SELECT. See this article for an example. It shows what I mean for SELECT ... FOR UPDATE, but the principle is the same. Typically Erwin's answers are well researched and better than mine, but he missed something here Oct 3, 2022 at 5:41
  • I see - what’s the point of the FOR UPDATE then, if you’re using REPEATABLE READ? Either way, if something has been updated, you’ll get a serialisation failure. Using REPEATABLE READ would give you optimistic locking. Oct 3, 2022 at 8:54
  • The SELECT ... FOR UPDATE would lock the rows, so that there can be no concurrent modifications and consequently no serialization errors. But sure, using REPEATABLE READ and not using FOR UPDATE would also solve the problem. Oct 3, 2022 at 8:58
0

Building on what a_horse_with_no_name said:

I would put such a condition into a (SQL) function. Another alternative to locking (if you expect this to occur rarely) would be to use the serializable isolation level and re-run the UPDATE if an error occurs.

Put the addition logic into a function, and then call that function each time you went to set a new value. This will help you in two ways.

  1. This allow you to avoid duplicating the addition logic each time you use it.
  2. This makes for a very simple update statement that can get in quick, lock just a few rows, and get out.

Something like this should work.

CREATE FUNCTION fn_my_addition(my_value int)
RETURNS INT
LANGUAGE SQL
AS
$$
  select CASE my_value1 > 100 THEN 50 ELSE 10 END;
$$;

UPDATE my_table
SET my_value1 = my_value1 + fn_my_addition(my_value1),
    my_value2 = my_value2 + fn_my_addition(my_value2)
WHERE my_id = $1;
3
  • What if fn_my_addition takes several seconds? In a CTE solution, it's executed only once. In your solution it's at least called multiple times, I don't know if Postgres does some caching here.
    – cis
    Mar 14, 2022 at 13:13
  • @cis The function should be very fast. It's not reading any data from the database. It's simply taking an input parameter and doing a CASE on it. No different than if you did the same CASE on the data, inline in the update. Mar 14, 2022 at 13:22
  • 2
    As mentioned several times, CASE my_value1 > 100 THEN 50 ELSE 10 END is just a placeholder for a MUCH more complicated expression which I cannot and don't want to show - because it's not my question.
    – cis
    Mar 14, 2022 at 13:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.