Thread: Unusual problem. A sequence is incrementing by 2 when increment_by = 1, think it's caused by an INSERT rule

Hi all,

I'm wondering if anyone has any ideas why an insert rule on a table with
a sequence would cause the sequence to be incremented by two on every
insert.

The table definition is :

CREATE TABLE "z_codes" (
        "idnum" integer DEFAULT nextval('z_codes_idnum_seq'::text) NOT
NULL,
        "description" character varying(30) NOT NULL,
        "enabled" boolean DEFAULT 't'::bool NOT NULL,
        CONSTRAINT "z_codes_description" CHECK ((length(description) <
31)),
        Constraint "z_codes_pkey" Primary Key ("idnum")
);

The RULE is :

CREATE RULE protect_z_insert AS ON INSERT TO z_codes WHERE (new.idnum =
0) DO INSTEAD NOTHING;

The present value of the sequence is :

foo=# select * from z_codes_idnum_seq;
        sequence_name         | last_value | increment_by | max_value  |
min_value | cache_value | log_cnt | is_cycled | is_called

-------------------+------------+--------------+------------+-----------+-------------+---------+-----------+-----------
 z_codes_idnum_seq |         98 |            1 | 2147483647 |         1
|           1 |      23 | f         | t
(1 row)

When doing an insert that uses the default value
(nextval('z_codes_idnum_seq')) for the idnum field, the inserted value
is only ever incremented by 2, never by 1.  This is confusing, as I
don't see how the rule is even applicable as the new.idnum is not going
to be 0 in any of these cases.

Any thoughts?

Regards and best wishes,

Justin Clift

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
     - Indira Gandhi

I think this is probably the same side effect that causes
insert rules using NEW.<nextvalcol> to increment and put
different values in a master and detail table.  IIRC,
when it sees the new.idnum in your rule anywhere it sees
the nextval(...) not the value and will call nextval again.
I think if you search for foreign key problems and
insert rules you'll probably find posts by Tom that
probably have more details.