PostgreSQL Antipatterns: menjamo podatke zaobiđujući okidač

Pr sooner or later, many face the need to make bulk corrections in table records. I have already discussed how to do this better, and how — not to do it better. Today, I will talk about the second aspect of bulk updating — trigger execution.

For example, on a table where you need to make some adjustments, there is a nasty trigger ON UPDATE, transferring all changes to some aggregates. But 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 ...; -- this takes a long time
  ALTER TABLE ... ENABLE TRIGGER ...;
COMMIT;

Actually, that's it — everything is already hanging.

Because ALTER TABLE nameće AccessExclusive-lock, under which no one running in parallel, not even a simple SELECT, will be able to read anything from the table. That is, as long as this transaction is not finished, everyone wishing to even 'just read' will wait. And we remember that UPDATE we have a loooong...

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

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

UPDATE ...;

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

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

  • ALTER TABLE it still waits for all other operations on the table, including lengthy ones SELECT
  • While the trigger is turned off, 'any changes' in the table will 'fly by' , not even ours. And it won't hit the aggregates, although it should. What a pity!

Session variable management

So, in the previous option, we encountered a fundamental issue — we need to somehow teach the trigger to distinguish between 'our' changes in the table and 'not ours'. To allow 'our' ones as is, and to activate on 'not ours'. For this, we can use session variables.

session_replication_role

Reading priručnik:

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

I must emphasize that the setting does not apply to all at once, as ALTER TABLE, a samo do naše specijalne veze. Dakle, da ne aktiviraju nikakvi aplikativni okidači:

SET session_replication_role = replica; -- isključili okidače
UPDATE ...;
SET session_replication_role = DEFAULT; -- vraćeno u prvobitno stanje

Uslov unutar okidača

Međutim, gornja opcija radi za sve okidače odjednom (ili je potrebno "alterovati" unapred okidače koje ne želite da isključite). A ako nam je potrebno "isključiti" jedan konkretni okidač?

U tome nam pomaže "korisnička" sesijska promenljiva:

Imena parametara ekstenzija se zapisuju na sledeći način: ime ekstenzije, tačka i zatim samo ime parametra, slično punim imenima objekata u SQL-u. Na primer: plpgsql.variable_conflict.
S obzirom da van-sistemskih parametara može biti postavljeno u procesima koji ne učitavaju odgovarajući modul ekstenzije, PostgreSQL prihvata vrednosti za bilo koja imena sa dve komponente.

Prvo doradimo okidač, otprilike ovako:

BEGIN
    -- procesu konverzije se može raditi sve
    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;
...

Inače, to se može uraditi "u hodu", bez zaključavanja, preko CREATE OR REPLACE za funkciju okidača. A zatim u specijalnoj vezi postavljamo "svoju" promenljivu:


SET mycfg.my_table_convert_process = 'TRUE';
UPDATE ...;
SET mycfg.my_table_convert_process = ''; -- vraćeno u prvobitno stanje

Znate li neke druge načine? Podelite u komentarima.

Izvor: habr.com

Kupite pouzdan hosting za sajtove uz zaštitu od DDoS, VPS VDS servere 🔥 Kupite pouzdan hosting za sajtove uz zaštitu od DDoS, VPS VDS servere | ProHoster