I want to have my PostgreSQL server send out notifications when a piece of data changes, preferably over JMS, but also considering any other Pub-Sub mechanism or Callback.
Any ideas if this is possible?
Are there any available Java Add-on Packages that replicate this sort of functionality?
EDIT: I've been informed that PostgreSQL does support stored procedures in Java. That means the following approach becomes feasible:
Essentially, the way I would go is to put a trigger on whatever it is you want to watch, and then call a stored procedure from that. The stored procedure then needs to communicate with the world outside the DB server; I once did an SP like this in Java that opened up a socket connection to a process on the same server listening on a port. If worst came to worst, you could maybe write a file and have something like imon monitoring that file, or you could start up a program in an exec() shell of its own... something like that.
the simplest approach is to use LISTEN/NOTIFY interface, write your own program that connects to database, issues some LISTENs, and does whatever you want when it gets notification - for example sens information over JMS, or simply does what should be done, without adding additional transportation layer.
If LISTEN/NOTIFY isn't accessible via JDBC, perhaps you could implement a long-polling HTTP comet-like mechanism via the LOCK statement, or plain "SELECT ... FOR UPDATE" and "SELECT ... FOR SHARE" or other similar queries from within a transaction that'd cause other transactions to block.
The message-writing party could e.g. start a transaction, perform "SELECT ... FOR UPDATE", wait (java code) until either something changes, or a timer expires (say after 30 seconds or so), update the locked row to indicate if data (elsewhere?) is available and commit the transaction to unblock others. Then repeat with a new transaction with "SELECT ... FOR UPDATE" immediately.
The message-reading party would perform a "SELECT ... FOR SHARE" which would block while a "SELECT ... FOR UPDATE" initiated elsewhere is active. It'd return an indication of message availability or the message data itself when the message-writing party's transaction ends.
Hopefully PostgreSQL queues the parties fairly, so that there's no risk of live-lock continuously blocking the message-reading party.
I would install PL/Java to Postgres and write a stored procedure based trigger for the the data you are interested in, which then calls JMS when being called. PL/Java documentation covers the trigger + stored procedure part pretty nicely btw.
I haven't used the JMS from the trigger code, but I'm pretty certain that there are no reasons why it wouldn't be doable, as this is standard Java code and my quick recheck on the documentation also didn't indicate anything suspicious.
Another possibility would be to call the JMS through a proxy service using either, perl, python or any other language that is available for postgres stored procedure development. Just as the JMS doesn't have a standard wire protocol you have to write a proxy service which does the translation.
You can certainly create a Java-language stored procedure and put it into PostgreSQL. But why not keep it simple and debuggable until you know you have your messaging scheme working perfectly? If I were doing this (I am actually doing something similar) here's what I'd do.
(1) create an "outbound message" table with columns for the payload and other info for your JMS messages. I'd put a timestamp column in each row.
(2) write a database trigger for each item that you want to generate a message. Have the trigger INSERT a row into your "outbound message" table.
(3) unit test (1) and (2) by looking at the contents of your outbound message table as you change stuff in your database that should generate messages.
(4) write yourself a simple but high-performance Java JDBC client program that will query this outbound message table, send a JMS message for each row, and then DELETE it. Order the rows in your query by timestamp to preserve your message order. To get it to be high performance you'll need to do a good job with PreparedStatement objects and other aspects of heap management.
(5) Unit test (4) by running it a few times while message-generating changes are happening to your data base.
(6) set up this program to repeat operation (6) several times a minute, while using a single persistent JDBC connection. Queries to a small or empty table aren't very expensive, so this won't smack down your table server.
(7) system test this whole setup.
(8) figure out how to start your Java program from your crontab or your startup script.
When you get all this working you'll have a functioning messaging / notification system ready for systems integration. More importantly, you'll know exactly what you want your Java message-originating software to do. Once you're up and running, if the latency of your messages or the database overhead proves to be a big problem, then you can migrate your Java program into a stored procedure.
Note also that there are message-origination bindings for PERL and other languages built into the Apache ActiveMQ package, so you have some choices about how you implement your message-originating agent.
This approach happens to have two advantages: you aren't critically dependent on postgreSQL's distinctive stored-procedure scheme, and you aren't putting code with external communications dependencies into your table server.
Good luck.