Re: proposal: contrib module - generic command scheduler - Mailing list pgsql-hackers
| From | Pavel Stehule |
|---|---|
| Subject | Re: proposal: contrib module - generic command scheduler |
| Date | |
| Msg-id | CAFj8pRDwOsjqKtcUnD7CKBHEsMjO55Z9YTJmJHWfsfYs14d3fA@mail.gmail.com Whole thread Raw |
| In response to | Re: proposal: contrib module - generic command scheduler (Jim Nasby <Jim.Nasby@BlueTreble.com>) |
| Responses |
Re: proposal: contrib module - generic command scheduler
|
| List | pgsql-hackers |
2015-05-13 7:50 GMT+02:00 Jim Nasby <Jim.Nasby@bluetreble.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
pgsql-hackers by date: