Subject: Unexpected results from a query with UNION ALL

From: Andrey <[email-masked]>
Date: Fri, 3 Jan 2025 20:19:21 +0200

Hi everyone,

Recently I got unexpected results from a query that seems to be legit.
The setup is like this:

-- setup
CREATE TABLE parents
(
    id       uuid    not null primary key,
    revision integer not null
);
CREATE TABLE children
(
    id        uuid    not null primary key,
    parent_id uuid    not null references parents
);
INSERT INTO parents (id, revision)
VALUES ('ec422e09-55bb-4465-a990-31f59859959d', 1);
INSERT INTO children (id, parent_id)
VALUES ('5cb82ceb-c5ef-4c59-a02e-f7b610470f8c',
'ec422e09-55bb-4465-a990-31f59859959d');
INSERT INTO children (id, parent_id)
VALUES ('ce5b22b0-c6c4-4c09-826c-7086c53ee9ec',
'ec422e09-55bb-4465-a990-31f59859959d');

The query is:

-- query
SELECT children_union.id AS child_id
FROM parents p
         JOIN (SELECT id, parent_id
                    FROM children
                    UNION ALL
                    SELECT null::uuid, null::uuid
                    WHERE false) children_union ON
children_union.parent_id = p.id
WHERE p.id = 'ec422e09-55bb-4465-a990-31f59859959d'
    FOR UPDATE OF p;

It looks weird, but it's just a simplification of a much bigger query.
The 'SELECT null::uuid, null::uuid WHERE false' part was actually more
meaningful but I substituted it with a query that returns 0 rows after
finding out that it's irrelevant.

If I just run this query I get something that I would expect to get:

-- result 1
               child_id
--------------------------------------
 5cb82ceb-c5ef-4c59-a02e-f7b610470f8c
 ce5b22b0-c6c4-4c09-826c-7086c53ee9ec
(2 rows)

But if I lock the single row in the parents table:

-- concurrent query
BEGIN;
UPDATE parents
SET revision = revision + 1
WHERE id = 'ec422e09-55bb-4465-a990-31f59859959d';

and then run my query again in a separate session, then it's waiting
for the lock to be released. Once I commit the concurrent query and
release the lock, I get this:

-- result 2
               child_id
--------------------------------------
 5cb82ceb-c5ef-4c59-a02e-f7b610470f8c
 5cb82ceb-c5ef-4c59-a02e-f7b610470f8c
(2 rows)

but I would expect to get the same result as previously. Is it a bug
or am I doing something wrong here?

Thank you,
Andrii

---

From: Tom Lane <[email-masked]>
Date: Sat, 04 Jan 2025 13:24:59 -0500

Andrey <[email-masked]> writes:
> ...
> ... Once I commit the concurrent query and

> or am I doing something wrong here?

It's a surprising result for sure, but I believe it's explained by
the algorithm for READ COMMITTED [1], specifically the bit about

    The search condition of the command (the WHERE clause) is
    re-evaluated to see if the updated version of the row still matches the
    search condition.  If so, the second updater proceeds with its operation
    using the updated version of the row.  

Once the tuple lock is released, the join query fetches the new
version of the parents row (with the new revision value, though that's
not actually relevant to the result).  It then effectively re-executes
the join against the UNION construct, and that means it'll always find
the first matching row in "children".  The "updated version of the
row" is taken to mean the entire join row, so it doesn't blink at the
fact that it got a different child output than it had started with.

Another way to look at this is that locking only "p" underspecifies
the query result: there's more than one child row that could join
to the "p" row, and the system doesn't promise that you get a result
from any particular one of them.

If you try to fix it by also locking the UNION result, or by adding
FOR UPDATE to the UNION arm that selects from "children", you get

ERROR:  FOR UPDATE is not allowed with UNION/INTERSECT/EXCEPT

This example makes me feel that we've missed some cases where we
probably ought to throw that error.  Or else work harder on making
the combination be supported --- but it looks tricky to produce
consistent results, and there have been few complaints about this
omission so far.

In the meantime, the most recommendable answer for you is probably
to switch over to using SERIALIZABLE mode.  That'd require adding
application logic to retry after a serialization failure, but it
would produce consistent results even for complex queries.

			regards, tom lane

[1] https://www.postgresql.org/docs/current/transaction-iso.html#XACT-READ-COMMITTED

---

From: Tom Lane <[email-masked]>
Date: Wed, 08 Jan 2025 12:01:36 -0500

I wrote:
>> ... but I would expect to get the same result as previously. Is it a bug

> the algorithm for READ COMMITTED [1], specifically the bit about

Actually, on further thought I believe this really is a bug,
because if you change the UNION ALL to UNION it works fine.
It probably used to work with UNION ALL too, but that was a few
decades ago before we started adding optimizations of UNION ALL :-(

I've been poking at this off and on for the last few days, and I've
found three different things that will need to be changed to make
it work again.  At least one of them looks too invasive to consider
for back-patch.  So don't hold your breath for a proper fix, but
perhaps you could use UNION as a workaround?

			regards, tom lane

---

From: Andrii Novikov <[email-masked]>
Date: Thu, 9 Jan 2025 18:49:38 +0200

Thanks for the update. Actually I was able to rewrite the query to get rid
of the union. But anyway it would be good to have this issue handled
somehow, it's tricky and it was hard to spot and reproduce.