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