Re: Scheduler in Postgres

On Sat, Dec 18, 2004 at 15:00:17 -0600,
"Jim C. Nasby" <decibel(at)decibel(dot)org> wrote:
>
> In oracle, jobs are fired off by the database engine. If the database is
> up and job support is enabled, your jobs will run. Without some kind of
> support for the database to fire up connections and execute some kind of
> code we would have to rely on some external means to do so. This is less
> robust, isn't cross-platform, and requires more work of the end-users.

In my experience, cron is never down, but databases are taken down on
a regular basis. I don't think the reliability of cron is a problem.

> The other feature is that the connection running jobs in oracle has the
> ability to re-create the connection environment used to submit the job.
> This means jobs are run as the same user who submitted the job, and
> certain session settings are also duplicated. In PostgreSQL, there's
> currently no way to assume the identity of another user.

cron also runs jobs as the user that created them. There is an issue when
you want to let people who don't have shell access to the database server
run scheduled jobs.

I am not sure how the identity issue applies here. If you are running a
cron script you can connect as whoever you are authorized to and can open
a separate session for each job.

> Even with lack of support for these two features, I still think it would
> be very usefull to create a generic job system, probably as a pgFoundary
> project. Enough people have asked about it that I'm sure there's plenty
> of re-invented code out there. If we have a solid framework that people
> are using, we'll have a much stronger case for getting the two features
> I mentioned added to the back-end.

That seems like the way to start. If people think they need something better
than cron, you should get some help. There is a lot you could do with
logging and job dependencies that are not built in to cron.