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

Re: Do we need a TODO? (was Re: Concurrently updating anupdatable view)

From: "Florian G(dot) Pflug" <fgp(at)phlo(dot)org>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: Bruce Momjian <bruce(at)momjian(dot)us>, Richard Huxton <dev(at)archonet(dot)com>, Hiroshi Inoue <inoue(at)tpf(dot)co(dot)jp>, Heikki Linnakangas <heikki(at)enterprisedb(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Do we need a TODO? (was Re: Concurrently updating anupdatable view)
Date: 2007-06-01 18:06:03
Message-ID: 4660600B.30102@phlo.org (view raw or flat)
Thread:
Lists: pgsql-hackers
Simon Riggs wrote:
> On Mon, 2007-05-28 at 19:56 -0400, Bruce Momjian wrote:
>> Added to TODO:
>>
>> * Fix self-referential UPDATEs seeing inconsistent row versions in
>>   read-committed mode
>>
>>   http://archives.postgresql.org/pgsql-hackers/2007-05/msg00507.php
>>
> 
> I'm sorry guys but I don't agree this is a TODO item.

Maybe the TODO suggested has a too narrow focus, but I think that
that *something* has to be done about this.

> IMHO this follows documented behaviour, even if y'all are shocked.
Yes, but documented != sensible && documented != intuitive &&
documented != logical.

> If you don't want the example cases to fail you can
> - use SERIALIZABLE mode to throw an error if inconsistency is detected
> - use SELECT FOR SHARE to lock the rows in the subselect
> e.g.
> 
> UPDATE foo
> SET pkcol = 'x'
> WHERE pkcol IN 
> (SELECT pkcol
>  FROM foo
>  ....
>  FOR SHARE);
> 
> In the case of concurrent UPDATEs the second UPDATE will normally
> perform the subSELECT then hang waiting to perform the UPDATE. If you
> use FOR SHARE the query will hang on the subSELECT (i.e. slightly
> earlier), which makes the second query return zero rows, as some of you
> were expecting.

Sure, but with a similar argument you could question the whole
update-in-read-committed-mode logic. After all, you wouldn't need
that logic if you always obtained a share lock on the rows to be updated
*before* you started updating them.

> Maybe we need a way of specifying that the non-UPDATE relation should be
> locked FOR SHARE in a self-referencing UPDATE? Though that syntax could
> seems to look pretty weird from here, so I'd say cover this situation in
> a code example and be done.
> 
> Also, methinks we should have agreed behaviour before we make something
> a TODO item. That would help us uncover this type of thing in more
> detail, or at least force TODO to read "investigate whether ...".

Ack. Thats why I initially asked if there was consesus on what the
correct behaviour is.

greetings, Florian Pflug


In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2007-06-01 18:12:55
Subject: Re: Do we need a TODO? (was Re: Concurrently updating anupdatable view)
Previous:From: Bruce MomjianDate: 2007-06-01 17:50:12
Subject: Re: TOAST usage setting

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