View source | View content page | Page history | Printable version   

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:

Retrieved from "http://wiki.openbravo.com/wiki/Projects:Improved_Trigger_Disabling_In_PostgreSQL/Specs"

This page has been accessed 805 times. This page was last modified on 28 May 2018, at 09:42. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.