PostgreSQL Antipatterns: gegevens wijzigen zonder een trigger te omzeilen

Vroeg of laat worden velen geconfronteerd met de noodzaak om massaal iets in de tabelrecords op te lossen. ik heb al vertel me hoe ik het beter kan doen, en hoe - het is beter om het niet te doen. Vandaag zal ik het hebben over het tweede aspect van de massale update - over triggers.

Op een tafel waar je iets moet repareren, hangt bijvoorbeeld een kwaadaardige trigger ON UPDATE, alle wijzigingen overbrengen naar sommige aggregaten. En u moet alles zo zorgvuldig bijwerken (bijvoorbeeld een nieuw veld initialiseren) dat deze aggregaten niet worden beïnvloed.

Laten we gewoon de triggers uitschakelen!

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

Eigenlijk is dat alles - alles hangt.

Omdat ALTER TABLE oplegt Exclusief toegang- een sluis waaronder niemand parallel loopt, zelfs niet een simpele SELECT, kan niets van de tafel lezen. Dat wil zeggen, totdat deze transactie eindigt, zal iedereen die zelfs maar "gewoon wil lezen" wachten. En dat onthouden we UPDATE we hebben een lange...

Laten we het snel uitschakelen en dan snel weer aanzetten!

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

UPDATE ...;

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

Hier is de situatie al beter, de wachttijd is veel korter. Maar slechts twee problemen bederven al het moois:

• ALTER TABLE wacht zelf op alle andere bewerkingen op tafel, inclusief lange SELECT
• Terwijl de trigger is uitgeschakeld "vlieg voorbij" elke verandering in de tafel, zelfs niet de onze. En het komt niet in de aggregaten terecht, hoewel het wel zou moeten. Probleem!

Sessievariabelen beheren

Dus in de vorige versie stuitten we op een fundamenteel punt: we moeten de trigger op de een of andere manier leren om onderscheid te maken tussen 'onze' wijzigingen in de tabel en 'niet de onze'. "Ours" wordt overgeslagen zoals het is, maar op "not ours" worden ze geactiveerd. Hiervoor kun je gebruiken sessie variabelen.

sessie_replicatie_rol

We lezen handmatig:

Het triggermechanisme wordt ook beïnvloed door de configuratievariabele sessie_replicatie_rol. Ingeschakeld zonder aanvullende instructies (standaard), worden triggers geactiveerd wanneer de replicatierol "oorsprong" (standaard) of "lokaal" is. Triggers ingeschakeld door op te geven ENABLE REPLICA, werkt alleen als huidige sessiemodus - "replica", en triggers ingeschakeld door te specificeren ENABLE ALWAYS, werkt ongeacht de huidige replicatiemodus.

Ik zal vooral benadrukken dat de instelling niet van toepassing is op alles-alles tegelijk, zoals ALTER TABLE, maar alleen naar onze afzonderlijke speciale verbinding. In totaal, zodat geen applicatie-triggers werken:

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

Conditie binnen trekker

Maar de bovenstaande optie werkt voor alle triggers tegelijk (of u moet van tevoren triggers "afwisselen" die u niet wilt uitschakelen). En als we nodig hebben één specifieke trigger "uitzetten".?

Dit zal ons helpen sessievariabele "gebruiker".:

Extensieparameternamen worden als volgt geschreven: de extensienaam gevolgd door een punt en vervolgens de parameternaam zelf, vergelijkbaar met volledige objectnamen in SQL. Bijvoorbeeld: plpgsql.variabele_conflict.
Omdat opties buiten het systeem kunnen worden ingesteld in processen die niet de juiste uitbreidingsmodule laden, accepteert PostgreSQL waarden voor alle namen met twee componenten.

Eerst maken we de trigger af, zoiets als dit:

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

Dit kan trouwens "voor winst", zonder te blokkeren, worden gedaan CREATE OR REPLACE voor de triggerfunctie. En dan pikken we in de speciale verbinding "onze" variabele:

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

Ken jij andere manieren? Deel in de reacties.

Bron: www.habr.com