Re: running maintenance tasks on DB

On Sat, Sep 23, 2000 at 12:45:02PM -0400, Neil Conway wrote:
> On Sat, Sep 23, 2000 at 05:26:47PM +0200, Louis-David Mitterrand wrote:
> > 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?
>
> I don't know if this is the best way, but I usually just write a Perl
> script and run it through cron. You just have to make sure that you
> maintain data integrity while doing maintainence - i.e. there is no
> point where the data is invalid. This usually means putting stuff
> into transactions, but that's no big deal.
>
> I don't believe you can get backend functions to run at certain
> times, without using a mechanism like cron to connect to the DB and
> run them.

It would be really great to have that functionality in the backend,

> A mod_perl handler is a totally different matter. That's for doing
> stuff with HTTP requests - isn't this unrelated?

Indeed I wasn't making much sense talking of mod_perl handlers to
perform DB maintenance :) The reason I came to thinking about them is
because cron jobs appear as less elegant and integrated with the whole
application, they appear as external crutch filling a missing DB
functionality (running scheduled tasks).

Ideally I'd like the auction application to only rely on its two main
components: Postgres and mod_perl/Apache. The mod_perl idea was to
install the maintenance script as a ChildInitHandler which is not
directly related to content generation. Is that totally harebrained?

> I would think that you'd have mod_perl handlers displaying your
> content and manipulating the DB, and have some scripts set to run
> every X minutes/hours which do maintainence.
>
> I use Perl, but you can obviously use any language you like, provided
> it has a Postgres interface.

Thanks for sharing your experience, cheers,

--
Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.org

        "You're cute when you're stupid"