Thread: Serials.
Please see below for my table schema. I have two questions.
(1) Why is a sequence limited to 2147483647, it seems very small?
(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?
Thankyou.
-- TABLE --
=# \d bookings Table "bookings" Attribute | Type | Modifier
--------------+-----------+---------------------------------------------------id | integer | not null
default
nextval('bookings_id_seq'::text)added | timestamp | not nullclient_id | smallint | not nullbooking_time |
timestamp| not nullduration | float4 | not nullnotes | text | not null
Index: bookings_pkey
=#
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.
Grant wrote: > > Please see below for my table schema. I have two questions. > > (1) Why is a sequence limited to 2147483647, it seems very small? That's 2 billion(ish) - the largest signed 32 bit integer. > (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 - you've told PG to only allow unique id values and then reset the sequence that generates its values. It's doing pretty much what you'd expect, I'd say. How fast are you inserting these bookings? According to my calculations that's a sustained rate of 68 inserts/sec over a whole year. If you just want unique records, put the primary key over id,added and let the id_seq cycle. If you want more than 2 billion unique id values you'll need to combine the sequence with another value - see my postgresql notes at techdocs.postgresql.org and perhaps look into the plpgsql cookbook (www.brasileiro.net from memory) - Richard Huxton
On Sat, Mar 24, 2001 at 03:55:09PM +1000, Grant allegedly wrote:
> Please see below for my table schema. I have two questions.
>
> (1) Why is a sequence limited to 2147483647, it seems very small?
This is the maximum value a signed integer (32-bit) can contain. If
this really is a problem for you, then it might be a good idea to
look into another database; one specifically suited for huge databases,
such as NCR Terabase.
To put things in perspective: if you have a table with more than two
billion records each about 50 bytes in size you would have more than
(!) 100 GB of data... I don't think PostgreSQL is meant for those
enormous amounts of data, although it may still work porperly. Most
larger commercial database offerings (Oracle and Sybase come to mind)
will still work well, but once you're database contains a few terabytes
of data you're just asking for problems. There is a very good chance
that you can spread the data accross several database, increasing
performance and maintainability.
Of course, writing your own serial implementation is not that difficult.
If you use 64-bit integers (int8), you should have plenty of space :)
Search the mailinglist archive for some clues, since there was a discussion
about this (implementing a sequence that doesn't have holes) one or two
days ago.
> (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?
Get the maximum primary key value from the table and start the sequence
from the number following it.
I hope this helps,
Mathijs
--
$_='while(read+STDIN,$_,2048){$a=29;$c=142;if((@a=unx"C*",$_)[20]&48){$h=5;
$_=unxb24,join"",@b=map{xB8,unxb8,chr($_^$a[--$h+84])}@ARGV;s/...$/1$&/;$d=
unxV,xb25,$_;$b=73;$e=256|(ord$b[4])<<9|ord$b[3];$d=$d>>8^($f=($t=255)&($d
>>12^$d>>4^$d^$d/8))<<17,$e=$e>>8^($t&($g=($q=$e>>14&7^$e)^$q*8^$q<<6))<<9
,$_=(map{$_%16or$t^=$c^=($m=(11,10,116,100,11,122,20,100)[$_/16%8])&110;$t
^=(72,@z=(64,72,$a^=12*($_%16-2?0:$m&17)),$b^=$_%64?12:0,@z)[$_%8]}(16..271))
[$_]^(($h>>=8)+=$f+(~$g&$t))for@a[128..$#a]}print+x"C*",@a}';s/x/pack+/g;eval
Grant writes: > (1) Why is a sequence limited to 2147483647, it seems very small? Because that's what a four-byte signed integer takes. No one has stepped forward to implement 8-byte sequence counters, yet. > (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 Why did you reset the sequence in the first place? You should probably set it back to where it was (using setval()). Sequences simply return incrementing numbers, they don't fill holes or have any constraint-avoiding logic. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
> > (1) Why is a sequence limited to 2147483647, it seems very small? > > Yikes! What are you counting? :-) I have a message board. Where users can send each other messages. I doubt I will ever get 2147483647 messages, but I want to make sure I never get an error where the message isn't sent. I guess the best option is to create a function that changes all the ids to the lowest values possible and then set the sequence to the maximum value + 1. Thanks everyone for your help.
Grant writes: > I have a message board. Where users can send each other messages. I > doubt I will ever get 2147483647 messages, but I want to make sure I > never get an error where the message isn't sent. Think about loads. If your users are going to be posting 10 messages/second, that's 864000 messages per day, you won't wrap for nearly 7 years. I've got a pretty heavy mail load, including spam I probably get 300 messages/day, weekends are lighter, so if you've got a bunch of weenies who are subscribed to a gazillion mailing lists you're talking three thousand users for six and a half years. A little light if you're planning on being the next Hotmail (A test account set up there gets 70 spams/day without my ever publishing the address), but for your average mid-range discussion forum you're probably good for a while. I doubt that, say, Salon's TableTalk forum gets even 10k new messages per day. > I guess the best option is to create a function that changes all the > ids to the lowest values possible and then set the sequence to the > maximum value + 1. Better idea, if one of your deletion criteria is going to be age, would just be to wrap the IDs. Dan
> Grant writes: > > I have a message board. Where users can send each other messages. I > > doubt I will ever get 2147483647 messages, but I want to make sure I > > never get an error where the message isn't sent. > > Think about loads. If your users are going to be posting 10 > messages/second, that's 864000 messages per day, you won't wrap for > nearly 7 years. I've got a pretty heavy mail load, including spam I > probably get 300 messages/day, weekends are lighter, so if you've got > a bunch of weenies who are subscribed to a gazillion mailing lists > you're talking three thousand users for six and a half years. > > A little light if you're planning on being the next Hotmail (A test > account set up there gets 70 spams/day without my ever publishing the > address), but for your average mid-range discussion forum you're > probably good for a while. I doubt that, say, Salon's TableTalk forum > gets even 10k new messages per day. I understand what you're saying. However it's not the amount of messages per day. I have cycle set on the sequence so thatwhen it reaches the limit it will start back at 1 again. If however some users still have messages in their accountsthat have used random ids from 1 onwards postgresql will produce the error that it's trying to insert a duplicateid that already exists, so in theory I want the system to run for infinity. I hope this makes sense. Thankyou foryour time.