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

>> 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).

I think this could be addressed simply by the ability to call actions at
a predefined interval, i.e.:

CREATE RECURRING JOB {job_name}
FOR EACH {interval}
[ STARTING {timestamptz} ]
[ ENDING {timestamptz} ]
EXECUTE PROCEDURE {procedure name}

CREATE RECURRING JOB {job_name}
FOR EACH {interval}
[ STARTING {timestamptz} ]
[ ENDING {timestamptz} ]
EXECUTE STATEMENT 'some statement'

(obviously, we'd want to adjust the above to use existing reserved
words, but you get the idea)

Activity and discretion beyond that could be defined in PL code,
including run/don't run conditions, activities, and dependancies. The
only thing Postgres doesn't currently have is a clock which fires
events. Anything we try to implement which is more complex than the
above is going to not work for someone. And the pg_agent could be
adapted easily to use the Postgres clock instead of cron.

Oh, and the ability to run VACUUM inside a larger statement in some way.
But that's a different TODO.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com