解决AWS RDS PostgreSQL逻辑副本触发器只读事务插入失败问题

你在AWS RDS PostgreSQL的蓝绿部署（基于逻辑复制）场景下，给绿色环境的表A创建了BEFORE INSERT触发器，想同步插入数据到表B，但因为RDS仅允许会话级修改default_transaction_read_only，触发器函数里的SET default_transaction_read_only = off未生效，导致插入表B时触发ERROR: cannot execute INSERT in a read-only transaction错误。

以下是除应用端包裹读写事务外的解决方案：

方案1：在触发器函数内用SET LOCAL临时修改事务只读状态 #

PostgreSQL的SET LOCAL可以在当前事务范围内修改参数优先级，覆盖会话级设置。直接修改触发器函数，在插入表B前添加事务级的读写设置：

CREATE OR REPLACE FUNCTION insert_to_B ()
    RETURNS TRIGGER
    LANGUAGE plpgsql
AS $TRIG$
BEGIN
    IF TG_OP = 'INSERT' THEN
        -- 仅在当前事务内临时开启读写权限
        SET LOCAL default_transaction_read_only = off;
        INSERT INTO public.B VALUES (NEW.*);
        RETURN NEW;
    END IF;
END
$TRIG$;

这个设置只会影响当前触发器所在的事务，不会改变其他会话的只读状态，符合RDS的参数限制。

方案2：改用异步通知+后台进程实现跨事务插入 #

如果SET LOCAL仍无法绕过RDS的只读限制（比如部分只读副本强制锁定事务状态），可以将同步插入改为异步操作：

1. 修改触发器为发送通知，不直接执行插入：

CREATE OR REPLACE FUNCTION notify_insert_to_B ()
    RETURNS TRIGGER
    LANGUAGE plpgsql
AS $TRIG$
BEGIN
    IF TG_OP = 'INSERT' THEN
        -- 将新数据转为JSON发送到指定频道
        PERFORM pg_notify('insert_to_B_channel', row_to_json(NEW)::text);
        RETURN NEW;
    END IF;
END
$TRIG$;

CREATE OR REPLACE TRIGGER some_trigger
    AFTER INSERT ON A
    FOR EACH ROW
    EXECUTE FUNCTION notify_insert_to_B ();

2. 启动一个独立的后台进程（或用RDS的pg_cron定时任务配合监听逻辑），持续监听通知频道并执行插入：
   • 先在后台会话执行LISTEN insert_to_B_channel;
   • 编写脚本（如Python/Shell）循环接收通知，解析JSON后执行INSERT INTO public.B VALUES (...);

这种方式的插入操作在独立的读写会话中完成，完全不受原只读事务的限制，适合严格只读的RDS副本场景。

方案3：调整逻辑复制规则，直接同步到表B #

如果你的蓝绿部署逻辑复制是从蓝色主库同步到绿色副本，可以在发布端（蓝色环境）配置多表映射：

• 在蓝色主库的发布中，将表A的变更同时映射到绿色环境的表A和表B。PostgreSQL逻辑复制默认是一对一表映射，你可以通过创建自定义复制槽，或在订阅端使用pg_logical_slot_get_changes处理变更，将表A的INSERT事件转发到表B。

这种方案不需要触发器，直接通过逻辑复制链路实现数据同步，避免了只读事务的限制。

内容的提问来源于stack exchange，提问作者Vasileios Giannakidis