Re: scheduler in core

On Sat, Feb 20, 2010 at 4:33 PM, Jaime Casanova
<jcasanov(at)systemguards(dot)com(dot)ec> wrote:
> Hi,
>
> I'm trying to figure out how difficult is this
>
> What we need:
> - a shared catalog
> - an API for filling the catalog
> - a scheduler daemon
> - pg_dump support
>
>
> A shared catalog
> -------------------------
> Why shared? obviously because we don't want to scan all database's
> pg_job every time the daemon wake up.
> Maybe something like:
>
> pg_job (
>    oid                -- use the oid as pk
>    jobname
>    jobdatoid       -- job database oid
>    jobowner       -- for permission's checking
>    jobstarttime   -- year to minute
>    jobfrequency  -- an interval?
>    jobnexttime or joblasttime
>    jobtype          -- if we are going to allow plain sql or
> executable/shell job types
>    jobexecute or jobscript
> )
>
> comments about the catalog?
>
>
> An API for filling the catalog
> -----------------------------------------
> do we want a CREATE JOB SQL synatx? FWIW, Oracle uses functions to
> create/remove jobs.
>
>
> An scheduler daemon
> --------------------------------
> I think we can use 8.3's autovacuum daemon as a reference for this...
> AFAIK, it's a child of postmaster that sleep for $naptime and then
> looks for something to do (it also looks in a
> catalog) and the send a worker to do it
> that's what we need to do but...
>
> for the $naptime i think we can autoconfigure it, when we execute a
> job look for the next job in queue and sleep
> until we are going to reach the time to execute it
>
> i don't think we need a max_worker parameter, it should launch as many
> workers as it needs
>
>
> pg_dump support
> --------------------------
> dump every entry of the pg_job catalog as a CREATE JOB SQL statement
> or a create_job() function depending
> on what we decided
>
> ideas? comments?

IMNSHO, an 'in core' scheduler would be useful. however, I think
before you tackle a scheduler, we need proper stored procedures. Our
existing functions don't cut it because you can manage the transaction
state yourself.

merlin