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

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

SET pkcol = 'x'
WHERE pkcol IN (SELECT pkcol
 FROM foo

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Reply via email to