Message-Id: <199912070358.OAA26398@mugca.cc.monash.edu.au>
Subject: Re: [INTERFACES] locking on database updates
To: jks@p1.selectacast.net (Joseph Shraibman)
Date: Tue, 7 Dec 1999 14:58:43 +1100 (EST)
Cc: tgl@sss.pgh.pa.us, gary.stainburn@ringways.co.uk,
	pgsql-interfaces@postgreSQL.org
In-Reply-To: <384C781E.F165774F@selectacast.net> from "Joseph Shraibman" at
	Dec 6, 99 09:59:42 pm
Reply-To: dougt@mugc.cc.monash.edu.au
From: Douglas Thomson <dougt@mugc.cc.monash.edu.au>
Content-Type: text

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.