PostgreSQL Antipatterns: gegevens wijzigen om triggers te omzeilen

Vroeg of laat wordt iedereen geconfronteerd met de noodzaak om iets in de records van een tabel massaal te corrigeren. Ik heb al verteld hoe je dit het beste kunt doen, en hoe vooral niet. Vandaag zal ik het hebben over de tweede kant van massale updates — de uitvoering van triggers..

Bijvoorbeeld, op de tabel waarin je iets moet corrigeren, hangt er een kwade trigger ON UPDATE, die alle wijzigingen naar bepaalde aggregaten verplaatst. En je moet alles bijwerken (bijvoorbeeld een nieuw veld initialiseren) op een manier die deze aggregaten niet raakt.

Laten we gewoon de triggers uitschakelen!

BEGIN;
  ALTER TABLE ... DISABLE TRIGGER ...;
  UPDATE ...; -- hier duurt het lang
  ALTER TABLE ... ENABLE TRIGGER ...;
COMMIT;

Eigenlijk is dit alles — alles hangt al.

Want ALTER TABLE legt een AccessExclusive-de vergrendeling, waaronder niemand die parallel uitgevoerd wordt, zelfs een eenvoudige SELECT, kan niets uit de tabel lezen. Dat wil zeggen, totdat deze transactie is voltooid, moeten alle geïnteresseerden zelfs "gewoon lezen" wachten. En we herinneren ons dat UPDATE we een ve-e-eel te lange…

Laten we dan snel uitschakelen, en dan snel weer inschakelen!

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

UPDATE ...;

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

Hier is de situatie al beter, de wachttijd is aanzienlijk korter. Maar er zijn twee problemen die de hele schoonheid verpesten:

• ALTER TABLE de trigger wacht op alle andere operaties op de tabel, inclusief lange SELECT
• Zolang de trigger uitgeschakeld is, "ontglipt" elke wijziging in de tabel, zelfs niet onze. En het komt helemaal niet in de aggregaten, hoewel het zou moeten. Een probleem!

Beheer van sessievariabelen

Dus, in de vorige optie zijn we tegen een principieel punt aangelopen — we moeten de trigger leren om "onze" wijzigingen in de tabel te onderscheiden van "niet onze". "Onze" ongewijzigd doorlaten, en voor "niet onze" activeren. Hiervoor kunnen we gebruikmaken van sessievariabelen.

session_replication_role

Lees meer handleiding:

Het mechanisme voor het activeren van triggers wordt ook beïnvloed door de configuratievariabele session_replication_role. Triggers die zonder aanvullende instructies (standaard) zijn ingeschakeld, worden geactiveerd wanneer de replicatierol — "origin" (standaard) of "local". Triggers die met de aanwijzing ENABLE REPLICA, worden alleen geactiveerd als de huidige sessiemodus — "replica", en triggers die met de aanwijzing ENABLE ALWAYS, worden geactiveerd ongeacht de huidige replicatiemodus.

Ik benadruk dat deze instelling niet voor iedereen geldt, zoals ALTER TABLE, maar alleen naar onze aparte speciale verbinding. Dus, om te voorkomen dat er enige trigger-activering is:

SET session_replication_role = replica; -- triggers uitgeschakeld
UPDATE ...;
SET session_replication_role = DEFAULT; -- in oorspronkelijke staat teruggebracht

Voorwaarde binnen de trigger

Maar de bovenstaande variant werkt voor alle triggers tegelijk (of je moet van tevoren de triggers 'alteren' die je niet wilt uitschakelen). En als we moeten één specifieke trigger "uitschakelen"?

Daarbij helpt ons "gebruikers" sessievariabele:

De namen van de extensieparameters worden als volgt genoteerd: naam van de extensie, punt en dan de naam van de parameter, vergelijkbaar met de volledige namen van objecten in SQL. Bijvoorbeeld: plpgsql.variable_conflict.
Aangezien niet-systeemparameters kunnen worden ingesteld in processen die de overeenkomstige extensiemodule niet laden, accepteert PostgreSQL waarden voor elke naam met twee componenten.

Eerst werken we de trigger als volgt bij:

BEGIN
    -- in het conversieproces kan alles gedaan worden
    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;
...

Overigens, dit kan "live" gedaan worden, zonder blokkades, via CREATE OR REPLACE voor de triggerfunctie. En dan in de speciale verbinding zetten we onze "variabele":

SET mycfg.my_table_convert_process = 'TRUE';
UPDATE ...;
SET mycfg.my_table_convert_process = ''; -- in oorspronkelijke staat teruggebracht

Ken je andere manieren? Deel ze in de opmerkingen.

Bron: habr.com