Get best of the week

PostgreSQL Antipatterns: changing data bypassing a trigger

Sooner or later, many are faced with the need to massively fix something in the table entries. I already told how to do it better , and how - it’s better not to do it. Today I’ll talk about the second aspect of the mass update - triggering triggers .

For example, on a table in which you need to fix something, an evil trigger hangs ON UPDATE, transferring all the changes to some aggregates. And you need to update everything (initialize a new field, for example) so carefully that these units are not affected.

Let's just turn off the triggers!


BEGIN;
  ALTER TABLE ... DISABLE TRIGGER ...;
  UPDATE ...; --  -
  ALTER TABLE ... ENABLE TRIGGER ...;
COMMIT;

Actually, that's all - everything is already hanging .

Because it ALTER TABLEimposes an AccessExclusive lock , under which no one running in parallel, even a simple one SELECT, can read anything from the table. That is, until this transaction is completed, everyone who wants to "just read" will wait. And we remember that UPDATEwe do-o-olgi ...

Let's quickly turn it off, then turn it on quickly!


BEGIN;
  ALTER TABLE ... DISABLE TRIGGER ...;
COMMIT;

UPDATE ...;

BEGIN;
  ALTER TABLE ... ENABLE TRIGGER ...;
COMMIT;

Here the situation is better, the waiting time is much shorter. But only two problems spoil all the beauty:

  • ALTER TABLE he waits for all other operations on the table, including long SELECT
  • While the trigger is off, any change in the table will fly by , not even ours. And well, it won’t get into the units, although it should. Trouble!

Session Variable Management


So, in the previous version, we stumbled upon a crucial point - we need to somehow teach the trigger to distinguish “our” changes in the table from “not ours”. Skip "ours" as is, but "not ours" - trigger. You can use session variables for this .

session_replication_role


Read the manual :
The trigger variable is also affected by the session_replication_role configuration variable . Triggers that are enabled without additional instructions (default) will fire when the replication role is “origin” (default) or “local”. Triggers enabled by an indication ENABLE REPLICAwill fire only if the current session mode is “replica”, and triggers enabled by an indication ENABLE ALWAYSwill fire regardless of the current replication mode.
I emphasize that the configuration does not apply to all-all at once, as ALTER TABLE, but only to our separate special connection. Total so that no application triggers fire:

SET session_replication_role = replica; --  
UPDATE ...;
SET session_replication_role = DEFAULT; --

Condition inside trigger


But the above option works for all triggers at once (or you need to “trigger” triggers in advance that you don't want to disable). And if we need to "turn off" one specific trigger ?

The “user" session variable will help us with this :
Extension parameter names are written as follows: extension name, period, and then the parameter name itself, like the full names of objects in SQL. For example: plpgsql.variable_conflict.
Since non-system parameters can be set in processes that do not load the corresponding extension module, PostgreSQL accepts values ​​for any names with two components .
First, modify the trigger, something like this:
BEGIN
    --     
    IF current_setting('mycfg.my_table_convert_process') = 'TRUE' THEN
        IF TG_OP IN ('INSERT', 'UPDATE') THEN
            RETURN NEW;
        ELSE
            RETURN OLD;
        END IF;
    END IF;
...

By the way, this can be done "profitably", without locks, through CREATE OR REPLACEfor a trigger function. And then in the special connection we cock "our" variable:

SET mycfg.my_table_convert_process = 'TRUE';
UPDATE ...;
SET mycfg.my_table_convert_process = ''; --

Do you know other ways? Share in the comments.

More articles:


All Articles