Re: Sheduling in SQL

Paul <magamos(at)mail(dot)ru> writes:
> CREATE SHEDULER name
> ON table.field
> [FOR [EACH]|[LAST]]
> EXECUTE PROCEDURE func(arguments)

> When the current time becomes equal or more than minimal time in
> the _table.field_, the event happens and the _func_ will be executed,
> and after that all records in this _table_ that in the _field_ have
> time equal or less than current time will be deleted.

This strikes me as way too problem-specific to be reasonable as a
general-purpose system extension.

You can actually build this sort of facility in Postgres as it stands,
using a background process that executes the items from the "todo"
table. You'd put rules or triggers on the todo table to send out a
NOTIFY event, which the background guy would listen for; that would cue
him to re-select the minimum timestamp in the table. Then he'd just
sleep until the next NOTIFY or time to do something.

The primary advantage of doing things this way is that you have an
actual client process executing the todo actions, so it could perform
outside-the-database actions as well as any database updates that might
be needed. In the scheme you describe, the "func" would have to be
executed in some disembodied backend context --- it wouldn't even have
a client to talk to, let alone any chance of doing outside-the-database
actions.

I've built applications that do roughly this sort of thing in Postgres
(some of the protocol changes in 6.4 were done to make it easier ;-)).
Unfortunately that was proprietary code and I can't show it to you,
but it's not really difficult. Perhaps you'd like to do up a simple
example and contribute it as a "contrib" module?

regards, tom lane