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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Tue, Oct 27, 2009 at 11:22 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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) ss
for 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