pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Richard Huxton <dev@archonet.com>
To: Grant <grant@conprojan.com.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: Serials.
Date: Sat, 24 Mar 2001 11:34:09 +0000
Message-ID: <3ABC8631.8041704F@archonet.com> (raw)
References: <Pine.LNX.4.21.0103241549070.21933-100000@webster.conprojan.com.au>

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



view thread (11+ messages)  latest in thread

Message-ID: <3ABC8631.8041704F@archonet.com>
Permalink:  ../3ABC8631.8041704F@archonet.com/
Also on:    postgresql.org/message-id/3ABC8631.8041704F@archonet.com

 · 

reply

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: dev@archonet.com, grant@conprojan.com.au
  Subject: Re: Serials.
  In-Reply-To: <3ABC8631.8041704F@archonet.com>
  References: <Pine.LNX.4.21.0103241549070.21933-100000@webster.conprojan.com.au> <3ABC8631.8041704F@archonet.com>

* 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