Re: BUG #15715: UPDATE using "in (subquery for update limit 1)" does not respect the limit in subquery

Lars Vonk <lars(dot)vonk(at)gmail(dot)com> writes:
> Thanks for the explanation. Our hotfix was to replace the IN with a = so
> the query fails and the transaction is rolled back when more than one is
> returned from the sub-SELECT.

And have you actually seen any such failures? If you have, then there
is certainly something going wrong here. The LIMIT should never allow
more than one row to be returned by the sub-SELECT, period, full stop.

The issue here is that the sub-SELECT is, in principle, executed afresh
for every row of the outer query, and you do not have a guarantee that
each such execution returns the *same* single row. Ordinary query
execution would provide such a guarantee, but you're using FOR UPDATE
on a table that's being modified concurrently (including by this query
itself), and therefore the guarantee disappears.

You might think that since the sub-SELECT is uncorrelated with the outer
query, there's no need to execute it more than once ... but that's an
optimization, not part of the guaranteed semantics. Without seeing an
EXPLAIN for this query on your system, we can't know whether it's being
done like that (though the fact that you're complaining suggests that
it isn't).

Interestingly, it's quite likely that your "hotfix" made the problem
go away, because PG generally *does* optimize uncorrelated sub-SELECTs
to be executed only once if they return scalar results. That doesn't
happen for IN subqueries though, as those are treated as joins.

Either way, a WITH is probably preferable because that does get you
into a place where we guarantee single-evaluation semantics.

regards, tom lane