Re: [OT] Dilemma about OS <-> Postgres interaction

Rory Campbell-Lange wrote:

> I have an application, largely written in plpgsql, where messages are
> dumped into an 'outbox' for sending via email or text message.
>
> I am in a dilemma about how best to pick up these messages and send them
> on to the message sending systems. At present I have looping perl
> scripts which have a permanent database connection, which I run through
> screen sessions on my server. Rather embarassingly whenever I upgrade my
> application code (reload the database and export the latest CVS code) or
> upgrade Postgres, my looping scripts die.
>
> I suppose I could use plperl and create a trigger to alert a system-wide
> daemon that a message was ready for sending whenever a new one dropped
> into the 'outbox', and restart the daemon if it had died. I feel
> superstitious about using an untrusted language for functions though.
>
> Any sage advice? I realise this is both a beginner's quandry, and also
> not particularly Postgresql specific. Apologies.
>
> Rory

Assuming you are on a unix of some sort,  Two possible solutions come
immediately to mind.

1) Fire off a non-looping version of your perl scrip from crontab

2) Run your looping perl script from the inittab.

the crontab solution would be my preference: it's easier to set up the job
to run under a user other than root, and you can tailor when your script
runs (say every 5 minutes between 8:00 AM and 6:00 PM Monday to Friday).

In both cases, you'd probably want to add some tests to see if your database
was up before trying to connect