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.