使用Python(psycopg2)在PostgreSQL中的通知无法工作

Question

我希望在PostgreSQL 12的特定表 "FileInfos" 中有新条目时收到通知，因此我编写了以下触发器：

create trigger trigger1 
after insert or update on public."FileInfos" 
for each row execute procedure notify_id_trigger();

还有以下函数:

create or replace function notify_id_trigger() 
returns trigger as $$
begin 
    perform pg_notify('new_Id'::text, NEW."Id"::text); 
    return new; 
end; 
$$ language plpgsql;

为了接收通知，我使用Python库psycopg2：

import psycopg2
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
import select


def dblistener():
    connection = psycopg2.connect(
            host="127.0.0.1",
            database="DBNAME",
            user="postgres",
            password="....")

    connection.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
    cur = connection.cursor()
    cur.execute("LISTEN new_Id;")
    while True:
        select.select([connection], [], [])
        connection.poll()
        while connection.notifies:
            notify = connection.notifies.pop()
            print("Got NOTIFY:", notify.pid, notify.channel, notify.payload)


if __name__ == '__main__':
    dblistener()

很不幸，我的Python代码无法运行，我做错了什么？ 另外：数据库和表是用Entity Framework（C＃）创建的。

- ChsharpNewbie

---

我猜您遇到了标识符("new_Id")问题。尝试全部使用小写字母以确认。 - Adrian Klaver

Answer 1

根据NOTIFY语法，通道(channel)是一个标识符。这意味着在new_Id中使用时，它表示的是一个标识符。

LISTEN new_Id

自动转换为new_id。不幸的是，pg_notify('new_Id'::text, new."Id"::text)会通知频道new_Id。你有两个选择。在触发器中更改频道：

perform pg_notify('new_id'::text, new."Id"::text); 

或在 LISTEN 中将频道用双引号括起来：

LISTEN "new_Id"

在Postgres中使用大写字母可能会导致意外情况发生。

- klin

---

如果您尝试将带引号的标识符与未带引号的标识符进行比较，大写字母（特别是混合大小写）可能会在所有（或大多数）数据库中引起意外。问题在于Postgres的情况与SQL标准相反，在未带引号的情况下将折叠为小写而不是大写。 - Adrian Klaver

@AdrianKlaver - 我并不想责备或诋毁Postgres。然而，这个案例很特殊，因为它涉及将标识符用作函数参数的使用。 - klin

你好 @klin，问题已解决，再次感谢 :-) 执行 pg_notify('new_id'::text, new. "Id"::text); - ChsharpNewbie

问题是将在SQL中混合引用和非引用标识符的一般情况转化为：“在Postgres中使用大写字母可能会导致意外情况。” 在任何混合引用/非引用标识符的SQL数据库中（据我所知），都可能会导致意外情况。 Postgres决定通过对标准进行180度的扭转来解决这个问题，因此应该注意这一点。 - Adrian Klaver

你好 @Adrian Klaver，我之前不知道这件事，谢谢你指出来 :-) - ChsharpNewbie