I-ProHoster > Блог > Ukuphatha > Ama-Antipatterns e-PostgreSQL: shintsha idatha ngokudlula i-trigger

Ama-Antipatterns e-PostgreSQL: shintsha idatha ngokudlula i-trigger

Ngokushesha noma kamuva, abaningi babhekene nesidingo sokulungisa okuthile emarekhodini etafula. Senginakho ngitshele ukuthi ngingayenza kanjani kangcono, futhi kanjani - kungcono ukungakwenzi. Namuhla ngizokhuluma ngesici sesibili sokuvuselelwa kwenqwaba - mayelana nezibangeli.

Isibonelo, etafuleni lapho udinga ukulungisa okuthile, i-trigger embi ilenga ON UPDATE, idlulisela zonke izinguquko kwezinye izilinganiso. Futhi udinga ukubuyekeza yonke into (ukuqalisa inkambu entsha, isibonelo) ngokucophelela ukuze lezi zibalo zingathinteki.

Asivele sikhubaze izibangeli!

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

Empeleni, yilokho kuphela - konke kulenga.

Ngoba ALTER TABLE kubeka Finyelela Okukhethekile- Ilokhi okungekho muntu ogijima ngaphansi kwayo ngokufana, ngisho nelula SELECT, ngeke akwazi ukufunda noma yini etafuleni. Okusho ukuthi, kuze kuphele lokhu kuthengiselana, wonke umuntu ofuna ngisho "ukufunda nje" uzolinda. Futhi siyakukhumbula lokho UPDATE sinesikhathi eside...

Masiyivale ngokushesha, bese siyivula ngokushesha!

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

UPDATE ...;

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

Lapha isimo sesingcono, isikhathi sokulinda sincane kakhulu. Kodwa izinkinga ezimbili kuphela zonakalisa bonke ubuhle:

  • ALTER TABLE yona ngokwayo ilinda yonke eminye imisebenzi esetafuleni, kuhlanganise nezinde SELECT
  • Ngenkathi i-trigger ivaliwe, "ndiza" nganoma yiluphi ushintsho etafuleni, ngisho nelethu. Futhi ngeke ingene kuma-aggregates, nakuba kufanele. Inkinga!

Ukuphatha okuguquguqukayo kweseshini

Ngakho-ke, enguqulweni edlule, sifike ephuzwini elibalulekile - sidinga ukufundisa isiqalisi ngandlela thize ukuhlukanisa izinguquko "zethu" etafuleni kokuthi "hhayi eyethu". “Okwethu” kweqiwa njengoba kwenzeka, kodwa kokuthi “hhayi okwethu” kubangelwa. Ukuze lokhu ungasebenzisa okuguquguqukayo kweseshini.

indima_yokuphindaphinda_indima

Funda imanuwali:

I-trigger mechanism nayo ithintwa ukuguquguquka kokucushwa indima_yokuphindaphinda_indima. Kunikwe amandla ngaphandle kwemiyalelo eyengeziwe (okuzenzakalelayo), izingcipho zizoqhuma lapho indima yokuphindaphinda "iyimvelaphi" (okuzenzakalelayo) noma "indawo". Izibangeli zinikwe amandla ngokucacisa ENABLE REPLICA, izosebenza kuphela uma imodi yeseshini yamanje - "i-replica", nezicupha zinikwe amandla ngokucacisa ENABLE ALWAYS, izosebenza ngokunganaki imodi yamanje yokuphindaphinda.

Ngizogcizelela ngokukhethekile ukuthi ukulungiselelwa akusebenzi kukho konke ngesikhathi esisodwa, njengalokhu ALTER TABLE, kodwa kuphela ekuxhumaneni kwethu okukhethekile okuhlukile. Sekukonke, ukuze kungabikho izibangeli zohlelo lokusebenza ezisebenzayo:

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

Isimo esingaphakathi kwe-trigger

Kodwa inketho engenhla isebenza kuzo zonke izibangeli ngesikhathi esisodwa (noma udinga "ukushintshanisa" izibangeli kusengaphambili ongafuni ukuzicisha). Futhi uma sidinga "vala" isibangeli esisodwa?

Lokhu kuzosisiza "umsebenzisi" okuguquguqukayo kweseshini:

Amagama epharamitha yesandiso abhalwe kanje: igama lesandiso lilandelwa ichashazi bese kuba igama lepharamitha ngokwalo, elifana namagama ezinto ezigcwele ku-SQL. Isibonelo: plpgsql.variable_conflict.
Ngenxa yokuthi izinketho ezingaphandle kwesistimu zingasethwa ezinqubweni ezingalayishi imojuli yesandiso efanele, i-PostgreSQL iyakwamukela amanani anoma yimaphi amagama anezingxenye ezimbili.

Okokuqala, siphothula i-trigger, into enjengale:

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

Ngendlela, lokhu kungenziwa "ngenzuzo", ngaphandle kokuvimbela, ngokusebenzisa CREATE OR REPLACE ngomsebenzi wokucupha. Bese-ke ekuxhumaneni okukhethekile sikhala "yethu" okuguquguqukayo:


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

Uyazazi ezinye izindlela? Yabelana kumazwana.

Source: www.habr.com

Engeza amazwana