PostgreSQL Antipatterns: sinthani deta podutsa choyambitsa

Posakhalitsa, ambiri akukumana ndi kufunikira kokonza kwambiri china chake patebulo. Ndatero kale ndiuzeni momwe ndingachitire bwino, ndi momwe - ndibwino kuti musachite. Lero ndilankhula za gawo lachiwiri lakusintha kwamisa - za zoyambitsa.

Mwachitsanzo, patebulo limene muyenera kukonza chinachake, choyambitsa choipa chimapachikidwa ON UPDATE, kusamutsa zosintha zonse kumagulu ena. Ndipo muyenera kusintha chilichonse (yambitsani gawo latsopano, mwachitsanzo) mosamala kwambiri kuti ma aggregates awa asakhudzidwe.

Tiyeni tingoyimitsa zoyambitsa!

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

Kwenikweni, ndizo zonse - zonse zikulendewera.

Chifukwa ALTER TABLE amakakamiza Access Exclusive- loko yomwe palibe amene akuthamanga mofanana, ngakhale yosavuta SELECT, sadzatha kuwerenga chilichonse patebulo. Ndiye kuti, mpaka ntchitoyi itatha, aliyense amene akufuna "kungowerenga" adzadikirira. Ndipo ife tikukumbukira izo UPDATE tili ndi nthawi ...

Tiyeni tizimitse mwachangu, kenako tiyatse mwachangu!

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

UPDATE ...;

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

Pano zinthu zili bwino kale, nthawi yodikira ndi yochepa kwambiri. Koma mavuto awiri okha amawononga kukongola konse:

• ALTER TABLE palokha imadikirira ntchito zina zonse patebulo, kuphatikiza zazitali SELECT
• Pamene choyambitsa chatsekedwa, "kuwuluka ndi" kusintha kulikonse mu gome, ngakhale yathu. Ndipo sizingalowe mumagulu, ngakhale ziyenera. Mavuto!

Kuwongolera magawo a magawo

Kotero, mu Baibulo lapitalo, tinapunthwa pa mfundo yofunikira - tiyenera mwanjira ina kuphunzitsa choyambitsa kusiyanitsa "zathu" zosintha pa tebulo ndi "osati zathu". "Zathu" zidalumphidwa monga zilili, koma "osati zathu" zimayambitsidwa. Kwa ichi mungagwiritse ntchito magawo osiyanasiyana.

gawo_replication_role

Werengani buku:

Makina oyambitsanso amakhudzidwanso ndi kusintha kwa kasinthidwe gawo_replication_role. Kuyatsidwa popanda malangizo owonjezera (osasintha), zoyambitsa zidzawomba ngati gawo lobwereza likhala "chiyambi" (chosasinthika) kapena "chapafupi". Zoyambitsa zimayatsidwa pofotokoza ENABLE REPLICA, idzagwira ntchito ngati panopa gawo mode - "replica", ndi zoyambitsa zomwe zimathandizidwa ndi kufotokoza ENABLE ALWAYS, idzagwira ntchito mosasamala kanthu za kubwereza komweku.

Nditsimikize makamaka kuti zoikamo sizikugwira ntchito kwa onse nthawi imodzi, monga ALTER TABLE, koma kungolumikizana kwathu kwapadera. Pazonse, kuti pasakhale zoyambitsa ntchito:

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

Mkhalidwe mkati mwa choyambitsa

Koma njira yomwe ili pamwambapa imagwira ntchito pazoyambitsa zonse nthawi imodzi (kapena muyenera "kusinthana" zoyambitsa pasadakhale zomwe simukufuna kuzimitsa). Ndipo ngati tikufuna "zimitsani" choyambitsa chimodzi?

Izi zidzatithandiza "user" gawo kusintha:

Mayina a parameter yowonjezera amalembedwa motere: dzina lowonjezera lotsatiridwa ndi dontho ndiyeno dzina la parameter palokha, lofanana ndi mayina azinthu zonse mu SQL. Mwachitsanzo: plpgsql.variable_conflict.
Chifukwa zosankha zakunja zimatha kukhazikitsidwa m'njira zomwe sizimakweza gawo loyenera lowonjezera, PostgreSQL imavomereza Makhalidwe a mayina aliwonse okhala ndi zigawo ziwiri.

Choyamba, timamaliza choyambitsa, monga chonchi:

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

Mwa njira, izi zikhoza kuchitika "chifukwa cha phindu", popanda kutsekereza, kupyolera CREATE OR REPLACE kwa ntchito yoyambitsa. Ndiyeno mu mgwirizano wapadera ife tambala "chathu" variable:

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

Kodi mukudziwa njira zina? Gawani nawo ndemanga.

Source: www.habr.com