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 6F35B329E0E for ; Thu, 16 Sep 2004 03:02:30 +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 86715-09 for ; Thu, 16 Sep 2004 02:02:21 +0000 (GMT) Received: from millenium.mst.co.jp (unknown [210.230.185.241]) by svr1.postgresql.org (Postfix) with ESMTP id A1373329EDA for ; Thu, 16 Sep 2004 03:02:23 +0100 (BST) Received: from mst1x5r347kymb (lc12114 [192.168.1.114]) by millenium.mst.co.jp (8.11.6p2/3.7W) with SMTP id i8G22Gq01549; Thu, 16 Sep 2004 11:02:16 +0900 Message-ID: <008c01c49b91$361e86f0$7201a8c0@mst1x5r347kymb> From: "Iain" To: "Mike Rylander" Cc: References: <002f01c49ad0$9491e180$7201a8c0@mst1x5r347kymb> Subject: Re: implementing an out-of-transaction trigger Date: Thu, 16 Sep 2004 11:02:25 +0900 MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 7bit 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=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/112 X-Sequence-Number: 18958 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" To: "Iain" Cc: 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