Thread: sequence increment jumps?
guys,
i inserted 1 record into my database (default
nextval('sequencename'::regclass) where (start 1 increment 1)). then i
tried to insert 1 other record twice but both those inserts failed
because of a domain check ("ERROR: value too long for type character
varying(X)". when i was finally able to insert that record the
sequence jumped 2 places. seems like it counted the failed inserts?
i had insert errors yesterday ("ERROR: invalid input syntax for
integer" "ERROR: column 'columnname' is of type date but expression is
of type integer") but they didn't cause any increment jumps. and when
i insert a record now the sequence increments just fine.
bug or nature of the beast? how to reset? btw using 8.1, got no other
sessions, no record deletes, no triggers, no rules.
jzs
http://www.postgresql.org/docs/8.1/interactive/sql-createsequence.html
http://archives.postgresql.org/pgsql-general/2001-11/msg01004.php
http://archives.postgresql.org/pgsql-admin/2002-02/msg00335.php
"John Smith" <jayzee.smith@gmail.com> writes:
> i had insert errors yesterday ("ERROR: invalid input syntax for
> integer" "ERROR: column 'columnname' is of type date but expression is
> of type integer") but they didn't cause any increment jumps. and when
> i insert a record now the sequence increments just fine.
>
> bug or nature of the beast? how to reset? btw using 8.1, got no other
> sessions, no record deletes, no triggers, no rules.
Nature of the beast. Sequence increments aren't rolled back on
transaction abort (for performance and concurrency reasons), so you
should expect gaps.
-Doug
On Thu, Jan 25, 2007 at 12:33:51 -0500,
John Smith <jayzee.smith@gmail.com> wrote:
> guys,
> i inserted 1 record into my database (default
> nextval('sequencename'::regclass) where (start 1 increment 1)). then i
> tried to insert 1 other record twice but both those inserts failed
> because of a domain check ("ERROR: value too long for type character
> varying(X)". when i was finally able to insert that record the
> sequence jumped 2 places. seems like it counted the failed inserts?
That is how sequences work. All your are guaranteed globally is that they
are unique. You can't rely on getting a sequence without gaps. Within a single
session you can get a guaranty that the values increase monotonicly if you
disallow wrap around for the sequence.
On 1/25/07, John Smith <jayzee.smith@gmail.com> wrote:
> guys,
> i inserted 1 record into my database (default
> nextval('sequencename'::regclass) where (start 1 increment 1)). then i
> tried to insert 1 other record twice but both those inserts failed
> because of a domain check ("ERROR: value too long for type character
> varying(X)". when i was finally able to insert that record the
> sequence jumped 2 places. seems like it counted the failed inserts?
if you absolutely must have gapless identifiers in your database,
follow this procedure:
http://www.varlena.com/GeneralBits/130.php
as others have stated, sequences are (much) faster than rule/trigger
based solutions and have better concurrency.
merlin
On Thursday 25 January 2007 09:53, Douglas McNaught wrote: > Nature of the beast. Sequence increments aren't rolled back on > transaction abort (for performance and concurrency reasons), so you > should expect gaps. Behavior long ago noted and accounted for. But I've always wondered why this was so? Is there a specific reason for this behavior? -Ben -- "The best way to predict the future is to invent it." - XEROX PARC slogan, circa 1978
Benjamin Smith <lists@benjamindsmith.com> writes: > On Thursday 25 January 2007 09:53, Douglas McNaught wrote: >> Nature of the beast. Sequence increments aren't rolled back on >> transaction abort (for performance and concurrency reasons), so you >> should expect gaps. > > Behavior long ago noted and accounted for. But I've always wondered why this > was so? Is there a specific reason for this behavior? Being able to roll back a sequence increment would require locking the sequence for the duration of the transaction, which would kill concurrency. -Doug
Douglas McNaught wrote: > Benjamin Smith <lists@benjamindsmith.com> writes: > > > On Thursday 25 January 2007 09:53, Douglas McNaught wrote: > >> Nature of the beast. ?Sequence increments aren't rolled back on > >> transaction abort (for performance and concurrency reasons), so you > >> should expect gaps. > > > > Behavior long ago noted and accounted for. But I've always wondered why this > > was so? Is there a specific reason for this behavior? > > Being able to roll back a sequence increment would require locking the > sequence for the duration of the transaction, which would kill > concurrency. This is an FAQ. -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +