2

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.

  • While I'm not aware of any bugs relating to LISTEN and NOTIFY, why are you on an old point-release with known bugs? Upgrade to 9.2.9 . Also - psql connection options? – Craig Ringer Oct 1 '14 at 7:56
  • For some reasons we can't easily update right now. But actually that listen-notify mechanism has been working for some time in our application, but today I've noticed that it doesn't work anymore. Also I've added psql connection options to my question. – hank Oct 1 '14 at 8:11
  • Very odd issue, especially if it was working before and isn't now. Anything informative in PostgreSQL's logs? – Craig Ringer Oct 1 '14 at 8:16
  • Suddenly the problem in our application disappeared, and I don't have any idea why. And what about psql, maybe it lacks some interactive mode. After I perform any SQL request, such as select 1, I get all pending notifications. – hank Oct 1 '14 at 8:41
  • Sounds like it's not pulling async notifications. That's a bit dependent on the platform and connection type, so I wouldn't be too surprised. – Craig Ringer Oct 1 '14 at 9:06
4

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.