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

On Wed, 17 Oct 2001 pgsql-bugs(at)postgresql(dot)org wrote:

> 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.

Yep. Both the rule's new.id and the actual id are going to increment the
counter, since they'll both see the nextval for the serial sequence, the
new.id is going to shove in whatever is specified for the value exactly
as it was specified, so new.id is nextval(...) not the numeric result
of nextval(...). You're probably going to want to use a trigger instead.

pgsql-bugs(at)postgresql(dot)org writes:
> 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));

"NEW" is a macro, not a variable. This rule will be expanded to
something like

INSERT INTO test2
(sum1, name2, id, id1) VALUES ('1', '2', NEXTVAL('"test1_id_seq"'::text),
nextval('"test2_id1_seq"'::text));

which means that nextval('test1_id_seq') is evaluated twice, once during
the rule and once during the actual insert into test1.

You can't do what you want with a rule; you'll need to use a
trigger instead.

regards, tom lane