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