PostgreSQL JDBC - implementing asynchronous notifications

Author

implementing asynchronous notifications

Andras Kadinger

2005-04-10, 7:23 am

Greetings everyone,

A Java beginner here, seeking the insight of more experienced.

" 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/document...stennotify.html

I am looking forward to have that limitation lifted.

When we are idle and not in a transaction, the backend actually sends us
notifications right away; in this case there is no need for us to do
polling with empty queries.

Under unix you can get the fd from libpq, select() on it, and then process
notifications by PQConsumeInput() and retrieve them with PQnotifies(). -
see last paragraph at
http://www.postgresql.org/docs/curr...bpq-notify.html

Does Java provide working infrastucture to implement this reliably? What's
needed is a reliable way to detect that bytes have arrived on our
connection socket.

I am hoping java.net.Socket.getInputStream.available() could be used. Or
even better the new select()-like functionality provided by java.nio since
1.4. Do these actually work?

If java.nio does, would the impact of choosing java.nio, and thereby
excluding pre-1.4 platforms, an acceptable way of conduct? Or is there
infrastructure in the jdbc.postgresql.org project in place to allow us to
preserve backwards-compatibility by simply omitting this feature from
pre-1.4 builds?

Thank you in advance,

Best Regards,
Andras

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Oliver Jowett

2005-04-10, 7:23 am

Andras Kadinger wrote:

> If java.nio does, would the impact of choosing java.nio, and thereby
> excluding pre-1.4 platforms, an acceptable way of conduct?

I don't think so.. I'm pretty sure there are users that are running the
driver under 1.2 (*bsd perhaps?)

> Or is there
> infrastructure in the jdbc.postgresql.org project in place to allow us to
> preserve backwards-compatibility by simply omitting this feature from
> pre-1.4 builds?

It'll be tricky to do this from a single codebase as the NIO changes
would need to reach right down to the underlying protocol stream.. it
seems hard to localize the changes.

-O

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Oliver Jowett

2005-04-11, 7:24 am

Andras Kadinger wrote:
> On Mon, 11 Apr 2005, Andras Kadinger wrote:
>
>
> Yes, it could: reading the PostgreSQL protocol documentation, it says
> "frontends should always be prepared to accept and display NoticeResponse
> messages, even when the connection is nominally idle".
>
> So I now added code to process Error 'N'otifications as well.

You also need to handle errors ('E'). Try shutting down a postmaster (-m
fast) while idle connections are around -- they'll get spontaneous FATAL
errors.

> + try {
> + executor.processNotifies();
> + } catch (SQLException e) {};

Don't eat the exceptions, let them propagate.

(ugh, getNotifications() does not throw SQLException. We should probably
change that..)

> + while (protoConnection. getTransactionState(
) == ProtocolConnection.TRANSACTION_IDLE && pgStream.getSocket().getInputStream().available()>0) {

Can you move that reference following into a method on PGStream?
(hasMessagePending()
or something)

The test on transaction state is a bit misleading since the connection's
transaction state should never change inside the loop. Perhaps making
that a separate test would be clearer.

I'm not sure if available() is guaranteed to work on a socket stream
everywhere (it works fine here, though), but I suppose that at worst you
get the existing behaviour where you need to send a query.

Otherwise, seems fine!

-O

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere
" to majordomo@postgresql
.org)

Oliver Jowett

2005-04-11, 7:24 am

Andras Kadinger wrote:
> On Mon, 11 Apr 2005, Oliver Jowett wrote:
>
>
>
> Are you certain? The protocol documentations specifically mentions this
> case, saying it would send a NoticeResponse:
>
> "It is possible for NoticeResponse messages to be generated due to outside
> activity; for example, if the database administrator commands a "fast"
> database shutdown, the backend will send a NoticeResponse indicating this
> fact before closing the connection. Accordingly, frontends should always
> be prepared to accept and display NoticeResponse messages, even when the
> connection is nominally idle." -
> http://www.postgresql.org/docs/8.0/...#PROTOCOL-ASYNC
>
> Still, I took your word on this now, and added code to handle 'E's.

This is what I get from a pg_ctl stop -m fast on 8.0.0:

recvfrom(3, " E\0\0\0mSFATAL\0C57P
01\0Mterminating connection due to
administrator command\0Fpostgres. c\0L2042\0RProcessIn
terrupts\0\0",
8192, 0, NULL, NULL) = 110

I guess the docs are out of sync..

-O

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Oliver Jowett

2005-04-19, 8:24 pm

Andras Kadinger wrote:

> PGConnection.getNotifies() now processes peding Async Notifies first - no
> need to send polling queries to the server anymore. The result is zero
> network traffic in the idle case.

I've applied your latest patch (with some cosmetic editing and a
testcase) to CVS HEAD. Thanks for implementing this!

-O

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere
" to majordomo@postgresql
.org)

David Gagnon

2005-04-20, 11:23 am

Hi,

I just read this thread. Wich seems really interesting. Is there an
example on how to use Async Notifies? I look at it a couple of months
ago and found it not sufficient for my needs. I need to implement a
notification mechanism to clear data from caches in my application on
data update.

Is this change allow me to do that .. ? That will be so nice :-)

If yes it's that possible to get a small example on how to use it.

Thanks!
/David

Oliver Jowett wrote:

>Andras Kadinger wrote:
>
>
>
>
>I've applied your latest patch (with some cosmetic editing and a
>testcase) to CVS HEAD. Thanks for implementing this!
>
>-O
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere
" to majordomo@postgresql
.org)
>
>
>

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Oliver Jowett

2005-04-20, 8:24 pm

David Gagnon wrote:

> I just read this thread. Wich seems really interesting. Is there an
> example on how to use Async Notifies? I look at it a couple of months
> ago and found it not sufficient for my needs. I need to implement a
> notification mechanism to clear data from caches in my application on
> data update.
>
> Is this change allow me to do that .. ? That will be so nice :-)
>
> If yes it's that possible to get a small example on how to use it.

Basically, you can now call PGConnection.getNotifications() and get
results without having to submit a dummy query first.

You will still need an application-level loop that periodically calls
it, though. Also, it's not guaranteed to give you notifications: you
must be not in a transaction (this is server-side behaviour), and you
must be using a Socket implementation that implements available() (SSL
connections may not do this).

-O

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere
" to majordomo@postgresql
.org)

Sponsored Links