Re: SELECT FOR UPDATE and LIMIT 1 behave oddly

Neil Conway <neilc(at)samurai(dot)com> writes:
> I agree backward compat is a concern, but it seems pretty clear to me
> that this is not the optimal behavior. If there are any people who
> actually need the old behavior, they can nest the FOR UPDATE in a
> FROM-clause subselect:

> SELECT * FROM foo FOR UPDATE LIMIT 5; -- used to lock the whole table

> SELECT * FROM (SELECT * FROM foo FOR UPDATE) x LIMIT 5; -- will always
> do so

Allowing FOR UPDATE in sub-selects opens a can of worms that I do not
think we'll be able to re-can (at least not without the proverbial
larger size of can). The fundamental question about the above construct
is: exactly which rows did it lock? And what's your proof that that set
is what it *should* have locked? What if some of the locked rows didn't
get returned to the client? Even if LIMIT happens to work in a
convenient way, allowing FOR UPDATE inside a subselect would expose us
to a lot of other cases (joins and aggregates for instance) that I don't
believe we can guarantee pleasant behavior for.

My recollection is that the original FOR UPDATE and LIMIT behaviors were
both implemented at the top level in execMain.c, and at that time LIMIT
effectively executed after FOR UPDATE. We later pushed LIMIT down to
become a plan node, which was a good idea in every respect except that
it changed the order of application of these two behaviors. I'm afraid
of the semantic consequences of pushing down FOR UPDATE into a plan node
however. Maybe it can be made to work but I think a lot of very careful
thought will need to go into it.

regards, tom lane