Re: Bug #822: Condition on rule is not respected.

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