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

Re: Endgame for all those SELECT FOR UPDATE changes: fix plan node order

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Alvaro Herrera <alvherre(at)commandprompt(dot)com>, "pgsql-hackers(at)postgreSQL(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Endgame for all those SELECT FOR UPDATE changes: fix plan node order
Date: 2009-10-26 14:44:38
Message-ID: 603c8f070910260744g5ac2ef9agad9789b715253c8f@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Mon, Oct 26, 2009 at 10:30 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
>> Tom Lane escribió:
>>> Yeah, it could definitely run slower than the existing code --- in
>>> particular the combination of all three (FOR UPDATE ORDER BY LIMIT)
>>> would tend to become a seqscan-and-sort rather than possibly just
>>> reading one end of an index.  However, I quote the old aphorism that
>>> it can be made indefinitely fast if it doesn't have to give the right
>>> answer.  The reason the current behavior is fast is it's giving the
>>> wrong answer :-(
>
>> So this probably merits a warning in the release notes for people to
>> check that their queries continue to run with the performance they
>> expect.
>
> One problem with this is that there isn't any good way for someone to
> get back the old behavior if they want to.  Which might be a perfectly
> reasonable thing, eg if they know that no concurrent update is supposed
> to change the sort-key column.  The obvious thing would be to allow
>
> select * from (select * from foo order by col limit 10) ss for update;
>
> to apply the FOR UPDATE last.  Unfortunately, that's not how it works
> now because the FOR UPDATE will get pushed down into the subquery.
> I was shot down when I proposed a related change, a couple weeks ago
> http://archives.postgresql.org/message-id/7741.1255278907@sss.pgh.pa.us
> but it seems like we might want to reconsider.

"Shot down" might be an overstatement of the somewhat cautious
reaction that proposal.  :-)

Could the desired behavior be obtained using a CTE?

...Robert

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2009-10-26 14:45:33
Subject: Re: Parsing config files in a directory
Previous:From: Robert HaasDate: 2009-10-26 14:41:15
Subject: Re: Parsing config files in a directory

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