Re: Database viewpoint of subject - Sending e-mails from database table with Cronjob

Andre Lopes <lopes80andre(at)gmail(dot)com> wrote:

> My doubt is... The CronJob runs every 10 minutes, but If I have 100.000
> e-mails to send the script will not be able to send all the 100.000 e-mails
> in 10 minutes.

More generally, your question is how to deal with a cron job that is
intended to run every ten minutes, but which you don't want to run more
than one copy of (i.e. this has little or nothing to do with PostgreSQL,
really).

My usual solution is twofold:

1. have the cron job take a lock file as the first thing it does: this
ensures only one job can run at a time

Something like FreeBSD's lockf(1) is good for this, and isn't hard to
port or write a similar utility for any OS I've had to use:

http://www.freebsd.org/cgi/man.cgi?query=lockf&apropos=0&sektion=1&manpath=FreeBSD+8.0-RELEASE&format=html

2. include in the job a 'dead man switch' that terminates the job
if it runs for "too long", whatever "too long" is in your context.

Obviously, 10 minutes is possible; 10 hours is probably not.

Making sure client applications terminate (be they cron jobs, CGI
scripts, or anything else) stops all your database connections
being tied up by hung scripts, to bring the discussion slightly
back to PostgreSQL).

> How can I deal with this problem? There is no problem to have multiple
> CronJobs runing in background?

Multiple cron jobs in general, no. Multiple instances of a single
cron job depends on the cron job.

As another poster wrote, I do hope this isn't for spam, as you say it
isn't, but the basics I'd follow are #1 and #2 above, and let the mail
system handle both the queue and the allowable amount of parallel
outbound SMTP connections. That's what mail software is for.

Of course, I'd _definitely_ not use any such thing for spamming....

Regards,

Giles