postgres listen not being received in python

Question

I have two notifications I am trying to receive in python, one of them works ("LISTEN raw_data_insert;"), the other one ("LISTEN device_snapshot_change;") does not.

The trigger does fire on an update, and notify function is called; I can see this output

[00000] UPDATE RECEIVED "{"test": 1, "something": 4}"

which shows the updated json field, but pg_notify does not seem to send to the python client?

I can remove the cursor.execute(f"LISTEN raw_data_insert;") line and it does not change anything, having both lines does not change anything. Only the raw_data_insert is currently working. I am not sure what the issue is, any ideas? Thanks

The python:

import asyncio
import psycopg2

conn = # custom service to build connection
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
cursor = conn.cursor()
cursor.execute(f"LISTEN raw_data_insert;")
cursor.execute(f"LISTEN device_snapshot_change;")

def do_notify():
    conn.poll()
    for notify in conn.notifies:
        print(f"NOTIFICATION RECEIVED {notify.channel}")
        if notify.channel == 'raw_data_insert':
            print(notify.payload)
            print("=====")
        elif notify.channel == 'device_snapshot_change':
            print(notify.payload)
            print("=====")
    conn.notifies.clear()

loop = asyncio.get_event_loop()
loop.add_reader(conn, do_notify)
loop.run_forever()

The sql:

CREATE OR REPLACE FUNCTION notify_device_snapshot_change()
RETURNS trigger AS $$
BEGIN
    RAISE NOTICE 'UPDATE RECEIVED "%"', NEW.device_snapshot;
    PERFORM pg_notify(
        'device_snapshot_change',
        NEW.device_snapshot::text
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER device_snapshot_change
AFTER UPDATE OF device_snapshot ON device
FOR EACH ROW
EXECUTE PROCEDURE notify_device_snapshot_change();

Answer 1

I fixed my issue a few days later on a different staging database instance where the trigger and function did not exist. It just worked the first time when I created the table and ran the above script, so I gave up and assumed there was some issue/typo on the other instance.