Re: Is FOR UPDATE an optimization fence? - Mailing list pgsql-hackers

From Tom Lane
Subject Re: Is FOR UPDATE an optimization fence?
Date
Msg-id 15044.1255292199@sss.pgh.pa.us
Whole thread Raw
In response to Re: Is FOR UPDATE an optimization fence?  (Robert Haas <robertmhaas@gmail.com>)
List pgsql-hackers
Robert Haas <robertmhaas@gmail.com> writes:
> On Sun, Oct 11, 2009 at 12:35 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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.

> It seems like this is somewhat related to the question of embedding an
> {INSERT|UPDATE|DELETE}...RETURNING in some arbitrary part of a query
> versus only allowing it in a WITH clause.  The argument for only
> allowing it in a WITH clause is that there is otherwise no guarantee
> that it is evaluated in its entirety but just once.  ISTM we could
> contrariwise give it the handling you're proposing here: allow it
> anywhere in the query, but make it act as an optimization fence.

I don't think this analogy to update queries is a very solid one.
SELECT FOR UPDATE has a couple of properties that make it much less
critical for it to have evaluate-exactly-once semantics:

* Locking the same row twice is a no-op.

* If you don't run the query to completion and thus don't lock all
the rows, so what?  Arguably, the semantics of the query are to
lock only the rows actually read/returned.

> For that reason, I think I'd be inclined to make it act as an
> optimization fence if used as a top-level CTE, but otherwise flatten
> it, so that the handling is consistent with what we've proposed to do
> elsewhere.

Well, the point of the WITH RETURNING restriction is that just because a
subselect isn't flattened doesn't mean you have evaluate-exactly-once
semantics for the subselect.  The upper query is still free to execute
the subselect multiple times, partially, or not at all.  A CTE, on the
other hand, is more than just an optimization fence --- we've decided to
give it evaluate-exactly-once semantics.  So SELECT FOR UPDATE at the
top of a CTE is really irrelevant to this discussion; it's going to be
treated the same no matter what.
        regards, tom lane


pgsql-hackers by date:

Previous
From: Andrew Chernow
Date:
Subject: Re: man pages
Next
From: Roger Leigh
Date:
Subject: Re: Unicode UTF-8 table formatting for psql text output