Thread: deferrable triggers

In a discussion on irc today, someone had a need to confirm that a
business rule (this table has two rows for every related one row in
another table) was true at commit time.  I innocently suggested a
deferrable (and deferred) trigger.  It was pointed out that the
docs:
http://www.postgresql.org/docs/9.1/interactive/sql-createtrigger.html#AEN68703
say:
| This can only be specified for constraint triggers.
Hmm.  Somehow I had gotten hold of deferrable triggers as a way to
solve a problem in moving our replication from our Java framework to
PostgreSQL triggers.  So we are using a hand-written DEFERRABLE
trigger in production, with it apparently working as intended.
Is this dangerous?  If not, should the docs be updated?
This is a potentially valuable feature.  For example, to ensure that
a double-entry accounting system leaves a financial transaction
balanced when a transaction commits.  There is no way to check that
with "EACH ROW" triggers, and it can be very clumsy to structure
things so that each single statement moves things from one valid
state to another.  (That *is* one of the jobs of a transaction,
after all.)
If we remove the above-referenced sentence from the docs, should we
include some warnings about the memory needed to track the need to
fire these triggers?
-Kevin

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> In a discussion on irc today, someone had a need to confirm that a
> business rule (this table has two rows for every related one row in
> another table) was true at commit time.  I innocently suggested a
> deferrable (and deferred) trigger.  It was pointed out that the
> docs:
> http://www.postgresql.org/docs/9.1/interactive/sql-createtrigger.html#AEN68703
> say:
> | This can only be specified for constraint triggers.
> Hmm.  Somehow I had gotten hold of deferrable triggers as a way to
> solve a problem in moving our replication from our Java framework to
> PostgreSQL triggers.  So we are using a hand-written DEFERRABLE
> trigger in production, with it apparently working as intended.

What do you mean by "hand-written DEFERRABLE trigger"?  AFAICS from the
grammar, DEFERRABLE and related attributes can only be specified when
you write CREATE CONSTRAINT TRIGGER, so the documentation's statement
appears correct to me.
        regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
>> In a discussion on irc today, someone had a need to confirm that
>> a business rule (this table has two rows for every related one
>> row in another table) was true at commit time.  I innocently
>> suggested a deferrable (and deferred) trigger.  It was pointed
>> out that the docs:
>> 
http://www.postgresql.org/docs/9.1/interactive/sql-createtrigger.html#AEN68703
>> 
>> say:
>> 
>> | This can only be specified for constraint triggers.
>> 
>> Hmm.  Somehow I had gotten hold of deferrable triggers as a way
>> to solve a problem in moving our replication from our Java
>> framework to PostgreSQL triggers.  So we are using a hand-written
>> DEFERRABLE trigger in production, with it apparently working as
>> intended.
> 
> What do you mean by "hand-written DEFERRABLE trigger"?  AFAICS
> from the grammar, DEFERRABLE and related attributes can only be
> specified when you write CREATE CONSTRAINT TRIGGER, so the
> documentation's statement appears correct to me.
Ah, I had forgotten that I had to use the CONSTRAINT keyword in the
trigger definition; the sentence in the docs makes more sense now.
I wrote a plpgsql trigger function and created a deferrable
initially deferred constraint trigger which referenced it.  Is that
a reasonable thing to do if you need a commit-time trigger based on
some particular action against a particular table?  Would it be a
reasonable way for the person on irc to enforce the business rule
mentioned above?
If so, I think there's room for the docs to clarify that CONSTRAINT
TRIGGERs are usable for things other than implementing declarative
constraints, which was the (apparently contagious) interpretation of
the person in irc.
-Kevin

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> What do you mean by "hand-written DEFERRABLE trigger"?
> Ah, I had forgotten that I had to use the CONSTRAINT keyword in the
> trigger definition; the sentence in the docs makes more sense now.
> I wrote a plpgsql trigger function and created a deferrable
> initially deferred constraint trigger which referenced it.  Is that
> a reasonable thing to do if you need a commit-time trigger based on
> some particular action against a particular table?  Would it be a
> reasonable way for the person on irc to enforce the business rule
> mentioned above?

Sure.  The reason we decided to document CREATE CONSTRAINT TRIGGER was
exactly that people sometimes need to use it for things besides foreign
keys.  However, it's not a "commit time" trigger exactly -- keep in mind
that SET CONSTRAINTS can override the trigger's own timing
specification.
        regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> wrote:
> However, it's not a "commit time" trigger exactly -- keep in mind
> that SET CONSTRAINTS can override the trigger's own timing
> specification.
Hmm.  Is there a way for trigger code to check whether it is running
deferred (at transaction commit time) versus immediate?  (If not, I
see writing a small patch in my future to allow it.)
-Kevin