From: Iain <iain@mst.co.jp>
To: pgsql-sql@postgresql.org
Subject: implementing an out-of-transaction trigger
Date: Wed, 15 Sep 2004 12:03:30 +0900
Message-ID: <002f01c49ad0$9491e180$7201a8c0@mst1x5r347kymb> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: iain@mst.co.jp
Subject: Re: implementing an out-of-transaction trigger
In-Reply-To: <002f01c49ad0$9491e180$7201a8c0@mst1x5r347kymb>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox