PostgreSQL LISTEN/NOTIFY异常：需执行命令才接收通知

核心机制说明 #

PostgreSQL的LISTEN/NOTIFY并非主动推送消息到客户端，而是需要客户端主动轮询消息队列。默认的psql CLI不会自动执行轮询操作，只有当你发起SQL命令（比如SELECT）时，psql才会在处理请求的间隙拉取待处理的通知，这就是为什么你必须执行SELECT才能收到之前发送的消息。

psql调试时的实时接收方法 #

在监听端的psql会话中运行以下循环脚本，即可自动轮询并打印通知：

LISTEN live_data_update;
WHILE true LOOP
  -- 主动查询通知队列
  SELECT pg_notification_queue();
  -- 每秒轮询一次，可根据需求调整间隔
  SELECT pg_sleep(1);
END LOOP;

执行后，再从另一个CLI发送NOTIFY，就能实时看到消息推送。

触发器端的排查点 #

先确认触发器内的通知逻辑无问题：

• 在PL/pgSQL中优先使用pg_notify()函数替代直接的NOTIFY语句，可靠性更高：

  PERFORM pg_notify('live_data_update', 'event: ' || NEW.id::text);
  

• 确保通知在事务提交后发送：行级触发器的事务会在SQL语句执行完成后自动提交，若触发器内显式开启嵌套事务，会导致通知延迟推送。

Python/psycopg2端的前置注意事项 #

这是你后续排查Python端功能异常的关键：

1. 必须设置自动提交隔离级别：若连接处于事务中，psycopg2不会接收通知，直到事务提交完成。
2. 主动调用poll()轮询：同步模式下需循环调用conn.poll()来拉取待处理的通知。

示例代码：

import psycopg2
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT

conn = psycopg2.connect("dbname=your_db user=your_user")
conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
cur = conn.cursor()
cur.execute("LISTEN live_data_update;")

while True:
    conn.poll()
    # 处理所有待接收的通知
    while conn.notifies:
        notify = conn.notifies.pop(0)
        print(f"收到通知: {notify.channel} -> {notify.payload}")

额外排查步骤 #

• 确认监听会话状态正常：执行SELECT * FROM pg_listening_channels();，检查live_data_update是否在结果列表中。
• 检查NOTIFY的事务状态：若手动执行NOTIFY，确保没有未提交的事务（可执行COMMIT验证）。

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