Message-ID: <4E4B6571.6080207@compulab.co.il>
Date: Wed, 17 Aug 2011 09:53:37 +0300
From: Sim Zacks <sim@compulab.co.il>
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: pgsql-general@postgresql.org
Subject: Re: Using Postgresql as application server
References: 
 <CAN2Y=uPjF14F5A9JXj02_vaPw4TMR0WGQid_w80xt4sC8o4qGg@mail.gmail.com>
	<4E498536.9080109@darrenduncan.net>
	<CAKt_Zft3XH_9bdcS-mZM6aSJ4824r7H3eFAx4CWwdoDwr=vb0A@mail.gmail.com>
In-Reply-To: 
 <CAKt_Zft3XH_9bdcS-mZM6aSJ4824r7H3eFAx4CWwdoDwr=vb0A@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 08/15/2011 11:50 PM, Chris Travers wrote:
> On Mon, Aug 15, 2011 at 1:44 PM, Darren Duncan<darren@darrenduncan.net>  wrote:
>
>> I believe that it is ideal for Postgres to be computationally complete in
>> that one *could* use it to implement a complete application.  That isn't to
>> say one should do this as a matter of course, good to use appropriate tools
>> for a job, but that it should at least be possible if one wanted to. --
>
> I think the limit is actually transactional control.
>
> So, suppose I manage inventory....
>
> I want an email to go out to the ordering manager when the quantity I
> have of an item drops below the re-order point.  I also want this
> email NOT to go out if the transaction rolls back.  (Wait, the order
> of 50000 widgets I just processed rolled back because it isn't to a
> valid customer!  We normally only sell 50000 per year anyway.  No need
> for the email.)
>
> 1)  I don't see how this is possible directly from within PostgreSQL
> 2)  Given the obvious ways around this, I don't see why this is
> desirable to add to PostgreSQL.....
>
> Best Wishes,
> Chris Travers
>
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.

Sim