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

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

From: "Simon Keen" <simon(dot)keen(at)eglimited(dot)co(dot)uk>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #4620: Unexpected(doc'd) side effects of using serial and rules
Date: 2009-01-18 09:19:24
Message-ID: 200901180919.n0I9JOJA081606@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
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.

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2009-01-18 16:35:19
Subject: Re: BUG #4620: Unexpected(doc'd) side effects of using serial and rules
Previous:From: Raymond NaseefDate: 2009-01-17 00:11:36
Subject: Re: BUG #4617: JDBC Drivers 8.2/8.3 return no ResultSet

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