Re: Trx issues: SELECT FOR UPDATE LIMIT

James Mancz <james(at)mancz(dot)com> writes:
> client 2 can carry on processing now,
> and its SELECT * FROM work WHERE alloc IS NULL LIMIT 1 FOR UPDATE;
> **returns no rows** despite the fact the second row matches.

This is annoying but I do not think it is readily fixable, because the
LIMIT filter acts before the FOR UPDATE processing. When FOR UPDATE
rejects the first row because it's been changed by the previous updater,
that's all she wrote --- the LIMIT's not gonna produce more rows.

I have some old notes suggesting that maybe FOR UPDATE could be turned
into a plan node that could be stuck underneath the LIMIT node, but
I'm not sure that's workable. It'd be a nontrivial change certainly,
and arguably unintuitive (the effects of SQL clauses normally act
left-to-right, but you'd be forcing LIMIT to occur after FOR UPDATE).

Those other databases presumably implement LIMIT in a different place
... I bet they don't handle LIMIT in sub-selects though, which is the
advantage to treating it as a plan step.

regards, tom lane