Skip site navigation (1) Skip section navigation (2)

implementing an out-of-transaction trigger

From: "Iain" <iain(at)mst(dot)co(dot)jp>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: implementing an out-of-transaction trigger
Date: 2004-09-15 03:03:30
Message-ID: 002f01c49ad0$9491e180$7201a8c0@mst1x5r347kymb (view raw, whole thread or download thread mbox)
Lists: pgsql-sql
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,


pgsql-sql by date

Next:From: Sanjay SinghDate: 2004-09-15 09:22:25
Subject: Re: sleep function
Previous:From: Stephan SzaboDate: 2004-09-15 02:07:17
Subject: Re: explain analyze results are different for each iteration

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group