Обсуждение: Sequence skipping values

Hello,
I have a table with and id field (primary key) which default value is the result of a sequence (increment explicitly set to 1).
To my surprise, the real increment on insert is the total number of rows of the table. For instance, with 41 rows and a sequence last_value of 1141, the next insert row will have a value of 1182. It is not a big problem but I don't like skipping and wasting values in a sequence. Also, I'd like to understand what's going on!
If someone could give me a clue that wold be greatly appreciated
thanks
JC

---

Brings words and photos together (easily) with
PhotoMail - it's free and works with Yahoo! Mail.

You likely roll back some transactions which insert. The sequence values
don't get rolled back, once allocated, it's gone, even if you won't keep
it. For concurrency/performance reasons sequence values are not
transactional.

Cheers,
Csaba.

On Fri, 2006-02-10 at 18:44, Jean-Christophe Roux wrote:
> Hello,
> I have a table with and id field (primary key) which default value is
> the result of a sequence (increment explicitly set to 1).
> To my surprise, the real increment on insert is the total number of
> rows of the table. For instance, with 41 rows and a sequence
> last_value of 1141, the next insert row will have a value of 1182. It
> is not a big problem but I don't like skipping and wasting values in a
> sequence. Also, I'd like to understand what's going on!
> If someone could give me a clue that wold be greatly appreciated
> thanks
> JC
>
>
> ______________________________________________________________________
> Brings words and photos together (easily) with
> PhotoMail  - it's free and works with Yahoo! Mail.

On Fri, 2006-02-10 at 11:44, Jean-Christophe Roux wrote:
> Hello,
> I have a table with and id field (primary key) which default value is
> the result of a sequence (increment explicitly set to 1).
> To my surprise, the real increment on insert is the total number of
> rows of the table. For instance, with 41 rows and a sequence
> last_value of 1141, the next insert row will have a value of 1182. It
> is not a big problem but I don't like skipping and wasting values in a
> sequence. Also, I'd like to understand what's going on!
> If someone could give me a clue that wold be greatly appreciated
> thanks

Have you got some kind of trigger or rule on this table that could be
causing such behaviour?

Jean-Christophe Roux wrote:
>   Hello,
> I have a table with and id field (primary key) which default value is
> the result of a sequence (increment explicitly set to 1).
> To my surprise, the real increment on insert is the total number of rows
> of the table. For instance, with 41 rows and a sequence last_value of
> 1141, the next insert row will have a value of 1182. It is not a big
> problem but I don't like skipping and wasting values in a sequence.
> Also, I'd like to understand what's going on!
> If someone could give me a clue that wold be greatly appreciated
> thanks
> JC

Any rules, triggers, etc. involved? How are the inserts done (insert one
record into the table)? What relationships does the table have to any
other tables? Anything else accessing that sequence?

There is no guarantee that a sequence will be contiguous. For example,
begin...insert into...rollback  will not reset the sequence as other
transactions could have incremented the sequence.

Cheers,
Steve

On Fri, Feb 10, 2006 at 09:57:31AM -0800, Steve Crawford wrote:
> There is no guarantee that a sequence will be contiguous. For example,
> begin...insert into...rollback  will not reset the sequence as other
> transactions could have incremented the sequence.

Also, deleting records won't modify the sequence (unless a trigger
does so; the point is that the delete itself doesn't).  If you
delete the latest records in the table, the sequence will continue
from where it was after the last insert (more properly, after the
last call to nextval), not from the new highest value in the table.

Other possibilities, though less likely, are that the sequence has
non-default CACHE or INCREMENT values.

--
Michael Fuhr

Hi,
Thanks four your answers. Let me give more details here.
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

Thanks
JC

Steve Crawford <scrawford@pinpointresearch.com> wrote:

Jean-Christophe Roux wrote:
> Hello,
> I have a table with and id field (primary key) which default value is
> the result of a sequence (increment explicitly set to 1).
> To my surprise, the real increment on insert is the total number of rows
> of the table. For instance, with 41 rows and a sequence last_value of
> 1141, the next insert row will have a value of 1182. It is not a big
> problem but I don't like skipping and wasting values in a sequence.
> Also, I'd like to understand what's going on!
> If someone could give me a clue that wold be greatly appreciated
> thanks
> JC

Any rules, triggers, etc. involved? How are the inserts done (insert one
record into the table)? What relationships does the table have to any
other tables? Anything else accessing that sequence?

There is no guarantee that a sequence will be contiguous. For example,
begin...insert into...rollback will not reset the sequence as other
transactions could have incremented the sequence.

Cheers,
Steve

---

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

Jean-Christophe Roux wrote:
> Hi,
> Thanks four your answers. Let me give more details here.
> 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

Start psql and on each of those tables run "\d <tablename>". Also run
"select * from <sequencename>" for any sequences involved. Post results
here - I suspect some clues will be contained therein.

Cheers,
Steve

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

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.

On Fri, Feb 10, 2006 at 08:10:36PM -0800, Jean-Christophe Roux wrote:
> here are the structures of the table involved:

