Skip site navigation (1) Skip section navigation (2)

Bug #822: Condition on rule is not respected.

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: Bug #822: Condition on rule is not respected.
Date: 2002-11-24 00:56:28
Message-ID: 20021124005628.9AF31475E82@postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
Lus Mota (lhrm(at)iscte(dot)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.

Thanks for any help, Lus 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


Responses

pgsql-bugs by date

Next:From: Luis MotaDate: 2002-11-24 02:53:22
Subject: Re: Bug #822: Condition on rule is not respected.
Previous:From: Rickard AnnellDate: 2002-11-23 15:07:02
Subject: Compile problem postgresql-7.3rc1 and Mac OS X 10.2.2

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group