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

On Thu, Jul 30, 2009 at 1:40 AM, Steve Caligo<Steve(dot)Caligo(at)ctie(dot)etat(dot)lu> wrote:
> 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

Um, I didn't write that email. That was Tom Lane.

>> 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.

Sure. I mean, I understand your frustration here, but this started
out as a documentation complaint. I think the current behavior is
documented reasonably OK; the problem is that the behavior is pretty
weird. I'd be all in favor of fixing it, but I'm not sure what would
be involved in that or what the trade-offs would be. I suspect if it
were easy it would have been done long ago; you're not the first
person to complain about it.

...Robert