PostgreSQL通过外部表操作时触发器未触发问题排查

我有两个PostgreSQL数据库DB1和DB2：

• DB1中的test_trg_tbd表存储业务记录，同库的test_trg_tbd2表由test_trg_tbd上的触发器维护，用于存储删除记录的归档数据
• 直接在DB1上对test_trg_tbd执行Insert/Update/Delete操作时，触发器能正常触发并更新test_trg_tbd2
• 但在DB2中创建test_trg_tbd的外部表后，通过该外部表删除test_trg_tbd的记录时，DB1上的触发器未触发，test_trg_tbd2没有新增归档数据

以下是测试场景的示例代码：

DB1上的操作 #

-- 创建测试表
create table test_trg_tbd( a integer , b varchar(10) );
create table test_trg_tbd2( a integer , b varchar(10) );

-- 创建触发器函数
CREATE OR REPLACE FUNCTION public.fn_test_trg_tbd()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
BEGIN
    if TG_OP = 'INSERT' then
    elsif TG_OP = 'UPDATE' then
    elsif TG_OP = 'DELETE' then
        insert into test_trg_tbd2 values( old.a, old.b );
    end if;
    RETURN NEW;
EXCEPTION
        when others then
                RETURN NEW;
END;
$function$;

-- 创建行级触发器
create or replace trigger trg_test_trg_tbd AFTER INSERT OR DELETE OR UPDATE ON test_trg_tbd FOR EACH ROW EXECUTE FUNCTION fn_test_trg_tbd();

-- 插入测试数据
insert into test_trg_tbd values(1, 'Matasya');
insert into test_trg_tbd values(2, 'Kurma');
insert into test_trg_tbd values(3, 'Varaha');

-- 查询初始数据
select * from test_trg_tbd;
-- 结果：
--  a |  b
---+------
--  1 | Matasya
--  2 | Kurma
--  3 | Varaha
-- (3 rows)

select * from test_trg_tbd2;
-- 结果：
--  a | b
---+---
-- (0 rows)

-- 直接在DB1删除记录
delete from test_trg_tbd where a = 1;

select * from test_trg_tbd;
-- 结果：
--  a |  b
---+------
--  2 | Kurma
--  3 | Varaha
-- (2 rows)

select * from test_trg_tbd2;
-- 结果：
--  a | b
---+---
--  1 | Matasya
-- (1 row)

DB2上的操作 #

-- 创建指向DB1的外部表
create foreign table test_trg_tbd_ft( a integer , b varchar(10) ) SERVER db1_ft
OPTIONS (schema_name 'public', table_name 'test_trg_tbd');

-- 查询外部表数据
select * from test_trg_tbd_ft;
-- 结果：
--  a |  b
---+------
--  2 | Kurma
--  3 | Varaha
-- (2 rows)

-- 通过外部表删除记录
delete from test_trg_tbd_ft where a = 2;

回到DB1验证结果 #

select * from test_trg_tbd;
-- 结果：
--  a |  b
---+------
--  3 | Varaha
-- (1 row)

select * from test_trg_tbd2;
-- 结果：
--  a | b
---+---
--  1 | Matasya
-- (1 row)

这不是你的代码错误，而是PostgreSQL外部表（Foreign Data Wrapper，FDW）的默认行为：

• 当通过FDW执行删除/更新操作时，默认采用批量操作模式，会绕过目标表的行级触发器（FOR EACH ROW）
• 只有当FDW的batch_size参数设置为1时，才会强制逐行执行操作，触发行级触发器
• 你的触发器是AFTER FOR EACH ROW类型，批量操作不会触发这类触发器

1. 修改FDW的批量操作参数：在创建外部表时，添加batch_size '1'选项，强制逐行执行操作：

create foreign table test_trg_tbd_ft( a integer , b varchar(10) ) SERVER db1_ft
OPTIONS (schema_name 'public', table_name 'test_trg_tbd', batch_size '1');

或者修改已有的外部表：

ALTER FOREIGN TABLE test_trg_tbd_ft OPTIONS (SET batch_size '1');

2. 改用语句级触发器：如果业务允许，将行级触发器改为FOR EACH STATEMENT的语句级触发器，批量操作会触发这类触发器。注意语句级触发器无法直接获取OLD/NEW行数据，需要通过系统表或临时表捕获变更数据，适合聚合统计类场景。

内容的提问来源于stack exchange，提问作者Anuj Sharma