strange behavior when creting rules

From: Tony Tomov <ttomov(at)abs(dot)bg>
To: pgsql-bugs(at)postgresql(dot)org
Subject: strange behavior when creting rules
Date: 2001-10-16 16:04:52
Message-ID: 3BCC5AA3.AD2E15CF@abs.bg
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

I do not know if this is a bug, but for me this is a strange behavior.
I will describe the following process.

CREATE TABLE "test1" (
"id" SERIAL,
"sum" numeric ,
"name" char (20) ,
PRIMARY KEY ("id"), UNIQUE ("id"));

CREATE TABLE "test2" (
"id1" SERIAL,
"sum1" numeric ,
"name2" char (20) ,
PRIMARY KEY ("id"), UNIQUE ("id"));

ALTER TABLE "test2" ADD "id" SERIAL ;
ALTER TABLE test2 ADD CONSTRAINT fk_id FOREIGN KEY ("id") REFERENCES
"test1"("id") ON DELETE CASCADE;

then
CREATE RULE insert_test2 AS ON INSERT TO test1 DO INSERT INTO test2
(sum1, name2, id, id1) VALUES (new.sum, new.name, new.id,
nextval('"test2_id1_seq"'::text));

trying to insert into table1 there is a referencial integrity error -
Why?.
OK I say - the values whitin transaction are unknown.
defening the key as DEFEREBLE is not solution too, since the values
are check at the end of the transaction.

OK - I say - then I must delete the refencial integrity and try again.
On my system this say (using pgadmin)

DROP TRIGGER "RI_ConstraintTrigger_64226" ON "test2";
DROP TRIGGER "RI_ConstraintTrigger_64228" ON "test1";
DROP TRIGGER "RI_ConstraintTrigger_64230" ON "test1";

After that I try to insert into table1:
INSERT INTO "test1" ("id", "sum", "name") VALUES
(NEXTVAL('"test1_id_seq"'::text), '1', '2');

then;
select * from table1;

id sum name
4 1.000000 2

select * from table2;
id1 sum1 name2 id
2 1.000000 2 3

as you see the id in table1 is 4, but the id in table2 is 3

Regards
Tony Tomov

Browse pgsql-bugs by date

  From Date Subject
Next Message Bruce Momjian 2001-10-16 18:48:33 Re: ecpg - GRANT bug
Previous Message Tom Lane 2001-10-16 15:38:48 Re: ecpg - GRANT bug