Problem with LISTEN - NOTIFY mechanism in PostgreSQL

I'm struggling with PostgreSQL LISTEN - NOTIFY mechanism.
I open two consoles and connect to my database using psql client:

psql -U postgres test_db

In the first console I subscribe to some notification:

test_db=# listen event;
LISTEN

In the second console I'm trying to send a notification:

test_db=# notify event;
NOTIFY

And nothing happens in the first console. But if I send a notification from console 1, I get it and also I get all previous notifications from console 2.

I use PostgreSQL 9.2.1 under CentOS 6.5 x64.

This is a psql issue. psql is waiting for you to enter something on its command line. It only checks for notifications from the server when it has a reason to contact the server, and you haven't given it a reason.

They could have implemented it differently, so that it uses a select loop to wait on either the keyboard or the server, whichever becomes interesting first. But that would make things much more complicated and it is not the way it was done.

This is the same reason that, if you bring down the server, psql doesn't notice until you try to do something that would try to talk to the server.

Note that the same issue will exist with clients/libraries other than psql, and different libraries solve (or fail to solve) it in different ways.

StackExchange Database Administrators Q#78122, answer score: 7