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
