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

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

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: Bug #485: strange behavior when creting rules with serial id
Date: 2001-10-17 05:19:46
Message-ID: 200110170519.f9H5Jki07220@postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
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


Responses

pgsql-bugs by date

Next:From: Lee KindnessDate: 2001-10-17 09:00:31
Subject: Re: ecpg - GRANT bug
Previous:From: pgsql-bugsDate: 2001-10-17 04:13:54
Subject: Bug #484: TIMESTAMP arithmetic insconsistencies

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