PostgreSQL触发器未触发Python脚本问题排查请求

问题背景 #

我在一台服务器部署了Python监听脚本，期望另一台服务器上的PostgreSQL表被第三台服务器更新时，通过触发器通知触发该脚本。目前数据能正常复制到表中，但触发器通知未生效，相关代码如下：

触发器函数 #

CREATE OR REPLACE FUNCTION notify_changes_my_table()
RETURNS TRIGGER AS
$$
BEGIN
PERFORM pg_notify('my_channel',TG_TABLE_NAME);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

触发器定义 #

CREATE TRIGGER trigger_notify_changes_to_others
AFTER INSERT OR UPDATE OR DELETE ON my_table
FOR EACH ROW EXECUTE FUNCTION notify_changes_my_table();

数据更新代码（第三台服务器） #

with open('my_table.tsv', 'r',) as file:
    cursor.execute("BEGIN;")
    cursor.copy_expert(sql = f"COPY {table_name} FROM STDIN WITH DELIMITER E'	' CSV HEADER;",file=file)
cursor.execute("COMMIT;")

监听Python脚本（目标服务器） #

import psycopg2
import subprocess

connection = psycopg2.connect(
host =  "0.0.0.0",
dbname = "db0",
user = "postgres",
password = "password",
port    = "5432"
)

cursor = connection.cursor()
cursor.execute("LISTEN my_channel;")
connection.commit()

try:
    while True:
        if connection.poll():
            print("received notificaation")
            notify = connection.notifies.pop(0)
            subprocess.run(["touch","success.txt"])
finally:
    print("there was some error")

可能的原因及排查步骤 #

1. COPY操作不触发行级触发器 #

PostgreSQL的COPY命令默认不会触发FOR EACH ROW类型的触发器，你的触发器属于行级触发器，因此通过COPY导入数据时不会执行触发器函数。

• 验证：手动执行INSERT语句插入单条数据，查看监听脚本是否收到通知。如果手动插入能触发，说明问题出在COPY的特性上。
• 解决：
  • 改用语句级触发器（FOR EACH STATEMENT），在COPY完成后发送一次通知（仅告知表有变更，无法获取单条数据）：

    CREATE OR REPLACE FUNCTION notify_changes_my_table()
    RETURNS TRIGGER AS
    $$
    BEGIN
        PERFORM pg_notify('my_channel', TG_TABLE_NAME);
        RETURN NULL; -- 语句级触发器需返回NULL
    END;
    $$ LANGUAGE plpgsql;
    
    CREATE TRIGGER trigger_notify_changes_to_others
    AFTER INSERT OR UPDATE OR DELETE ON my_table
    FOR EACH STATEMENT EXECUTE FUNCTION notify_changes_my_table();
    

  • 或在COPY完成后手动调用触发器函数发送通知：

    # COPY执行完成后添加
    cursor.execute("SELECT notify_changes_my_table();")
    

2. 监听脚本的连接配置错误 #

• 检查psycopg2.connect中的host：0.0.0.0是服务绑定地址，不是数据库的实际IP，需替换为PostgreSQL服务器的真实IP/域名。
• 验证网络连通性：在监听脚本所在服务器执行以下命令，确认能正常连接数据库：

  psql -h <数据库真实IP> -U postgres -d db0
  

• 检查数据库权限：确认pg_hba.conf允许监听服务器的IP连接，且postgresql.conf中listen_addresses未限制为仅localhost。

3. 通知接收逻辑存在缺陷 #

connection.poll()的调用方式可能导致错过通知，建议改用select模块等待连接事件，修改监听脚本的循环逻辑：

import select
# ... 保留原有连接代码 ...
try:
    while True:
        # 等待连接有数据，超时设为1秒避免空轮询
        if select.select([connection], [], [], 1) == ([], [], []):
            continue
        connection.poll()
        while connection.notifies:
            notify = connection.notifies.pop(0)
            print(f"received notification: {notify.channel}, {notify.payload}")
            subprocess.run(["touch","success.txt"])
finally:
    connection.close()
    print("connection closed")

• 额外验证：在数据库中执行SELECT * FROM pg_listening_channels();，确认my_channel存在，说明LISTEN命令已生效。

4. 事务提交的潜在影响 #

虽然COPY操作在事务中提交，但AFTER类型触发器会在事务提交后发送通知。可在COPY执行完成后打印connection.status，确认事务已正常提交（状态应为psycopg2.extensions.STATUS_READY）。

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