I couldn't duplicate the problem in 8.1.2 or 8.0.6 with the code
you posted.  I created the given tables and rules (plus guesses for
the administrators and status_list tables), then inserted several
records into topics, then inserted a few records into releases,
then inserted a few more records into topics.  The topics_id_seq
sequence incremented by one each time with no gaps; that probably
means my test didn't match exactly what you're doing.

Does the topics table have any rules or triggers that you didn't
show?  Could you post a minimal but complete test case, i.e, all
SQL statements that somebody could load into an empty database to
reproduce the problem?  If not then it might be useful to see the
EXPLAIN ANALYZE output of a series of statements that exhibit the
unexpected behavior.  What version of PostgreSQL are you running?

--
Michael Fuhr

Hi Michael,

I'm running PostgreSQL 7.4.5 on a shared wehosting, which means among other things that I have limited control over the database settings and capabilities, For instance, I cannot install languages and therefore I cannot write triggers.

Yes you are right, I did not show one rule on table topics:
CREATE OR REPLACE RULE topics_last_administrator_id AS
    ON INSERT TO topics DO  UPDATE topics SET last_administrator_id = new.administrator_id
  WHERE topics.topic_id = new.topic_id;

I am going to try to replicate the problem, using a local 8.1.2 database.

Thank you very much for your help
JCR

Michael Fuhr <mike@fuhr.org> wrote:

On Fri, Feb 10, 2006 at 08:10:36PM -0800, Jean-Christophe Roux wrote:
> here are the structures of the table involved:

I co uldn't duplicate the problem in 8.1.2 or 8.0.6 with the code
you posted. I created the given tables and rules (plus guesses for
the administrators and status_list tables), then inserted several
records into topics, then inserted a few records into releases,
then inserted a few more records into topics. The topics_id_seq
sequence incremented by one each time with no gaps; that probably
means my test didn't match exactly what you're doing.

Does the topics table have any rules or triggers that you didn't
show? Could you post a minimal but complete test case, i.e, all
SQL statements that somebody could load into an empty database to
reproduce the problem? If not then it might be useful to see the
EXPLAIN ANALYZE output of a series of statements that exhibit the
unexpected behavior. What version of PostgreSQL are you running?

--
Michael Fuhr

---

Brings words and photos together (easily) with
PhotoMail - it's free and works with Yahoo! Mail.

On Sat, Feb 11, 2006 at 06:06:21AM -0800, Jean-Christophe Roux wrote:
> Yes you are right, I did not show one rule on table topics:
> CREATE OR REPLACE RULE topics_last_administrator_id AS
>     ON INSERT TO topics DO  UPDATE topics SET last_administrator_id = new.administrator_id
>   WHERE topics.topic_id = new.topic_id;
>
> I am going to try to replicate the problem, using a local 8.1.2 database.

No need; the version shouldn't matter in this case.  The above rule
is responsible because of what I mentioned in a previous message,
viz., new.topic_id is being rewritten as an expression instead of
a constant.  If the insert doesn't provide a value for topic_id
then it takes its value from its default expression, which is a
call to nextval.  When the rule is rewritten, new.topic_id isn't
replaced with that value but rather with the nextval expression.

Here's another example:

CREATE TABLE foo (id serial PRIMARY KEY, x integer, lastx integer);

CREATE RULE foo AS ON INSERT TO foo
  DO ALSO UPDATE foo SET lastx = NEW.x WHERE id = NEW.id;

INSERT INTO foo (x) VALUES (1);
SELECT * FROM foo;
 id | x | lastx
----+---+-------
  1 | 1 |
(1 row)

INSERT INTO foo (x) VALUES (2);
SELECT * FROM foo;
 id | x | lastx
----+---+-------
  1 | 1 |
  3 | 2 |
(2 rows)

INSERT INTO foo (x) VALUES (3);
SELECT * FROM foo;
 id | x | lastx
----+---+-------
  1 | 1 |
  3 | 2 |
  6 | 3 |
(3 rows)

As you can see, this example doesn't do what the rule appears to
intend.  The last insert, for example, causes the following update
statement to be run:

UPDATE foo SET lastx = 3 WHERE id = nextval('foo_id_seq')

Since nextval is volatile each row in the table is checked, causing
nextval to be evaluated each time; that's why it's incrementing by
the number of rows in the table.  Also notice that lastx isn't being
assigned because id never matches the sequence's next value -- do
you see that problem in your case as well?

You might be able to use a rule that uses currval instead of referring
to the id column (but see below for a warning):

CREATE RULE foo AS ON INSERT TO foo
  DO ALSO UPDATE foo SET lastx = NEW.x WHERE id = currval('foo_id_seq');

If we drop and recreate the foo table and add the above rule then
we get this:

INSERT INTO foo (x) VALUES (1);
SELECT * FROM foo;
 id | x | lastx
----+---+-------
  1 | 1 |     1
(1 row)

INSERT INTO foo (x) VALUES (2);
SELECT * FROM foo;
 id | x | lastx
----+---+-------
  1 | 1 |     1
  2 | 2 |     2
(2 rows)

