Re: PL/pgSQL trigger and sequence increment

jonesd(at)xmission(dot)com writes:
> Greetings. I noticed an interesting behavior when using a PL/pgSQL
> trigger. I'm running PostgreSQL 8.3. The trigger function checks a
> newly inserted or updated row for a type of uniqueness. Specifically,
> each row in the table has a submitter id and an entry timestamp. No
> two rows can have the same submitter id and entry timestamp month
> (basically, this means that there can be one entry per submitter per
> month). In other words, the trigger function is along the lines of:

> BEGIN
> IF NOT EXISTS (SELECT * FROM table_entry WHERE submitter_id =
> new.submitter_id AND date_trunc('month',entry_timestamp) =
> date_trunc('month',new.entry_timestamp)) THEN RETURN new;
> ELSE RETURN NULL;
> END IF;
> END

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.

> 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.

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.

regards, tom lane