Postgresql pglogical订阅端触发器如何控制

夏侯阳

2023-12-01

如果我们使用pglogical，表上有触发器，在复制的时候，肯定会考虑复制的时候触发器是否触发，根据官方文档，这个是可以自己控制的。如下：

alter table xxxxx DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER
该命令会加一个SHARE ROW EXCLUSIVE锁。
表上的触发器由session_replication_role参数控制，值可以是 origin (默认), replica和local，分别有以下几种情况

如果不写 [ REPLICA | ALWAYS ] ，那么就是default,按官方文档，如果表为default,
session_replication_role参数为origin，local，触发器是会触发的，但是我实验结果是不触发的
如果表设置为replica,参数session_replication_role为replica的时候才触发
如果表设置为always,参数session_replication_role不管为什么，都会触发

以下为实例：
建测试环境，这里使用的pglogical插件，原生的pub/sub还没有测试

#建测试表
create table  tbl_hank_trigger (a bigint primary key,b text);
CREATE TABLE tbl_trigger_record(
    operation         text   NOT NULL,
    create_time             timestamp NOT NULL,
    userid            text      NOT NULL,
    a           bigint     NOT NULL,
    b       text
);

#建触发器函数，作用为记录tbl_hank_trigger的dml,并写入tbl_trigger_record表
CREATE OR REPLACE FUNCTION f_trigger_audit() RETURNS TRIGGER AS $$
    BEGIN
       
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO logic.tbl_trigger_record SELECT 'delete', now(), current_user, OLD.*;
        ELSIF (TG_OP = 'UPDATE') THEN
          INSERT INTO logic.tbl_trigger_record SELECT 'Update_delete', now(), current_user, OLD.*;
            INSERT INTO logic.tbl_trigger_record SELECT 'Update_insert', now(), current_user, NEW.*;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO logic.tbl_trigger_record SELECT 'insert', now(), current_user, NEW.*;
        END IF;
        RETURN NULL; 
    END;
$$ LANGUAGE plpgsql;

#建触发器
CREATE TRIGGER t_oper
AFTER INSERT OR UPDATE OR DELETE  ON tbl_hank_trigger FOR EACH ROW EXECUTE FUNCTION f_trigger_audit();

如果表设置为replica,参数session_replication_role为replica的时候才触发

#订阅端：session_replication_role设置为replica，而且修改表的tbl_hank_trigger为replica触发器才会触发，其他不触发
logic=> show session_replication_role ;
 session_replication_role 
--------------------------
 replica
(1 row)

logic=> alter table tbl_hank_trigger enable replica trigger t_oper ;
ALTER TABLE
logic=>  select * from tbl_hank_trigger;
 a |   b    
---+--------
 1 | banana
 2 | banana
(2 rows)

logic=> select * from tbl_trigger_record;
 operation |        create_time         |  userid  | a |   b    
-----------+----------------------------+----------+---+--------
 insert    | 2021-07-12 16:14:48.568678 | postgres | 2 | banana

#发布端插入一条数据
logic=# insert into logic.tbl_hank_trigger values (3,'banana');
INSERT 0 1
#订阅端触发器发出，也写入一条数据
logic=>  select * from tbl_hank_trigger;
 a |   b    
---+--------
 1 | banana
 2 | banana
 3 | banana
(3 rows)
#可见触发器已经触发写入相应表
logic=> select * from tbl_trigger_record;
 operation |        create_time         |  userid  | a |   b    
-----------+----------------------------+----------+---+--------
 insert    | 2021-07-12 16:14:48.568678 | postgres | 2 | banana
 insert    | 2021-07-12 16:37:04.515104 | postgres | 3 | banana
(2 rows)

#订阅端表修改为default,那么触发器不会触发
logic=> show session_replication_role ;
 session_replication_role 
--------------------------
 replica
(1 row)
logic=>  alter table tbl_hank_trigger enable  trigger t_oper ;
ALTER TABLE
logic=> select * from logic.tbl_trigger_record ;
 operation |        create_time         |  userid  | a |   b    
-----------+----------------------------+----------+---+--------
 insert    | 2021-07-12 16:14:48.568678 | postgres | 2 | banana
 insert    | 2021-07-12 16:37:04.515104 | postgres | 3 | banana
#发布端插入一条数据
logic=# insert into logic.tbl_hank_trigger values (5,'banana');
INSERT 0 1
logic=# select * from logic.tbl_trigger_record ;
 operation |        create_time         |  userid  | a |   b    
-----------+----------------------------+----------+---+--------
 insert    | 2021-07-12 16:12:10.225303 | postgres | 1 | banana
 insert    | 2021-07-12 16:14:48.567486 | postgres | 2 | banana
 insert    | 2021-07-12 16:37:04.494155 | postgres | 3 | banana
 insert    | 2021-07-12 17:35:21.716215 | postgres | 5 | banana
(5 rows)


#查看订阅端数据，触发器没有触发
logic=> select * from logic.tbl_hank_trigger;
 a |   b    
---+--------
 1 | banana
 2 | banana
 3 | banana
 5 | banana
(5 rows)

logic=> select * from logic.tbl_trigger_record ;
 operation |        create_time         |  userid  | a |   b    
