PostgreSQL Antipatterns: shandura data nekupfuura chinokonzeresa

Nenguva isipi, vazhinji vakatarisana nekudiwa kwekugadzirisa zvakanyanya chimwe chinhu mumarekodhi etafura. Ndatova nayo akandiudza kuti ndozviita sei zvirinani, uye sei - zviri nani kusazviita. Nhasi ndichataura nezve chechipiri chikamu chekuvandudzwa kwevanhu vakawanda - nezvezvinokonzeresa.

Somuenzaniso, patafura yaunoda kugadzirisa chimwe chinhu, chinokonzera chakaipa chinorembera ON UPDATE, kuendesa shanduko dzese kune mamwe aggregates. Uye iwe unofanirwa kugadzirisa zvese (kutanga iyo itsva ndima, semuenzaniso) nekuchenjera kuti aya akaunganidzwa asakanganiswe.

Ngatingodzima zvinokonzeresa!

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

Chaizvoizvo, ndizvo chete - zvese zvatorembera.

Nokuti ALTER TABLE zvinomanikidza AccessExclusive-kichi pasi pacho pasina ari kumhanya zvakafanana, kunyangwe iri nyore SELECT, havazokwanisi kuverenga chero chinhu kubva patafura. Ndiko, kusvikira kutengeserana uku kwapera, munhu wose anoda kunyange "kuverenga chete" achamirira. Uye tinorangarira izvozvo UPDATE tine nguva refu...

Ngatichimbidzei nekukasira, tobva tabatidza nekukasira!

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

UPDATE ...;

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

Pano mamiriro acho atova nani, nguva yekumirira yakanyanya kuderera. Asi matambudziko maviri chete anoshatisa runako rwese:

• ALTER TABLE pachayo inomirira mamwe mabasa ese ari patafura, kusanganisira akareba SELECT
• Panguva iyo trigger yakadzimwa "bhururuka ne" chero shanduko patafura, kunyange neyedu. Uye hazvipindi mumayuniti, kunyangwe zvichifanira. Dambudziko!

Kugadzirisa Session Variables

Saka, mushanduro yapfuura, takagumburwa pane chinhu chakakosha - isu tinofanirwa kudzidzisa chinokonzeresa kusiyanisa shanduko "dzedu" mutafura kubva "kwete yedu". "Yedu" inosvetuka sezvairi, uye "kwete yedu" inotangwa. Nokuda kweizvi unogona kushandisa session variables.

session_replication_role

Kuverenga manual:

Iyo trigger mechanism inobatwawo neiyo configuration variable session_replication_role. Inogoneswa pasina mimwe mirairo (default), zvinokonzeresa zvichapisa kana basa rekudzokorora riri "mabviro" (default) kana "yenzvimbo". Zvikonzero zvinogoneswa nekutsanangura ENABLE REPLICA, zvichashanda chete kana yazvino sesion mode - "replica", uye zvinokonzeresa zvinogoneswa nekutsanangura ENABLE ALWAYS, ichakonzerwa pasinei nezvino yekudzokorora modhi.

Ini ndichanyanya kusimbisa kuti marongero haashande kune vese-zvose kamwechete, se ALTER TABLE, asi chete kune yedu yakasiyana yakakosha kubatana. Pakazara, kuitira kuti pasave nezvinokonzeresa application zvinokonzeresa:

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

Condition mukati me trigger

Asi sarudzo iri pamusoro inoshanda kune zvese zvinokonzeresa kamwechete (kana iwe unofanirwa "kuchinjira" zvinokonzeresa zvisati zvaitika zvausingade kudzima). Uye kana tichida "dzima" imwe chaiyo inokonzeresa?

Izvi zvichatibatsira "mushandisi" chikamu chinosiyana:

Extension parameter mazita akanyorwa sezvinotevera: zita rekuwedzera rinoteverwa nedoti uye ipapo zita reparameter pacharo, rakafanana nemazita echinhu chakazara muSQL. Somuenzaniso: plpgsql.variable_conflict.
Nekuti kunze-kwe-system sarudzo dzinogona kusetwa mumaitiro asingaregi akakodzera ekuwedzera module, PostgreSQL inobvuma kukosha kune chero mazita ane zvikamu zviviri.

Kutanga, tinopedzisa trigger, chimwe chinhu chakadai:

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

Nenzira, izvi zvinogona kuitwa "kuitira purofiti", pasina kuvhara, kuburikidza CREATE OR REPLACE nokuda kweiyo trigger basa. Uye zvakare mukubatana kwakakosha isu jongwe "yedu" kusiyanisa:

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

Unoziva dzimwe nzira here? Govera mumhinduro.

Source: www.habr.com