|
Hi All,
I've come across a situation where I'd
like to use some kind of "out-of-transaction trigger" to do some processing
after changes to some tables, but without extending the duration of the main
transaction. Of course, it's important that the processing be completed so it
has to be, as far as possible, reliable and "safe". The extra processing should
be completed within a reasonable time after the original transaction, but it
needn't happen immediately.
In the past, we have used triggers
written in C that call perl scripts. It seems untidy to me as we introduce 2
more programming languages and I'm not so comfortable with the idea of
calling an OS program from a trigger which is part of the original transaction
anyway. It doesn't seem to be helping the situation much.
I havn't been able to come up wth a standard way to do this in postgres so
I'm contemplating writing a kind of background process that checks for changes
and processes them periodically, nothing very sophisticated. I'm wondering if
there is a better way. Maybe other people here have dealt with this kind of
situation.
If I were using Oracle, I might
use Streams or Advanced Queuing, this would probably be a good case for
using them - though I have no experience myself. In oracles case the subscribed
changes are captured from the redo log. After that it's fairly standard
publisher/subsriber type stuff. I expect that this should, in theory, be
possible with postgres too though AFAIK
this isn't on the todo list.
Anyway, if anyone has any thoughts on
this, I'd be interested to hear them,
regards
Iain |
