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