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 – about triggering triggers.

For example, on a table in which you need to fix something, an evil trigger hangs ON UPDATEtransferring all 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 ALTER TABLE imposes Accessexclusive-lock, under which no one is executed in parallel, even simple SELECT, cannot read anything from the table. That is, until this transaction is completed, everyone who wants to “just read” will wait. And we remember that UPDATE we do-o-olgiy …

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, “Fly by” any change on the table, 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. To do this, you can use session variables.

session_replication_role

Read manual:

The trigger variable is also affected by the configuration variable. session_replication_role. Triggers that are enabled without additional instructions (default) will fire when the replication role is “origin” (default) or “local”. Indicated triggers ENABLE REPLICAwill only work if current session mode – “replica”, and triggers included by indicating ENABLE ALWAYSwill work regardless of the current replication mode.

I emphasize that the setting 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 “Turn off” one specific trigger?

This will help us “User” session variable:

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 appropriate 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 blocking, through CREATE OR REPLACE for 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.