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.
>
> To compensate for this, I want to add a code snippet on inserts that
> checks for the high number in the unique number field, increments it,
> and inserts. However, it seems to me that this snippet should be an
> insert trigger so that I don't have to promulgate it to all clients,
> present and future.
>
> Because this is NOVICE I have no trouble admitting I have no trigger
> experience. However, before I get into it, I want to ask if this is
> already available on some contrib forum.
>
Lan,

It is great to hear that you want to get into using triggers. However,
the problem you are trying to solve has already been solved in postgres,
and that solution is the "serial" data type. You can add a unique
constraint to the column to guarantee uniqueness. You DO NOT want to do
what you are proposing, as it will kill performance for any moderately
large table and does not guarantee that you get a unique number.
Imagine I do some insert while inside a long-running transaction and
while the transaction is running, you do a quick insert. You insert the
same value that I was just given if you use your method. Serial types
do not allow this to happen.

As for your dump/restore problem, perhaps you should tell us how you did
the dump and restore. Generally, if you dump a whole database, you will
not have this problem. If you dump only the table and not the
associated sequence, you will need to reset the sequence, I think.

In any case, the serial datatype is the best way to go here. Save
learning triggers for another day.

Sean