PostgreSQL Antipatterns: thay đổi dữ liệu bỏ qua trình kích hoạt

Sớm hay muộn, nhiều người phải đối mặt với nhu cầu sửa chữa hàng loạt thứ gì đó trong bản ghi bảng. tôi đã cho tôi biết làm thế nào để làm điều đó tốt hơn, và làm thế nào - tốt hơn là không nên làm điều đó. Hôm nay tôi sẽ nói về khía cạnh thứ hai của bản cập nhật hàng loạt - về kích hoạt.

Ví dụ: trên một cái bàn mà bạn cần sửa một thứ gì đó, một bộ kích hoạt xấu xa bị treo ON UPDATE, chuyển tất cả các thay đổi sang một số tập hợp. Và bạn cần cập nhật mọi thứ (ví dụ: khởi tạo một trường mới) thật cẩn thận để các tập hợp này không bị ảnh hưởng.

Hãy vô hiệu hóa các kích hoạt!

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

Trên thực tế, đó là tất cả - mọi thứ đang treo.

Bởi vì ALTER TABLE áp đặt Truy cập độc quyền- một khóa mà không ai chạy song song, ngay cả một khóa đơn giản SELECT, sẽ không thể đọc bất cứ thứ gì từ bảng. Đó là, cho đến khi giao dịch này kết thúc, tất cả những người muốn thậm chí “chỉ đọc” sẽ đợi. Và chúng tôi nhớ rằng UPDATE chúng tôi có một thời gian dài ...

Hãy nhanh chóng tắt nó, sau đó nhanh chóng bật nó!

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

UPDATE ...;

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

Ở đây tình hình đã tốt hơn, thời gian chờ đợi ít hơn nhiều. Nhưng chỉ có hai vấn đề làm hỏng tất cả vẻ đẹp:

• ALTER TABLE chính nó chờ tất cả các thao tác khác trên bàn, bao gồm cả các thao tác dài SELECT
• Trong khi kích hoạt tắt "bay qua" bất kỳ thay đổi nào trong bảng, thậm chí không phải của chúng tôi. Và nó sẽ không đi vào tập hợp, mặc dù nó nên như vậy. Rắc rối!

Quản lý các biến phiên

Vì vậy, trong phiên bản trước, chúng tôi đã tình cờ phát hiện ra một điểm cơ bản - bằng cách nào đó chúng tôi cần dạy trình kích hoạt phân biệt các thay đổi của “của chúng tôi” trong bảng với “không phải của chúng tôi”. “Của chúng tôi” vẫn bị bỏ qua, nhưng trên “không phải của chúng tôi” thì chúng được kích hoạt. Đối với điều này, bạn có thể sử dụng biến phiên.

phiên_replication_role

Đọc thủ công:

Cơ chế kích hoạt cũng bị ảnh hưởng bởi biến cấu hình phiên_replication_role. Được bật mà không cần hướng dẫn bổ sung (mặc định), trình kích hoạt sẽ kích hoạt khi vai trò sao chép là "gốc" (mặc định) hoặc "cục bộ". Kích hoạt kích hoạt bằng cách chỉ định ENABLE REPLICA, sẽ chỉ hoạt động nếu chế độ phiên hiện tại - "bản sao" và kích hoạt kích hoạt bằng cách chỉ định ENABLE ALWAYS, sẽ hoạt động bất kể chế độ sao chép hiện tại.

Tôi sẽ đặc biệt nhấn mạnh rằng cài đặt không áp dụng cho tất cả cùng một lúc, vì ALTER TABLE, nhưng chỉ với kết nối đặc biệt riêng biệt của chúng tôi. Tổng cộng, để không có trình kích hoạt ứng dụng nào hoạt động:

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

Điều kiện bên trong kích hoạt

Tuy nhiên, tùy chọn trên hoạt động cho tất cả các trình kích hoạt cùng một lúc (hoặc bạn cần "thay thế" trước các trình kích hoạt mà bạn không muốn tắt). Và nếu chúng ta cần "tắt" một trình kích hoạt cụ thể?

Điều này sẽ giúp chúng tôi biến phiên "người dùng":

Tên tham số phần mở rộng được viết như sau: tên phần mở rộng theo sau bởi dấu chấm và sau đó là chính tên tham số, tương tự như tên đối tượng đầy đủ trong SQL. Ví dụ: plpgsql.variable_conflict.
Vì các tùy chọn ngoài hệ thống có thể được đặt trong các quy trình không tải mô-đun mở rộng thích hợp, PostgreSQL chấp nhận giá trị cho bất kỳ tên nào có hai thành phần.

Đầu tiên, chúng tôi hoàn thiện trình kích hoạt, đại loại như thế này:

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

Nhân tiện, điều này có thể được thực hiện "vì lợi nhuận", mà không bị chặn, thông qua CREATE OR REPLACE cho chức năng kích hoạt. Và sau đó, trong kết nối đặc biệt, chúng tôi chọn biến "của chúng tôi":

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

Bạn có biết những cách khác? Chia sẻ trong các ý kiến.

Nguồn: www.habr.com