Re: Want to schedule tasks for the future

matt(at)tplus1(dot)com (Matthew Wilson) writes:
> Just recently I discovered the listen/notify feature in postgresql.
> Now I don't have external processes polling tables, watching for new
> inserted rows.
>
> Anyhow, I'm curious if there is some other feature that will help me out
> with a new puzzle.
>
> I want to store emails to deliver at a later time in my database. For
> example, I want to remember that tomorrow morning at 9:00 am, I want to
> send a particular email.
>
> I'll use a table sort of like this
>
> create table scheduled_email (
> to_address text,
> email_subject text,
> email_body text,
> deliver_at timestamp,
> sent boolean
> );
>
> I know I could write an external process to poll this table and select
> all rows where deliver_at < current_timestamp and sent = 'f'.
>
> But is there some other way inside postgresql that will do something
> similar? I would want something like listen/notify, where postgres
> starts an external process when any data exist.
>
> Thanks for the help.

You do need to have some set of infrastructure there that is
interested in listening to such events, and doing something about
them.

That "something" needs to know about the nature of the queue that you
have set up, and it needs to know what sorts of "somethings" you want
done to the items in the queue.

Those various "somethings" are pretty specific to your application, so
I don't imagine you'll get much agreement on the notion that there
should be some internal Postgres component that should act on this.

It would probably be a neat idea to have some "sample listening
daemon" that has a series of parameters:

- PGHOST/PGDATABASE/PGPORT/PGUSER... to indicate what database to listen to

- LISTENEVENT to indicate which event to listen for

- PROCESSQUEUE to indicate the Unix program to run to process the
queue when the event of LISTENEVENT is received

That's enough to characterize how to invoke such a listener.

There *might* be meaningful structure inside the queue processor, what
with various possibilities:

- You might want to do something in a transaction for each item in the
queue, if it's of a "pretty transactional" nature. This is actually
about the simplest case

- You might want to process several queue items in a single
transaction, to cut down on transactional costs.

One might then debate:
- Doing the first $N items, marking them "done," and iterating
until complete;
- Opening a cursor, and grabbing a few items from the cursor at a
time.

That's starting to add to a goodly number of possibilities.

You could push work requests over to some sort of in-memory message
queueing system (lots of them out there - AMQP, RabbitMQ, ActiveMQ, or
even Spread) to make it easy to efficiently spread work across a bunch
of worker processes, if that seemed useful. That needs extra
infrastructure (e.g. - another "MQ" subsystem), and certainly adds
complications. To some, that may be quite worthwhile.

This is all looking increasingly like "stuff that isn't inside the
DBMS engine," and I don't imagine you'd get much agreement on the
merits of trying to force the functionality into the core of Postgres.

I don't think it's a notably good idea, myself. Indeed, this would
force things into the identical security context as the DBMS itself,
which seems a pretty bad thing to me.
--
let name="cbbrowne" and tld="gmail.com" in String.concat "@" [name;tld];;
http://linuxfinances.info/info/linuxdistributions.html
Rules of the Evil Overlord #77. "If I have a fit of temporary insanity
and decide to give the hero the chance to reject a job as my trusted
lieutentant, I will retain enough sanity to wait until my current
trusted lieutenant is out of earshot before making the offer."
<http://www.eviloverlord.com/>