Re: Sequence skipping values

On Fri, Feb 10, 2006 at 03:27:23PM -0800, Jean-Christophe Roux wrote:
> The table with the id not incrementing by 1 as I expected is named topics.
>
> I have three other tables that contain rules that on insert into those
> tables, some fields of the table Topic should be updated.
> Each of those three tables contain a column that refer to topics.id as a
> foreign key.
> Those three columns contain id automatically generated by sequences and I
> have not observed any problem

The word "rules" attracts attention; questions about sequences being
incremented multiple times due to rules appear in the lists regularly.
The problem is that where you might think the rule uses a value it's
really using an expression, so each time you use the "value" in the
rule you're evaluating the expression again. Example:

CREATE TABLE foo (id serial);
CREATE TABLE bar (id1 integer, id2 integer, id3 integer);

CREATE RULE foorule AS ON INSERT TO foo
DO ALSO INSERT INTO bar VALUES (NEW.id, NEW.id, NEW.id);

INSERT INTO foo DEFAULT VALUES;

SELECT * FROM foo;
id
----
1
(1 row)

SELECT * FROM bar;
id1 | id2 | id3
-----+-----+-----
2 | 3 | 4
(1 row)

When the rule rewrote the query it didn't use

INSERT INTO bar VALUES (1, 1, 1)

but rather

INSERT INTO bar VALUES (nextval('foo_id_seq'), nextval('foo_id_seq'),
nextval('foo_id_seq'))

because NEW.id evaluates to a nextval expression, not to the result
of that expression.

If you post the table definitions as Steve requested we'll be able
to see whether the above is indeed what's happening.

--
Michael Fuhr