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

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 (view raw or flat)
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





pgsql-bugs by date

Next:From: Bruce MomjianDate: 2001-10-16 18:48:33
Subject: Re: ecpg - GRANT bug
Previous:From: Tom LaneDate: 2001-10-16 15:38:48
Subject: Re: ecpg - GRANT bug

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