PostgreSQL Antipatterns: change data bypassing a trigger

Sooner or later, many are faced with the need to massively fix something in the table records. I have already tell me how to do it better, and how - it is better not to do it. Today I will talk about the second aspect of the mass update - about triggers.

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

Let's just disable the triggers!

BEGIN;
  ALTER TABLE ... DISABLE TRIGGER ...;
  UPDATE ...; -- тут долго-долго
  ALTER TABLE ... ENABLE TRIGGER ...;
COMMIT;

Actually, that's all - everything is hanging.

Because ALTER TABLE imposes Access Exclusive- a lock under which no one is running in parallel, even a simple one SELECT, will not be able to read anything from the table. That is, until this transaction ends, everyone who wants to even “just read” will wait. And we remember that UPDATE we have a long ...

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

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

UPDATE ...;

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

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

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

Managing session variables

So, in the previous version, we stumbled upon a fundamental point - we need to somehow teach the trigger to distinguish “our” changes in the table from “not ours”. “Ours” are skipped as is, but on “not ours” they are triggered. For this you can use session variables.

session_replication_role

Read manual:

The trigger mechanism is also affected by the configuration variable session_replication_role. Enabled without additional instructions (default), triggers will fire when the replication role is "origin" (default) or "local". Triggers enabled by specifying ENABLE REPLICA, will work only if current session mode - "replica", and triggers enabled by specifying ENABLE ALWAYS, will work regardless of the current replication mode.

I will especially emphasize that the setting does not apply to all-all at once, as ALTER TABLE, but only to our separate special connection. In total, so that no application triggers work:

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 “alternate” 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: the extension name followed by a dot and then the parameter name itself, similar to full object names in SQL. For example: plpgsql.variable_conflict.
Because out-of-system options can be set in processes that do not load the appropriate extension module, PostgreSQL accepts values ​​for any names with two components.

First, we finalize 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 "for profit", without blocking, through CREATE OR REPLACE for the 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.

Source: habr.com