From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andrew Sullivan <ajs@crankycanuck.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: Question on triggers and plpgsql
Date: Fri, 08 Apr 2005 11:35:47 -0400
Message-ID: <14740.1112974547@sss.pgh.pa.us> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, ajs@crankycanuck.ca
Subject: Re: Question on triggers and plpgsql
In-Reply-To: <14740.1112974547@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox