Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 8C9E7B5DBE4 for ; Thu, 18 Aug 2011 01:36:05 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 26584-04 for ; Thu, 18 Aug 2011 04:35:59 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from softlayer.compulab.co.il (50.23.254.54-static.reverse.softlayer.com [50.23.254.54]) by mail.postgresql.org (Postfix) with ESMTP id D49ACB5D803 for ; Thu, 18 Aug 2011 01:35:58 -0300 (ADT) Received: from [62.90.235.247] (port=39857 helo=zimbra-mta.compulab.co.il) by softlayer.compulab.co.il with esmtp (Exim 4.69) (envelope-from ) id 1QtuKj-0007Wl-HN for pgsql-general@postgresql.org; Thu, 18 Aug 2011 07:35:57 +0300 Received: from localhost (localhost.localdomain [127.0.0.1]) by zimbra-mta.compulab.co.il (Postfix) with ESMTP id A96A27E925D for ; Thu, 18 Aug 2011 07:35:54 +0300 (IDT) X-Virus-Scanned: amavisd-new at compulab.co.il Received: from zimbra-mta.compulab.co.il ([127.0.0.1]) by localhost (zimbra-mta.compulab.co.il [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id sQp8+IWR-1Ho for ; Thu, 18 Aug 2011 07:35:54 +0300 (IDT) Received: from [10.1.1.27] (sim-pc.compulab.local [10.1.1.27]) by zimbra-mta.compulab.co.il (Postfix) with ESMTP id 0D44F7E925A for ; Thu, 18 Aug 2011 07:35:54 +0300 (IDT) Message-ID: <4E4C968E.3060900@compulab.co.il> Date: Thu, 18 Aug 2011 07:35:26 +0300 From: Sim Zacks User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.2.18) Gecko/20110617 Lightning/1.0b2 Thunderbird/3.1.11 MIME-Version: 1.0 To: PostgreSQL general Subject: Re: Using Postgresql as application server References: <4E498536.9080109@darrenduncan.net> <4E4B6571.6080207@compulab.co.il> In-Reply-To: Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - softlayer.compulab.co.il X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - compulab.co.il X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.379 tagged_above=-5 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, KHOP_DYNAMIC=0.797, MIME_HTML_ONLY=0.723 X-Spam-Level: X-Archive-Number: 201108/576 X-Sequence-Number: 178858 On 08/17/2011 06:13 PM, Chris Travers wrote:

On Tue, Aug 16, 2011 at 11:53 PM, Sim Zacks <sim@compulab.co.il> wrote:

We are doing this same sort of thing now. If the transaction goes through,
the email record gets written to a table. We have a cron job that calls a
database function that processes all emails that have not been processed
yet. If the transaction gets rolled back, the email record does not get
written to the table and the email does not get sent.
In your scenario, if you send the NOTIFY message and then you roll back the
transaction, the helper application will still send the email. IOW, doing
this outside of the database can more easily break your transactional
integrity.

Notify hits on commit, right?

Best Wishes,
Chris Travers

My bad. I just tested this. Notify doesn't get send until after commit.