Pavel Stehule wrote:
> 2007/9/12, Jay Dickon Glanville <dickon.glanville@gmail.com>:
>> - I write a function (it doesn't matter what language it's in:
>> PL/pgSQL, PL/Java, etc)
>> - I register that function as a "post-commit" callback function
>> - when a client commits a transaction, the function gets called, and
>> the database passes the function some general information as to the
>> content of the transaction
>>
>> Note how similar this process is to writing triggers. The only
>> problem I have with triggers is that events get generated per-table.
>> I'd like to get notifications based on transactions, not table
>> changes.
>>
>> What I'd like to be able to do with this event is to notify any
>> applications of this change, so they can update their cached view of
>> the database.
Although I'm happy to use triggers as-is (not per transaction, etc) I've
also wondered about firing events from the database. I'm curious to
know if anyone has attempted to write a trigger that will open a socket
and send an event packet to an application server on the network.
I've considered using a message queue like JMS to manage events on my
network and have PostgreSQL fire off UDP messages to a socket server
that would insert jobs into the message queue as triggers get fired in
the database. Doing this would be an alternative to storing the queue
as a database table and having to use polling to constantly check the
database for events in the queue.
I am interested what anybody might contribute to this thread. Let us
know what you tried whether it worked or not, it might be useful.
-- Dante