Hiroshi Inoue wrote:
> Florian G. Pflug wrote:
>
>> I think there should be a big, fat warning that self-referential
>> updates have highly non-obvious behaviour in read-committed mode,
>> and should be avoided.
>
> It seems pretty difficult for PostgreSQL rule system to avoid such
> kind of updates. I'm suspicious if UPDATABLE VIEWS can be implemented
> using the rule system.

Remember this affects all self-referential joins on an UPDATE (and
DELETE?) not just views. It's just that a rule is more likely to produce
that type of query.

--
Richard Huxton
Archonet Ltd

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

Richard Huxton wrote:
> Hiroshi Inoue wrote:
>> Florian G. Pflug wrote:
>>
>>> I think there should be a big, fat warning that self-referential
>>> updates have highly non-obvious behaviour in read-committed mode,
>>> and should be avoided.
>>
>> It seems pretty difficult for PostgreSQL rule system to avoid such
>> kind of updates. I'm suspicious if UPDATABLE VIEWS can be implemented
>> using the rule system.
>
> Remember this affects all self-referential joins on an UPDATE (and
> DELETE?) not just views. It's just that a rule is more likely to produce
> that type of query.

Is there consensus what the correct behaviour should be for
self-referential updates in read-committed mode? Does the SQL Spec
have anything to say about this?

greetings, Florian Pflug


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Florian G. Pflug wrote:
>
> Is there consensus what the correct behaviour should be for
> self-referential updates in read-committed mode? Does the SQL Spec
> have anything to say about this?

This seems to have gone all quiet. Do we need a TODO to keep a note of
it? Just "correct behaviour for self-referential updates"

Hiroshi originally noted the problem in one of his views here:
http://archives.postgresql.org/pgsql...5/msg00507.php

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Added to TODO:

* Fix self-referential UPDATEs seeing inconsistent row versions in
read-committed mode

http://archives.postgresql.org/pgsql...5/msg00507.php


---------------------------------------------------------------------------

Richard Huxton wrote:
> Florian G. Pflug wrote:
> >
> > Is there consensus what the correct behaviour should be for
> > self-referential updates in read-committed mode? Does the SQL Spec
> > have anything to say about this?
>
> This seems to have gone all quiet. Do we need a TODO to keep a note of
> it? Just "correct behaviour for self-referential updates"
>
> Hiroshi originally noted the problem in one of his views here:
> http://archives.postgresql.org/pgsql...5/msg00507.php
>
> --
> Richard Huxton
> Archonet Ltd
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

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...5/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



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

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...5/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


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

"Simon Riggs" <simon@2ndquadrant.com> writes:
> I'm sorry guys but I don't agree this is a TODO item.
....
> Also, methinks we should have agreed behaviour before we make something
> a TODO item.

There is a whole *lot* of stuff in the TODO list that does not have a
consensus solution yet. You should not imagine that it's gospel.

At the same time, it'd be better if this item were worded more like
"investigate this issue" rather than presupposing a particular
form of answer.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Tom Lane wrote:
> "Simon Riggs" <simon@2ndquadrant.com> writes:
> > I'm sorry guys but I don't agree this is a TODO item.
> ...
> > Also, methinks we should have agreed behaviour before we make something
> > a TODO item.
>
> There is a whole *lot* of stuff in the TODO list that does not have a
> consensus solution yet. You should not imagine that it's gospel.
>
> At the same time, it'd be better if this item were worded more like
> "investigate this issue" rather than presupposing a particular
> form of answer.

OK, new wording:

o Research self-referential UPDATEs that see inconsistent row versions
in read-committed mode

http://archives.postgresql.org/pgsql...5/msg00507.php

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

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

On Fri, 2007-06-01 at 20:06 +0200, Florian G. Pflug wrote:
> 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...5/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.

I've regrettably spent too many years with locking databases, so to me
the behaviour is perfectly logical, and intuitive, because I didn't
expect a lock avoiding strategy to be "free". We have a way of bypassing
locks, but there is no way to bypass the need to think about what the
answer will be if you do chose to bypass them.

This is just a classic database problem. Many books and courses bypass
these issues because they appear to be solved. Every couple of years
people go ballistic when they discover this kind of stuff exists; its
made the papers and SIGMOD too. I don't mean to dismiss everybody's
concerns, but it does seem I have a different perspective on them.

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

Document it better if you will, or add an option that would allow you to
set the default to always use read locks, but there's just nothing
actually wrong with the current behaviour.

BTW, DB2 has just such an option, IIRC, but the equivalent "Repeatable
Read" (doesn't mean same thing as the ANSI phrase) mode is not
recommended for normal use. You'll then run up against the need to
optimise the locking further and end up with "Cursor Stability" mode.
Thats a lot of work for something few people will use in practice.

Read Committed == Show me the data, whether or not its been updated

Bear in mind this has nothing to do with self-referencing joins, its a
problem-of-misunderstanding of any UPDATE or DELETE with a sub-select
that references a table that can be updated concurrently.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Fri, 2007-06-01 at 14:12 -0400, Tom Lane wrote:
> "Simon Riggs" <simon@2ndquadrant.com> writes:
> > I'm sorry guys but I don't agree this is a TODO item.
> ...
> > Also, methinks we should have agreed behaviour before we make something
> > a TODO item.
>
> There is a whole *lot* of stuff in the TODO list that does not have a
> consensus solution yet. You should not imagine that it's gospel.

Well, I don't, though many think it is and some have been
surprised/annoyed to find out that implementing a TODO item doesn't mean
automatic acceptance of the idea, let alone the code (not myself, I
hasten to add).

> At the same time, it'd be better if this item were worded more like
> "investigate this issue" rather than presupposing a particular
> form of answer.

Agreed.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq