Re: elegant and effective way for running jobs inside a database

On Mon, Mar 5, 2012 at 12:17 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> Hello
>
> 2012/3/5 Alvaro Herrera <alvherre(at)commandprompt(dot)com>:
>>
>> Excerpts from Artur Litwinowicz's message of lun mar 05 16:18:56 -0300 2012:
>>> Dear Developers,
>>>    I am looking for elegant and effective way for running jobs inside a
>>> database or cluster - for now I can not find that solution.
>>
>> Yeah, it'd be good to have something.  Many people say it's not
>> necessary, and probably some hackers would oppose it; but mainly I think
>> we just haven't agreed (or even discussed) what the design of such a
>> scheduler would look like.  For example, do we want it to be able to
>> just connect and run queries and stuff, or do we want something more
>> elaborate able to start programs such as running pg_dump?  What if the
>> program crashes -- should it cause the server to restart?  And so on.
>> It's not a trivial problem.
>>
>
> I agree - it is not simple
>
> * workflow support
> * dependency support
>
> a general ACID scheduler can be nice (in pg) but it is not really
> simple. There was some proposal about using autovacuum demon like
> scheduler.

I've been thinking about making autovacuum a special case of a general
*non*-transactional job-running system because dealing with large
physical changes to a database (where one wants to rewrite 300GB of
data, or whatever) that are prohibitive in a transaction are -- to
understate things -- incredibly painful. Painful enough that people
will risk taking their site down with a large UPDATE or ALTER TABLE,
hoping that they can survive the duration (and then when they cancel
it and are left with huge volumes of dead tuples, things get a lot
more ugly).

The closest approximation a client program can make is "well, I guess
I'll paginate through the database and rewrite small chunks". Instead,
it may make more sense to have the database spoon-feed work to do the
transformations little-at-a-time ala autovacuum.

--
fdr