PostgreSQL Antipatterns: changing data bypassing triggers

Sooner or later, many face the need to make bulk changes to table entries. I've already discussed the best ways to do this,and what to avoid. Today, I will talk about the second aspect of bulk updates — trigger firing..

For example, a malicious trigger is hanging over the table where you need to make adjustments, ON UPDATE,transferring all changes to some aggregates. You need to update everything (initialize a new field, for instance) carefully, so that these aggregates are not affected.

Let's just disable the triggers!

BEGIN;
  ALTER TABLE ... DISABLE TRIGGER ...;
  UPDATE ...; -- this will take a while
  ALTER TABLE ... ENABLE TRIGGER ...;
COMMIT;

That's basically it — everything is already hanging..

Because ALTER TABLE imposes AccessExclusive- the lock, under which no one performing parallel operations, even a simple one, SELECT, will be able to read anything from the table. This means that until this transaction is completed, everyone wishing to 'just read' will have to wait. And we remember that UPDATE it's going to be a l-o-o-o-ng wait…

So let's quickly disable it, then quickly enable it!

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

UPDATE ...;

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

The situation is already better here, the wait time is significantly reduced. But two problems spoil the beauty:

• ALTER TABLE it still waits for all other operations on the table, including long ones. SELECT
• While the trigger is off, any change to the table, even if it's not ours, will 'fly by'. And it won’t make its way to the aggregates, although it should. That’s a problem!

Session Variable Management

So, in the previous option, we encountered a fundamental point — we need to teach the trigger to distinguish 'our' changes in the table from 'not ours'. 'Our' changes should pass as is, while 'not ours' should trigger. To achieve this, we can use session variables..

session_replication_role.

Let's read manual:

The trigger firing mechanism is also influenced by the configuration variable session_replication_role.. Triggers enabled without further specifications (by default) will fire when the replication role is 'origin' (by default) or 'local'. Triggers enabled with the specification ENABLE REPLICA, will only fire if the current session mode is 'replica', and triggers enabled with the specification ENABLE ALWAYS, will fire regardless of the current replication mode.

I want to emphasize that this setting does not apply to all at once, as ALTER TABLE, but only to our separate special connect. In total, to ensure that no application triggers are activated:

SET session_replication_role = replica; -- disabled triggers
UPDATE ...;
SET session_replication_role = DEFAULT; -- reverted to the initial state

The condition inside the trigger

However, the above option works for all triggers at once (or you need to 'alter' in advance the triggers you do not want to disable). But if we need to disable one specific trigger?

This will be aided by a 'user' session variable:

Extension parameter names are recorded as follows: the name of the extension, a dot, and then the actual name of the parameter, similar to fully qualified object names 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, we improve the trigger, approximately as follows:

BEGIN
    -- the conversion process can do everything
    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 'live', without locks, through CREATE OR REPLACE for the trigger function. Then in the special connect we set our variable:

SET mycfg.my_table_convert_process = 'TRUE';
UPDATE ...;
SET mycfg.my_table_convert_process = ''; -- reverted to the initial state

Do you know other methods? Share in the comments.

Source: habr.com