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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
