Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)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