Re: BUG #4925: "select ... for update" doesn't affect rows from sub-query

> On Thu, Jul 16, 2009 at 12:34 PM, Steve Caligo<steve(dot)caligo(at)ctie(dot)etat(dot)lu>
> wrote:

>> 2) FIRST TRY, USING "UPDATE WHERE ID = ( SELECT )"
>> The goal is to have two clients set their unique ID to a
>> single/different
>> row from the table. First, using "limit" in a slightly different way:

> This is pretty clearly NOT the situation described in the
> documentation. There is no FOR UPDATE or FOR SHARE anywhere in this
> query. You could argue that we should treat an UPDATE statement as
> applying an implicit FOR UPDATE to any subqueries found therein, but
> that has nothing to do with whether the current behavior matches the
> documentation; it's a discussion of whether the current behavior is
> good or bad.

Should there really be a difference between doing an UPDATE or just
requesting rows to be locked for a later modification (update or deletion,
whatever) through SELECT ... FOR UPDATE?

I'm aware that there's no FOR UPDATE in that query, as its direct use is
discouraged in the documentation (SELECT ... LIMIT ... FOR UPDATE) and
reported as "odd behavior" previously as well:
http://archives.postgresql.org/pgsql-bugs/2004-10/msg00138.php

Yes, I'm considering this behavior bad, as I'd expect the UPDATE statement
to lock either the whole table (if there's no other way) or only the rows
returned by the sub-SELECT, but as far as I can see, such row-level
locking can't be achieved in PostgreSQL but through the use of
serialization.

Allowing FOR UPDATE in sub-queries has been rejected in this same thread,
you've also mentioned it your previous reply.
http://archives.postgresql.org/pgsql-bugs/2004-10/msg00150.php

> This is pretty weird behavior, and I am among those who think it
> sucks. But it is documented.
>
> http://www.postgresql.org/docs/8.4/static/transaction-iso.html#MVCC-SERIALIZABILITY

This behavior may be consistent with the transaction isolation levels
PostgreSQL provides (read committed/serializable, while one would need
true repeatable read in this case), it is a huge drawback when working
with the database, as one has to think about potential collateral damage
with every single SELECT...FOR UPDATE / UPDATE query one writes,
especially the complex ones.

> An interesting fact is that if you stick another "for update" into the
> subquery here, the command will fail utterly, with the following error
> message:
>
> ERROR: SELECT FOR UPDATE/SHARE is not allowed with aggregate functions

See my second link above, it works as designed.

The document states that the FOR UPDATE is applied to the view or
sub-query, which I assume means: "applied to a SELECT containing a
sub-query or view". As such, I'm expecting a single statement to be an
atomic operation, i.e. always works on the same data snapshot, independent
of the transaction isolation level.

This atomicity has to be enforced through a full table lock (which is
often the easiest to implement, but also the most expensive
efficiency-wise) or serialization.

Side note: a cross-database test showed that Oracle, DB2 nor MySQL (with
InnoDB storage engine) run the query as true repeatable read, whether one
makes use of the "skip locked rows" (when available) or not. One always
ends up with two different rows being updated.

Best regards,

Steve Caligo