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

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.

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