PostgreSQL Antipatterns: ubah data melewati pemicu

Cepat atau lambat, banyak yang dihadapkan pada kebutuhan untuk secara besar-besaran memperbaiki sesuatu di tabel catatan. Aku sudah katakan padaku bagaimana melakukannya dengan lebih baik, dan bagaimana - lebih baik tidak melakukannya. Hari ini saya akan berbicara tentang aspek kedua dari pembaruan massal - tentang pemicu.

Misalnya, di atas meja tempat Anda perlu memperbaiki sesuatu, pemicu jahat digantung ON UPDATE, mentransfer semua perubahan ke beberapa agregat. Dan Anda perlu memperbarui semuanya (menginisialisasi bidang baru, misalnya) dengan sangat hati-hati agar agregat ini tidak terpengaruh.

Mari kita nonaktifkan pemicunya!

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

Sebenarnya, itu saja - semuanya menggantung.

Karena ALTER TABLE memaksakan Akses Eksklusif- kunci di mana tidak ada yang berjalan secara paralel, bahkan yang sederhana SELECT, tidak akan dapat membaca apa pun dari tabel. Artinya, hingga transaksi ini berakhir, setiap orang yang ingin “baca saja” akan menunggu. Dan kita ingat itu UPDATE kita punya panjang...

Mari kita matikan dengan cepat, lalu hidupkan dengan cepat!

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

UPDATE ...;

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

Di sini situasinya sudah lebih baik, waktu tunggunya jauh lebih sedikit. Tapi hanya dua masalah yang merusak semua keindahan:

• ALTER TABLE sendiri menunggu semua operasi lain di atas meja, termasuk yang panjang SELECT
• Saat pemicunya mati, "terbang melewati" setiap perubahan di meja, bahkan bukan milik kita. Dan itu tidak akan masuk ke agregat, meskipun seharusnya. Masalah!

Mengelola variabel sesi

Jadi, di versi sebelumnya, kami menemukan poin mendasar - entah bagaimana kami perlu mengajarkan pemicu untuk membedakan perubahan "kami" di tabel dari "bukan milik kami". "Milik kita" dilewati sebagaimana adanya, tetapi pada "bukan milik kita" mereka dipicu. Untuk ini, Anda dapat menggunakan variabel sesi.

sesi_replikasi_peran

Kami membaca panduan:

Mekanisme pemicu juga dipengaruhi oleh variabel konfigurasi sesi_replikasi_peran. Diaktifkan tanpa instruksi tambahan (default), pemicu akan diaktifkan saat peran replikasi adalah "asal" (default) atau "lokal". Pemicu diaktifkan dengan menentukan ENABLE REPLICA, hanya akan berfungsi jika mode sesi saat ini - "replika", dan pemicu diaktifkan dengan menentukan ENABLE ALWAYS, akan berfungsi terlepas dari mode replikasi saat ini.

Saya terutama akan menekankan bahwa pengaturan tidak berlaku untuk semua sekaligus, seperti ALTER TABLE, tetapi hanya untuk koneksi khusus kami yang terpisah. Secara total, agar tidak ada pemicu aplikasi yang berfungsi:

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

Kondisi di dalam pemicu

Tetapi opsi di atas berfungsi untuk semua pemicu sekaligus (atau Anda perlu "bergantian" pemicu terlebih dahulu yang tidak ingin Anda nonaktifkan). Dan jika kita membutuhkan "matikan" satu pemicu tertentu?

Ini akan membantu kita variabel sesi "pengguna".:

Nama parameter ekstensi ditulis sebagai berikut: nama ekstensi diikuti dengan titik, lalu nama parameter itu sendiri, mirip dengan nama lengkap objek di SQL. Misalnya: plpgsql.variable_conflict.
Karena opsi out-of-system dapat diatur dalam proses yang tidak memuat modul ekstensi yang sesuai, PostgreSQL menerimanya nilai untuk setiap nama dengan dua komponen.

Pertama, kami menyelesaikan pemicunya, 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;
...

Omong-omong, ini bisa dilakukan "untuk mendapat untung", tanpa memblokir, terus menerus CREATE OR REPLACE untuk fungsi pemicu. Dan kemudian dalam koneksi khusus kita mengokang variabel "kami":

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

Apakah Anda tahu cara lain? Bagikan di komentar.

Sumber: www.habr.com