INSERT INTO foo (x) VALUES (3);
SELECT * FROM foo;
 id | x | lastx
----+---+-------
  1 | 1 |     1
  2 | 2 |     2
  3 | 3 |     3
(3 rows)

Unfortunately the update will fail to set lastx correctly if you
insert multiple rows with INSERT ... SELECT:

INSERT INTO foo (x) SELECT n FROM generate_series(4, 6) AS g(n);
SELECT * FROM foo;
 id | x | lastx
----+---+-------
  1 | 1 |     1
  2 | 2 |     2
  3 | 3 |     3
  4 | 4 |
  5 | 5 |
  6 | 6 |     4
(6 rows)

I haven't considered other cases you so you might find additional
failure modes.  This method is also inefficient because currval is
volatile so each row in the table will have to be checked, which
will make the insert/update slow as the table grows.  Additionally,
the update creates a dead tuple for every insert so you should
vacuum the table often if it's updated often.

A trigger would probably be better for this.  If your database
doesn't have a trigger-capable language like PL/pgSQL and your
webhosting admins won't create it for you, then consider changing
services.

--
Michael Fuhr

Micheal,

Thanks a lot for you very clear explanation. To solve the problem, I have created another table so that the table does not use a rule to update itself. It works fine now.
Regards
JCR

Michael Fuhr <mike@fuhr.org> wrote:

On Sat, Feb 11, 2006 at 06:06:21AM -0800, Jean-Christophe Roux wrote:
> Yes you are right, I did not show one rule on table topics:
> CREATE OR REPLACE RULE topics_last_administrator_id AS
> ON INSERT TO topics DO UPDATE topics SET last_administrator_id = new.administrator_id
> WHERE topics.topic_id = new.topic_id;
>
> I am going to try to replicate the problem, using a local 8.1.2 database.

No need; the version shouldn't matter in this case. The above rule
is responsible because of what I mentioned in a previous message,
viz., new.topic_id is being rewritten as an expression instead of
a constant. If the insert doesn't provide a value for topic_id
then it takes its value from its default expression, which is a
call to nextval. When the rule is rewritten, new.topic_id isn't
replaced with that value but rather with the nextval expression.

Here's another example:

CREATE TABLE foo (id serial PRIMARY KEY, x integer, lastx integer);

CREATE RULE foo AS ON INSERT TO foo
DO ALSO UPDATE foo SET lastx = NEW.x WHERE id = NEW.id;

INSERT INTO foo (x) VALUES (1);
SELECT * FROM foo;
id | x | lastx
----+---+-------
1 | 1 |
(1 row)

INSERT INTO foo (x) VALUES (2);
SELECT * FROM foo;
id | x | lastx
----+---+-------
1 | 1 |
3 | 2 |
(2 rows)

INSERT INTO foo (x) VALUES (3);
SELECT * FROM foo;
id | x | lastx
----+---+-------
1 | 1 |
3 | 2 |
6 | 3 |
(3 rows)

As you can s ee, this example doesn't do what the rule appears to
intend. The last insert, for example, causes the following update
statement to be run:

UPDATE foo SET lastx = 3 WHERE id = nextval('foo_id_seq')

Since nextval is volatile each row in the table is checked, causing
nextval to be evaluated each time; that's why it's incrementing by
the number of rows in the table. Also notice that lastx isn't being
assigned because id never matches the sequence's next value -- do
you see that problem in your case as well?

You might be able to use a rule that uses currval instead of referring
to the id column (but see below for a warning):

CREATE RULE foo AS ON INSERT TO foo
DO ALSO UPDATE foo SET lastx = NEW.x WHERE id = currval('foo_id_seq');

If we drop and recreate the foo table and add the above rule then
we get this:

INSERT INTO foo (x) VALUES (1);
SELECT * FROM foo;
id | x | lastx
----+---+-------
1 | 1 | 1
(1 row)

INSERT INTO foo (x) VALUES (2);
SELECT * FROM foo;
id | x | lastx
----+---+-------
1 | 1 | 1
2 | 2 | 2
(2 rows)

INSERT INTO foo (x) VALUES (3);
SELECT * FROM foo;
id | x | lastx
----+---+-------
1 | 1 | 1
2 | 2 | 2
3 | 3 | 3
(3 rows)

Unfortunately the update will fail to set lastx correctly if you
insert multiple rows with INSERT ... SELECT:

INSERT INTO foo (x) SELECT n FROM generate_series(4, 6) AS g(n);
SELECT * FROM foo;
id | x | lastx
----+---+-------
1 | 1 | 1
2 | 2 | 2
3 | 3 | 3
4 | 4 |
5 | 5 |
6 | 6 | 4
(6 rows)

I haven't considered other cases you so you might find additional
failure modes. This method is also inefficient because currval is
volatile so each row in the table will have to be checked, which
will make the insert/update slow as the table grows. Additionally,the update creates a dead tuple for every insert so you should
vacuum the table often if it's updated often.

A trigger would probably be better for this. If your database
doesn't have a trigger-capable language like PL/pgSQL and your
webhosting admins won't create it for you, then consider changing
services.

--
Michael Fuhr

---

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