Re: Trigger/Sequence headache

Stephen,

You don't need to use a seperate batch to clean up the table. As
Stephan pointed out, you can call nextval after you determine that the
new row isn't a duplicate.

In case you misunderstood what Stephan had suggested let me try to
explain what is happening.

When PostgreSQL receives an INSERT request it aggregates field values
for the new row from several sources.

1) The statement itself (INSERT INTO ... VALUES ...)
2) The fields' DEFAULT values (provided by the CREATE TABLE statement)
3) Any CONSTRANTs are taken into consideration
4) BEFORE triggers are called in alphabetical order
5) The new row is inserted
6) AFTER triggers are called in alphabetical order

The sequence is being incrimented when NEXTVAL is evaluated. NEXTVAL
can be evaluated in steps 1, 2 or 4 depending on how your application
is written. Since you want to be able to cancel the operation in step 4
without NEXTVAL having been evaluated, this is where you should call
NEXTVAL (instead of in steps 1 or 2). So in your trigger you want to do
something like this:

-- Check for duplicates here...RETURN NULL;
NEW.ID = NEXTVAL('SEQ_MYTABLE_ID');
RETURN NEW;

Also, are you sure you want to return NULL rather than raise an
exception? Either way will work depending on what the desired behavour
is. The thing is that silently dropping rows might be the source of a
debugging nightmare later on where-as raising an exception is a bit
easier to detect. Just me $0.02. To raise an exception see RAISE
EXCEPTION in the postgresql manual. Its really easy but will cancel the
current transaction so it may not be desirable for your uses.

-Robert