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

On Tue, Mar 6, 2012 at 3:21 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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.

On Tue, Mar 6, 2012 at 4:36 PM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:

> What such an external scheduler would need from core is support for
> starting up and shutting down along postmaster (as well as restarts at
> appropriate times). Postmaster already has the ability to start and
> shut down many processes depending on several different policies; I
> think it's mostly a matter of exporting that functionality in a sane way.

Tom's question is exactly on the money, and so is Alvaro's answer.

Many, many people have requested code that "runs in core", but the key
point is that all they actually want are the core features required to
build one. The actual projects actively want to live outside of core.
The "run in core" bit is actually just what Alvaro says, the ability
to interact gracefully for startup and shutdown.

What I think we need is an API like the LWlock add in requests, so we
can have a library that requests it is assigned a daemon to run in,
looking very much like autovacuum launcher, with the guts removed. It
would then be a matter for the code authors as to whether it was a
client program that interacts with server, or whether it was a full
blown daemon like autovacuum.

We talked about this at last year's Dev meeting. And we got
sidetracked into "what we really want is stored procedures". Maybe we
want that, but its a completely separate thing. Please lets not get
distracted from a very simple thing because of the existence of other
requirements.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services