Re: Sequence skipping values - Mailing list pgsql-general

Hi

here are the structures of the table involved:

CREATE TABLE topics
(
  topic_id int8 NOT NULL DEFAULT nextval('topics_id_seq'::text),
  datetime timestamp NOT NULL DEFAULT ('now'::text)::timestamp(6) with time zone,
  topic text NOT NULL,
  administrator_id int8 NOT NULL,
  status_id int8 DEFAULT 0,
  last_change timestamp NOT NULL DEFAULT ('now'::text)::timestamp(6) with time zone,
  links int8 DEFAULT 0,
  releases int8 DEFAULT 0,
  last_administrator_id int8,
  CONSTRAINT topics_pk PRIMARY KEY (topic_id),
  CONSTRAINT topics_fk_administrator_id FOREIGN KEY (administrator_id)
      REFERENCES administrators (administrator_id) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT topics_status_fk FOREIGN KEY (status_id)
      REFERENCES status_list (status_id) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT
)
select * from topics_id_seq
"topics_id";1224;1;9223372036854775807;0;1;23;f;t

it is this topics_id_seq that does not increment by one unit at a time, but rather by the number of rows of the table topics.

To make things shorter, I am posting only one of the three tables. The other two tables are similar and have the same kind of rules

CREATE TABLE releases
(
  topic_id int8 NOT NULL,
  release_id int8 NOT NULL DEFAULT nextval('releases_id_seq'::text),
  datetime timestamp NOT NULL DEFAULT ('now'::text)::timestamp(6) with time zone,
  body text NOT NULL,
  administrator_id int8 NOT NULL,
  CONSTRAINT releases_pk PRIMARY KEY (release_id),
  CONSTRAINT releases_fk_administrator_id FOREIGN KEY (administrator_id)
      REFERENCES administrators (administrator_id) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT releases_fk_topic_id FOREIGN KEY (topic_id)
      REFERENCES topics (topic_id) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT
)

CREATE OR REPLACE RULE releases_increment_topics AS
    ON INSERT TO releases DO  UPDATE topics SET releases = topics.releases + 1
  WHERE topics.topic_id = new.topic_id;

CREATE OR REPLACE RULE releases_last_administrator_id AS
    ON INSERT TO releases DO  UPDATE topics SET last_administrator_id = new.administrator_id
  WHERE topics.topic_id = new.topic_id;

CREATE OR REPLACE RULE releases_last_change AS
    ON INSERT TO releases DO  UPDATE topics SET last_change = now()
  WHERE topics.topic_id = new.topic_id;

Thanks again for your time and explanations; it is qu ite useful.
Regards
JCR


Michael Fuhr <mike@fuhr.org> wrote:

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 expr ession, 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

---

Yahoo! Mail
Use Photomail to share photos without annoying attachments.