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
