PostgreSQL 11中避免AFTER INSERT触发器因外部表不可用延迟的问询

嘿，你的场景太典型了——用postgres_fdw做远程备份，但同步触发器遇到远程故障就卡主表，这在生产环境里绝对是要避免的。咱们针对你的三个问题逐一解决：

1. 让查询先返回客户端，不阻塞后续插入 #

当然可以！核心思路是把同步的远程插入改成异步处理，触发器不再直接操作远程表，而是把数据暂存到本地队列，然后让后台进程去异步完成远程同步。这样主表的INSERT操作写完本地就返回，完全不受远程状态影响。

具体实现步骤： #

• 第一步：创建本地队列表，用来暂存待同步的数据

CREATE TABLE sync_queue (
    id SERIAL PRIMARY KEY,
    bar1 TEXT NOT NULL,
    bar2 TEXT NOT NULL,
    bar3 TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'pending' -- pending/processing/success/failed
);

• 第二步：修改触发器函数，只往本地队列写数据

CREATE OR REPLACE FUNCTION after_insert_queue() RETURNS TRIGGER AS $after_insert_queue$
BEGIN
    -- 只写入本地队列，瞬间完成，不阻塞主表插入
    INSERT INTO sync_queue(bar1, bar2, bar3) VALUES(NEW.bar1, NEW.bar2, NEW.bar3);
    RETURN NEW;
END;
$after_insert_queue$ LANGUAGE plpgsql;

-- 替换原触发器
DROP TRIGGER IF EXISTS after_insert ON my_table;
CREATE TRIGGER after_insert AFTER INSERT ON my_table FOR EACH ROW EXECUTE PROCEDURE after_insert_queue();

• 第三步：用后台进程处理队列
  你可以选几种方式实现后台同步：
  • 用pg_background扩展：在PostgreSQL内部启动后台任务执行同步
  • 写Python/Shell脚本，配合pg_cron或系统crontab定期轮询队列
  • 用LISTEN/NOTIFY：触发器发送通知，后台程序监听后实时处理

举个pg_background的示例（需先安装扩展：CREATE EXTENSION pg_background;）：

CREATE OR REPLACE FUNCTION sync_to_remote() RETURNS VOID AS $sync_to_remote$
DECLARE
    rec RECORD;
BEGIN
    -- 取未处理数据并加锁，避免重复执行
    FOR rec IN SELECT * FROM sync_queue WHERE status = 'pending' FOR UPDATE SKIP LOCKED LOOP
        BEGIN
            INSERT INTO foo(bar1, bar2, bar3) VALUES(rec.bar1, rec.bar2, rec.bar3);
            UPDATE sync_queue SET status = 'success' WHERE id = rec.id;
        EXCEPTION WHEN OTHERS THEN
            UPDATE sync_queue SET status = 'failed' WHERE id = rec.id;
            RAISE NOTICE 'Sync failed for queue id %: % %', rec.id, SQLSTATE, SQLERRM;
        END;
    END LOOP;
END;
$sync_to_remote$ LANGUAGE plpgsql;

-- 启动后台任务（可搭配pg_cron定时执行，比如每分钟一次）
SELECT pg_background_launch('SELECT sync_to_remote();');

2. 检测服务器有效性，标记不可用状态暂不尝试插入 #

完全可行，咱们可以维护一个远程状态表，结合定时探测动态调整同步行为：

具体实现： #

• 第一步：创建远程状态表

CREATE TABLE remote_server_status (
    server_id INT PRIMARY KEY DEFAULT 1,
    is_active BOOLEAN DEFAULT TRUE,
    last_check TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    error_msg TEXT
);
-- 初始化默认记录
INSERT INTO remote_server_status DEFAULT VALUES ON CONFLICT DO NOTHING;

• 第二步：写远程探测函数

CREATE OR REPLACE FUNCTION check_remote_status() RETURNS VOID AS $check_remote_status$
DECLARE
    test_result BOOL;
BEGIN
    BEGIN
        -- 用简单查询测试远程连接可用性
        SELECT 1 INTO test_result FROM foo LIMIT 1;
        UPDATE remote_server_status SET is_active = TRUE, error_msg = NULL, last_check = CURRENT_TIMESTAMP;
    EXCEPTION WHEN OTHERS THEN
        UPDATE remote_server_status SET is_active = FALSE, error_msg = SQLERRM, last_check = CURRENT_TIMESTAMP;
    END;
END;
$check_remote_status$ LANGUAGE plpgsql;

• 第三步：修改同步函数，先检查状态再执行

CREATE OR REPLACE FUNCTION sync_to_remote() RETURNS VOID AS $sync_to_remote$
DECLARE
    rec RECORD;
    is_active BOOLEAN;
BEGIN
    -- 先获取远程状态
    SELECT is_active INTO is_active FROM remote_server_status WHERE server_id = 1;
    IF NOT is_active THEN
        RAISE NOTICE 'Remote server is inactive, skipping sync';
        RETURN;
    END IF;

    -- 后续同步逻辑同之前
    FOR rec IN SELECT * FROM sync_queue WHERE status = 'pending' FOR UPDATE SKIP LOCKED LOOP
        BEGIN
            INSERT INTO foo(bar1, bar2, bar3) VALUES(rec.bar1, rec.bar2, rec.bar3);
            UPDATE sync_queue SET status = 'success' WHERE id = rec.id;
        EXCEPTION WHEN OTHERS THEN
            UPDATE sync_queue SET status = 'failed' WHERE id = rec.id;
            -- 同步失败后立即更新远程状态
            PERFORM check_remote_status();
            RAISE NOTICE 'Sync failed for queue id %: % %', rec.id, SQLSTATE, SQLERRM;
        END;
    END LOOP;
END;
$sync_to_remote$ LANGUAGE plpgsql;

• 第四步：定时探测状态（比如每30秒一次）

-- 先安装pg_cron扩展
CREATE EXTENSION pg_cron;
-- 配置定时任务
SELECT cron.schedule('check-remote-status', '*/30 * * * *', 'SELECT check_remote_status();');

3. 不影响主表插入、无连接延迟的最优方案 #

结合前面两点，最优方案是本地队列异步同步+远程状态自动探测+失败重试机制，完全隔离主表与远程操作：

完整流程： #

1. 主表INSERT触发触发器，数据写入本地队列（毫秒级完成，无阻塞）
2. 后台任务定时/实时检查远程状态：
   • 远程可用时，从队列读取数据尝试同步到远程
   • 远程不可用时，暂停同步，直到探测到恢复
3. 同步失败的数据标记为failed，可设置自动重试次数或手动处理
4. 可选：加死信队列，存储多次重试失败的数据，避免占用资源

额外优化点： #

• 队列表按日期分区，避免数据量过大影响性能
• 给队列表的status和created_at字段加索引，提升查询效率
• 同步函数用FOR UPDATE SKIP LOCKED，支持多进程并行处理队列

补充：你原来的触发器问题在于同步执行远程插入，一旦远程超时，整个INSERT事务都会被阻塞。改成异步队列后彻底解决了这个问题，同时状态检测避免了无效的连接尝试，减少不必要的资源消耗。

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