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

"Simon Keen" <simon.keen@eglimited.co.uk> writes:
> 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.

This is a well-known issue arising from the fact that a rule is really a
macro: multiple references to any expression in the original statement
result in multiple evaluations of that expression.

By and large, the best way to propagate inserted information to other
tables is with a trigger.

            regards, tom lane

Many thanks. I guessed as much.  It would be useful to add something to the
documentation to explain that functions used as defaults are re-written into
the sql and the implications of this if used with rules.

I did re-write it with a trigger but because statement level triggers do not
have access to NEW and OLD it means row level triggers and the performance
issues they bring with changes in large numbers of rows.

Cheers

Simon Keen

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 18 January 2009 16:35
To: Simon Keen
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #4620: Unexpected(doc'd) side effects of using
serial and rules

"Simon Keen" <simon.keen@eglimited.co.uk> writes:
> 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.

This is a well-known issue arising from the fact that a rule is really a
macro: multiple references to any expression in the original statement
result in multiple evaluations of that expression.

By and large, the best way to propagate inserted information to other tables
is with a trigger.

            regards, tom lane