Re: Unexpected results from a query with UNION ALL

Andrey <adnyre(at)gmail(dot)com> writes:
> Recently I got unexpected results from a query that seems to be legit.
> ...
> ... 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?

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