Thread: PL/pgSQL trigger and sequence increment

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

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

Any assistance is appreciated - thanks in advance!

---

Dominic Jones, Ph.D.

jonesd@xmission.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

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

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

Unfortunately, it didn't work.

CREATE UNIQUE INDEX one_entry_per_submitter_per_month ON table_entry
(submitter_id , date_trunc('month',entry_timestamp));

runs into

ERROR:  functions in index expression must be marked IMMUTABLE.

If I'm reading this correctly, date_trunc is not IMMUTABLE and thus
not usable in an index.


Dominic Jones, Ph.D.

jonesd@xmission.com writes:
> CREATE UNIQUE INDEX one_entry_per_submitter_per_month ON table_entry
> (submitter_id , date_trunc('month',entry_timestamp));
> runs into
> ERROR:  functions in index expression must be marked IMMUTABLE.

> If I'm reading this correctly, date_trunc is not IMMUTABLE and thus
> not usable in an index.

It is not immutable because it depends on the timezone setting: the same
timestamptz might be truncated to different absolute time instants
depending on which zone you are in.  IOW, when is midnight of the first
of the month, exactly?

You could work around this with something like

date_trunc('month',entry_timestamp AT TIME ZONE 'UTC')

(feel free to substitute a different zone name reflecting what you want
to have happpen) but I wonder whether this doesn't reflect a gap in your
database specification.

            regards, tom lane