Thread: implementing an out-of-transaction trigger
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
>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. Check out http://www.postgresql.org/docs/7.4/static/sql-listen.html and http://www.postgresql.org/docs/7.4/static/sql-notify.html Then look at the Rules system for generating a NOTIFY: http://www.postgresql.org/docs/7.4/static/sql-createrule.html --miker
Hi Mike, Thanks, that's exactly what I was looking for. A quick quote from the docs: ...if a NOTIFY is executed inside a transaction, the notify events are not delivered until and unless the transaction is committed. ...if a listening session receives a notification signal while it is within a transaction, the notification event will not be delivered to its connected client until just after the transaction is completed... This is pretty good so far. Though, as far as I can tell, there is no way to have the notify activate a pl/pgsql function directly. I'll still need to write a client program to create a session and actually do the listening, that is if I havn't missed anything else... Thanks again, Iain ----- Original Message ----- From: "Mike Rylander" <mrylander@gmail.com> To: "Iain" <iain@mst.co.jp> Cc: <pgsql-sql@postgresql.org> Sent: Thursday, September 16, 2004 1:03 AM Subject: Re: [SQL] implementing an out-of-transaction trigger > >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. > > Check out > http://www.postgresql.org/docs/7.4/static/sql-listen.html > and > http://www.postgresql.org/docs/7.4/static/sql-notify.html > > Then look at the Rules system for generating a NOTIFY: > http://www.postgresql.org/docs/7.4/static/sql-createrule.html > > --miker > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings
"Iain" <iain@mst.co.jp> writes: > Though, as far as I can tell, there is no way to have the notify activate a > pl/pgsql function directly. I'll still need to write a client program to > create a session and actually do the listening, that is if I havn't missed > anything else... Right, presumably some sort of daemon that sits and waits for events. Much like you would have with Oracle advanced queuing and such. The big difference is that NOTIFY doesn't pass along any parameters. You will need some way for your daemon to find any pending data it needs to process. You might need some kind of queue table, or you might be able to get by without one. -- greg
Hi Greg, I would have like to have avoided writing a daemon if possible, but presumably it should be possible to write one flexible enough to be used in a variety of situations. If I didn't use notify, I'd would just be checking for the presence of data in "transfer areas" or queue tables as you called them on a periodic basis. This wouldn't change so much using notify, except that instead of writing the process so as to sleep and periodically check, I can just have it wake up when the data actually gets there. A minor saving perhaps, but at least I could use the name of the notification to determine what processing to activate. Thanks, Iain ----- Original Message ----- From: "Greg Stark" <gsstark@mit.edu> To: "Iain" <iain@mst.co.jp> Cc: "Mike Rylander" <mrylander@gmail.com>; <pgsql-sql@postgresql.org> Sent: Thursday, September 16, 2004 12:57 PM Subject: Re: [SQL] implementing an out-of-transaction trigger > > "Iain" <iain@mst.co.jp> writes: > > > Though, as far as I can tell, there is no way to have the notify activate a > > pl/pgsql function directly. I'll still need to write a client program to > > create a session and actually do the listening, that is if I havn't missed > > anything else... > > Right, presumably some sort of daemon that sits and waits for events. Much > like you would have with Oracle advanced queuing and such. > > The big difference is that NOTIFY doesn't pass along any parameters. You will > need some way for your daemon to find any pending data it needs to process. > You might need some kind of queue table, or you might be able to get by > without one. > > -- > greg