Sender: jks@p1.selectacast.net
Message-ID: <384C8A41.F168EF9D@selectacast.net>
Date: Mon, 06 Dec 1999 23:17:05 -0500
From: Joseph Shraibman <jks@p1.selectacast.net>
Organization: Xtenit
MIME-Version: 1.0
To: dougt@mugc.cc.monash.edu.au
CC: Joseph Shraibman <jks@p1.selectacast.net>, tgl@sss.pgh.pa.us,
	gary.stainburn@ringways.co.uk, pgsql-interfaces@postgreSQL.org
Subject: Re: [INTERFACES] locking on database updates
References: <199912070358.OAA26398@mugca.cc.monash.edu.au>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Douglas Thomson wrote:

> Joseph Shraibman <jks@p1.selectacast.net> writes:
> > Tom Lane wrote:
> > > Gary Stainburn <gary.stainburn@ringways.co.uk> writes:
> > > > My question is this.  Is there a safe way of retrieving the next
> > > > number from the parameter table in such a way that the same number
> > > > cannot be retrieved twice.
> > >
> > > Use a SEQUENCE object for each "parameter table".  This gives you
> > > a well-tested, efficient generator mechanism.  It may be "auto
> > > generated" but you do have the option of resetting the sequence's
> > > nextval and so forth, so I don't see that there is a good reason
> > > to build a SEQUENCE substitute by hand.
> >
> > Can you give an example of sql that creates a table with that?
>
> I created my sequence using code like:
>     CREATE SEQUENCE name_map_seq START 1
> and then used it as the default in another table:
>     CREATE TABLE name_map (
>         id INT DEFAULT nextval('name_map_seq'),
>         name TEXT,
>         info TEXT
>     )
> I also added a unique index to avoid possible mistakes:
>     CREATE UNIQUE INDEX name_map_unq ON name_map (id)
>
> If I just insert into the name_map table without supplying any
> id:
>     INSERT INTO name_map (name, info) VALUES ('some name', 'some info')
> then I get the next sequence number filled in for the id attribute.
>
> On the other hand, if I need to rebuild a table using the same id
> values as before, I can simply provide a value explicitly, and then
> the default is ignored:
>     INSERT INTO name_map (id, name, info) VALUES (24, 'name', 'info')
>
> If I need to find out what value my sequence is up to, I can extract
> it using:
>     SELECT last_value FROM name_map_seq
>
> Finally, if I need to prime the sequence when reconstructing tables,
> I use:
>     SELECT setval('name_map_seq', 24)
> (looks weird modifying a table with a SELECT, but it works!).
>
> Hope this helps. Criticism welcome; I make no claim to be an expert
> on either PostgreSQL or SQL in general, I just wanted to contribute
> something by way of thanks for all the assistance I have received
> from this list!
>
> Doug.

Thanks.  I just wish the postgres documentation were a little better.  Does
anyone know of a SQL refrence on the web *anywhere*?  I've just started
programming in sql, and I can't even find one in a big Barnes & Noble.