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

On Wed, Mar 7, 2012 at 2:15 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> 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.

The reason why stored procedures were brought up is because they are
one way to implement an ad hoc scheduler without rewriting cron.
Another (better) way to do that would be to have the postgres expose a
heartbeat ticker that you could layer a scheduler on top of. These
are minimalist approaches with the intent of providing scaffolding
upon which robust external solutions can be built. Not having them
forces dependency on the operating system (cron) or an external daemon
like pgqd. PGQ does exactly this (over the daemon) so that the bulk
of the algorithm can be kept in SQL which is IMNSHO extremely nice.

With a built in heartbeat you can expose a 100% SQL api that user
applications can call without having to maintain a separate process to
drive everything (although you can certainly do that if you wish).
This is exactly what PGQ (which I consider to be an absolute marvel)
does. So if you want to start small, do that -- it can be used to do
a number of interesting things that aren't really possible at the
moment.

OTOH, if you want to implement a fully fledged out job scheduler
inside of the postmaster, then do that...it's a great solution to the
problem. But it's a little unfair to dismiss those who are saying:
"If I had stored procedures, this could get done" and conclude that
scheduling through the postmaster is the only way forward.

merlin