Search Postgresql Archives

Re: Sequence skipping values

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux