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

On Tue, Oct 27, 2009 at 1:06 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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.

That's a pretty odd construction.

In some sense I don't like the proposed behavior, because it's
imaginable that someone would use this syntax without realizing that
it could produce wrong answers. My own gut instinct would be to
always push down the FOR UPDATE as being a clearer way to convey what
was meant - but we've already established that not everyone's gut
instincts agree with mine, and if someone does write this, they might
easily fail to understand the risk that it poses.

I'm not sure what to do about it, though. Not giving people ANY way
to recover the old behavior is a little troubling.

...Robert