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

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