Unusual problem. A sequence is incrementing by 2 when increment_by = 1, think it's caused by an INSERT rule - Mailing list pgsql-general

From Justin Clift
Subject Unusual problem. A sequence is incrementing by 2 when increment_by = 1, think it's caused by an INSERT rule
Date
Msg-id 3BE609DE.1C7EA7A2@postgresql.org
Whole thread Raw
Responses Re: Unusual problem. A sequence is incrementing by 2 when
List pgsql-general
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

pgsql-general by date:

Previous
From: "Aasmund Midttun Godal"
Date:
Subject: Re: newbie question
Next
From: Bruce Momjian
Date:
Subject: My new job