[GENERAL] 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
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] sequence increment jumps?
John Smith [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] sequence increment jumps?
On Thu, Jan 25, 2007 at 12:33:51 -0500,
John Smith [EMAIL PROTECTED] 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.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] sequence increment jumps?
On 1/25/07, John Smith [EMAIL PROTECTED] 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
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] sequence increment jumps?
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] sequence increment jumps?
Benjamin Smith [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] sequence increment jumps?
Douglas McNaught wrote: Benjamin Smith [EMAIL PROTECTED] 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 [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
