Обсуждение: Plperlu and sending emails, is it safe?

Hello,

I need to send emails from Postgresql triggers.

I was able to do it with a 'plperlu' function, that calls the 'system'
perl function, that itself calls the 'mail' shell function.

Is is safe to do things like this? Is there a possible concurrent access
that could arise?

Another solution would be to use cron every 5 minutes, and read the
content of a table.

Thanks

Philippe Lang

> Another solution would be to use cron every 5 minutes, and read the
> content of a table.
Yet another one to have a script listen for a notify.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Philippe Lang wrote:
> Hello,
>
> I need to send emails from Postgresql triggers.
>
> I was able to do it with a 'plperlu' function, that calls the 'system'
> perl function, that itself calls the 'mail' shell function.
>
> Is is safe to do things like this? Is there a possible concurrent access
> that could arise?

Not that I can think of, but "mail" will called as the postgres user,
which opens up a possible security hole.

> Another solution would be to use cron every 5 minutes, and read the
> content of a table.

This is my preferred solution, but there's plenty of discussion in the
archives about this.

--
   Richard Huxton
   Archonet Ltd

"Philippe Lang" <philippe.lang@attiksystem.ch> writes:
> I need to send emails from Postgresql triggers.

> I was able to do it with a 'plperlu' function, that calls the 'system'
> perl function, that itself calls the 'mail' shell function.

> Is is safe to do things like this? Is there a possible concurrent access
> that could arise?

The real issue here is transactional semantics.  What happens if the
transaction that fired the trigger rolls back due to a later error?
The transaction effectively never happened, but you can't unsend the mail.

> Another solution would be to use cron every 5 minutes, and read the
> content of a table.

This would probably be better because the cron job could only see the
results of committed transactions.  The failure mode in this case is
that the same mail could be sent more than once (if the cron job fails
between sending the mail and committing its update that deletes the
entry in the pending-mails table).  But you'd not be wondering why you
got mail that seems not to be related to anything visible in the
database.

As noted elsewhere, you could use NOTIFY of a persistent daemon process
instead of a cron job.  This might be a win if you would like the mail
sent more quickly than ~5 minutes.  The point is to put the actual
sending in a separate transaction ...

            regards, tom lane

Thomas Hallgren wrote:
> I would like to make it possible to add a callback that's called just at
> the start of a transaction as well. Such callbacks would have the
> ability to generate an error and abort the transaction. Would such a
> patch be well received?
>
Correction. Not "start of a transaction" but "just prior to commit"

- thomas

Tom Lane wrote:
> The real issue here is transactional semantics.  What happens if the
> transaction that fired the trigger rolls back due to a later error?
> The transaction effectively never happened, but you can't unsend the mail.
>
As I browsed to the code for commit, I found this in xact.c (much to my
surprise, I was told nothing like this existed):

/*
  * Register or deregister callback functions for end-of-xact cleanup
  *
  * These functions are intended for use by dynamically loaded modules.
  * For built-in modules we generally just hardwire the appropriate calls
  * (mainly because it's easier to control the order that way, where
  * needed).
  *
  * Note that the callback occurs post-commit or post-abort, so the
  * callback functions can only do noncritical cleanup.
  */
void
RegisterEOXactCallback(EOXactCallback callback, void *arg)
{
    ...

If Plperlu have hooks for these callbacks, you could prepare the email
using a trigger up to a point where everything is as ready as it
possibly can be without actually being sent. Then, at the end of the
transaction, you either send or remove all prepared emails depending on
the outcome.

I plan to add hooks for EOXactCallbacks in Pl/Java if I no one advice me
that it would be really bad idea.

I would like to make it possible to add a callback that's called just at
the start of a transaction as well. Such callbacks would have the
ability to generate an error and abort the transaction. Would such a
patch be well received?

Kind regards,

Thomas Hallgren

On Mon, Jun 28, 2004 at 05:32:55PM +0200, Thomas Hallgren wrote:
> Thomas Hallgren wrote:
> >I would like to make it possible to add a callback that's called just at
> >the start of a transaction as well. Such callbacks would have the
> >ability to generate an error and abort the transaction. Would such a
> >patch be well received?
>
> Correction. Not "start of a transaction" but "just prior to commit"

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.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Jude: I wish humans laid eggs
Ringlord: Why would you want humans to lay eggs?
Jude: So I can eat them

Tom Lane <tgl@sss.pgh.pa.us> writes:

> "Philippe Lang" <philippe.lang@attiksystem.ch> writes:
>
> > Another solution would be to use cron every 5 minutes, and read the
> > content of a table.
>
> This would probably be better because the cron job could only see the
> results of committed transactions.  The failure mode in this case is
> that the same mail could be sent more than once (if the cron job fails
> between sending the mail and committing its update that deletes the
> entry in the pending-mails table).  But you'd not be wondering why you
> got mail that seems not to be related to anything visible in the
> database.

I have experience with a system that was implemented this way and we found it
was a *huge* win. Mail is often subject to major problems caused by
circumstances outside your control. If AOL is unreachable then suddenly you
have a crisis when your mail spool fills up and your MTAs become slow
responding...

Taking the mail generation out of the critical path of the application and
into a separate process was an extremely robust approach. It let us shut down
mail generation while we emptied queues or reconfigured MTAs without impacting
the database or application at all.

Incidentally, you can arrange things to fail in either direction. In our case
if the cron job failed we would lose a batch of emails, not generate
duplicates. I'm not sure if failing by generating duplicates is as convenient
for scaling to multiple mail generation processes.

--
greg

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