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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-hackers by date

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