Skip site navigation (1) Skip section navigation (2)

Re: Plperlu and sending emails, is it safe?

From: Thomas Hallgren <thhal(at)mailblocks(dot)com>
To: Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl>
Cc: pgsql-general(at)postgresql(dot)org, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: Plperlu and sending emails, is it safe?
Date: 2004-06-28 23:08:45
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
Tom Lane wrote:

>It's a *really* bad idea to expose that to users of the PL.
Alvaro Herrera wrote:

>You want to abort the transaction on the callback?  What for?  You could
>have aborted it earlier.
>Of course, in a function you could save the mails you are going to send
>and register a callback for the actual send, or discard if the
>transaction is going to abort.  But you have to be very careful not to
>do cause errors during the sending of the mails ...
>All in all, I think it's easier to do it with NOTIFY or a cron job.
I think I need to explain what I want to do in more detail.

What I have in mind would not in any way compromise the transaction 
code. If the EOXactCallback will have that effect if it fails, then 
that's not the mechanism that I want (see questions at the end). What I 
really want are two things. Both of them should, IMHO be completely 
harmless from postgres point of view:

1. A callback that occurs "after the fact", i.e. after *all* 
transactional code has completed for a specific transaction. Thus, a 
failure in this code would not have any effect whatsoever on the 
transaction. Hence, it would be perfectly safe to invoke this callback 
just as any other function and there would be no danger involved if user 
code is executed in a Pl/<lang> module. It would execute very similar to 
a NOTIFY after transaction commit with the slight difference that no 
explicit NOTIFY is needed and that there will be a callback invocation 
assoicated with both commit and abort.

The documentation for this callback must of course clearly state that 
there's no way that the user can change the outcome of the transaction. 
It's already complete (or aborted). For the email case this would mean 
that if the sending of the mail fails, the best the callback can do is 
to log that fact and rely on other mechanisms to clean it up later. No 
big deal since smtp is far from fail safe anyway.

2. A callback that occurs "before the fact", i.e. just before the commit 
code is entered. It's executed just like any deferred trigger would be 
and here, an error would cause the transaction to abort. Since it's just 
like any other trigger, there should be no problem relaying the call to 
user code executed by a Pl/<lang> module.

So why not abort earlier or use NOTIFY this?
Assume that the code executed "before the fact" does some complex form 
of integrigy checking that cannot be bound to one specific table (and 
thus, not to one deferred trigger). It might for instance be based on a 
RETE Rule  executing on a "bill of material" collected from actions 
performed throughout the transaction. To rely on that the client will 
issue a NOTIFY just before the commit in order to maintain integrity is 
not feasible. If anything, that would be to move the responsabilities to 
the wrong place. I see the SQL layer as the access API towards my data. 
Integrity must be maintained behind that API without the need for 
additional calls that might be forgotten.

The email example would probably have limited use of the "before the 
fact" callback. Perhaps it could ensure that an SMTP connection exists 
if emails have been generated (using a fairly short timeout :-) ).

Could NOTIFY be used for the "after the fact" case then?
Well, AFAIK there is no NOTIFY equipped with an abort so where would I 
place the clean-up code? And even if there is, I'd argue that this, 
again, would be moving responsabilities to the wrong place. As the 
database designer I'd like the ability to design a system where an 
attempt will be made to send the emails on commit if some specific 
changes has been made to the database, no matter what. I don't want to 
rely on that all clients will issue a NOTIFY.

Questions I have now are:
1. I see that some additonal callbacks are executed after the EOXact 
stuff and that interrupts are disabled during exeuction. So this is 
probably not the mechanism that I want. Question is, is this something 
that is in use today? Or could the CallEOXactCallbacks be moved to the end?

2. I guess that the answer to my first question is no, it cannot be 
moved since that would break backward compatibility. If that's the case, 
do you see a problem in introducing a more harmless callback mechanism 
that can be called with a state enum denoting PreCommit, PostCommit, 

Kind regards,

Thomas Hallgren

In response to

pgsql-general by date

Next:From: Carl E. McMillinDate: 2004-06-29 00:04:45
Subject: Re: Seeing uncommitted transactions
Previous:From: Alvaro HerreraDate: 2004-06-28 21:35:25
Subject: Re: Q: upgrading from 7.4.1 to 7.4.3.

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group