Bug #485: strange behavior when creting rules with serial id

Tony Tomov (ttomov(at)abs(dot)bg) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
strange behavior when creting rules with serial id

Long Description
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.
defining 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 phppgadmin)

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.
What is the reason for this? This mean that the values
are inserted first in the table2 and then in the table1 and
there is double increment of the id in table1 - first when inserting
in table2 and then again in table1.

Regards
Tony Tomov

Sample Code

No file was uploaded with this report