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