X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 9304A329C65
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 15 Sep 2004 04:03:36 +0100 (BST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 28726-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 15 Sep 2004 03:03:27 +0000 (GMT)
Received: from millenium.mst.co.jp (unknown [210.230.185.241])
	by svr1.postgresql.org (Postfix) with ESMTP id E47E7329ECB
	for <pgsql-sql@postgresql.org>; Wed, 15 Sep 2004 04:03:25 +0100 (BST)
Received: from mst1x5r347kymb (lc12114 [192.168.1.114])
	by millenium.mst.co.jp (8.11.6p2/3.7W) with SMTP id i8F33Nq30563
	for <pgsql-sql@postgresql.org>; Wed, 15 Sep 2004 12:03:23 +0900
Message-ID: <002f01c49ad0$9491e180$7201a8c0@mst1x5r347kymb>
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
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_002B_01C49B1C.04215760"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1437
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=1.7 tagged_above=0.0 required=5.0 tests=HTML_20_30,
	HTML_MESSAGE, NO_EXPERIENCE
X-Spam-Level: *
X-Archive-Number: 200409/103
X-Sequence-Number: 18949

This is a multi-part message in MIME format.

------=_NextPart_000_002B_01C49B1C.04215760
Content-Type: text/plain;
	charset="iso-2022-jp"
Content-Transfer-Encoding: 7bit

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

------=_NextPart_000_002B_01C49B1C.04215760
Content-Type: text/html;
	charset="iso-2022-jp"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-2022-jp=
">
<META content=3D"MSHTML 6.00.2800.1458" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3D"MS UI Gothic" size=3D2>Hi All,</FONT></DIV>
<DIV><FONT face=3D"MS UI Gothic" size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"MS UI Gothic" size=3D2>I've come across a situation wher=
e I'd=20
like to use some kind of "out-of-transaction trigger" to do some processing=
=20
after changes to some tables, but without extending the duration of the mai=
n=20
transaction. Of course, it's important that the processing be completed so =
it=20
has to be, as far as possible, reliable and "safe". The extra processing sh=
ould=20
be completed within a reasonable time after the original transaction, but i=
t=20
needn't happen immediately.</FONT></DIV>
<DIV><FONT face=3D"MS UI Gothic" size=3D2></FONT>&nbsp;</DIV><FONT=20
face=3D"MS UI Gothic" size=3D2>
<DIV><FONT face=3D"MS UI Gothic" size=3D2>In the past, we have&nbsp;used tr=
iggers=20
written in C that call perl scripts. It seems untidy to me as we introduce =
2=20
more programming languages and&nbsp; I'm not so comfortable with the idea o=
f=20
calling an OS program from a trigger which is part of the original transact=
ion=20
anyway. It doesn't seem to be helping the situation much.</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV>I havn't been able to come up wth a standard way to do this in postgre=
s so=20
I'm contemplating writing a kind of background process that checks for chan=
ges=20
and processes them periodically, nothing very sophisticated. I'm wondering =
if=20
there is a better way. Maybe other people here have dealt with&nbsp;this ki=
nd of=20
situation.</FONT></DIV>
<DIV><FONT face=3D"MS UI Gothic" size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"MS UI Gothic" size=3D2>If I were using Oracle, I might=
=20
use&nbsp;Streams or Advanced Queuing, this would probably be a good case fo=
r=20
using them - though I have no experience myself. In oracles case the subscr=
ibed=20
changes are captured from the redo log. After that it's fairly standard=20
publisher/subsriber type stuff. I expect that this should, in theory,&nbsp;=
be=20
possible with postgres too though </FONT><FONT face=3D"MS UI Gothic" size=
=3D2>AFAIK=20
this isn't on the todo list.</FONT></DIV>
<DIV><FONT face=3D"MS UI Gothic" size=3D2></FONT><FONT face=3D"MS UI Gothic=
"=20
size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"MS UI Gothic" size=3D2>Anyway, if anyone has any thought=
s on=20
this, I'd be interested to hear them,</FONT></DIV>
<DIV><FONT face=3D"MS UI Gothic" size=3D2>regards</FONT></DIV>
<DIV><FONT face=3D"MS UI Gothic" size=3D2>Iain</FONT></DIV></BODY></HTML>

------=_NextPart_000_002B_01C49B1C.04215760--