Received: from smartt.COM (ktk6.smartt.com [209.52.5.253]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eADIXds86672 for ; Mon, 13 Nov 2000 13:33:39 -0500 (EST) (envelope-from drfrog@smartt.com) Received: from drfrog.localdomain (alpnb13234.smarttadsl.com [209.52.13.234]) by smartt.COM (8.11.1/8.11.1) with SMTP id eADIeTa20941; Mon, 13 Nov 2000 10:40:29 -0800 (PST) Message-Id: <200011131840.eADIeTa20941@smartt.COM> Content-Type: text/plain; charset="iso-8859-1" Content-Disposition: inline Content-Transfer-Encoding: 7bit Mime-Version: 1.0 From: Dr Frog To: Steve Wampler , Poet/Joshua Drake Cc: postgres-sql Subject: Re: Using a postgres table to maintain unique id? X-Mailer: Pronto v2.2.1 Date: 13 Nov 2000 10:30:55 PST Reply-To: Dr Frog In-Reply-To: <3A102875.C545E345@noao.edu> References: <3A102875.C545E345@noao.edu> X-Archive-Number: 200011/95 X-Sequence-Number: 126 On Mon, 13 Nov 2000 10:44:21 -0700, Steve Wampler said: > Poet/Joshua Drake wrote: > > > > >However, I also use Postgres (7.0.2) throughout this > > >application and it seems cleaner to me to keep the current > > >id value in a table and just use postgres to provide access > > >(with a trigger function to increment the id on access). > > > > Why not a sequence? > > Can someone show me how to create (and use) an int8 sequence? > create sequnece seq_name ; there are additional options start sql and type drfrog=# \h create sequence Command: CREATE SEQUENCE Description: Creates a new sequence number generator Syntax: CREATE SEQUENCE seqname [ INCREMENT increment ] [ MINVALUE minvalue ] [ MAXVALUE maxvalue ] [ START start ] [ CACHE cache ] [ CYCLE ] drfrog=# more info in the docs too! > > >Is this reasonable? Is it fast? (I need 10 or more IDs > > >generated each second.) Can I avoid having the table > > >gradually fill with "old" rows for this entry, and this > > >avoid the need to run VACUUM ANALYZE periodically? > > > > The only problem I have had with this type of thing is when a number gets > > deleted, it does not get recycled. > > Fortunately, I don't want any number to ever get recycled - the id needs to > be unique throughout the 25+ year lifetime of the project. The table > would have a single row with a single column. Selecting that table cell > would return the current value, but leave the value incremented in the > table cell (it's ok if it increments the value before returning). > > > -- > Steve Wampler- SOLIS Project, National Solar Observatory > swampler@noao.edu > >