Pavel Stehule wrote:
2007/9/12, Jay Dickon Glanville <dickon.glanville@xxxxxxxxx>:
- 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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
