Re: proposal: contrib module - generic command scheduler

On 5/12/15 11:32 PM, Pavel Stehule wrote:
> I would not to store state on this level - so "at" should be
> implemented on higher level. There is very high number of
> possible strategies, what can be done with failed tasks - and I
> would not to open this topic. I believe with proposed scheduler,
> anybody can simply implement what need in PLpgSQL with dynamic
> SQL. But on second hand "run once" can be implemented with
> proposed API too.
>
>
> That seems reasonable in a v1, so long as there's room to easily
> extend it without pain to add "at"-like one-shot commands,
> at-startup commands, etc.

Yeah, being able to run things after certain system events would be nice.

> I'd prefer to see a scheduling interface that's a close match for
> cron's or that leaves room for it - so things like "*/5" for every
> five minutes, ranges like "Mon-Fri", etc. If there's a way to
> express similar capabilities more cleanly using PostgreSQL's types
> and conventions that makes sense, but I'm not sure a composite type
> of arrays fits that.

It seems unfortunate to go with cron's limited syntax when we have such
fully capable timestamp and interval capabilities already in the
database. :/

Is there anything worth stealing from pgAgent?

> I though about it too - but the parser for this cron time will be longer
> than all other code probably. I see a possibility to write constructors
> that simplify creating a value of this type. Some like
>
> make_scheduled_time(secs => '*/5', dows => 'Mon-Fri') or
> make_scheduled_time(at =>'2015-014-05 10:00:0'::timestamp);

Wouldn't that be just as bad as writing the parser in the first place?

> 1. don't hold a states, results of commands
...
> 3. When command fails, it writes info to log only
Unfortunate, but understandable in a first pass.

> 4. When command runs too long (over specified timeout), it is killed.

I think that needs to be optional.

> 5. When command waits to free worker, write to log
> 6. When command was not be executed due missing workers (and max_workers
> > 0), write to log

Also unfortunate. We already don't provide enough monitoring capability
and this just makes that worse.

Perhaps it would be better to put something into PGXN first; this
doesn't really feel like it's baked enough for contrib yet. (And I say
that as someone who's really wanted this ability in the past...)
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com