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

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Greg Stark <gsstark(at)mit(dot)edu>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Endgame for all those SELECT FOR UPDATE changes: fix plan node order
Date: 2009-10-26 18:01:57
Message-ID: 13436.1256580117@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Greg Stark <gsstark(at)mit(dot)edu> writes:
> On Sun, Oct 25, 2009 at 7:34 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> All that we have to do to fix the first one is to put the LockRows node
>> below the Limit node instead of above it. The solution for the second
>> one is to also put LockRows underneath the Sort node, and to regard its
>> output as unsorted so that a Sort node will certainly be generated.
>> (This in turn implies that we should prefer the cheapest-total plan
>> for the rest of the query.)

> I'm not following how this would work. Would it mean that every record
> would end up being locked?

In the case of LIMIT, no, because LIMIT doesn't fetch any more rows than
it needs from its input node.  In the case of ORDER BY, yes,
potentially.  So we might conceivably decide we should fix the first
issue and not the second.  However, I'd prefer to have a solution
whereby the query does what it appears to mean and you have to write
something more complicated if you want performance over correctness.

			regards, tom lane

In response to

pgsql-hackers by date

Next:From: Greg StarkDate: 2009-10-26 18:15:06
Subject: Re: Parsing config files in a directory
Previous:From: Greg StarkDate: 2009-10-26 17:58:14
Subject: Re: Endgame for all those SELECT FOR UPDATE changes: fix plan node order

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