To: Andrew Sullivan <ajs@crankycanuck.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: Question on triggers and plpgsql 
In-reply-to: <20050408145928.GA27718@phlogiston.dyndns.org> 
References: <4255AA15.8000508@mochima.com>
	<BE1E8DAA-A829-11D9-9125-000A95B03262@pgedit.com>
	<fef7e50cf33b53ca481ecb2554cd6403@mail.nih.gov>
	<14244.1112970986@sss.pgh.pa.us>
	<20050408145928.GA27718@phlogiston.dyndns.org>
Comments: In-reply-to Andrew Sullivan <ajs@crankycanuck.ca>
	message dated "Fri, 08 Apr 2005 10:59:28 -0400"
Date: Fri, 08 Apr 2005 11:35:47 -0400
Message-ID: <14740.1112974547@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Andrew Sullivan <ajs@crankycanuck.ca> writes:
> On Fri, Apr 08, 2005 at 10:36:26AM -0400, Tom Lane wrote:
>> AFAICS the only way that you could get into a can't-roll-back situation
>> is if the trigger tries to propagate the update outside the database.
>> For instance, the proverbial trigger to send mail: once sent you can't
>> cancel it.  But really this is dangerous even in an AFTER trigger ---
>> the transaction could still be rolled back after the AFTER trigger
>> fires.

> People who know more about this will no doubt correct me, but isn't
> such a case crying out for LISTEN/NOTIFY instead?  That is, your
> trigger puts the mail content into a table of mails to be sent, and
> wakes up the mail-sender client with the NOTIFY; the NOTIFY and the
> commit to the mail-it table only happen in that case if the
> transaction commits.  And since mail is async anyway, the extra few
> seconds shouldn't make any difference, right?

We do often recommend that, though it occurs to me that this just moves
the failure case somewhere else.  The hypothetical mail-sending process
would presumably want to send mail and then delete the associated record
from the table of pending mails ... so what if it fails after sending
the mail and before committing the delete?

What this does do for you is replace the risk of phantom emails (mail
sent but corresponding action inside the database never committed)
with the risk of duplicate emails (mail-sender sends you another one
after it restarts).  In most cases I think I'd prefer the latter.

			regards, tom lane