Antipatterns di PostgreSQL: modificare i dati aggirando il trigger

Prima o poi, molti si trovano nella necessità di correggere massivamente le registrazioni di una tabella. Ho già parlato di come farlo nel modo migliore, e di come — non farlo affatto. Oggi parlerò del secondo aspetto dell'aggiornamento di massa — l'attivazione dei trigger.

Per esempio, su una tabella, dove devi correggere qualcosa, pende un temuto trigger ON UPDATE, che trasferisce tutte le modifiche in alcuni aggregati. E tu devi aggiornare tutto (inizializzare un nuovo campo, per esempio) in modo tale che questi aggregati non vengano toccati.

Facciamo così, disattiviamo i trigger!

BEGIN;
  ALTER TABLE ... DISABLE TRIGGER ...;
  UPDATE ...; -- qui ci vorrà tempo
  ALTER TABLE ... ENABLE TRIGGER ...;
COMMIT;

In sostanza, questo è tutto — è già presente.

Perché ALTER TABLE impone AccessExclusive-la blocco, sotto la quale nessuno, nemmeno un'operazione semplice in parallelo, SELECT, potrà leggere nulla dalla tabella. Cioè, finché questa transazione non terminerà, chiunque voglia anche solo «leggere» dovrà aspettare. E noi ricordiamo che UPDATE abbiamo un’operazione molto l-unga…

Allora velocemente disattiviamo, poi velocemente riattiviamo!

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

UPDATE ...;

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

Qui la situazione è già migliore, i tempi di attesa sono significativamente più brevi. Ma ci sono due problemi che rovinano tutto:

• ALTER TABLE sta aspettando tutte le altre operazioni sulla tabella, comprese le lunghe SELECT
• Finché il trigger è disattivato, qualsiasi modifica passerà inosservata nella tabella, anche quelle non nostre. E non entrerà negli aggregati, anche se dovrebbe. Un vero peccato!

Gestione delle variabili di sessione

Quindi, nella versione precedente abbiamo affrontato un aspetto fondamentale: dobbiamo in qualche modo insegnare al trigger a distinguere le modifiche "nostre" nella tabella dalle "non nostre". Dobbiamo passare le "nostre" così come sono, mentre per le "non nostre" deve attivarsi. Per questo possiamo utilizzare le variabili di sessione.

session_replication_role

Leggiamo manuale:

Il meccanismo di attivazione dei trigger è influenzato anche da una variabile di configurazione session_replication_role. I trigger attivati senza ulteriori indicazioni (per impostazione predefinita) si attiveranno quando il ruolo di replica è "origin" (predefinito) o "local". I trigger attivati specificando ENABLE REPLICA, si attiveranno solo se la modalità corrente della sessione è "replica", e i trigger attivati specificando ENABLE ALWAYS, si attiveranno indipendentemente dalla modalità attuale di replica.

Voglio sottolineare che la configurazione non riguarda tutti immediatamente, ma solo il nostro specifico collegamento speciale. In sintesi, per non attivare nessun trigger applicativo: ALTER TABLESET session_replication_role = replica; -- disattiviamo i trigger UPDATE ...; SET session_replication_role = DEFAULT; -- ripristinato allo stato originale

Condizione all'interno del trigger

Ma la versione sopra funziona per tutti i trigger contemporaneamente (oppure bisogna "alterare" prima i trigger che non si desidera disattivare). E se abbiamo bisogno di

"disattivare" un singolo trigger specifico "variabile" di sessione personalizzata?

In questo ci aiuterà 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 sistemici possono essere impostati nei processi che non caricano il modulo di estensione corrispondente, PostgreSQL accetta
valori per qualsiasi nome con due componenti Prima raffiniamo il trigger, più o meno così:.

BEGIN -- il processo di conversione 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; ...

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

Inoltre, è possibile farlo «in tempo reale», senza bloccaggi, attraverso CREATE OR REPLACE per la funzione trigger. Poi, nel connessione speciale, attiviamo la nostra variabile:

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

Conoscete altri metodi? Condividete nei commenti.

Fonte: habr.com