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

Excerpts from Pavel Stehule's message of mar mar 06 14:57:30 -0300 2012:
> 2012/3/6 Robert Haas <robertmhaas(at)gmail(dot)com>:
> > On Tue, Mar 6, 2012 at 12:37 PM, Christopher Browne <cbbrowne(at)gmail(dot)com> wrote:
> >> On Tue, Mar 6, 2012 at 12:20 PM, Artur Litwinowicz <admin(at)ybka(dot)com> wrote:
> >>> Algorithm for first loop:
> >>> check jobs exists and is time to run it
> >>>   run job as other sql statements (some validity check may be done)
> >>>   get next job
> >>> no jobs - delay
> >>
> >> There are crucial things missing here, namely the need to establish at
> >> least one database connection in order to be able to check for the
> >> existence of jobs, as well as to establish additional connections as
> >> contexts in which to run jobs.
> >>
> >> That implies the need for some New Stuff that isn't quite the same as
> >> what we have within server processes today.
> >>
> >> There is nothing horrible about this; just that there's some extra
> >> mechanism that needs to come into existence in order to do this.
> >
> > And also some interface.  It'd be useful to have background jobs that
> > executed either immediately or at a certain time or after a certain
> > delay, as well as repeating jobs that execute at a certain interval or
> > on a certain schedule.  Figuring out what all that should look like
> > is, well, part of the work that someone has to do.
>
> +1

It seems to me that we could simply have some sort of external daemon
program running the schedule, i.e. starting up other programs or running
queries; that daemon would connect to the database somehow to fetch
tasks to run. Separately a client program could be provided to program
tasks using a graphical interface, web, or whatever (more than one, if
we want to get fancy); this would also connect to the database and store
tasks to run by the daemon. The client doesn't have to talk to the
daemon directly (we can simply have a trigger on the schedule table so
that the daemon receives a notify whenever the client changes stuff).

It seems to me that the only thing that needs core support is the
ability to start up the daemon when postmaster is ready to accept
queries, and shut the daemon down when postmaster kills backends (either
because one crashed, or because it's shutting down).

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support