Consider the following demo in postgres 9.6.
I did the following in this order:
I ran in PSQL: LISTEN foo;
I ran in DataGrip. NOTIFY foo,'thad man';
Went back to the PSQL console and don't see my expected:
Asynchronous notification "foo" with payload "thad man" received from server process with PID 4117.
It is only in PSQL when I run any statement do I get my notification. How might I be able to see notification are made without doing anything? Is this possible? If I have to run a statement like SELECT 1; what benefit might I get from listening in the application layer? Would I have to be running SELECT 1; in a constant loop every second or something to get notified?
Best Answer
An application doesn't have to poll the database to receive notifications, but it has to poll the status of its socket (network connection) to know that there is a notification to collect, otherwise there's no way to know about it. psql doesn't do that when waiting on user input. It blocks on user input, as most command-line oriented applications.
No, but you need to implement some sort of event loop in your application to detect the notification and handle it. But neither the detection nor the handling need to send any query to the database server.