PostgreSQL Antipatterns: modificare i dati bypassando il trigger

Prima o poi molti si trovano di fronte alla necessità di correggere in massa alcune voci in una tabella. Io ho già parlato di come fare questo nel modo migliore, e di come non farlo. Oggi parlerò del secondo aspetto dell'aggiornamento massivo — l'attivazione dei trigger.

Ad esempio, sulla tabella in cui devi apportare delle modifiche, c'è un trigger malefico ON UPDATE, che trasferisce tutte le modifiche in qualche aggregato. E tu hai bisogno di aggiornare tutto (inizializzare un nuovo campo, ad esempio) in modo tale che questi aggregati non vengano toccati.

Perfetto, disattiviamo i trigger!

BEGIN;
  ALTER TABLE ... DISABLE TRIGGER ...;
  UPDATE ...; -- qui ci vorrà un bel po'
  ALTER TABLE ... ENABLE TRIGGER ...;
COMMIT;

In effetti, qui ci siamo — tutto è già in sospeso.

Perché ALTER TABLE applica AccessExclusive-lock, sotto cui nessuno in esecuzione in parallelo, nemmeno il semplice SELECT, potrà leggere nulla dalla tabella. Vale a dire, finché questa transazione non è conclusa, tutti coloro che vogliono anche solo "leggere" dovranno aspettare. E noi ricordiamo che UPDATE abbiamo un tempo lunghissimo…

Allora disattiviamo velocemente, poi riattiviamo rapidamente!

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

UPDATE ...;

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

Qui la situazione è già migliore, il tempo di attesa è notevolmente ridotto. Ma due problemi rovinano tutta la bellezza:

• ALTER TABLE stesso resta in attesa di tutte le altre operazioni sulla tabella, comprese quelle lunghe SELECT
• Finché il trigger è spento, qualsiasi modifica nella tabella, anche se non nostra, "sfuggirà". E agli aggregati non entrerà, anche se dovrebbe. Che guaio!

Gestione delle variabili di sessione

Quindi, nell'opzione precedente ci siamo imbattuti in un punto fondamentale: dobbiamo insegnare al trigger a differenziare le modifiche "nostre" nella tabella da quelle "non nostre". Le "nostre" devono passare come tali, mentre le "non nostre" devono attivarsi. Per questo, possiamo avvalerci delle variabili di sessione.

session_replication_role

Leggiamo il manuale:

Il meccanismo di attivazione dei trigger è influenzato anche dalla variabile di configurazione session_replication_role. I trigger attivati senza ulteriori specifiche (per impostazione predefinita) si attiveranno quando il ruolo di replica è "origin" (per impostazione predefinita) o "local". I trigger attivati con l'indicazione ENABLE REPLICA, si attiveranno solo se la modalità corrente della sessione è "replica", mentre i trigger attivati con l'indicazione ENABLE ALWAYS, si attiveranno indipendentemente dalla modalità corrente di replica.

Sottolineo in particolare che l'impostazione si applica non a tutti quanti insieme, come ALTER TABLE, ma solo alla nostra connessione speciale. In sintesi, per evitare che si attivino trigger applicativi:

SET session_replication_role = replica; -- disattivare i trigger
UPDATE ...;
SET session_replication_role = DEFAULT; -- ripristinato allo stato originale

Condizione dentro il trigger

Ma l'opzione sopra funziona per tutti i trigger contemporaneamente (oppure bisogna 'modificare' preventivamente i trigger che non vogliamo disattivare). E se vogliamo disattivare un singolo trigger specifico?

In questo ci aiuterà una variabile di sessione 'personalizzata':

I nomi dei parametri delle estensioni vengono scritti nel seguente modo: nome dell'estensione, punto e poi il nome del parametro, simile ai nomi completi degli oggetti in SQL. Ad esempio: plpgsql.variable_conflict.
Poiché i parametri non di sistema possono essere impostati in processi che non caricano il corrispondente modulo di estensione, PostgreSQL accetta valori per qualsiasi nome composto da due componenti.

Iniziamo a modificare il trigger, più o meno così:

BEGIN
    -- nel processo di conversione si può fare tutto
    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 'in diretta', senza blocchi, tramite CREATE OR REPLACE per la funzione del trigger. E poi nella connessione speciale impostiamo la nostra variabile:

SET mycfg.my_table_convert_process = 'TRUE';
UPDATE ...;
SET mycfg.my_table_convert_process = ''; -- ripristinato allo stato originale

Conoscete altri modi? Condividete nei commenti.

Fonte: habr.com