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

On Wed, Mar 7, 2012 at 7:55 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> 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.

It's not the only way, I agree. But we do need a way forwards
otherwise nothing gets done.

The stored procedure route sounds attractive but its a long way off
and doesn't address all of the states needs people have voiced. I'm
not against doing both, I just want to do the quickest and easiest.

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