Re: [INTERFACES] locking on database updates

Douglas Thomson wrote:

> 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.

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.