Hacker News Reader

Comment by gtaylor | Hacker News Reader

I had no clue that Postgres supported LISTEN and NOTIFY. Granted, it's a new addition, but this is really slick:

http://www.postgresql.org/docs/9.1/static/sql-listen.html http://www.postgresql.org/docs/9.1/static/sql-notify.html

New addition?

listen and notify go back to at least 6.4 which came out 1998 ish http://www.postgresql.org/docs/6.4/static/sql-listen.html http://www.postgresql.org/docs/6.4/static/sql-notify.html

What might have changed is the implementation though. I seem to remember that some point in the past I was investigating listen/notify and the driver (libpq) was forcing you to handle LISTEN by polling (calling PQnotifies periodically) which doesn't really help compared to polling on your own.

This has not changed so far:

http://www.postgresql.org/docs/9.1/static/sql-listen.htm

states

> With the libpq library, the application issues LISTEN as an ordinary SQL command, and then must periodically call the function PQnotifies to find out whether any notification events have been received.

It might be possible for drivers working without libpq by talking the postgres protocol directly on the wire to get real asynchronous behavior, though I don't know anything about how this is being handled on the server right now (it might still be polling internally on the server end).

I'm not sure about long ago, but the recent version of libpq has NOTIFY working asynchronously (you can stick the connection file descriptor in a select(2) and you'll get woken up when a notify is available).

Hmm, pretty certain nothing has changed recently there either. At least as far back as 7.1 the documentation has remained the same about this.

http://www.postgresql.org/docs/7.1/static/libpq-notify.html

What has happened recently though is that more language bindings to libpq have started exposing the NOTIFY functionality in more convenient ways.

One of the bug fixes for Postgres95 Release 0.03 (Released 1995-07-21) is:

* the LISTEN/NOTIFY asynchronous notification mechanism now work

http://www.postgresql.org/docs/7.4/static/release-0-03.html

I'm not sure how much I like stuffing random functionality into the database... Looking at the docs: "and then must periodically call the function PQnotifies to find out whether any notification events have been received" - it's actually polling via an SQL connection - that means even more persistent connections to the DB, another timer in the application, more difficult connection sharing (two pools rather than one).

Without knowing much about how it works - why would I want to use this system rather than just ZMQ/RMQ/...?

You do not have to poll. You can wait on the socket until you get data and only then poll.

I quoted that part from the documentation - the way I understand it is that I can't detect if I have data (so I cannot "wait on the socket") in any other way than by polling via that function. Is that not correct?

LISTEN and NOTIFY are a great feature. We used them heavily in one system I worked on. As the project evolved though, we decided the best way to use them was to have clients to both our main message brokers and the postgres server, and translate between them. Largely this was to avoid overloading the pg server as a message broker also -- particularly because for our uses dbus was a much better alternative for local system message passing. Now that NOTIFYs officially have payload support, YMMV.

It's not exactly new, 7.4 was the oldest version I cared to check, but I think it goes back more :) http://www.postgresql.org/docs/7.4/static/sql-notify.html

A recent addition to NOTIFY is the ability for notifies to carry payloads (which makes them even more useful)

As the other posters have said, it has been around forever, but was rewritten to be much faster and support a payload in 9.0.

Reply on news.ycombinator.com