I want to be notified when there is a new entry in a specific table
"FileInfos" in PostgreSQL 12, so I wrote the following trigger:
create trigger trigger1 after insert or update on public."FileInfos" for each row execute procedure notify_id_trigger();
and the following function:
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;
to get the notifications I use the python library 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()
But unfortunately my python code does not work, what did I do wrong? BTW: The database and the table were created with the Entity Framework (C#).
According to NOTIFY syntax, channel is an identifier. That means that
is automaticaly converted to
pg_notify('new_Id'::text, new."Id"::text) notifies on channel
new_Id. You have two options. Change the channel in the trigger:
perform pg_notify('new_id'::text, new."Id"::text);
or enclose the channel in double-quotes in
The use of capital letters in Postgres can cause surprises.