Notifications in PostgreSQL with Python(psycopg2) does not work

Question

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

I'm guessing you are having identifier("new_Id") issues. Try with all lower case to confirm. — Adrian Klaver
– Adrian Klaver, Commented Sep 19, 2021 at 21:05

klin · Accepted Answer · 2021-09-20 06:56:07Z

5

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.

edited Sep 20, 2021 at 6:56

answered Sep 19, 2021 at 21:28

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Adrian Klaver Over a year ago

The issue with capital letters(especially mixed case) will cause surprises in all(most?) databases if you try to compare quoted identifiers to unquoted ones. The difference is the Postgres issue will be opposite of the SQL standard, fold to lower case instead of upper case in the unquoted situation.

klin Over a year ago

@AdrianKlaver - I didn't want to blame or slander Postgres in the least. However, this case is special because it concerns the use of an identifier as an argument to a function.

ChsharpNewbie Over a year ago

Hello @klin ,that solved the problem, thanks again :-) perform pg_notify('new_id'::text, new. "Id"::text);

Adrian Klaver Over a year ago

The issue is taking the general case of mixing quoted and unquoted identifiers in SQL and turning it into : "The use of capital letters in Postgres can cause surprises." It can cause surprises in any SQL database(AFAIK) you mix quoted/unquoted identifiers. Postgres decided to stand the problem on its head by doing a 180 on the standard, so that should be noted.

ChsharpNewbie Over a year ago

Hello @Adrian Klaver I wasn't aware of that, thank you for pointing it out :-)

Collectives™ on Stack Overflow

Notifications in PostgreSQL with Python(psycopg2) does not work

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related