Antipattern PostgreSQL: modifica i dati ignorando un trigger

Prima o poi, molti si trovano di fronte alla necessità di correggere in modo massiccio qualcosa nei record della tabella. ho già dimmi come farlo meglio, e come - è meglio non farlo. Oggi parlerò del secondo aspetto dell'aggiornamento di massa: sui trigger.

Ad esempio, su un tavolo in cui devi aggiustare qualcosa, si blocca un grilletto malvagio ON UPDATE, trasferendo tutte le modifiche ad alcuni aggregati. E devi aggiornare tutto (inizializzare un nuovo campo, ad esempio) con tanta attenzione che questi aggregati non ne risentano.

Disattiviamo solo i trigger!

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

In realtà, questo è tutto - tutto è sospeso.

perché ALTER TABLE impone Accesso esclusivo- una serratura sotto la quale nessuno corre in parallelo, nemmeno una semplice SELECT, non sarà in grado di leggere nulla dalla tabella. Cioè, fino al termine di questa transazione, tutti coloro che vogliono anche solo "leggere" aspetteranno. E ce lo ricordiamo UPDATE abbiamo un lungo...

Spegniamolo rapidamente, quindi accendiamolo rapidamente!

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

UPDATE ...;

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

Qui la situazione è già migliore, i tempi di attesa sono molto ridotti. Ma solo due problemi rovinano tutta la bellezza:

• ALTER TABLE stesso attende tutte le altre operazioni sul tavolo, comprese quelle lunghe SELECT
• Mentre il grilletto è spento "vola" ogni cambiamento in tavola, nemmeno la nostra. E non entrerà negli aggregati, anche se dovrebbe. Guaio!

Gestione delle variabili di sessione

Quindi, nella versione precedente, ci siamo imbattuti in un punto fondamentale: dobbiamo in qualche modo insegnare al trigger a distinguere i "nostri" cambiamenti nella tabella da "non nostri". I "nostri" vengono saltati così come sono, ma su "non nostri" vengono attivati. Per questo puoi usare variabili di sessione.

session_replication_role

Leggiamo Manuale:

Il meccanismo di attivazione è influenzato anche dalla variabile di configurazione session_replication_role. Abilitati senza istruzioni aggiuntive (impostazione predefinita), i trigger verranno attivati ​​quando il ruolo di replica è "origine" (impostazione predefinita) o "locale". Trigger abilitati specificando ENABLE REPLICA, funzionerà solo se modalità della sessione corrente - "replica" e trigger abilitati specificando ENABLE ALWAYS, funzionerà indipendentemente dalla modalità di replica corrente.

Sottolineerò in particolare che l'impostazione non si applica a tutto in una volta, come ALTER TABLE, ma solo alla nostra connessione speciale separata. In totale, in modo che nessun trigger di applicazione funzioni:

SET session_replication_role = replica; -- выключили триггеры
UPDATE ...;
SET session_replication_role = DEFAULT; -- вернули в исходное состояние

Condizione all'interno del grilletto

Ma l'opzione sopra funziona per tutti i trigger contemporaneamente (oppure è necessario "alternare" i trigger in anticipo che non si desidera disabilitare). E se abbiamo bisogno "disattiva" un trigger specifico?

Questo ci aiuterà variabile di sessione "utente".:

I nomi dei parametri di estensione sono scritti come segue: il nome dell'estensione seguito da un punto e quindi il nome del parametro stesso, simile ai nomi completi degli oggetti in SQL. Ad esempio: plpgsql.variable_conflict.
Poiché le opzioni fuori dal sistema possono essere impostate in processi che non caricano il modulo di estensione appropriato, PostgreSQL accetta valori per qualsiasi nome con due componenti.

Innanzitutto, finalizziamo il trigger, qualcosa del genere:

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;
...

A proposito, questo può essere fatto "a scopo di lucro", senza bloccare, attraverso CREATE OR REPLACE per la funzione trigger. E poi nella connessione speciale inseriamo la "nostra" variabile:

SET mycfg.my_table_convert_process = 'TRUE';
UPDATE ...;
SET mycfg.my_table_convert_process = ''; -- вернули в исходное состояние

Conosci altri modi? Condividi nei commenti.

Fonte: habr.com