Re: Do we need a TODO? (was Re: [HACKERS] Concurrently updating an updatable view)

2007-05-28 Thread Bruce Momjian

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


---

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-hackers/2007-05/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  [EMAIL PROTECTED]  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


Do we need a TODO? (was Re: [HACKERS] Concurrently updating an updatable view)

2007-05-22 Thread Richard Huxton

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-hackers/2007-05/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


Re: [HACKERS] Concurrently updating an updatable view

2007-05-15 Thread Richard Huxton

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


Re: [HACKERS] Concurrently updating an updatable view

2007-05-15 Thread Florian G. Pflug

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 [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly


Re: [HACKERS] Concurrently updating an updatable view

2007-05-14 Thread Heikki Linnakangas
Hiroshi Inoue wrote:
 Concurrently updating an updatable view seems to cause
 an unexpected result. Is it a known issue?

Looks right to me. What did you expect?


-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly


Re: [HACKERS] Concurrently updating an updatable view

2007-05-14 Thread Hiroshi Inoue
Heikki Linnakangas wrote:
 Hiroshi Inoue wrote:
 Concurrently updating an updatable view seems to cause
 an unexpected result. Is it a known issue?
 
 Looks right to me. What did you expect?

Shouldn't the last response
  (session-2)
 UPDATE 1

be
  (seesion-2)
 UPDATE 0
?

regards,
Hiroshi Inoue


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org


Re: [HACKERS] Concurrently updating an updatable view

2007-05-14 Thread Heikki Linnakangas

Hiroshi Inoue wrote:

Heikki Linnakangas wrote:

Hiroshi Inoue wrote:

Concurrently updating an updatable view seems to cause
an unexpected result. Is it a known issue?

Looks right to me. What did you expect?


Shouldn't the last response
  (session-2)
 UPDATE 1

be
  (seesion-2)
 UPDATE 0
?


Ah, I re-read the example and I see what you mean now.

The problem is that the new tuple version is checked only against the 
condition in the update rule, id=OLD.id, but not the condition in the 
original update-claus, dt='a'.


Yeah, that's confusing :(.

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [HACKERS] Concurrently updating an updatable view

2007-05-14 Thread Richard Huxton

Heikki Linnakangas wrote:
The problem is that the new tuple version is checked only against the 
condition in the update rule, id=OLD.id, but not the condition in the 
original update-claus, dt='a'.


Yeah, that's confusing :(.


Bit more than just normal rule confusion I'd say. Try the following two 
statements in parallel (assuming you've just run the previous):


UPDATE test SET dt='c';
UPDATE test SET dt='x' FROM test t2 WHERE test.id=t2.id AND t2.dt='b';

This isn't a problem with the view mechanism - it's a problem with 
re-checking clauses involving subqueries or joins I'd guess.


I'm trying to decide if it's unexpected or just plain wrong, and I think 
I'd have to argue wrong.


--
  Richard Huxton
  Archonet Ltd

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

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


Re: [HACKERS] Concurrently updating an updatable view

2007-05-14 Thread Richard Huxton

Richard Huxton wrote:

Heikki Linnakangas wrote:
The problem is that the new tuple version is checked only against the 
condition in the update rule, id=OLD.id, but not the condition in the 
original update-claus, dt='a'.


Yeah, that's confusing :(.


Bit more than just normal rule confusion I'd say. Try the following two 
statements in parallel (assuming you've just run the previous):


UPDATE test SET dt='c';
UPDATE test SET dt='x' FROM test t2 WHERE test.id=t2.id AND t2.dt='b';

This isn't a problem with the view mechanism - it's a problem with 
re-checking clauses involving subqueries or joins I'd guess.


I'm trying to decide if it's unexpected or just plain wrong, and I think 
I'd have to argue wrong.


Or perhaps I'd not argue that :-/

This is really about MVCC in read committed mode, and the just right 
for simpler cases:

http://www.postgresql.org/docs/8.2/interactive/transaction-iso.html#XACT-READ-COMMITTED

Clearly there needs to be a change to the sentence: Because of the 
above rule, it is possible for an updating command to see an 
inconsistent snapshot: it can see the effects of concurrent updating 
commands that affected the same rows it is trying to update


Not true if there's a subquery/join involved.

--
  Richard Huxton
  Archonet Ltd

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

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


Re: [HACKERS] Concurrently updating an updatable view

2007-05-14 Thread Florian G. Pflug

Richard Huxton wrote:

Richard Huxton wrote:

Heikki Linnakangas wrote:
The problem is that the new tuple version is checked only against the 
condition in the update rule, id=OLD.id, but not the condition in the 
original update-claus, dt='a'.


Yeah, that's confusing :(.


Bit more than just normal rule confusion I'd say. Try the following 
two statements in parallel (assuming you've just run the previous):


UPDATE test SET dt='c';
UPDATE test SET dt='x' FROM test t2 WHERE test.id=t2.id AND t2.dt='b';

This isn't a problem with the view mechanism - it's a problem with 
re-checking clauses involving subqueries or joins I'd guess.


I'm trying to decide if it's unexpected or just plain wrong, and I 
think I'd have to argue wrong.


Or perhaps I'd not argue that :-/

Well, src/backend/executor/README agrees with you that it's wrong..

Note a fundamental bogosity of this approach: if the relation containing
the original tuple is being used in a self-join, the other instance(s) of
the relation will be treated as still containing the original tuple, whereas
logical consistency would demand that the modified tuple appear in them too.
But we'd have to actually substitute the modified tuple for the original,
while still returning all the rest of the relation, to ensure consistent
answers.  Implementing this correctly is a task for future work.

This is really about MVCC in read committed mode, and the just right 
for simpler cases:
http://www.postgresql.org/docs/8.2/interactive/transaction-iso.html#XACT-READ-COMMITTED 

Clearly there needs to be a change to the sentence: Because of the 
above rule, it is possible for an updating command to see an 
inconsistent snapshot: it can see the effects of concurrent updating 
commands that affected the same rows it is trying to update


Not true if there's a subquery/join involved.

If the cited part of the README is correct, then all joins and subqueries
are fine, except if they refer to the table being updated.

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.

greetings, Florian Pflug



---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [HACKERS] Concurrently updating an updatable view

2007-05-14 Thread Hiroshi Inoue

Richard Huxton wrote:

Heikki Linnakangas wrote:
The problem is that the new tuple version is checked only against the 
condition in the update rule, id=OLD.id, but not the condition in the 
original update-claus, dt='a'.


Yeah, that's confusing :(.


Bit more than just normal rule confusion I'd say. Try the following two 
statements in parallel (assuming you've just run the previous):


UPDATE test SET dt='c';
UPDATE test SET dt='x' FROM test t2 WHERE test.id=t2.id AND t2.dt='b';

This isn't a problem with the view mechanism - it's a problem with 
re-checking clauses involving subqueries or joins I'd guess.


I don't understand the PostgreSQL specific *FROM* clause correctly.
Currently the relations in the *FROM* clause seem to be read only
and UPDATE operations seem to acquire no tuple level lock on them.

regards,
Hiroshi Inoue

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org


Re: [HACKERS] Concurrently updating an updatable view

2007-05-14 Thread Richard Huxton

Hiroshi Inoue wrote:

Richard Huxton wrote:

Heikki Linnakangas wrote:
The problem is that the new tuple version is checked only against the 
condition in the update rule, id=OLD.id, but not the condition in the 
original update-claus, dt='a'.


Yeah, that's confusing :(.


Bit more than just normal rule confusion I'd say. Try the following 
two statements in parallel (assuming you've just run the previous):


UPDATE test SET dt='c';
UPDATE test SET dt='x' FROM test t2 WHERE test.id=t2.id AND t2.dt='b';

This isn't a problem with the view mechanism - it's a problem with 
re-checking clauses involving subqueries or joins I'd guess.


I don't understand the PostgreSQL specific *FROM* clause correctly.
Currently the relations in the *FROM* clause seem to be read only
and UPDATE operations seem to acquire no tuple level lock on them.


Yes, the above query is equivalent to:
UPDATE test SET dt='x' WHERE id IN (SELECT id FROM test WHERE dt='b');

There are some expressions more naturally expressed as a set of where 
conditions though, and I think the FROM is just to provide a place to 
name them.


The FROM form seemed to be the more natural match to the plan your view 
was generating - I'm not sure which the plan transformation process 
produces.


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


Re: [HACKERS] Concurrently updating an updatable view

2007-05-14 Thread Hiroshi Inoue

Florian G. Pflug wrote:

Richard Huxton wrote:

Richard Huxton wrote:

Heikki Linnakangas wrote:


snip

Bit more than just normal rule confusion I'd say. Try the following 
two statements in parallel (assuming you've just run the previous):


UPDATE test SET dt='c';
UPDATE test SET dt='x' FROM test t2 WHERE test.id=t2.id AND t2.dt='b';

This isn't a problem with the view mechanism - it's a problem with 
re-checking clauses involving subqueries or joins I'd guess.


I'm trying to decide if it's unexpected or just plain wrong, and I 
think I'd have to argue wrong.


Or perhaps I'd not argue that :-/



Well, src/backend/executor/README agrees with you that it's wrong..


Thanks for the pointer.


Note a fundamental bogosity of this approach: if the relation containing
the original tuple is being used in a self-join, the other instance(s) of
the relation will be treated as still containing the original tuple, 
whereas
logical consistency would demand that the modified tuple appear in them 
too.


Is the above description about UPDATE or DELETE operations?
AFAIR SELECT FOR UPDATE operations avoided the incosistency from the
 first for joins though I'm not sure about subqueries.
Or I may be misunderstanding something?


But we'd have to actually substitute the modified tuple for the original,
while still returning all the rest of the relation, to ensure consistent
answers.  Implementing this correctly is a task for future work.


snip


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.

regards,
Hiroshi Inoue


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