Re: implementing an out-of-transaction trigger

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