Обсуждение: Bug #822: Condition on rule is not respected.
Luís Mota (lhrm@iscte.pt) reports a bug with a severity of 2
The lower the number the more severe it is.
Short Description
Condition on rule is not respected.
Long Description
I think I spoted a bug while developing a database. To better understand if this was really a bug, I developed a small
examplethat I include further down.
The question is that I want to write a rule that inserts a record in a second table (table B) each time a record is
insertedon a first table A, and vice-versa.
To do this, I wrote two insert rules. To avoid a dead-lock, I created a boolean field which serves as guard to the
insertion.Unfortunatly, I think that the condition I set up in the insert rules is not respected.
Thanks for any help, Luís Mota
Sample Code
CREATE TABLE num_int (id serial, valor int, dummy bool);
CREATE TABLE num_real (id serial, valor real, dummy bool);
CREATE RULE int_insert AS ON INSERT TO num_int WHERE NEW.dummy IS NULL
DO
INSERT INTO num_real (valor,dummy) SELECT NEW.valor,true;
CREATE RULE real_insert AS ON INSERT TO num_real WHERE NEW.dummy IS NULL
DO
INSERT INTO num_int (valor,dummy) SELECT NEW.valor,true;
When I try to insert a record, e.g:
INSERT INTO num_int (valor,dummy) values (7);
I get the following error:
ERROR: query rewritten 10 times, may contain cycles
Also, if I try the following setup:
CREATE TABLE num_int (id serial, valor int, dummy bool);
CREATE TABLE num_real (id serial, valor real, dummy bool);
CREATE RULE int_insert AS ON INSERT TO num_int WHERE NEW.dummy IS NULL
DO
INSERT INTO num_real (valor,dummy) SELECT NEW.valor,true;
CREATE RULE real_insert AS ON INSERT TO num_real WHERE NEW.dummy IS NULL
DO
SELECT NEW.valor, NEW.dummy
INTO tanga;
After an insertion in num_int, which is successful, if I try the following query:
SELECT * from tanga;
valor
-------
(0 rows)
This means that this table was created, even it has no record. My conclusion is that the real_insert rule was fired.
No file was uploaded with this report
On Sat, 23 Nov 2002 pgsql-bugs@postgresql.org wrote: > Lu=EDs Mota (lhrm@iscte.pt) reports a bug with a severity of 2 > The lower the number the more severe it is. > > Short Description > Condition on rule is not respected. > > Long Description > I think I spoted a bug while developing a database. To better > understand if this was really a bug, I developed a small example that > I include further down. > > The question is that I want to write a rule that inserts a record in a > second table (table B) each time a record is inserted on a first table > A, and vice-versa. > > To do this, I wrote two insert rules. To avoid a dead-lock, I created > a boolean field which serves as guard to the insertion. Unfortunatly, > I think that the condition I set up in the insert rules is not > respected. The rule where condition does not stop the rule from being expanded. It changes the rows that are acted upon, but not whether or not the rule action occurs. If you're looking to do something like that, you probably actually need a trigger.
I forgot to add details of instalation: I'm running linux kernel 2.2.22 and postgres PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96 lmota pgsql-bugs@postgresql.org wrote: > Luís Mota (lhrm@iscte.pt) reports a bug with a severity of 2 > The lower the number the more severe it is. > > Short Description > Condition on rule is not respected. > > Long Description > I think I spoted a bug while developing a database. To better understand if this was really a bug, I developed a smallexample that I include further down. > > The question is that I want to write a rule that inserts a record in a second table (table B) each time a record is insertedon a first table A, and vice-versa. > > To do this, I wrote two insert rules. To avoid a dead-lock, I created a boolean field which serves as guard to the insertion.Unfortunatly, I think that the condition I set up in the insert rules is not respected. > > Thanks for any help, Luís Mota > > Sample Code > CREATE TABLE num_int (id serial, valor int, dummy bool); > CREATE TABLE num_real (id serial, valor real, dummy bool); > > CREATE RULE int_insert AS ON INSERT TO num_int WHERE NEW.dummy IS NULL > DO > INSERT INTO num_real (valor,dummy) SELECT NEW.valor,true; > > CREATE RULE real_insert AS ON INSERT TO num_real WHERE NEW.dummy IS NULL > DO > INSERT INTO num_int (valor,dummy) SELECT NEW.valor,true; > > When I try to insert a record, e.g: > INSERT INTO num_int (valor,dummy) values (7); > > I get the following error: > ERROR: query rewritten 10 times, may contain cycles > > Also, if I try the following setup: > CREATE TABLE num_int (id serial, valor int, dummy bool); > CREATE TABLE num_real (id serial, valor real, dummy bool); > > CREATE RULE int_insert AS ON INSERT TO num_int WHERE NEW.dummy IS NULL > DO > INSERT INTO num_real (valor,dummy) SELECT NEW.valor,true; > > CREATE RULE real_insert AS ON INSERT TO num_real WHERE NEW.dummy IS NULL > DO > SELECT NEW.valor, NEW.dummy > INTO tanga; > > After an insertion in num_int, which is successful, if I try the following query: > SELECT * from tanga; > valor > ------- > (0 rows) > > This means that this table was created, even it has no record. My conclusion is that the real_insert rule was fired. > > > > > No file was uploaded with this report > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster