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

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

From: "Simon Riggs" <simon(at)2ndquadrant(dot)com>
To: "Bruce Momjian" <bruce(at)momjian(dot)us>
Cc: "Richard Huxton" <dev(at)archonet(dot)com>,"Florian G(dot) Pflug" <fgp(at)phlo(dot)org>,"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 16:34:00
Message-ID: 1180715640.26297.199.camel@silverbirch.site (view raw or flat)
Thread:
Lists: pgsql-hackers
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.

IMHO this follows documented behaviour, even if y'all are shocked.

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.

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

-- 
  Simon Riggs             
  EnterpriseDB   http://www.enterprisedb.com



In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2007-06-01 16:38:09
Subject: Re: TOAST usage setting
Previous:From: Alvaro HerreraDate: 2007-06-01 15:57:36
Subject: Re: Autovacuum versus rolled-back transactions

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