Обсуждение: Questions about Listen/Notify mechanism
Hallo, I need to write a Java app which deals with as low as possible network resources. That's why the database should inform my Java app about changes and I does not have to poll the database every 5 seconds to recognize them. I read the Postgresql-JDBC documentation about the Listen/Notify commands. I do not understand that note "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? 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 I hope somebody can help me with that. Regard, Max
Yes, you do have to poll with a "SELECT 1" to get the notifications, this is very quick and no problem at all, and you can have lots of LISTEN/NOTIFY without adding much load on the network, this works very good so I don't see why you cannot use it. Mikael ----- Original Message ----- From: "max" <maxbox@directbox.com> To: <pgsql-jdbc@postgresql.org> Sent: Sunday, July 18, 2010 4:33 AM Subject: [JDBC] Questions about Listen/Notify mechanism > Hallo, > > I need to write a Java app which deals with as low as possible network > resources. That's why the database should inform my Java app about > changes and I does not have to poll the database every 5 seconds to > recognize them. > I read the Postgresql-JDBC documentation about the Listen/Notify > commands. I do not understand that note > > "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? 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 > > I hope somebody can help me with that. > > Regard, > > Max > > > -- > Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-jdbc
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." The reason for this is that the driver does not have a separate I/O thread, so the driver code is only running (and reading from the database connection) when a query is run by the application. -O
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
On 07/18/10 03:33, max wrote: > Hallo, > > I need to write a Java app which deals with as low as possible network > resources. That's why the database should inform my Java app about > changes and I does not have to poll the database every 5 seconds to > recognize them. > I read the Postgresql-JDBC documentation about the Listen/Notify > commands. I do not understand that note > > "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? 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 > > I hope somebody can help me with that. > > Regard, > > Max > Hi Max, Sounds like you need a message queue in order to implement an event driven architecture, which would eliminate polling entirely. I'm going to be looking into this shortly for a project at work, and I'm hoping to implement the database part of it using Skytools PgQ: http://wiki.postgresql.org/wiki/Skytools http://wiki.postgresql.org/wiki/PGQ_Tutorial I'm planning on pushing messages to a generic queuing package (HornetQ is my preferred choice), with AMQP as the message protocol. From what I've read, this is definitely one of the intended uses of PgQ, although descriptions of how it can be accomplished seems to be limited to a a presentation from PGCon 2010 and a handful blog posts at the moment. Chris Chris Wareham Senior Software Engineer Visit London Ltd 6th floor, 2 More London Riverside London SE1 2RR Tel: +44 (0)20 7234 5848 Fax: +44 (0)20 7234 5753 http://www.visitlondon.com/ BT Visit London Awards 2010 are now open for entry. Go to http://www.visitlondon.com/awards for more info on categories andhow to enter. Back England’s bid to host the 2018 FIFA World Cup and London as a Candidate Host City. Visit http://www.england2018bid.com/or Text ‘England’ to 62018 'Visit London Limited' is registered in England under No.761149; Registered Office: Visit London, 2 More London Riverside, London SE1 2RR. Visit London is the official visitor organisation for London. Visit London is partly funded by Partnership, the Mayor's LondonDevelopment Agency and London Councils. The information contained in this e-mail is confidential and intended for the named recipient(s) only. If you have receivedit in error, please notify the sender immediately and then delete the message. If you are not the intended recipient,you must not use, disclose, copy or distribute this email. The views expressed in this e-mail are those of theindividual and not of Visit London. We reserve the right to read and monitor any email or attachment entering or leavingour systems without prior notice. Please don't print this e-mail unless you really need to.