Re: PL/pgSQL trigger and sequence increment

> Seems like you would be a lot better off enforcing this with a unique
> index on (submitter_id, date_trunc('month',entry_timestamp)).  The above
> not only doesn't provide any feedback, it's got serious race-condition
> problems.

I'll take a look at using an index to do this.  The trigger is an ugly
solution.

> > Each row in the table also has a SERIAL identifier with a sequence
>   > providing values.  I'd like to provide information to the user
> > regarding why the INSERT or UPDATE failed, as the examples in the
>  > documentation do via using a RAISE EXCEPTION instead of RETURN
> NULL   > (see   >
> http://www.postgresql.org/docs/8.3/interactive/plpgsql-trigger.html,
>  >  which appears to be unchanged in the documentation for 9.0).   >
> However,  if I do so, the sequence increments after the attempted  >
> INSERT or  UPDATE, which is not desired (and does not happen if  >
> RETURN NULL is  the result of the trigger function).

> Really?  Frankly, I don't believe it.  Any default value will get filled
> in long before triggers run.  In any case, you'd still have issues from
> errors occurring later in the transaction.  In general, you *can not*
> expect to not have "holes" in the serial number assignment when using a
> sequence object.  You'll save yourself a lot of grief if you just accept
> that fact, rather than imagining (falsely) that you've found a
> workaround to avoid it.

I double-checked it and got the same behavior each time I did it.
Poking around in the documentation makes me think that the key is when
the trigger fires.  The trigger in question is a BEFORE trigger, so
according to the docs if it returns NULL the INSERT never happens.
Thus, the sequence wouldn't increment - makes sense to me.  It appears
that, if you get an exception instead, the sequence does increment,
which is the part that doesn't make sense.

> If you really must have gap-free serial numbers, it's possible, but it's
> slow, expensive, and doesn't rely on sequence objects.  You can find the
> details in the list archives, but basically each insert has to lock the
> table against other inserts and then examine it to find the max current
> id.

Been there, done that, implemented a solution (which doesn't use
sequences).  I'm not using that solution here - just don't see why a
BEFORE trigger should be incrementing a sequence.


Dominic Jones, Ph.D.