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

Robert Haas <robertmhaas@gmail.com> writes:
> On Tue, Oct 27, 2009 at 11:22 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> What I am thinking we should do is define that FOR UPDATE happens before
>> ORDER BY or LIMIT normally, but that if the FOR UPDATE is inherited from
>> an outer query level, it happens after the sub-select's ORDER BY or
>> LIMIT. �The first provision fixes the bugs noted in our documentation,
>> and the second one allows people to get back the old behavior if they
>> need it for performance. �This also seems reasonably non-astonishing
>> from a semantic viewpoint.

> When you refer to an "outer query level", is that the same thing as a
> sub-select?  If so, I think I agree that the behavior is
> non-astonishing.

Right, the case would be something like
select * from  (select * from foo order by x limit n) ssfor update of ss;

If you try this in any existing release it will just fail, because the
planner knows that it hasn't got a way to execute FOR UPDATE in a
subquery.
        regards, tom lane