Thread: Sheduling in SQL

Sorry, if I used not corresponding mailing list, but I really dont
know where to send such email.

Is that possible to add one more SQL command to Postgres? The problem
that IMHO no one RDBMS allows SQL command for sheduling. To support
sheduling SQL programmers have to use outer tools to periodically
check database if event commit. But IMHO it's much better to add one
more SQL command to allow sheduling in same SQL.

My thoughts about such command follow:
=====================================================================

The SQL command for sheduler creating:

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.

The other fields of this _table_ could be used as _arguments_ (or
agregates of the other fields when _FOR EACH_ is absent).

_FOR LAST_ - only for the record(s) of the _table_ that has(ve) the
maximum time (that equal or less the current time) the event(s) will
be processed.

_FOR EACH_ - if there is such parameter for each corresponding record
the event could be processed, not for all at once.

For each _CREATE SHEDULER_ will be created:
1. B-tree index on _table.field_.
2. Inner trigger on insert/delete/update _table.field_ to have up to
date min(_table.field_) for nearest event processing.


The SQL command for sheduler deleting:

DELETE SHEDULER name

====================================================================

-- 
Best regards,Paul Mamin                      mailto:magamos@mail.ru

Paul <magamos@mail.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

Tom Lane wrote:

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

I may misremember, but IIRC some older protocol (or at least libpq) 
returned 0 as backend pid to listening client if it was notified by itself.

Currently it returns the actual pid for any backend. Is this what you 
changed?

Anyhow we need some _documented_ way to get backend pid (there is one 
actually received and stored with "cookie" for Ctrl-C processing, but 
AFAIK it is neither documented as being the backend id nor is there a 
function to get at it).

For my own use I created a C function pid() but perhaps there should be 
something mainstream for this.

---------------
Hannu

Hannu Krosing <hannu@tm.ee> writes:
> Tom Lane wrote:
>> (some of the protocol changes in 6.4 were done to make it easier ;-)).

> I may misremember, but IIRC some older protocol (or at least libpq) 
> returned 0 as backend pid to listening client if it was notified by itself.

> Currently it returns the actual pid for any backend. Is this what you 
> changed?

That was one of the smaller items.  The bigger problem was that the
backend wouldn't forward you NOTIFY events unless you issued a constant
stream of dummy queries.

> Anyhow we need some _documented_ way to get backend pid

PQbackendPID() seems adequately documented to me ...
        regards, tom lane

On Thu, 15 Mar 2001, Paul wrote:

> Sorry, if I used not corresponding mailing list, but I really dont
> know where to send such email.
> 
> Is that possible to add one more SQL command to Postgres? The problem
> that IMHO no one RDBMS allows SQL command for sheduling. To support
> sheduling SQL programmers have to use outer tools to periodically
> check database if event commit. But IMHO it's much better to add one
> more SQL command to allow sheduling in same SQL.
> 
> My thoughts about such command follow:
> =====================================================================


one option for doing this, ( in a fairly non-portable way ), is to create
a 'C' function contained in a shared library.  on most unixen you can put
in _init and _fini functions such that when the library is dlopened/closed
the functions execute.  simply create a thread in the _init, that sits
arround on a timer, then does some stuff.  not ideal, but an option



PGP key:  http://codex.net/pgp/pgp.asc