Обсуждение: CREATE RULE ON UPDATE/DELETE
Can a rule see the where statement in a query which it has been triggered by? or is it simply ignored?? what happens? i.e. CREATE TABLE foo (id INTEGER PRIMARY KEY,name TEXT ); CREATE VIEW bar AS SELECT * FROM foo; -- Great view? CREATE RULE bar_update AS ON UPDATE TO bar DO INSTEAD UPDATE foo SET id = NEW.id, name = NEW.name WHERE OLD.id = id; Now if I do a: UPDATE bar SET id = id + 10, WHERE id > 10; What really happens? Does the update first select from bar, and pick out which rows to do the update on, and then do the update on these rowsor what? I tried it, and I got an answer I cannot explain, first it works, then it doesn't: envisity=# CREATE TABLE foo ( envisity(# id INTEGER PRIMARY KEY, envisity(# name TEXT envisity(# ); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'foo_pkey' for tabl e 'foo' CREATE envisity=# envisity=# CREATE VIEW bar AS SELECT * FROM foo; -- Great view? CREATE envisity=# envisity=# CREATE RULE bar_update AS ON UPDATE TO bar DO INSTEAD UPDATE foo SET foo.id = NEW.id, foo.name = NEW.name WHERE OLD.id = foo.id; ERROR: parser: parse error at or near "." envisity=# CREATE RULE bar_update AS ON UPDATE TO bar DO INSTEAD UPDATE foo SET id = NEW.id, name = NEW.name WHERE OLD.id = id; CREATE envisity=# INSERT INTO foo (1, 't'); ERROR: parser: parse error at or near "1" envisity=# INSERT INTO foo VALUES(1, 't'); INSERT 57054 1 envisity=# INSERT INTO foo VALUES(2, 'tr'); INSERT 57055 1 envisity=# INSERT INTO foo VALUES(12, 'tg'); INSERT 57056 1 envisity=# INSERT INTO foo VALUES(15, 'tgh'); INSERT 57057 1 envisity=# INSERT INTO foo VALUES(14, 'th'); INSERT 57058 1 envisity=# UPDATE bar SET id = id + 10 > envisity=# UPDATE bar SET id = id + 10 where id > 10; UPDATE 3 -- Here it works envisity=# select * from bar;id | name ----+------ 1 | t 2 | tr22 | tg24 | th25 | tgh (5 rows) envisity=# #CREATE VIEW bar AS SELECT * FROM foo; -- Great view? ERROR: parser: parse error at or near "#" envisity=# DROP VIEW bar; DROP envisity=# CREATE VIEW bar AS SELECT id * 2 as id, name FROM foo; -- Great view ? CREATE envisity=# CREATE RULE bar_update AS ON UPDATE TO bar DO INSTEAD UPDATE foo SET id = NEW.id, name = NEW.name WHERE OLD.id = id; CREATE envisity=# UPDATE bar SET id = id + 10 where id > 10; UPDATE 0 envisity=# select * from bar;id | name ----+------ 2 | t 4 | tr44 | tg48 | th50 | tgh (5 rows) envisity=# UPDATE bar SET id = id + 10 where id > 10; UPDATE 0 envisity=# select * from foo;id | name ----+------ 1 | t 2 | tr22 | tg24 | th25 | tgh (5 rows) envisity=# UPDATE bar SET id = id + 10 where id > 10; UPDATE 0 -- Here it doesn't work. Aasmund Midttun Godal aasmund@godal.com - http://www.godal.com/ +47 40 45 20 46
On Sat, 20 Oct 2001, Aasmund Midttun Godal wrote: > Can a rule see the where statement in a query which it has been > triggered by? or is it simply ignored?? what happens? > Looking over your question, I wanted to clarify the problem a bit, so: (cleaned up example a bit from Aasmund) -- set up tables drop view normal; drop view dbl; drop table raw; CREATE TABLE raw (id INT PRIMARY KEY, name TEXT ); INSERT INTO raw VALUES(1, 'a'); INSERT INTO raw VALUES(2, 'b'); INSERT INTO raw VALUES(12, 'c'); INSERT INTO raw VALUES(15, 'd'); INSERT INTO raw VALUES(14, 'e'); -- set up two views: "normal", a simple view, -- and "dbl", which shows id * 2 -- create basic rules to allow update to both views CREATE VIEW normal AS SELECT * FROM raw; CREATE RULE normal_update AS ON UPDATE TO normal DO INSTEAD UPDATE raw SET id = NEW.id, name = NEW.name WHERE OLD.id = id; CREATE VIEW dbl AS SELECT id * 2 as id, name FROM raw; CREATE RULE dbl_update AS ON UPDATE TO dbl DO INSTEAD UPDATE raw SET id = NEW.id, name = NEW.name WHERE OLD.id = id; -- now test this UPDATE normal SET id = id + 10 where id > 10; -- works fine UPDATE dbl SET id = id + 10 where id > 10; -- above shows UPDATE 0 -- eventhough there are ids > 10 UPDATE dbl SET id = id + 10; -- UPDATE 1; shows table SELECT * FROM dbl; -- inconsistencies: two "a"s SELECT * FROM raw; The issue is that there are no IDs over 10 that have another ID that is exactly their value, so the first update to "dbl" does nothing. The second time, w/o the ID>10 restriction, it finds 1(a), and double that, 2(b), and adds 10; getting confused about which record to edit. Is this the best way to interpret this? Is this a bug? -- Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton Independent Knowledge Management Consultant
Yes, I agree perfectly... I never thought of that! I would really like it if some more info was added to the docs regardinginfo on rules and triggers. The section on update rules is quite good, but some more would never hurt. One pointin the trigger vs rules section which at least to me is very important is the simple fact that you cannot have a triggeron a select... Ok I understand why - but it took some time... Thank you for answering my questions! regards, Aasmund. On Sun, 21 Oct 2001 12:47:41 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Joel Burton <joel@joelburton.com> writes: > > > Surely you'd need something like > > CREATE RULE dbl_update AS ON UPDATE TO dbl DO INSTEAD UPDATE raw SET > id = NEW.id / 2, name = NEW.name WHERE OLD.id = id * 2; > > (untested...) > > regards, tom lane Aasmund Midttun Godal aasmund@godal.com - http://www.godal.com/ +47 40 45 20 46
Yes, I agree perfectly... I never thought of that! I would really like it if some more info was added to the docs regardinginfo on rules and triggers. The section on update rules is quite good, but some more would never hurt. One pointin the trigger vs rules section which at least to me is very important is the simple fact that you cannot have a triggeron a select... Ok I understand why - but it took some time... Thank you for answering my questions! regards, Aasmund. On Sun, 21 Oct 2001 12:47:41 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Joel Burton <joel@joelburton.com> writes: > > > Surely you'd need something like > > CREATE RULE dbl_update AS ON UPDATE TO dbl DO INSTEAD UPDATE raw SET > id = NEW.id / 2, name = NEW.name WHERE OLD.id = id * 2; > > (untested...) > > regards, tom lane Aasmund Midttun Godal aasmund@godal.com - http://www.godal.com/ +47 40 45 20 46
> Don't think so. I think the rule doesn't make any sense. > NEW.id and OLD.id are probably dbl values, so saying OLD.id=id (where id > is raw.id since that's the update table) isn't correct. It probably > should be OLD.id=id*2 (which seems to work for me, btw) It's editing > a different row than the one that's being selected. I forgot to mention in this that I needed to made an additional change in the rule to make the ids come out correct at the end :(. The update set id=NEW.id should be id=NEW.id/2 of course... Otherwise the +10 becomes a +20.
Joel Burton <joel@joelburton.com> writes:
> CREATE VIEW dbl AS SELECT id * 2 as id, name FROM raw;
> CREATE RULE dbl_update AS ON UPDATE TO dbl DO INSTEAD UPDATE raw SET
> id = NEW.id, name = NEW.name WHERE OLD.id = id;
Surely you'd need something like
CREATE RULE dbl_update AS ON UPDATE TO dbl DO INSTEAD UPDATE raw SET
id = NEW.id / 2, name = NEW.name WHERE OLD.id = id * 2;
(untested...)
regards, tom lane
On Sat, 20 Oct 2001, Joel Burton wrote: > On Sat, 20 Oct 2001, Aasmund Midttun Godal wrote: > > > Can a rule see the where statement in a query which it has been > > triggered by? or is it simply ignored?? what happens? > > > > Looking over your question, I wanted to clarify the problem a bit, so: > (cleaned up example a bit from Aasmund) > drop view normal; > drop view dbl; > drop table raw; > > CREATE TABLE raw (id INT PRIMARY KEY, name TEXT ); > INSERT INTO raw VALUES(1, 'a'); > INSERT INTO raw VALUES(2, 'b'); > INSERT INTO raw VALUES(12, 'c'); > INSERT INTO raw VALUES(15, 'd'); > INSERT INTO raw VALUES(14, 'e'); > > > -- set up two views: "normal", a simple view, > -- and "dbl", which shows id * 2 > > -- create basic rules to allow update to both views > > CREATE VIEW normal AS SELECT * FROM raw; > > CREATE RULE normal_update AS ON UPDATE TO normal DO INSTEAD UPDATE raw SET > id = NEW.id, name = NEW.name WHERE OLD.id = id; > > CREATE VIEW dbl AS SELECT id * 2 as id, name FROM raw; > > CREATE RULE dbl_update AS ON UPDATE TO dbl DO INSTEAD UPDATE raw SET > id = NEW.id, name = NEW.name WHERE OLD.id = id; > The issue is that there are no IDs over 10 that have another ID that is > exactly their value, so the first update to "dbl" does nothing. > > The second time, w/o the ID>10 restriction, it finds 1(a), and double > that, 2(b), and adds 10; getting confused about which record to edit. > > Is this the best way to interpret this? Is this a bug? Don't think so. I think the rule doesn't make any sense. NEW.id and OLD.id are probably dbl values, so saying OLD.id=id (where id is raw.id since that's the update table) isn't correct. It probably should be OLD.id=id*2 (which seems to work for me, btw) It's editing a different row than the one that's being selected.
I have added the following text to the CREATE TRIGGER manual page to address this issue. It often confuses people so it is good to point out: <para> <command>SELECT</command> does not modify any rows so you can not create <command>SELECT</command> triggers. </para> --------------------------------------------------------------------------- > Yes, I agree perfectly... I never thought of that! I would really like it if some more info was added to the docs regardinginfo on rules and triggers. The section on update rules is quite good, but some more would never hurt. One pointin the trigger vs rules section which at least to me is very important is the simple fact that you cannot have a triggeron a select... Ok I understand why - but it took some time... > > Thank you for answering my questions! > > regards, > > Aasmund. > On Sun, 21 Oct 2001 12:47:41 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Joel Burton <joel@joelburton.com> writes: > > > > > > Surely you'd need something like > > > > CREATE RULE dbl_update AS ON UPDATE TO dbl DO INSTEAD UPDATE raw SET > > id = NEW.id / 2, name = NEW.name WHERE OLD.id = id * 2; > > > > (untested...) > > > > regards, tom lane > > Aasmund Midttun Godal > > aasmund@godal.com - http://www.godal.com/ > +47 40 45 20 46 > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026