Re: Do we need a TODO? (was Re: [HACKERS] Concurrently updating an updatable view)
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)
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
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
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
Florian G. Pflug wrote: Richard Huxton wrote: Richard Huxton wrote: Heikki Linnakangas wrote: 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." 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
Re: [HACKERS] Concurrently updating an updatable view
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
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
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
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
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
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
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
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
[HACKERS] Concurrently updating an updatable view
Hi developers, Concurrently updating an updatable view seems to cause an unexpected result. Is it a known issue? => select version(); version --- PostgreSQL 8.2.1 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.4 (mingw special) (1 row) => create table test (id int4 primary key, dt text) NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test" CREATE TABLE => insert into test values (1, 'a'); INSERT 0 1 => create view test_v as select * from test; CREATE VIEW => create rule test_upd as on update to test_v do instead update test set dt=NEW.dt where id=OLD.id; CREATE RULE The result of concurrently running the same query update test_v set dt='b' where dt='a' is as follows. session-1 => begin; BEGIN session-1 => update test_v set dt='b' where dt='a'; UPDATE 1 session-2 => begin; BEGIN session-2 => update test_v set dt='b' where dt='a'; (blocked) session-1 => commit; COMMIT (session-2) UPDATE 1 *Explain* shows the following plan for the query here. => explain update test_v set dt='b' where dt='a'; QUERY PLAN - Hash Join (cost=24.57..50.59 rows=6 width=10) Hash Cond: (public.test.id = public.test.id) -> Seq Scan on test (cost=0.00..21.60 rows=1160 width=10) -> Hash (cost=24.50..24.50 rows=6 width=4) -> Seq Scan on test (cost=0.00..24.50 rows=6 width=4) Filter: (dt = 'a'::text) (6 rows) regards, Hiroshi Inoue ---(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