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