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

On 03/10/2012 07:11 AM, Simon Riggs wrote:
> On Fri, Mar 9, 2012 at 6:51 PM, Andrew Dunstan<andrew(at)dunslane(dot)net> wrote:
>>
>> On 03/09/2012 01:40 PM, Robert Haas wrote:
>>> On Fri, Mar 9, 2012 at 12:02 PM, David E. Wheeler<david(at)justatheory(dot)com>
>>> wrote:
>>>> On Mar 9, 2012, at 7:55 AM, Merlin Moncure wrote:
>>>>> 100% agree (having re-read the thread and Alvaro's idea having sunk
>>>>> in). Being able to set up daemon processes side by side with the
>>>>> postmaster would fit the bill nicely. It's pretty interesting to
>>>>> think of all the places you could go with it.
>>>> pgAgent could use it *right now*. I keep forgetting to restart it after
>>>> restarting PostgreSQL and finding after a day or so that no jobs have run.
>>> That can and should be fixed by teaching pgAgent that failing to
>>> connect to the server, or getting disconnected, is not a fatal error,
>>> but a reason to sleep and retry.
>>
>> Yeah. It's still not entirely clear to me what a postmaster-controlled
>> daemon is going to be able to do that an external daemon can't.
> Start and stop at the same time as postmaster, without any pain.
>
> It's a considerable convenience to be able to design this aspect once
> and then have all things linked to the postmaster follow that. It
> means people will be able to write code that runs on all OS easily,
> without everybody having similar but slightly different code about
> starting up, reading parameters, following security rules etc.. Tight
> integration, with good usability.

The devil is in the details, though, pace Mies van der Rohe.

In particular, it's the "tight integration" piece I'm worried about.

What is the postmaster supposed to do if the daemon start fails? What if
it gets a flood of failures? What access will the daemon have to
Postgres internals? What OS privileges will it have, since this would
have to run as the OS postgres user? In general I think we don't want
arbitrary processes running as the OS postgres user.

I accept that cron might not be the best tool for the jobs, since a) its
finest granularity is 1 minute and b) it would need a new connection for
each job. But a well written external daemon that runs as a different
user and is responsible for making its own connection to the database
and re-establishing it if necessary, seems to me at least as clean a
design for a job scheduler as one that is stopped and started by the
postmaster.

cheers

andrew