Thread

1. Problems with rules

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2000-11-14T09:20:55Z

   Carsten Zerbst (zerbst@tu-harburg.de) reports a bug with a severity of 2
   The lower the number the more severe it is.
   
   Short Description
   Problems with rules
   
   Long Description
   The given code is destilled from a reallife DB. All attempts to define a rule which automatically updates the column aenderungsdatum from table mitglieder on updates failed. The result was every time 
   
   psql:ruleerror:17: ERROR:  query rewritten 10 times, may contain cycles
   
   
   Sample Code
   DROP TABLE "mitglieder";
   CREATE TABLE "mitglieder" (
           "mitid" int4 PRIMARY KEY,
           "name" character varying(100) NOT NULL,
           "vorname" character varying(100),
           "aenderungsdatum" date DEFAULT 'now'
   );
   
   INSERT INTO "mitglieder" (mitid, name, vorname) 
            VALUES (42,'Surname','firstname');
   
   DROP RULE "test";
   CREATE RULE "test" AS
     ON UPDATE TO "mitglieder" WHERE OLD.name <> NEW.name
     DO UPDATE "mitglieder" SET "aenderungsdatum" = 'now' ;
     
   UPDATE "mitglieder" SET name='Nachname' where mitid=42;
   
   
   No file was uploaded with this report
   
   
   

2. Re: Problems with rules

   Gena Gurchonok <gena@rt.mipt.ru> — 2000-11-14T10:13:34Z

   Hello
   
   > Long Description
   > The given code is destilled from a reallife DB. All attempts to define a rule which automatically updates the column aenderungsdatum from table mitglieder on updates failed. The result was every
   > time 
   
   > psql:ruleerror:17: ERROR:  query rewritten 10 times, may contain cycles
   This error raises because your rule executed recursively. (You are
   using UPDATE statment in rule for UPDATE).
   
   > Sample Code
   > DROP TABLE "mitglieder";
   > CREATE TABLE "mitglieder" (
   >         "mitid" int4 PRIMARY KEY,
   >         "name" character varying(100) NOT NULL,
   >         "vorname" character varying(100),
   >         "aenderungsdatum" date DEFAULT 'now'
                                           ^^^^^^^^^^^
   Suggestion: If you want date of insertion to be written here use
   CURRENT_DATE insterad of 'now'
   > );
   
   > INSERT INTO "mitglieder" (mitid, name, vorname) 
   >          VALUES (42,'Surname','firstname');
   
   > DROP RULE "test";
   > CREATE RULE "test" AS
   >   ON UPDATE TO "mitglieder" WHERE OLD.name <> NEW.name
   >   DO UPDATE "mitglieder" SET "aenderungsdatum" = 'now' ;
   Suggestion: If you want date of insertion to be written here use
   CURRENT_DATE insterad of 'now'
     
   > UPDATE "mitglieder" SET name='Nachname' where mitid=42;
   
   I had the same problem some months ago. Tom Lane advised me to use
   triggers instead of rules for this case. It's faster and more
   convinient.
   
   But if you want to use RULES you have to create table t_mitglieder
   and view mitglieder as select * from t_mitglieder,
   and you have to create rules for insert, update and delete from
   mitglieder that will do appropriate operations on t_mitglieder.
   
   
   Gena