Re: Scheduler in Postgres

On Fri, Dec 17, 2004 at 03:25:20PM -0800, Ben wrote:
> I have many such tasks. Depending on implementation, it has the potential
> to be a TINY amount of less work to schedule such tasks from inside
> the database, but it takes all of about a minute to schedule it through
> cron. Including the amount of time it takes to refer to the man page.

Sure, it might take no amount of time to schedule it, but that's not the
point. Now you have to have code to:

- deal with what you do if the database is down when cron fires off
- provide some kind of semaphore so you don't have multiple copies of
one job running at once
- connect to the database as the proper user using the proper
credentials and setup the proper environment

What this all boils down to is there are 2 different sets of needs. For
some things cron works great, and there's no need to worry about any of
this. For other things it would be much easier not to worry about cron
at all and just have the database handle your periodic tasks (which
presumably are stored procedures or something similar).

I think pgcron is an interesting idea, though not directly PostgreSQL
related (I'd argue that if you're going to make a fancier replacement
for cron you should make it back-end agnostic). I also think a lot of
people would benefit from having something similar to dbms_job (Oracle's
scheduling system) available for PostgreSQL. As I mentioned in another
email, I think it's possible to get 90-95% of dbms_job's functionality
without having to touch the backend at all, which eliminates the
argument that development time is better spent elsewhere (since the core
PostgreSQL folks wouldn't have to worry about it at all). BTW, I mention
dbms_job as an example not because it's necessarily the right way to
handle scheduling, but because it's a convenient example. Oracle's newer
job system is even better.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"