Thread: BUG #2857: Sequence and table partitioning

The following bug has been logged online:

Bug reference:      2857
Logged by:          Tomislav Karastojkovic
Email address:      karastojko@gmail.com
PostgreSQL version: 8.1.4, 8.2.0
Operating system:   Linux
Description:        Sequence and table partitioning
Details:

Sequence does not increase by 1 when tables is partitioned!
For example, let define 'parent' and 'child' tables, with column 'id' of
type 'serial':

CREATE TABLE parent(id serial, message text);
CREATE TABLE child() INHERITS(parent);
ALTER TABLE child ALTER id DROP DEFAULT;

Default value is dropped from the child table, so sequence is used only in
the 'parent' table.
If we set a rule

CREATE OR REPLACE RULE r AS ON INSERT TO parent DO INSTEAD INSERT INTO child
VALUES(NEW.id, NEW.message);

then each inserting into table 'parent' increases sequence 'parent_id_seq'
by 1, as it should. But if we add a condition for 'id' when setting the rule
like

CREATE OR REPLACE RULE r AS ON INSERT TO parent WHERE id>=1 AND id <= 100 DO
INSTEAD INSERT INTO child VALUES(NEW.id, NEW.message);

then the sequence is increasing by 5 when inserting into 'parent' table.
This seems to be a bug.

"Tomislav Karastojkovic" <karastojko@gmail.com> writes:
> CREATE OR REPLACE RULE r AS ON INSERT TO parent WHERE id>=1 AND id <= 100 DO
> INSTEAD INSERT INTO child VALUES(NEW.id, NEW.message);

> then the sequence is increasing by 5 when inserting into 'parent' table.

"id" is basically a macro that gets replaced with the expression being
inserted, ie, the nextval() call.  So you're getting bit with multiple
evaluation.

There isn't any very safe way to do this sort of thing with a rule.
Consider using a trigger instead.

            regards, tom lane