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

On Tue, Mar 6, 2012 at 10:21 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Mon, Mar 5, 2012 at 5:03 PM, Artur Litwinowicz <admin@ybka.com> wrote:
>>> Regarding a functional area I can help... but I can not understand why
>>> this idea is so unappreciated?
>
>> I think it's a bit unfair to say that this idea is unappreciated.
>
> Well, there is the question of why we should re-invent the cron wheel.
>
>> There are LOTS of good features that we don't have yet simply because
>> nobody's had time to implement them.
>
> Implementation work is only part of it.  Any large feature will create
> an ongoing, distributed maintenance overhead.  It seems entirely
> possible to me that we'd not accept such a feature even if someone
> dropped a working implementation on us.
>
> 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.

I was trying to make a general comment about PostgreSQL development,
without diving too far into the merits or demerits of this particular
feature.  I suspect you'd agree with me that, in general, a lot of
valuable things don't get done because there aren't enough people or
enough hours in the day, and we can always use more contributors.

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).

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.  Those are things that could
presumably fixed, or done differently in a new implementation, and
maybe that's all anyone needs.  Or maybe it's not.  But at any rate I
think the idea of a better job scheduler is a good one, and if anyone
is interested in working on that, I think we should encourage them to
do so, regardless of what happens vis-a-vis core.  This is a very
common need, and the current solutions are clearly more awkward than
our users would like.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company