Skip site navigation (1) Skip section navigation (2)

Re: SELECT FOR UPDATE and LIMIT 1 behave oddly

From: Neil Conway <neilc(at)samurai(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Josh Berkus <josh(at)agliodbs(dot)com>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: SELECT FOR UPDATE and LIMIT 1 behave oddly
Date: 2004-10-15 05:21:02
Message-ID: 1097817662.29932.208.camel@localhost.localdomain (view raw or flat)
Thread:
Lists: pgsql-bugs
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



In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2004-10-15 05:30:16
Subject: Re: SELECT FOR UPDATE and LIMIT 1 behave oddly
Previous:From: Tom LaneDate: 2004-10-15 04:47:49
Subject: Re: 'configure' bug on Mac OS X 10.3.5

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group