Re: proposal: contrib module - generic command scheduler

2015-05-13 7:50 GMT+02:00 Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com>:

> 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.
> :/
>

It is next option - MySQL event scheduler use it. The usage is trivial -
but it is little bit weak - it is hard to describe some asymmetric events -
like run in working days only - but if I use named parameters and axillary
constructor function I am thinking so it can be supported too.

make_scheduled_time(at => '2015-014-05 10:00:0', repeat => '1day',
stop_after => '...')

>
> Is there anything worth stealing from pgAgent?
>

Surely not - although I have little bit different goals - pgAgent is top
end scheduler - little bit complex due support jobs/steps. My target is
implementation of low end scheduler. pgAgent and others can be implemented
as next layer. It should be strong enough for some simple admin tasks, and
strong enough for base for implementation some complex scheduler and
workflow systems - but it should be simple as possible. In this moment
PLpgSQL is strong enough for implementation very complex workflow system -
but missing the low end scheduling functionality.

> 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?
>

yes - I am thinking about special type, where input function will be empty
and value has to be created with constructor function - it can simplify
parser lot.

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

you can specify timeout for any command - so if you specify timeout 0, then
it will run without timeout.

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

theoretically it can be supported some pg_stat_ view - but I would not to
implement a some history table for commands. Again it is task for higher
layers.

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

It is plan B. I am thinking so PostgreSQL missing some lowend scheduler so
I am asking here. Some features can be implemented later, some features can
be implemented elsewhere. I have to specify limit, borders, what is simple
scheduler, and what is not. The full functionality scheduler is relatively
heavy application - so it should not be a contrib module. But simple
generic scheduler can be good enough for 50% and with some simple plpgsql
code for other 40%

Regards

Pavel

> --
> Jim Nasby, Data Architect, Blue Treble Consulting
> Data in Trouble? Get it in Treble! http://BlueTreble.com
>