Skip site navigation (1) Skip section navigation (2)

Cheapest way to poll for notifications? & Driver improvement question re SSL and notify

From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
To: PG-JDBC Mailing List <pgsql-jdbc(at)postgresql(dot)org>
Subject: Cheapest way to poll for notifications? & Driver improvement question re SSL and notify
Date: 2009-12-19 07:00:35
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-generalpgsql-jdbc
Hi folks

Just to follow up on an earlier discussion on -general that turns out to 
be JDBC-specific: it turns out that there _is_ a need to poll for 
notifications using a dummy statement when a Java/JDBC client is using 
an SSL socket.

The short version is that the JDBC driver can't check an SSL socket to 
see if any data is availible for reading - the check always returns 0 
due to a limitation in the underling SSLSocket provided by the JRE. So a 
JDBC client using SSL must still send dummy statements to get notifications.

So my question is: is there any particularly low-overhead statement that 
might be suitable for generating pointless client/server chat to check 
for received async notifications? Should I just use "SELECT 1" ? Or 
would I be better off using a SHOW statement like "SHOW role" to avoid 
creating a snapshot etc?

I had a look at the v3 protocol documentatation and didn't see any sort 
of "echo" or "ping"-type message that might be used to (a) test the 
server for aliveness and (b) guarantee readable data of a known size on 
the client's input stream. So the polling looks like it has to be done 
at the SQL level not the protocol level.

=== alternative: blocking getNotifications() ===

An alternative is to provide an alternate form of getNotifications() 
that can block. It'd be unsynchronized, being intended for a dedicated 
thread in the app to use to poll for notifications. It'd call a blocking 
equivalent to QueryExecutorImpl.processNotifies() ( let's call it 
waitForNoitifies() ) that didn't check available() before attempting to 
read from the input stream.

The problem here is that while PGStream.readChar() as called from 
QueryExecutorImpl.waitForNotifies() was blocked waiting for input, 
someone else in another thread might try to read from the PGStream while 
doing normal work. PGStream would have to be able to block that read 
until the readChar() from processNotifies() returned, AND would have to 
be able to push the result of readChar() back onto the 
VisibleBufferedInputStream used by PGStream if it wasn't an async 
notification message.

I don't know how to do that without incurring plenty of nasty 
synchronization overhead.

So, what I'm wondering is if it's worth having an alternative stream 
class, say PGSynchronizedStream, that extends a PGStream with thread 
safety. The driver user could, via the PGConnection interface, request 
that blocking notification checking be enabled, causing the usual 
PGStream to be replaced with PGSynchronizedStream. It would then be 
possible to call something like getNotificationsBlocking() to trigger 
QueryExecutorImpl.waitForNotifies() as described above.

The only other change needed to support this would be add a pushChar() 
method to VisibleBufferedInputStream to permit input the blocking 
notification checker read but didn't want to be pushed back into the 
input stream.

This way, if you wanted async notifications over an SSL socket you'd 
have to pay the price of some read-synchronization overhead in 
PGSynchronizedStream, but otherwise not much would change anywhere in 
the code.

Is this crazy? Is there something obvious I've missed?

=== Why can't JDBC/SSL just test for data ready to read, anyway? ===

The server can send async notifications over an SSL socket, no problem. 
It generates an appropriate SSL message via the ssl library and sends it 
down the wire.

A Java SSL client, though, cannot check to see if it can read from an 
SSL socket without blocking. InputStream.available() always returns zero 
on a Java SSL socket, because while there may be data in the underlying 
connection buffer, the SSL client isn't sure if (a) it's a full SSL 
message, and (b) if that message contains data for the client. It 
doesn't seem to want to do the processing and buffering required to 
figure that out in the available() call.

It's an annoying issue. In theory there's nothing that prevents 
non-blocking I/O on SSL sockets - OpenSSL can do it, with a few quirks. 
Java's SSLSocket can't do it, though. To get non-blocking SSL in Java 
you apparently have to completely re-write your client using java.nio 
async I/O and use the SSLEngine to do manual SSL handling on top of 
that. There's no standard friendly-to-use non-blocking SSL socket 
wrapper on top of that, and direct use of SSLEngine is ... "interesting".

There are 3rd party implementations of wrappers that make a java.nio & 
SSLEngine-based system look like a normal SSLSocket, like ScalableSSL's 
SSLSocketChannel. that brings behaviour much like OpenSSL's 
almost-transparent non-blocking SSL - ie it looks like a normal 
non-blocking socket except for a few quirks re handshaking. 
SSLSocketChannel still doesn't do available() checking, though the docs 
say it can be added.

Even if that was sorted out though, SSLEngine is also present only in 
JDK 1.5 and above, so the JDBC driver can't use it for a while yet.

On 11/12/2009 11:39 PM, Craig Ringer wrote:
> Scott, Tom, Merlin:
> Thanks for the comments and help. It's all sorted now - the origin of
> the confusion was some outdated information in the JDBC driver
> documentation.
> The question arose because I was originally looking at polling from JDBC
> (which I know I forgot to mention), where the docs state that:
> "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."
> .... and show a `SELECT 1' being issued to push any notifications.
> I'd assumed that was a JDBC limitation until I tested with psql and
> found that it, too, required some kind of client-initiated communication
> to see NOTIFY events, at which point I began wondering if the backend
> pushed them at all rather than waiting for client interaction. Hence my
> question.
> Anyway, as pointed out, psql just doesn't bother polling for
> notifications because it's not important for psql, but it could if it
> needed to - the notifications are waiting in its recieve buffer for it
> to notice and care.
> As for the JDBC driver - it turns out that the documentation is
> out-of-date and/or misleading. The JDBC driver *does* support reading
> notifications the backend has pushed to its receive buffer, and does
> *not* have to poll the backend or issue a statement to receive
> notifications. Some searching suggests that this changed in 8.0 or 8.1 .
> The documentation needs adjusting, so I've sent a patch to it off to the
> JDBC folks.
> --
> Craig Ringer

In response to


pgsql-jdbc by date

Next:From: Craig RingerDate: 2009-12-19 07:24:53
Subject: Re: Cheapest way to poll for notifications?
Previous:From: Kris JurkaDate: 2009-12-18 22:32:15
Subject: Re: JDBC docs download page issue

pgsql-general by date

Next:From: Alex -Date: 2009-12-19 07:02:31
Subject: Re: PL/Perl Performance Problems
Previous:From: Scott MarloweDate: 2009-12-19 06:45:07
Subject: Re: PL/Perl Performance Problems

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group