• Home
  • Jobs
  • Courses
  • Questions
  • Teachers
  • For business
  • ES/EN

0

80
Views
Notifications in PostgreSQL with Python(psycopg2) does not work

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#).

3 months ago ·

Santiago Trujillo

1 answers
Answer question

0

According to NOTIFY syntax, channel is an identifier. That means that new_Id in

LISTEN new_Id

is automaticaly converted to new_id. Unfortunately, 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 LISTEN:

LISTEN "new_Id"

The use of capital letters in Postgres can cause surprises.

3 months ago · Santiago Trujillo Report
Answer question
Find remote jobs
Loading

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post job Plans Our process Sales
Legal
Terms and conditions Privacy policy
© 2022 PeakU Inc. All Rights Reserved.