Re: [INTERFACES] locking on database updates

Douglas Thomson <dougt(at)mugc(dot)cc(dot)monash(dot)edu(dot)au> writes:
> Joseph Shraibman <jks(at)p1(dot)selectacast(dot)net> writes:
>> Can you give an example of sql that creates a table with that?

A couple footnotes on Doug's fine example:

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

Declaring a column as "SERIAL" is a handy shortcut for exactly these
declarations: a sequence, a default value of nextval('sequence'), and
a unique index on the column. (Plus a NOT NULL constraint, which you
might perhaps not want.) You can reach in and inspect/modify the
sequence object for a SERIAL column just as if you'd made the sequence
by hand.

> 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')

Right. Dumping and restoring the table with COPY commands works the
same way. In fact, if you dump the database with pg_dump, you'll find
that the resulting script not only restores all the values of the "id"
column via COPY, but also recreates the current state of the sequence
object.

regards, tom lane