BUG #4620: Unexpected(doc'd) side effects of using serial and rules

The following bug has been logged online:

Bug reference: 4620
Logged by: Simon Keen
Email address: simon(dot)keen(at)eglimited(dot)co(dot)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.