Projects:Improved Trigger Disabling In PostgreSQL/Specs

Overview

Some processes, such as Order Loader, require to be executed with DB triggers disabled only for that transaction, whereas the rest of transactions should have them enabled.

Currently this is implemented by ad_session_status table, to disable triggers a new row is inserted in that table within current transaction and it is removed before commiting it. Triggers must check if that row is present and if so, they should perform no operation.

Issue

In PostgreSQL this approach generates a big bloat on this table, that even if it is expected to have one or none rows, every time triggers are disabled and enabled a new row is created, even they are marked as dead as soon as the transctation finalizes.

In situations of high concurrency, with many threads enabling and disabling triggers concurrently, these non removed dead rows can cause the query to check if triggers are enabled to rapidly degrade making it much slower than it usually is.

This issue has not been detected in Oracle.

Proposed solution

In PostgreSQL, instead of using this temporary table, we will use transaction settings. This has been proved to remove this bloat overhead.

As there is no detected issue in Oracle, current implementation will be kept.

Having different implementation in Oracle and PostgrSQL will require:

• When disabling/enabling triggers from Java code using TriggerHandler nothing will be required to be changed, this class will handle different implementation per database.
• So far it was possible to disable triggers in PL code, as there was no standard API to do so, this was done by directly inserting/deleting a row in ad_session_status table (for example, here). This is not longer permitted. Two new PL functions, that internally will handle differences in Oracle and PostgreSQL will be implemented:
  • ad_disable_triggers to disable triggers in current transaction.
  • ad_enable_triggers to enable them afterwards.
• Existent ad_istriggerenabled PL function will keep its API although it will be re-implemented to take into account DB specific implementation.