Re: Serial and triggers

Lan Barnes wrote:
> I have come to the conclusion that the serial data type is inadequate
> for providing a reliable unique record number on inserts. The final
> sticking point is that after restoring (or replicating) a data base from
> a pg_dump, the seed number for the serial value isn't updated and I get
> dupe numbers.

Hi Lan,

Don't go for triggers till you understand serials.

A sequence is what actually gives you the values used to populate the
serial column. You can see the sequence with \d in psql.

public | some_sequence | sequence | pguser

You can read the sequence values (min, max, next, increment...) by:
select * from some_sequence;

A variety of functions like setval, nextval and so on can manipulate the
sequence.

When you create table with type serial, PostgreSQL will will be kind and
create a sequence for you (and tell you that is what it did). However if
you desire, you can do the steps manually to tailor your app. For
instance you can have multiple tables all accessing the same sequence to
get unique values across tables.

If you use pg_dumpall to back up an entire database then the sequence
values are backed up so they can be set appropriately on restore.

If you dump a single table that happens to rely on a sequence, then you
will have to take steps on restore to set the sequence appropriately.

Depending on your reasons for replicating a single table from your
database, this might be as simple as locking the table, inserting the
data, selecting the max serial value, setting the sequence to one higher
and unlocking the table.

Cheers,
Steve