-----------+----------------------------+----------+---+--------
 insert    | 2021-07-12 16:14:48.568678 | postgres | 2 | banana
 insert    | 2021-07-12 16:37:04.515104 | postgres | 3 | banana

如果表设置为always,参数session_replication_role不管为什么，都会触发


#订阅端查看触发器已触发
logic=>  alter table tbl_hank_trigger enable always trigger t_oper ;
ALTER TABLE
#发布端插入一条数据
logic=# insert into logic.tbl_hank_trigger values (6,'banana');
INSERT 0 1
#订阅端查看已经触发
logic=> select * from logic.tbl_trigger_record ;
 operation |        create_time         |  userid  | a |   b    
-----------+----------------------------+----------+---+--------
 insert    | 2021-07-12 16:14:48.568678 | postgres | 2 | banana
 insert    | 2021-07-12 16:37:04.515104 | postgres | 3 | banana
 insert    | 2021-07-12 17:43:48.640222 | postgres | 6 | banana
(4 rows)


#修改session_replication_role为local，并在发布端插入一条数据
logic=# insert into logic.tbl_hank_trigger values (7,'banana');
INSERT 0 1
postgres=# show session_replication_role ;
 session_replication_role 
--------------------------
 local
(1 row)
#订阅端查看已触发
postgres=# \c logic postgres 
You are now connected to database "logic" as user "postgres".
logic=# select * from logic.tbl_trigger_record ;
 operation |        create_time         |  userid  | a |   b    
-----------+----------------------------+----------+---+--------
 insert    | 2021-07-12 16:14:48.568678 | postgres | 2 | banana
 insert    | 2021-07-12 16:37:04.515104 | postgres | 3 | banana
 insert    | 2021-07-12 17:43:48.640222 | postgres | 6 | banana
 insert    | 2021-07-12 17:46:37.155429 | postgres | 7 | banana


#修改session_replication_role为origin，并在发布端插入一条数据
logic=# insert into logic.tbl_hank_trigger values (8,'banana');
INSERT 0 1
#订阅端查看
postgres=# \c logic postgres 
You are now connected to database "logic" as user "postgres".
logic=# show session_replication_role ;
 session_replication_role 
--------------------------
 origin
(1 row)

logic=# select * from logic.tbl_trigger_record ;
 operation |        create_time         |  userid  | a |   b    
-----------+----------------------------+----------+---+--------
 insert    | 2021-07-12 16:14:48.568678 | postgres | 2 | banana
 insert    | 2021-07-12 16:37:04.515104 | postgres | 3 | banana
 insert    | 2021-07-12 17:43:48.640222 | postgres | 6 | banana
 insert    | 2021-07-12 17:46:37.155429 | postgres | 7 | banana
 insert    | 2021-07-12 17:48:43.970513 | postgres | 8 | banana
(6 rows)

如果不写 [ REPLICA | ALWAYS ] ，那么就是default,按官方文档，如果表为default,
session_replication_role参数为origin，local，触发器是会触发的，但是我实验结果是不触发的

#订阅端表为default,session_replication_role为origin，发布端插入一条数据，并没有触发触发器。
logic=# insert into logic.tbl_hank_trigger values (9,'banana');
INSERT 0 1
#订阅端表改为default
logic=>  alter table tbl_hank_trigger enable  trigger t_oper ;
ALTER TABLE
logic=> select * from tbl_trigger_record ;
 operation |        create_time         |  userid  | a |   b    
-----------+----------------------------+----------+---+--------
 insert    | 2021-07-12 16:14:48.568678 | postgres | 2 | banana
 insert    | 2021-07-12 16:37:04.515104 | postgres | 3 | banana
 insert    | 2021-07-12 17:43:48.640222 | postgres | 6 | banana
 insert    | 2021-07-12 17:46:37.155429 | postgres | 7 | banana
 insert    | 2021-07-12 17:48:43.970513 | postgres | 8 | banana
(6 rows)

logic=> show session_replication_role ;
 session_replication_role 
--------------------------
 origin
(1 row)


#修改session_replication_role为local,表为default，不会触发触发器。
#发布端插入一条数据
logic=# insert into logic.tbl_hank_trigger values (10,'banana');
INSERT 0 1

#订阅端无变化
logic=> show session_replication_role ;
 session_replication_role 
--------------------------
 local
(1 row)

logic=> select * from tbl_trigger_record ;
 operation |        create_time         |  userid  | a |   b    
-----------+----------------------------+----------+---+--------
 insert    | 2021-07-12 16:14:48.568678 | postgres | 2 | banana
 insert    | 2021-07-12 16:37:04.515104 | postgres | 3 | banana
 insert    | 2021-07-12 17:43:48.640222 | postgres | 6 | banana
 insert    | 2021-07-12 17:46:37.155429 | postgres | 7 | banana
 insert    | 2021-07-12 17:48:43.970513 | postgres | 8 | banana
(6 rows)

参考：
https://www.postgresql.org/docs/devel/sql-altertable.html
https://www.postgresql.org/docs/devel/runtime-config-client.html#GUC-SESSION-REPLICATION-ROLE

Postgresql pglogical订阅端触发器如何控制

相关阅读

相关文章

相关问答

相关文档