Re: Questions about Listen/Notify mechanism

On 18/07/10 10:33, max wrote:

> "A key limitation of the JDBC driver is that it cannot receive
> asynchronous notifications and must poll the backend to check if any
> notifications were issued."
>
> Does that mean that the driver will poll the database, e.g. every 5
> seconds?

The driver never will, but your app might have to in order to receive
notifications.

What happens is that notifications are processed whenever any other I/O
to the database is done. If you need to poll for notifications you can
send a dummy statement.

If you're not using SSL you can actually use the PgConnection's methods
to check for new notifications without any network I/O. This won't work
with SSL connections, though, because there's no reliable way to check
to see if there are application stream bytes availible to read in from
ssl connection.

I've written some utility code for this - a class that will maintain the
LISTEN list on a connection, restore it if the connection is broken and
re-established, and poll for notifications. It'll avoid network polling
unless it suspects the connection might be using ssl.

I need to clean it up a bit for public use, though, as it's currently
tied to my app's Hibernate connection provider, which is a custom one
designed to maintain only one connection and let the listen/notify
poller use it when the app isn't busy with it.

> If so, that would not be acceptable for me, because that is
> exactly what I want to avoid. The only advantage of using Listen/Notify
> in that case would be that I don't need an extra notification table that
> my trigger would fill. Am I wrong? Oo

It's a *LOT* cheaper than polling a table's contents, and you can avoid
network polling if you're not using SSL.

--
Craig Ringer