Thread: Serial and triggers

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.

TIA,

--
Lan Barnes
Linux Guy, SCM Specialist
Tcl/Tk Enthusiast

Let me take this opportunity to dispel the notion, the canard,
that scientists are against transcendentalism, ... particularly
intelligent design. If any positive evidence could be found of a
supernatural guiding force, there would be a land rush of
scientists into it.
                          - Edward O. Wilson

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

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