Thread: 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