pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Serials
11+ messages / 9 participants
[nested] [flat]

* Serials
@ 2001-01-12 16:01 Sharmad  Naik <sharmad@im.eth.net>
  2001-01-12 16:35 ` Re: Serials Josh Berkus <josh@agliodbs.com>
  2001-01-12 22:02 ` Re: Serials Thomas Swan <tswan-lst@ics.olemiss.edu>
  0 siblings, 2 replies; 11+ messages in thread

From: Sharmad Naik @ 2001-01-12 16:01 UTC (permalink / raw)
  To: pgsql-sql

What's the purpose of Serials in Data types of postgres ? 
TIA 
Sharmad

^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Serials
  2001-01-12 16:01 Serials Sharmad  Naik <sharmad@im.eth.net>
@ 2001-01-12 16:35 ` Josh Berkus <josh@agliodbs.com>
  1 sibling, 0 replies; 11+ messages in thread

From: Josh Berkus @ 2001-01-12 16:35 UTC (permalink / raw)
  To: pgsql-sql

Mr. Naik,

> What's the purpose of Serials in Data types of postgres ?
> TIA 
> Sharmad

This topic is more than adequately covered in both the
online documentation and Bruce Momjian's book.  If language
or other problems are preventing you from finding the
correct reference, please reply and I'll give you a pointer.
Othervwise, RTFM.

-Josh Berkus




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Serials
  2001-01-12 16:01 Serials Sharmad  Naik <sharmad@im.eth.net>
@ 2001-01-12 22:02 ` Thomas Swan <tswan-lst@ics.olemiss.edu>
  1 sibling, 0 replies; 11+ messages in thread

From: Thomas Swan @ 2001-01-12 22:02 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; pgsql-sql

At 1/12/2001 10:35 AM, Josh Berkus wrote:
>Mr. Naik,
>
> > What's the purpose of Serials in Data types of postgres ?
> > TIA
> > Sharmad

SERIAL is the same as makeing a column and then setting the default value
to a sequence.  If you insert and don't specify the column that is of type
serial it will have a new unique value on each insert.  The datatype in a 
serial
column is an int4.


>This topic is more than adequately covered in both the
>online documentation and Bruce Momjian's book.  If language
>or other problems are preventing you from finding the
>correct reference, please reply and I'll give you a pointer.
>Othervwise, RTFM.

These things are explained with little effort.  Granted sometimes these
are easily answered by consulting documentation, but sometimes all
anyone is looking for is a simple, quick answer.

Thomas




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Serials.
@ 2001-03-24 05:55 Grant <grant@conprojan.com.au>
  2001-03-24 11:34 ` Re: Serials. Richard Huxton <dev@archonet.com>
  2001-03-24 12:57 ` Re: Serials. D'Arcy J.M. Cain <darcy@druid.net>
  2001-03-24 13:49 ` Re: Serials. Mathijs Brands <mathijs@ilse.nl>
  2001-03-24 14:29 ` Re: Serials. Peter Eisentraut <peter_e@gmx.net>
  0 siblings, 4 replies; 11+ messages in thread

From: Grant @ 2001-03-24 05:55 UTC (permalink / raw)
  To: pgsql-sql

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 null
 client_id    | smallint  | not null
 booking_time | timestamp | not null
 duration     | float4    | not null
 notes        | text      | not null
Index: bookings_pkey

=#




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Serials.
  2001-03-24 05:55 Serials. Grant <grant@conprojan.com.au>
@ 2001-03-24 11:34 ` Richard Huxton <dev@archonet.com>
  3 siblings, 0 replies; 11+ messages in thread

From: Richard Huxton @ 2001-03-24 11:34 UTC (permalink / raw)
  To: Grant <grant@conprojan.com.au>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Serials.
  2001-03-24 05:55 Serials. Grant <grant@conprojan.com.au>
@ 2001-03-24 12:57 ` D'Arcy J.M. Cain <darcy@druid.net>
  2001-03-24 21:56   ` Re: Serials. Grant <grant@conprojan.com.au>
  3 siblings, 1 reply; 11+ messages in thread

From: darcy@druid.net @ 2001-03-24 12:57 UTC (permalink / raw)
  To: Grant <grant@conprojan.com.au>; +Cc: pgsql-sql

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.



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Serials.
  2001-03-24 05:55 Serials. Grant <grant@conprojan.com.au>
  2001-03-24 12:57 ` Re: Serials. D'Arcy J.M. Cain <darcy@druid.net>
