4

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

klin
  • 112,967
  • 15
  • 204
  • 232
ChsharpNewbie
  • 1,040
  • 3
  • 9
  • 21

1 Answers1

4

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.

klin
  • 112,967
  • 15
  • 204
  • 232
  • 1
    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. – Adrian Klaver Sep 20 '21 at 00:15
  • 1
    @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. – klin Sep 20 '21 at 06:55
  • Hello @klin ,that solved the problem, thanks again :-) perform pg_notify('new_id'::text, new. "Id"::text); – ChsharpNewbie Sep 20 '21 at 09:08
  • 2
    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. – Adrian Klaver Sep 20 '21 at 15:29
  • Hello @Adrian Klaver I wasn't aware of that, thank you for pointing it out :-) – ChsharpNewbie Sep 24 '21 at 09:24