Re: Is FOR UPDATE an optimization fence?

On Mon, Oct 12, 2009 at 1:59 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Sun, Oct 11, 2009 at 12:35 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Of course the downside of changing it is that queries that worked fine
>>> before might work differently (and much slower) now; first because not
>>> flattening the sub-select might lead to a worse plan, and second because
>>> locking more rows takes more time.
>>>
>>> The alternative would be to let it continue to flatten such sub-selects
>>> when possible, and to tell anyone who doesn't want that to stick in
>>> OFFSET 0 as an optimization fence.
>>>
>>> It's an entirely trivial code change either way.  I'm inclined to think
>>> that we should prevent flattening, on the grounds of least astonishment.
>
>> The other comment I have is that I *expect* subqueries to be pulled
>> up.  So my own personal POLA would not be violated by locking only the
>> rows with a join partner; in fact it would be more likely to be
>> violated by the reverse behavior.  I might not be typical, though.  My
>> experience is that not pulling up subqueries tends to have disastrous
>> effects on performance, so I'm somewhat biased against creating more
>> situations where that will happen.
>
> On further reflection I've decided to stick with the old behavior on
> this point, at least for the time being.  I'm concerned about subtly
> altering the behavior of existing queries, and I've also realized that
> changing it isn't as much of a one-liner as I thought.  The current
> behavior of the parser and rewriter really depends on the assumption
> that there's not much of a semantic difference between FOR UPDATE
> markings at different syntactic levels, because they will happily push
> down a FOR UPDATE *into* a sub-select.  That is,

For the record, I wasn't sure if I agreed with your original point that:

select * from a join (select * from b for update) ss on a.x = ss.y;

should necessarily be expected to lock all rows from b (does the
standard insist on it?). The select inside the join clause describes
'how you get' the records, not that they should be all gotten. Along
the same vein, does:
create view foo_update as select * from foo for update;

necessarily lock all the rows from foo for any query against the view?
(It doesn't and IMO shouldn't). ISTM that the particular rows being
locked in your first example are not really defined very well.

merlin