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

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