From: Douglas Thomson <dougt@mugc.cc.monash.edu.au>
To: jks@p1.selectacast.net
Cc: tgl@sss.pgh.pa.us
Cc: gary.stainburn@ringways.co.uk
Cc: pgsql-interfaces@postgreSQL.org
Subject: Re: [INTERFACES] locking on database updates
Date: Tue, 7 Dec 1999 14:58:43 +1100 (EST)
Message-ID: <199912070358.OAA26398@mugca.cc.monash.edu.au> (raw)
In-Reply-To: <384C781E.F165774F@selectacast.net>
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.
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 using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: dougt@mugc.cc.monash.edu.au, jks@p1.selectacast.net, tgl@sss.pgh.pa.us, gary.stainburn@ringways.co.uk
Subject: Re: [INTERFACES] locking on database updates
In-Reply-To: <199912070358.OAA26398@mugca.cc.monash.edu.au>
* 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