Re: Scheduler in Postgres - Mailing list pgsql-general

On Fri, 17 Dec 2004, 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.
>
> Additionally, cron has already been written and is already maintained,
> neither of which is true about an internal postgres scheduler.
>
> I suppose I can see the point of needing a scheduler in postgres if you
> don't give your DBAs access to cron, but from my point of view (for
> whatever that's worth), giving your DBAs access to cron seems like a small
> price to pay for an elegant database design.

You're making too many assumptions about cron:

- cron is available: true only for UNIX-like systems. Nowadays PostgreSQL
   runs natively under other OSes. You may need to install an external
   tool in order to get this functionality, and there are good chances
   it won't be cron-alike.

- you have access to cron: most of DBA tasks on PostgreSQL can be
   performed remotely, all you need is SQL access. Right now, there's
   no way to schedule simple db jobs from SQL. Think of an application
   management web interface: how are you expected to set a simple job
   up? Consider that the web server might (perhaps should) run on a
   different server than than PostgreSQL, and that using the local
   cron service might be unfeasable (again, the web server might run
   on some non-UNIX-like system).

- it is feasable to give cron automated DBA access: unauthorized
   access to either cron or the user used to run DB-relates jobs
   immediately scales up to DBA-level access. Note that I use 'DBA'
   in a broader sense here: any credendial that grants you some sort
   of dangerous write access to a certain database (might be the mere
   owner of tables, w/o other special permission as far as the DB is
   concerned). Again, this cron daemon might run on a different server.

I think that a cron replacement with a DB backend would be nice, but
that's _not_ what I'm referring to here.

I think the need to run administrative tasks on a database is _very_
common, and it's a natural part of an database system. It'd be nice
if the server provided a simple way to schedule db-related jobs.
It'd solve many issues in a very _elegant_ way, IMHO.

> On Fri, 17 Dec 2004, Guy Rouillier wrote:
>
>> Here is a real world example where a scheduler in PostgreSQL would be
>> helpful.  We collect usage statistics from our network throughout the
>> day (raw stats.)  After midnight, we roll up those raw stats into daily
>> statistics.
>> We have a very large amount of data, about 2 million rows a day a
>> growing, so I want this whole operation done on the database server.
>> It's all database work, just summing up data from one table and putting
>> the result in another table.  We have all that logic in a stored
>> procedure.  So why do I need to set up a cron job and a shell script
>> whose only task is to connect to the database and start up the stored
>> procedure?  Wouldn't it be much simpler just to have a schedule in
>> PostgreSQL that says "at 12:01, run this stored procedure"?
>>
>> Another advantage to having a scheduler in the database is to ease your
>> DBA's job in maintenance, and to coordinate work by multiple systems.
>>
>> --
>> Guy Rouillier

Thanks Guy for your contribution: that's _exactly_ the kind of use case
I was thinking of. With an in-core scheduler, you are able to set the
jobs up via SQL commands (no need for shell access, no need for cron/at
access on the server, no need to store passwords anywhere), and they'll
run at the right time, with the right permissions.

.TM.
--
       ____/  ____/   /
      /      /       /            Marco Colombo
     ___/  ___  /   /              Technical Manager
    /          /   /             ESI s.r.l.
  _____/ _____/  _/               Colombo@ESI.it