Re: elegant and effective way for running jobs inside a database

Robert Haas <robertmhaas@gmail.com> writes:
> On Tue, Mar 6, 2012 at 10:21 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> But having said that, it's not apparent to me why such a thing would
>> need to live "inside the database" at all. �It's very easy to visualize
>> a task scheduler that runs as a client and requires nothing new from the
>> core code. �Approaching the problem that way would let the scheduler
>> be an independent project that stands or falls on its own merits.

> But since you brought it up, I think there is a lot of value to having
> a scheduler that's integrated with the database.  There are many
> things that the database does which could also be done outside the
> database, but people want them in the database because it's easier
> that way.  If you have a web application that talks to the database,
> and which sometimes needs to schedule tasks to run at a future time,
> it is much nicer to do that by inserting a row into an SQL table
> somewhere, or executing some bit of DDL, than it is to do it by making
> your web application know how to connect to a PostgreSQL database and
> also how to rewrite crontab (in a concurrency-safe manner, no less).

Sure, and I would expect that a client-side scheduler would work just
the same way: you make requests to it through database actions such
as inserting a row in a task table.

> Now, the extent to which such a schedule requires core support is
> certainly arguable.  Maybe it doesn't, and can be an entirely
> stand-alone project.  pgAgent aims to do something like this, but it
> has a number of deficiencies, including a tendency to quit
> unexpectedly and a very klunky interface.

Well, if they didn't get it right the first time, that suggests that
it's a harder problem than people would like to think.  All the more
reason to do it as an external project, at least to start with.
I would much rather entertain a proposal to integrate a design that's
been proven by an external implementation, than a proposal to implement
a design that's never been tested at all (which we'll nonetheless have
to support for eternity, even if it turns out to suck).
        regards, tom lane