Re: Plperlu and sending emails, is it safe? - Mailing list 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,
PostAbort?

Kind regards,

Thomas Hallgren