Skip site navigation (1) Skip section navigation (2)

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

From: "Steve Caligo" <Steve(dot)Caligo(at)ctie(dot)etat(dot)lu>
To: "Robert Haas" <robertmhaas(at)gmail(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #4925: "select ... for update" doesn't affect rows from sub-query
Date: 2009-07-30 05:40:01
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs
> On Thu, Jul 16, 2009 at 12:34 PM, Steve Caligo<steve(dot)caligo(at)ctie(dot)etat(dot)lu>
> wrote:

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

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

Allowing FOR UPDATE in sub-queries has been rejected in this same thread,
you've also mentioned it your previous reply.

> This is pretty weird behavior, and I am among those who think it
> sucks.  But it is documented.

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

In response to


pgsql-bugs by date

Next:From: Heiko FolkertsDate: 2009-07-30 06:59:32
Subject: BUG #4955: ECPG produces incomplete code
Previous:From: Robert HaasDate: 2009-07-30 02:54:36
Subject: Re: BUG #4925: "select ... for update" doesn't affect rows from sub-query

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group