PostgreSQL Antipatterns: změna dat obcházením spouštěče

Dříve nebo později se mnozí potýkají s potřebou něco masivně opravit v tabulkových záznamech. už mám řekni mi, jak to udělat lépe, a jak - je lepší to nedělat. Dnes budu mluvit o druhém aspektu hromadné aktualizace - o spouštěčích.

Například na stole, ve kterém potřebujete něco opravit, visí zlá spoušť ON UPDATE, přenášející všechny změny do některých agregátů. A vše musíte aktualizovat (například inicializovat nové pole) tak pečlivě, aby tyto agregáty nebyly ovlivněny.

Prostě deaktivujeme spouštěče!

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

Vlastně, to je vše- všechno visí.

Protože ALTER TABLE ukládá Exkluzivní přístup- zámek, pod kterým nikdo paralelně neběží, ani jednoduchý SELECT, nebude moci z tabulky nic přečíst. To znamená, že dokud tato transakce neskončí, každý, kdo chce „jen číst“, bude čekat. A my si to pamatujeme UPDATE máme dlouhou...

Pojďme to rychle vypnout, pak rychle zapnout!

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

UPDATE ...;

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

Zde je již situace lepší, čekací doba je mnohem kratší. Ale jen dva problémy kazí všechnu krásu:

• ALTER TABLE sám čeká na všechny ostatní operace na stole, včetně dlouhých SELECT
• Když je spoušť vypnutá "proletět" jakoukoli změnu v tabulce, ani naši. A nedostane se do agregátů, i když by měl. Problémy!

Správa proměnných relace

V předchozí verzi jsme tedy narazili na zásadní věc - musíme nějak naučit spoušť, aby rozlišoval „naše“ změny v tabulce od „ne naše“. „Naše“ jsou přeskočeny tak, jak jsou, ale na „ne naše“ jsou spuštěny. K tomu můžete použít proměnné relace.

session_replication_role

Přečtěte si manuál:

Spouštěcí mechanismus je také ovlivněn konfigurační proměnnou session_replication_role. Povoleno bez dalších pokynů (výchozí), spouštěče se spustí, když je role replikace „původní“ (výchozí) nebo „místní“. Spouštěče povoleny zadáním ENABLE REPLICA, bude fungovat pouze v případě aktuální režim relace - "replika" a spouštěče povolené zadáním ENABLE ALWAYS, bude fungovat bez ohledu na aktuální režim replikace.

Zvláště zdůrazním, že nastavení se nevztahuje na vše najednou, jako ALTER TABLE, ale pouze do našeho samostatného speciálního spojení. Celkem, takže žádné spouštěče aplikací nefungují:

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

Stav uvnitř spouště

Ale výše uvedená možnost funguje pro všechny spouštěče najednou (nebo musíte předem „střídat“ spouštěče, které nechcete deaktivovat). A pokud potřebujeme "vypnout" jednu konkrétní spoušť?

To nám pomůže "uživatelská" proměnná relace:

Názvy parametrů rozšíření se zapisují následovně: název rozšíření následovaný tečkou a poté samotný název parametru, podobně jako úplné názvy objektů v SQL. Například: plpgsql.variable_conflict.
Protože volby mimo systém lze nastavit v procesech, které nenačítají příslušný rozšiřující modul, PostgreSQL akceptuje hodnoty pro všechna jména se dvěma komponentami.

Nejprve dokončíme spouštěč, něco takového:

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

Mimochodem, lze to udělat "pro zisk", bez blokování, prostřednictvím CREATE OR REPLACE pro spouštěcí funkci. A pak ve speciálním spojení natáhneme "naši" proměnnou:

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

Znáte jiné způsoby? Podělte se v komentářích.

Zdroj: www.habr.com