Re: [INTERFACES] locking on database updates

Joseph Shraibman <jks(at)p1(dot)selectacast(dot)net> writes:
> Tom Lane wrote:
> > Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)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.