The following bug has been logged online:
Bug reference: 4620
Logged by: Simon Keen
Email address: simon.keen@eglimited.co.uk
PostgreSQL version: 8.3.5
Operating system: Ubuntu Linux
Description: Unexpected(doc'd) side effects of using serial and rules
Details:
I have used serial columns as primary keys in some tables. I have a rule
that on insert to a table inserts in another table. However, the rule
appears to cause the seq nextval() to be invoked multiple times thus
destroying any value in NEW.serial_col.
Here is an example that causes the issue:
DROP RULE IF EXISTS base_ins ON base;
DROP TABLE IF EXISTS TC;
DROP TABLE IF EXISTS base;
CREATE TABLE base (
id serial PRIMARY KEY,
owner integer REFERENCES base(id),
value varchar(10));
CREATE TABLE TC (
parent integer REFERENCES base(id),
child integer REFERENCES base(id),
distance smallint);
CREATE OR REPLACE RULE base_ins AS ON INSERT TO base DO ALSO (
INSERT INTO TC (parent, child, distance) VALUES
(NEW.owner, NEW.id, 1);
INSERT INTO TC (parent, child, distance)
SELECT parent, NEW.id, distance+1
FROM TC
WHERE child=NEW.owner );
NEW.id has the wrong value in it and appears to have a value 1 higher than
the row inserted in the base table.
To test this theory I replaced the rule with the following:
CREATE OR REPLACE RULE base_ins AS ON INSERT TO base DO ALSO (
SELECT NEW.id);
This reports a value of NEW.id 1 higher than the inserted row. If I change
the rule to:
CREATE OR REPLACE RULE base_ins AS ON INSERT TO base DO ALSO (
SELECT NEW.id, NEW.id);
I get 2 different values. If the value of id inserted in the base table is
1 the values reported are 2 and 3.
I would view this as a bug in the rule re-write system as it is incorrectly
invoking a function. However, it shoudl at least have a warning in the
documentation for SERIAL about this. It makes the use of functions as
defaults dangerous when rules are used.
