How to write a Postgres SELECT FOR UPDATE when using the EXCEPT set operator?

Question

In Postgres (11, if it matters), I need to do a SELECT FOR UPDATE to obtain a collection of rows that I'll subsequently be doing some alterations on, and which I don't want anyone outside my transaction messing with while I do those alterations.

However, the set of rows I want to lock is actually defined by a set-difference, i.e.,

SELECT <columns> FROM table1 t1 JOIN table2 t2 ON ... WHERE ...
EXCEPT
SELECT <columns> FROM table1 t1 JOIN table3 t3 ON ... WHERE ...

I want the result-set of this set-difference to determine the set of rows that get locked; that is, those rows that are selected by the second SELECT should ideally not get locked.

But I'm not quite sure where to put the FOR UPDATE clause to achieve this. It seems like putting the FOR UPDATE immediately after either of the SELECT lines above would not give me what I want. And in fact I suspect that I can't legally put it after the first of those SELECT lines (i.e., just before the EXCEPT).

One idea that occurred to me was to parenthesize the second SELECT (the one that's the subject of the EXCEPT), so that the FOR UPDATE won't be interpreted as part of that second SELECT:

SELECT <columns> FROM table1 t1 JOIN table2 t2 ON ... WHERE ...
EXCEPT
(SELECT <columns> FROM table1 t1 JOIN table3 t3 ON ... WHERE ...)
FOR UPDATE

But I'm not sure that that gives me what I want either, even if it turns out to be syntactically acceptable.

It's possible that if I had an idea of the shape of the parse tree for a (Postgres) select statement, I could easily figure this out myself; but as it is, I'm a bit lost right now.

Laurenz Albe Laurenz Albe · Accepted Answer · 2020-12-19T02:12:05

You cannot use FOR UPDATE together with UNION, INTERSECT or EXCEPT, because this could cause ambiguities in the general case.

I can think of two approaches:

Use EXISTS and NOT EXISTS:

SELECT ... FROM table1
WHERE EXISTS (SELECT 1 FROM table2 ...
              WHERE table2.x = table1.x AND ...)
  AND NOT EXISTS (SELECT 1 FROM table3 ...
                  WHERE table3.y = table1.y AND ...)
FOR UPDATE OF table1;

Use a subquery:

SELECT ... FROM table1
WHERE id IN (SELECT t1.id
             FROM table1 t1 JOIN table2 t2 ON ...
             WHERE ...
             EXCEPT
             SELECT t1.id
             FROM table1 t1 JOIN table3 t3 ON ...
             WHERE ...)
FOR UPDATE OF table1;

How to write a Postgres SELECT FOR UPDATE when using the EXCEPT set operator?

1 Answers