This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
Date: Sat, 23 Sep 2000 17:27:26 +0200
From: Louis-David Mitterrand <cunctator@apartia.ch>
To: modperl@apache.org
Subject: running maintenance tasks on DB
Hello,
Approaching completion of an online auction system based on Postgres and
mod_perl/Apache I yet have to devise a good way of running certain
maintenance tasks on the DB like closing auctions, notifying winners,
transferring old records to archive tables, etc.
What is the usual way of programming such tasks? Write a backend
function (in pl/sql or C) and call it from a cron job? From a mod_perl
handler?
Thanks in advance for any insight, cheers,
--
Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.org
===
Date: Sun, 24 Sep 2000 08:00:40 +0800
To: ldm@apartia.ch, modperl@apache.org
From: Gunther Birznieks <gunther@extropia.com>
Subject: Re: running maintenance tasks on DB
I usually write such things in Java. That's how I do it. :)
Just kidding.
Anyway, there are several ways:
1) Have the auction script do the cleanup every so often itself
The problem with this method is that it is non-deterministic and it can be
more resource intensive if the script is used a lot.
If the script is not used a lot, it really doesn't matter and it can be
nice not having to worry about a cron job -- however you script seems to
want to do a LOT not just a few things in maintenance so I wouldn't
recommend this method anyway.
2) Write a separate mod_perl handler yourself.
Why mod_perl? If it's an admin script you can just simply whip it up and
use it. However, I will say that I am always wary of admin web pages as it
exposes your system to the outside world and potential hackers.
This model is useful if your maintenance is so infrequent you don't need to
run the admin more than once a day or once a week on your own schedule.
3) CRON Job
Do this if you want the job timed to a low granularity. eg once a day.
4) Perl Daemon that sleeps and then wakes up to try maintenance tasks.
Do this if you want the job timed to a low granularity. eg once every 5
minutes. It sucks to have CRON load up your system with calls to load Perl
every 5 minutes after putting in so much effort to make things faster with
mod_perl.
At 05:27 PM 9/23/00 +0200, Louis-David Mitterrand wrote:
>Hello,
>
>Approaching completion of an online auction system based on Postgres and
>mod_perl/Apache I yet have to devise a good way of running certain
>maintenance tasks on the DB like closing auctions, notifying winners,
>transferring old records to archive tables, etc.
>
>What is the usual way of programming such tasks? Write a backend
>function (in pl/sql or C) and call it from a cron job? From a mod_perl
>handler?
>
>Thanks in advance for any insight, cheers,
>
>--
>Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.org
===
Date: Sat, 23 Sep 2000 20:02:39 -0700
From: drfrog@smartt.com
To: ldm@apartia.ch
Subject: Re: running maintenance tasks on DB
Louis-David Mitterrand wrote:
>
> Hello,
>
> Approaching completion of an online auction system based on Postgres and
> mod_perl/Apache I yet have to devise a good way of running certain
> maintenance tasks on the DB like closing auctions, notifying winners,
> transferring old records to archive tables, etc.
>
> What is the usual way of programming such tasks? Write a backend
> function (in pl/sql or C) and call it from a cron job? From a mod_perl
> handler?
>
> Thanks in advance for any insight, cheers,
>
> --
> Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.org
another possible is to use postgres's internal module ability
for instance you could have a trigger activate a module at a certain
time through postgres that would do any sort of database call and
process.
multiple languages supported
perl c procedural
look in postgresql src/contrib dir
in there look for a dir named fulltextindex or fti
its a good example of how to do this sort of thing in c
in perl i dont know what is involved but its sure to be similar
as far as hooking the module up to postgres's function command
i've used the cron job type before
and if you decide on that, id suggest using cursors and break up the
load
on the database especially if you are running db and web server on same
system
===
Date: Sun, 24 Sep 2000 11:31:44 +0200
From: Louis-David Mitterrand <cunctator@apartia.ch>
To: Gunther Birznieks <gunther@extropia.com>
Cc: modperl@apache.org
Subject: Re: running maintenance tasks on DB
On Sun, Sep 24, 2000 at 08:00:40AM +0800, Gunther Birznieks wrote:
> I usually write such things in Java. That's how I do it. :)
>
> Just kidding.
<aol>Me too</aol> I used to program in java. But it brought me only
frustration. After that experience I pledged to only use "free"
languages and now I feel much better ;)
> Anyway, there are several ways:
>
> 1) Have the auction script do the cleanup every so often itself
>
> The problem with this method is that it is non-deterministic and it can be
> more resource intensive if the script is used a lot.
>
> If the script is not used a lot, it really doesn't matter and it can be
> nice not having to worry about a cron job -- however you script seems to
> want to do a LOT not just a few things in maintenance so I wouldn't
> recommend this method anyway.
You mean write a DB trigger function that would be called after a SELECT
on the (say) auction table and only perform its stuff if it has not been
called in the last X minutes?
That could an interesting strategy: the function would exit immediately
upon checking a time condition and not be a burden on the system.
> 2) Write a separate mod_perl handler yourself.
>
> Why mod_perl? If it's an admin script you can just simply whip it up and
> use it. However, I will say that I am always wary of admin web pages as it
> exposes your system to the outside world and potential hackers.
>
> This model is useful if your maintenance is so infrequent you don't need to
> run the admin more than once a day or once a week on your own schedule.
I was thinking of a PerlRestartHandler for certain daily tasks (as
Apache is restarted each night after log rotation, right?) combined with
a ChildInitHandler for more frequent tasks. Of course that last handler
would check a time condition before doing its stuff so as not to be
activated more than every X minutes (same as the trigger function).
The drawback is if the site has such low traffic that Apache children
are not spawned often enough for the system's proper maintenance. But
the in that case I have other worries, right? ;-)
> 3) CRON Job
>
> Do this if you want the job timed to a low granularity. eg once a day.
Yes, this is the standard solution I am trying to avoid in name of
better "integration". But I'll probably have to use it at some point.
> 4) Perl Daemon that sleeps and then wakes up to try maintenance tasks.
>
> Do this if you want the job timed to a low granularity. eg once every 5
> minutes. It sucks to have CRON load up your system with calls to load Perl
> every 5 minutes after putting in so much effort to make things faster with
> mod_perl.
Hmm, I hadn't thought about the inefficiency of loading a perl
interpreter, especially in relation with the otherwise efficient
mod_perl. If I run the maintenance script from cron it will probably
take the form of a simple shell command to launch a DB backend function
(in pl/sql or C) that would take care of everything (I wrote DB
functions that can send mail to users using their respective locale).
Thanks for your input, cheers,
> At 05:27 PM 9/23/00 +0200, Louis-David Mitterrand wrote:
> >Hello,
> >
> >Approaching completion of an online auction system based on Postgres and
> >mod_perl/Apache I yet have to devise a good way of running certain
> >maintenance tasks on the DB like closing auctions, notifying winners,
> >transferring old records to archive tables, etc.
> >
> >What is the usual way of programming such tasks? Write a backend
> >function (in pl/sql or C) and call it from a cron job? From a mod_perl
> >handler?
> >
> >Thanks in advance for any insight, cheers,
> >
> >--
> >Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.org
>
--
Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.org
** Hello and Welcome to the Psychiatric Hotline **
If you are paranoid-delusional, we know who you are and what you want
- just stay on the line so we can trace the call.