3

If I make a query using CTEs that looks like that:

WITH cte_a AS (
    SELECT a.id, a.something
    FROM a
    WHERE a.something IS NOT NULL 
),
-- [...] some other CTEs
cte_d AS (
    SELECT 
        cte_a.id,
        cte_a.something
    FROM cte_a
    JOIN 
        -- something
    JOIN
        -- something
    WHERE 
        -- something
    ORDER BY cte_a.id ASC
    FOR UPDATE  -- Here, will the `FOR UPDATE` locks `a` rows?
),
-- rest of the query, which will update `a.something`.

Will the lock for update apply on the rows of the table a, or will the lock apply on a materialized table generated by cte_a?

If the lock applies to the materialized table, would the following solve the problem?

WITH cte_a AS NOT MATERIALIZED (
-- Rest of the query

I'm using PostgreSQL v14.

If it changes anything, I'm mostly interested in the behavior with the default READ COMMITTED isolation level.

4
  • 1
    You can delete the comment - I put it at the end. Any additional information should go into the question itself - you use the comments to notify the questioner about updates to the question. Notify me when you receive a good answer.
    – Vérace
    Commented Jun 26 at 22:00
  • Please clarify: cte_a is not even listed in the FROM clause of cte_d. Show a minimal, complete, working query to make it easier to test and discuss. Ideally, with table definitions to go along with it. And always declare your version of Postgres. Commented Jun 27 at 1:21
  • @ErwinBrandstetter yes, sorry, I forgot to replace the value in the FROM clause. I also added the version of PostgreSQL, but I guess that if the behavior change depending on the versions, ideally it would better to specify that in the answer to help people not having the same version than me. I'll edit the description with a complete working query later, but I've made the other changes now.
    – Adrian B.
    Commented Jun 27 at 1:38
  • 1
    Always declare the Postgres version in use. Even if you are interested in other versions, too (which you might declare in your question). It's often hard enough to give a clear answer for the given version. Covering the version history is quite a bit harder, yet. While Postgres does not change behavior between versions lightly, it has to be done sometimes (in which case that's documented in the release notes). For the case at hand: like when MATERIALIZED CTEs where added with Postgres 12. Commented Jun 27 at 3:22

1 Answer 1

5

The foundation of my answer is provided in the manual:

If specific tables are named in a locking clause, then only rows coming from those tables are locked; any other tables used in the SELECT are simply read as usual. A locking clause without a table list affects all tables used in the statement. [...]
However, these clauses do not apply to WITH queries referenced by the primary query. If you want row locking to occur within a WITH query, specify a locking clause within the WITH query.

So:

  • To only lock rows of specific table(s) in the FROM clause, list their names in the locking clause. Else, all qualifying rows of all tables in the FROM list are locked.

    • But you can only list actual table names, not CTE names, so you cannot target CTEs to begin with (which would not make sense either way, because the locking clause does not propagate to CTEs, as discussed below.)
  • Without table restriction in the locking clause, all selected rows of all tables in the FROM clause are locked.

  • A locking clause in the primary query is not propagated to CTEs.

This does not completely clarify your complex case, yet:

  1. SELECT from table a in cte_a.
  2. Then your cte_d has cte_a in its FROM clause, plus some other tables, plus more filters.
    • There you add the FOR UPDATE locking clause without naming specific tables.
  3. Finally, UPDATE a in the primary query.

From experience and some additional quick superficial tests:

  • The locking clause does not apply to WITH queries in the FROM list in any case. Not just in the primary query, in other CTEs as well.

  • What's more, adding a locking clause prevents "inlining" of the same CTE (automatic optimization, or explicitly demanded with NOT MATERIALIZED in Postgres 12+). So all rows that qualify in this particular CTE are locked, additional filters in other ("later") CTEs or the primary query do not narrow down the set.

Conclusion

To lock rows of table a, you must add the locking clause in a query where that table is listed in the FROM clause. Your query could look like this:

WITH cte_a AS (
   SELECT a.id, a.something
   FROM   a
   WHERE  a.something IS NOT NULL 
   ORDER  BY a.id  -- possibly useful to lock rows in particular order (?)
   FOR    UPDATE   -- HERE !!!
   )
--   ... other CTEs
, cte_d AS (
   SELECT a.id, a.something
   FROM   cte_a a
   JOIN   some_table
-- WHERE  something
-- ORDER BY a.id  -- still needed (?)
-- FOR UPDATE     -- still needed for other tables (?)
   )
UPDATE a
SET    something = null
FROM   cte_d d
WHERE  a.id = d.id;

But, this may lock more rows than necessary. Try to apply all filters before the locking clause. (In the same SELECT counts.)
I would most probably rewrite the whole query.

How to test?

Minimal setup:

-- drop table a;
CREATE TABLE a (id int PRIMARY KEY, something int);
INSERT INTO a VALUES
  (1, 1)
, (2, NULL)
, (3, 3) -- not in table b !
;

-- drop table b;
CREATE TABLE b (id int PRIMARY KEY, something int);
INSERT INTO b VALUES
  (1, 1)
, (2, NULL)
;

Then start a transaction in one session and leave it open:

BEGIN;

-- EXPLAIN  -- also interesting!
WITH cte_a AS (  -- row 1 & 3
-- WITH cte_a AS NOT MATERIALIZED (  -- ?
   SELECT *
   FROM   a
   WHERE  something IS NOT NULL
-- FOR    UPDATE  -- Here?
   )
-- more CTEs
, cte_d AS (  -- only row 1
   SELECT a.id, a.something
   FROM   cte_a a
   JOIN   b USING (id)  -- eliminates row 3
-- ORDER  BY a.id
-- FOR    UPDATE  -- Here?
   )
TABLE cte_d;

Then try in another session (separate connection / separate query window):

BEGIN;
-- UPDATE b SET something = 0 WHERE id = 1;
UPDATE a SET something = 0 WHERE id = 1;  -- 3?

This will either go through, or be stalled by a row lock (in which case you abort).

In each session run ROLLBACK; before the next test.

Vary to see effects of each constellation.

Your Answer

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

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