From: darcy@druid.net
To: Grant <grant@conprojan.com.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: Serials.
Date: Sat, 24 Mar 2001 07:57:50 -0500 (EST)
Message-ID: <20010324125750.906311A62@druid.net> (raw)
In-Reply-To: <Pine.LNX.4.21.0103241549070.21933-100000@webster.conprojan.com.au>
Thus spake Grant
> Please see below for my table schema. I have two questions.
>
> (1) Why is a sequence limited to 2147483647, it seems very small?
Yikes! What are you counting? :-)
The value 2147483647 is the largest value that can fit into an int. It is
equal to 0x7fffffff in hex. If you add one to that you get -2147483648.
I suppose the number could be doubled by using an unsigned int for the
serial type. It should never be negative anyway.
> (2) If I reset the sequence, then try another insert. It will not insert
> anything until it cycles through all sequences and finds an unused one. It
> will give the following error each time it tries to insert a row with a
> used sequence:
>
> PostgreSQL query failed: ERROR: Cannot insert a duplicate key into unique
> index releases_pkey
>
> How can I possibly get around this issue so that I can be sure to always
> have a free id without getting the error above?
Well, there are a lot of questions to ask before answering that one. What
is the reason for resetting the sequence? Do you actually have values
in that range and are hitting a ceiling. Are there a lot of holes in
your sequence?
The short ansqwer is to use max and setval to reset your index above the
highest number but if holes are the problem then that won't help. You
may need to run a script that renumbers down but don't forget to renumber
any tables that reference it. Referential integrity constraints may
also get in your way.
Hey, this is PostgreSQL. If you don't like the way that nextval works
then just create your own function with the behaviour that you need. You
can keep your own list of numbers and fill holes and all sorts of things.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: darcy@druid.net, grant@conprojan.com.au
Subject: Re: Serials.
In-Reply-To: <20010324125750.906311A62@druid.net>
References: <Pine.LNX.4.21.0103241549070.21933-100000@webster.conprojan.com.au> <20010324125750.906311A62@druid.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox