CODEWITHSUNDEEP
databasepostgresqlnotifylisten
jmciver
jmciver

Reputation: 33

Postgresql listen/notify not working - only receive notification by committing a command on the listener?

I am making an app using Python/psycopg2. I have a Postgresql database, on which I have set up a trigger to send a notification on insert/update.

It wasn't working, and to debug, I set up 2 Postgresql CLIs, one acting as a listener and the other for sending notifications. What I have noticed is that I do not receive any notification on the listener CLI, unless I send through a command of some kind. For example, I will send NOTIFY live_data_update, 'Test notification'; from the notification CLI, and I do not receive it on the listener CLI. If I go over to the listener CLI, and type SELECT;, and press enter, I then receive the notification Asynchronous notification "live_data_update" with payload "Test notification" received from server process with PID 2164.

It seems that the notification sits in the buffer until I force it to flush by sending it a command, but what I keep reading about is how this should happen automatically.

I can't seem to find anyone else that has run into this. To be clear, on the listener, using the Postgresql CLI I am running:

LISTEN live_data_update;

and on the notification CLI I am running:

NOTIFY live_data_update, 'Test notification';

I then make my way back to the listener CLI, (see nothing) and type SELECT;, and then receive the notification (along with the empty return from the SELECT statement)

If anyone could be so kind as to help me understand what I might have missed, I would be very grateful. I was led to believe this was a good way to create the sense of a live feed to the database, and would love to accomplish this.

This is just a step on the way to figuring out why it's not working in Python, which I guess might be a whole different kettle of fish.

Upvotes: 0

Views: 902

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247615

From the documentation:

Notifications are received after every query execution. If the user is interested in receiving notifications but not in performing any query, the poll() method can be used to check for new messages without wasting resources.

A simple application could poll the connection from time to time to check if something new has arrived. A better strategy is to use some I/O completion function such as select() to sleep until awakened by the kernel when there is some data to read on the connection, thereby using no CPU unless there is something to read:

import select
import psycopg2
import psycopg2.extensions

conn = psycopg2.connect(DSN)
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)

curs = conn.cursor()
curs.execute("LISTEN test;")

print("Waiting for notifications on channel 'test'")
while True:
    if select.select([conn],[],[],5) == ([],[],[]):
        print("Timeout")
    else:
        conn.poll()
        while conn.notifies:
            notify = conn.notifies.pop(0)
            print("Got NOTIFY:", notify.pid, notify.channel, notify.payload)

Upvotes: 0

Related Questions