@ 2001-03-24 21:56   ` Grant <grant@conprojan.com.au>
  2001-03-25 02:28     ` Re: Serials. Dan Lyke <danlyke@flutterby.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Grant @ 2001-03-24 21:56 UTC (permalink / raw)
  To: D'Arcy J.M. Cain <darcy@druid.net>; +Cc: pgsql-sql

> > (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.




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Serials.
  2001-03-24 05:55 Serials. Grant <grant@conprojan.com.au>
  2001-03-24 12:57 ` Re: Serials. D'Arcy J.M. Cain <darcy@druid.net>
  2001-03-24 21:56   ` Re: Serials. Grant <grant@conprojan.com.au>
@ 2001-03-25 02:28     ` Dan Lyke <danlyke@flutterby.com>
  2001-03-25 23:58       ` Re: Serials. Grant <grant@conprojan.com.au>
  0 siblings, 1 reply; 11+ messages in thread

From: Dan Lyke @ 2001-03-25 02:28 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Serials.
  2001-03-24 05:55 Serials. Grant <grant@conprojan.com.au>
  2001-03-24 12:57 ` Re: Serials. D'Arcy J.M. Cain <darcy@druid.net>
  2001-03-24 21:56   ` Re: Serials. Grant <grant@conprojan.com.au>
  2001-03-25 02:28     ` Re: Serials. Dan Lyke <danlyke@flutterby.com>
@ 2001-03-25 23:58       ` Grant <grant@conprojan.com.au>
  0 siblings, 0 replies; 11+ messages in thread

From: Grant @ 2001-03-25 23:58 UTC (permalink / raw)
  To: Dan Lyke <danlyke@flutterby.com>; +Cc: pgsql-sql

> 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 that when it reaches the limit it will start back at 1 again. If however some users still have messages in their accounts that have used random ids from 1 onwards postgresql will produce the error that it's trying to insert a duplicate id that already exists, so in theory I want the system to run for infinity. I hope this makes sense. Thankyou for your time.




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Serials.
  2001-03-24 05:55 Serials. Grant <grant@conprojan.com.au>
@ 2001-03-24 13:49 ` Mathijs Brands <mathijs@ilse.nl>
  3 siblings, 0 replies; 11+ messages in thread

From: Mathijs Brands @ 2001-03-24 13:49 UTC (permalink / raw)
  To: Grant <grant@conprojan.com.au>; +Cc: pgsql-sql

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 



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Serials.
  2001-03-24 05:55 Serials. Grant <grant@conprojan.com.au>
@ 2001-03-24 14:29 ` Peter Eisentraut <peter_e@gmx.net>
  3 siblings, 0 replies; 11+ messages in thread

From: Peter Eisentraut @ 2001-03-24 14:29 UTC (permalink / raw)
  To: Grant <grant@conprojan.com.au>; +Cc: pgsql-sql

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/




^ permalink  raw  reply  [nested|flat] 11+ messages in thread


end of thread, other threads:[~2001-03-25 23:58 UTC | newest]

Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-12 16:01 Serials Sharmad  Naik <sharmad@im.eth.net>
2001-01-12 16:35 ` Josh Berkus <josh@agliodbs.com>
2001-01-12 22:02 ` Thomas Swan <tswan-lst@ics.olemiss.edu>
2001-03-24 05:55 Serials. Grant <grant@conprojan.com.au>
2001-03-24 11:34 ` Re: Serials. Richard Huxton <dev@archonet.com>
2001-03-24 12:57 ` Re: Serials. D'Arcy J.M. Cain <darcy@druid.net>
2001-03-24 21:56   ` Re: Serials. Grant <grant@conprojan.com.au>
2001-03-25 02:28     ` Re: Serials. Dan Lyke <danlyke@flutterby.com>
2001-03-25 23:58       ` Re: Serials. Grant <grant@conprojan.com.au>
2001-03-24 13:49 ` Re: Serials. Mathijs Brands <mathijs@ilse.nl>
2001-03-24 14:29 ` Re: Serials. Peter Eisentraut <peter_e@gmx.net>

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