Thread: the feasibility of sending email from stored procedure in Postgres
Can send email from stored procedure in Postgres?
On Fri, 15 Feb 2008 11:59:19 +0800 hewei <heweiweihe@gmail.com> wrote: > Can send email from stored procedure in Postgres? Sure, see plperl. Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit
Attachment
Joshua D. Drake wrote: > On Fri, 15 Feb 2008 11:59:19 +0800 > hewei <heweiweihe@gmail.com> wrote: > >> Can send email from stored procedure in Postgres? > > Sure, see plperl. There was also this: http://sourceforge.net/projects/pgmail/ but no idea whether it's being maintained. -- Postgresql & php tutorials http://www.designmagick.com/
Re: the feasibility of sending email from stored procedure in Postgres
From
"Christopher Browne"
Date:
On 2/14/08, hewei <heweiweihe@gmail.com> wrote: > Can send email from stored procedure in Postgres? In principle, yes, using one of the "untrusted" stored function languages. pl/perl, pl/sh, pl/python, and such. I wouldn't do things that way... I would instead queue messages (or suitable information about them) in a table, and have a process outside PostgreSQL periodically poll for them. There are several benefits to that approach: 1. You're not pushing error handling of email problems inside the PostgreSQL back end. That could be rather risky. 2. You're not spawning an MTA connection every time you submit a message. This could be rather expensive. In contrast, the "poll a queue" approach lets something completely external deal with email problems. And it should be able to submit multiple messages more or less at once, which should improve efficiency rather a lot; no need to open up sockets to port 25 a whole bunch of times... -- http://linuxfinances.info/info/linuxdistributions.html "The definition of insanity is doing the same thing over and over and expecting different results." -- assortedly attributed to Albert Einstein, Benjamin Franklin, Rita Mae Brown, and Rudyard Kipling
> I would instead queue messages (or suitable information about them) in > a table, and have a process outside PostgreSQL periodically poll for them Why poll when you can wait? http://www.postgresql.org/docs/8.2/interactive/sql-notify.html
Em Friday 15 February 2008 12:36:37 Adam Rich escreveu: > > I would instead queue messages (or suitable information about them) in > > a table, and have a process outside PostgreSQL periodically poll for them > > Why poll when you can wait? > > http://www.postgresql.org/docs/8.2/interactive/sql-notify.html To use cron and prevent system resources being allocated when not needed? To prevent one other external system to keep a connection open to the database? To guarantee that if the external system crashed somehow during one batch execution it would still keep sending emails when it was activated again? I can see notify being useful to GUI clients that need to update some information in near real time. Even for web applications it is easier to code a "poll" and a refresh using AJAX to prevent full screen redraws. I don't see LISTEN/NOTIFY as an useful tool for sending emails, though. -- Jorge Godoy <jgodoy@gmail.com>
Re: the feasibility of sending email from stored procedure in Postgres
From
"Christopher Browne"
Date:
On Feb 17, 2008 7:47 AM, Jorge Godoy <jgodoy@gmail.com> wrote: > Em Friday 15 February 2008 12:36:37 Adam Rich escreveu: > > > I would instead queue messages (or suitable information about them) in > > > a table, and have a process outside PostgreSQL periodically poll for them > > > > Why poll when you can wait? > > > > http://www.postgresql.org/docs/8.2/interactive/sql-notify.html > > To use cron and prevent system resources being allocated when not needed? > > To prevent one other external system to keep a connection open to the > database? > > To guarantee that if the external system crashed somehow during one batch > execution it would still keep sending emails when it was activated again? > > I can see notify being useful to GUI clients that need to update some > information in near real time. Even for web applications it is easier to > code a "poll" and a refresh using AJAX to prevent full screen redraws. > > I don't see LISTEN/NOTIFY as an useful tool for sending emails, though. It is not useful as a single tool for the purpose. It is, however, useful as a component that allows a "polling" system to be more quickly notified of change without needing to pummel the DBMS with more queries. Indeed, the use of LISTEN/NOTIFY would mean that the "polling system" could fairly much ensure that the only time it submitted queries to the DBMS is when there is actually work to be done. Yes, you need to hold a connection open, but that could be *cheaper* than having a cron job open and close a connection once a minute. It's fair to say that a cron-based system would need to be designed to "reset itself" for each iteration. That does not prevent putting the same design aspects into a LISTEN/NOTIFY-based solution to the problem, THAT WOULD BE CHEAPER. But by quibbling over this, I think we're diverging from the Original Poster's real problem. In that he was considering sending email direct from a SP, I don't think fine points of optimization were high in his priorities. I'd agree that a cron-based system that wakes up once every few minutes is likely to be a bit simpler to build and deploy than a "daemon" that uses LISTEN/NOTIFY. That simplicity may make the cron approach preferable. -- http://linuxfinances.info/info/linuxdistributions.html "The definition of insanity is doing the same thing over and over and expecting different results." -- assortedly attributed to Albert Einstein, Benjamin Franklin, Rita Mae Brown, and Rudyard Kipling