Received: from www-uk-05.digiserve.com ([195.97.231.11]) by mail.postgresql.org (8.11.3/8.11.1) with ESMTP id f2OE4kw51718 for ; Sat, 24 Mar 2001 09:04:46 -0500 (EST) (envelope-from dev@archonet.com) Received: from mainbox.archonet.com (mwynhau.demon.co.uk [193.237.186.96]) by www-uk-05.digiserve.com (8.9.1/8.9.1) with ESMTP id OAA26808; Sat, 24 Mar 2001 14:05:15 GMT Received: from archonet.com (ibook.archonet.com [192.168.1.32]) by mainbox.archonet.com (Postfix) with ESMTP id 6EACD2A68A; Sat, 24 Mar 2001 11:34:45 +0000 (GMT) Message-ID: <3ABC8631.8041704F@archonet.com> Date: Sat, 24 Mar 2001 11:34:09 +0000 From: Richard Huxton X-Mailer: Mozilla 4.7C-CCK-MCD {C-UDP; EBM-APPLE} (Macintosh; I; PPC) X-Accept-Language: en MIME-Version: 1.0 To: Grant Cc: pgsql-sql@postgresql.org Subject: Re: Serials. References: Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200103/475 X-Sequence-Number: 2144 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