I-PostgreSQL Antipatterns: tshintsha idatha ngokudlula i-trigger

Kungekudala okanye kamva, abantu abaninzi bajongene nesidingo sokulungisa into enkulu kwiirekhodi zetafile. Sele ndinayo undixelele indlela yokwenza ngcono, kwaye njani - kungcono ukuba ungakwenzi. Namhlanje ndiza kuthetha ngenqaku lesibini lohlaziyo oluninzi - malunga nezibangela.

Ngokomzekelo, etafileni apho kufuneka ulungise into ethile, kukho i-trigger embi ON UPDATE, idlulisela lonke utshintsho kwezinye iiaggregates. Kwaye kufuneka uhlaziye yonke into (ukuqalisa intsimi entsha, umzekelo) ngokucophelela ukuba ezi yunithi azichaphazeleki.

Masizicime ii-triggers!

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

Ngokwenyani, kuphelele apho- yonke into sele ijinga.

Ngenxa yokuba ALTER TABLE ibeka AccessExclusive-iqhaga ekungekho mntu ubalekayo phantsi kwalo, nokuba lilula SELECT, abayi kukwazi ukufunda nantoni na etafileni. Oko kukuthi, de kugqitywe le ntengiselwano, wonke umntu ofuna "ukufunda nje" uya kulinda. Kwaye siyayikhumbula loo nto UPDATE sinexesha elide...

Masiyicime ngokukhawuleza, emva koko siyivule ngokukhawuleza!

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

UPDATE ...;

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

Apha imeko sele ingcono, ixesha lokulinda lincinci kakhulu. Kodwa zimbini kuphela iingxaki ezonakalisa bonke ubuhle:

• ALTER TABLE ngokwayo ulinda yonke eminye imisebenzi phezu kwetafile, kuquka elide SELECT
• Ngelixa i-trigger ivaliwe, naluphi na utshintsho "luya kubhabha" etafileni, hayi neyethu. Kwaye ayiyi kungena kwiiyunithi, nangona kufanelekile. Ingxaki!

Ukulawula iiNguqulelo zeSeshini

Ke, kwinguqulelo yangaphambili siye safumana inqaku elisisiseko - kufuneka ngandlel' ithile sifundise umqalisi ukwahlula utshintsho "lwethu" kwitafile ukusuka "hayi eyethu". “Eyethu” ifanele idlulwe njengoko injalo, yaye “ingabi eyethu” iya kuxhonywa. Kule nto ungasebenzisa iinguqu zeseshoni.

iseshoni_yokuphindaphinda_indima

Siyafunda incwadi yesandla:

Isixhobo sokuxhobisa sikwachatshazelwa kukuguquguquka kobumbeko iseshoni_yokuphindaphinda_indima. Xa yenziwe yasebenza ngaphandle kweminye imiyalelo (ehlala ikho), izitshisi ziya kudubula xa indima yophindaphindo “imvelaphi” (ehlala ikho) okanye “indawo”. Izichukumisi zenziwe ngemiyalelo ENABLE REPLICA, iya kusebenza kuphela ukuba imo yeseshoni yangoku - "ireplica", kunye nezivuseleli zenziwe ukuba zikwazi ukucacisa ENABLE ALWAYS, iyakuqhutywa nokuba ithini imo yophindaphindo yangoku.

Ndingathanda ukugxininisa ngokukodwa ukuba ukusetwa akusebenzi kuye wonke umntu ngexesha elinye, njengoko ALTER TABLE, kodwa kuphela kuqhagamshelo lwethu olulodwa olulodwa. Lilonke, ukuze kungabikho zichukumisi zesicelo zivuswayo:

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

Imeko ngaphakathi kwesiqhushumbisi

Kodwa olu khetho lungasentla lusebenza kuzo zonke izinto ezibangelayo ngaxeshanye (okanye kufuneka "utshintshe" kwangaphambili izinto ezibangela ukuba ungafuni ukukhubaza). Kwaye ukuba sifuna "cima" enye into ethile?

Oku kuya kusinceda "Umsebenzisi" iseshoni eguquguqukayo:

Amagama eparameter yolwandiso abhalwe ngolu hlobo lulandelayo: igama lolwandiso, ichaphaza, kwaye ke igama lepharamitha ngokwalo, lifana namagama ezinto eziqeqeshwe ngokupheleleyo kwiSQL. Umzekelo: plpgsql.variable_conflict.
Ekubeni ukhetho olungelulo lwenkqubo lunokusetwa kwiinkqubo ezingalayishi imodyuli yolwandiso oluhambelanayo, iPostgreSQL iyayamkela amaxabiso awo nawaphi na amagama anamalungu amabini.

Okokuqala silungisa i-trigger, into efana nale:

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, oku kunokwenziwa "bukhoma", ngaphandle kokuthintela, ngokusebenzisa CREATE OR REPLACE kumsebenzi wetrigger. Kwaye ke kunxibelelwano olukhethekileyo siseta "yethu" ukuguquguquka:

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

Ngaba uyazazi ezinye iindlela? Yabelana kwizimvo.

umthombo: www.habr.com