We're in the process of converting our triggers from a custom
framework where they ran in Java just above the database into native
PostgreSQL triggers. We've run into an issue in testing, and I
think I see a workable solution. It seems like a bit of a hack, but
it looks like it will work, and I can't see a better alternative.
I'm posting looking for feedback.
The problem is with our "purge" application. While the court data
is "mostly insert" with updates to things like case status and
receivable amounts, with very little deleting, data can be purged
after certain conditions are met. The existing Java-based triggers
have a special way to determine that a delete is part of a purge
process, which happens at a "logical" level -- like a financial
receivable or a court case. There is a function which validates
that the purge is OK, based on very complicated records retention
rules. Having determined that the purge of (for example) the "Case"
record and all associated records for that case is OK, the current
purge process disables trigger execution and ruthlessly deletes all
data from the bottom up.
To emulate this logic in a PostgreSQL trigger, I think I can define
a security definer trigger function created by the database
superuser with session_replication_mode set for the function to
'replica'. It's a lie, but it seems like it does the right thing --
triggers won't be called for what this trigger does unless we flag
them as ENABLE ALWAYS or ENABLE REPLICA. We will want to do this
for our triggered change notifications and replication publishing
triggers.
I assume this will prevent us from using, for example, Slony with
such a database, but are there any other down sides of abusing this
GUC in this fashion? Does anyone see a reasonable alternative?
-Kevin