Cheapest way to poll for notifications? & Driver improvement question re SSL and notify - Mailing list pgsql-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."
>
>    http://jdbc.postgresql.org/documentation/84/listennotify.html
>
> .... 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
>