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.