Re: SELECT FOR UPDATE and LIMIT 1 behave oddly

On Fri, 2004-10-15 at 14:22, Tom Lane wrote:
> 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).

Ah, I see. I had tried some trivial queries to determine if we supported
FOR UPDATE in subqueries, such as:

select * from def, abc, (select * from abc for update) x;

But of course a more careful examination shows that we don't (I'd guess
the planner is transforming the above subquery into a join). I think it
would make sense to reject the above query for the sake of consistency.
It seems that would be easy to do by rejecting FOR UPDATE of subqueries
in the analysis phase, rather than going to the trouble of explicitly
allowing them (see analyze.c circa line 2753) and then rejecting them in
the planner.

BTW, FOR UPDATE's interaction with LIMIT is not undocumented -- we
actually document the opposite of what we implement. From the SELECT ref
page:

FOR UPDATE may appear before LIMIT for compatibility with
PostgreSQL versions before 7.3. It effectively executes after
LIMIT, however, and so that is the recommended place to write
it.

> The fundamental question about the above construct is: exactly which
> rows did it lock?

I'm not sure I understand. The rows the query locks should be the result
set of the subquery. Also, I think it only makes sense to allow FOR
UPDATE in FROM-clause subselects, and it would also be reasonable to
disable some subquery optimizations (e.g. subquery pullup) when FOR
UPDATE is specified.

> What if some of the locked rows didn't get returned to the client?

In the case of SELECT ... FOR UPDATE LIMIT x, exactly the same condition
applies: some number of locked rows will not be returned to the client.

-Neil