Antipattern PostgreSQL: menukar data memintas pencetus

Lambat laun, ramai yang berhadapan dengan keperluan untuk membetulkan sesuatu secara besar-besaran dalam rekod jadual. saya sudah beritahu saya bagaimana untuk melakukannya dengan lebih baik, dan bagaimana - adalah lebih baik untuk tidak melakukannya. Hari ini saya akan bercakap tentang aspek kedua kemas kini massa - tentang pencetus.

Sebagai contoh, di atas meja di mana anda perlu membetulkan sesuatu, pencetus jahat tergantung ON UPDATE, memindahkan semua perubahan kepada beberapa agregat. Dan anda perlu mengemas kini segala-galanya (memulakan medan baharu, contohnya) dengan berhati-hati supaya agregat ini tidak terjejas.

Mari kita lumpuhkan sahaja pencetus!

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

Sebenarnya, itu sahaja - semuanya tergantung.

Kerana ALTER TABLE mengenakan Akses Eksklusif- kunci di mana tiada siapa berjalan selari, walaupun yang mudah SELECT, tidak akan dapat membaca apa-apa daripada jadual. Iaitu, sehingga urus niaga ini tamat, semua orang yang mahu "baca sahaja" akan menunggu. Dan kami ingat itu UPDATE kami mempunyai masa yang panjang...

Mari cepat matikan, kemudian cepat hidupkan!

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

UPDATE ...;

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

Di sini keadaan sudah lebih baik, masa menunggu adalah lebih sedikit. Tetapi hanya dua masalah merosakkan semua keindahan:

• ALTER TABLE sendiri menunggu semua operasi lain di atas meja, termasuk yang panjang SELECT
• Semasa picu dimatikan "terbang dengan" sebarang perubahan dalam meja, bukan kita pun. Dan ia tidak akan masuk ke dalam agregat, walaupun ia sepatutnya. Masalah!

Mengurus pembolehubah sesi

Jadi, dalam versi sebelumnya, kami terjumpa satu perkara asas - entah bagaimana kita perlu mengajar pencetus untuk membezakan perubahan "kita" dalam jadual daripada "bukan milik kita". "Milik kami" dilangkau seperti sedia ada, tetapi pada "bukan milik kami" ia dicetuskan. Untuk ini anda boleh gunakan pembolehubah sesi.

sesi_replikasi_peranan

Baca manual:

Mekanisme pencetus juga dipengaruhi oleh pembolehubah konfigurasi sesi_replikasi_peranan. Didayakan tanpa arahan tambahan (lalai), pencetus akan menyala apabila peranan replikasi adalah "asal" (lalai) atau "tempatan". Pencetus didayakan dengan menentukan ENABLE REPLICA, akan berfungsi hanya jika mod sesi semasa - "replika", dan pencetus didayakan dengan menentukan ENABLE ALWAYS, akan berfungsi tanpa mengira mod replikasi semasa.

Saya terutamanya akan menekankan bahawa tetapan tidak terpakai kepada semua-semua sekali gus, sebagai ALTER TABLE, tetapi hanya untuk sambungan khas kami yang berasingan. Secara keseluruhan, supaya tiada aplikasi mencetuskan kerja:

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

Keadaan dalam pencetus

Tetapi pilihan di atas berfungsi untuk semua pencetus sekaligus (atau anda perlu "ganti" pencetus terlebih dahulu yang anda tidak mahu lumpuhkan). Dan jika kita perlukan "matikan" satu pencetus tertentu?

Ini akan membantu kita pembolehubah sesi "pengguna".:

Nama parameter sambungan ditulis seperti berikut: nama sambungan diikuti dengan titik dan kemudian nama parameter itu sendiri, serupa dengan nama objek penuh dalam SQL. Contohnya: plpgsql.variable_conflict.
Oleh kerana pilihan di luar sistem boleh ditetapkan dalam proses yang tidak memuatkan modul sambungan yang sesuai, PostgreSQL menerima nilai untuk mana-mana nama dengan dua komponen.

Mula-mula, kami memuktamadkan pencetus, seperti ini:

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

Dengan cara ini, ini boleh dilakukan "untuk keuntungan", tanpa menyekat, melalui CREATE OR REPLACE untuk fungsi pencetus. Dan kemudian dalam sambungan khas kami menjejak pembolehubah "kami":

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

Adakah anda tahu cara lain? Kongsi dalam komen.

Sumber: www.habr.com