Re: [v9.3] Extra Daemons (Re: elegant and effective way for running jobs inside a database)

2012-06-29 16:44 keltezéssel, Kohei KaiGai írta:
> 2012/6/29 Boszormenyi Zoltan <zb(at)cybertec(dot)at>:
>> 2012-04-25 11:40 keltezéssel, Kohei KaiGai írta:
>>
>>> 2012/3/10 Simon Riggs <simon(at)2ndquadrant(dot)com>:
>>>> 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.
>>>>
>>> I tried to implement a patch according to the idea. It allows extensions
>>> to register an entry point of the self-managed daemon processes,
>>> then postmaster start and stop them according to the normal manner.
>>>
>>> [kaigai(at)iwashi patch]$ ps ax | grep postgres
>>> 27784 pts/0 S 0:00 /usr/local/pgsql/bin/postgres
>>> 27786 ? Ss 0:00 postgres: writer process
>>> 27787 ? Ss 0:00 postgres: checkpointer process
>>> 27788 ? Ss 0:00 postgres: wal writer process
>>> 27789 ? Ss 0:00 postgres: autovacuum launcher process
>>> 27790 ? Ss 0:00 postgres: stats collector process
>>> 27791 ? Ss 0:00 postgres: auth_counter <== (*)
>>>
>>> The auth_counter being included in this patch is just an example of
>>> this functionality. It does not have significant meanings. It just logs
>>> number of authentication success and fails every intervals.
>>>
>>> I'm motivated to define an extra daemon that attach shared memory
>>> segment of PostgreSQL as a computing server to avoid limitation of
>>> number of GPU code that we can load concurrently.
>>>
>>> Thanks,
>>
>> I have tested this original version. The patch has a single trivial reject,
>> after fixing it, it compiled nicely.
>>
>> After adding shared_preload_libraries='$libdir/auth_counter', the extra
>> daemon start and stops nicely with pg_ctl start/stop. The auth_counter.c
>> code is a fine minimalistic example on writing one's own daemon.
>>
> Thanks for your testing.
>
> According to Simon's comment, I'm waiting for his integration of this patch
> with another implementation by him.
>
> The auth_counter is just an proof-of-concept patch, so, it is helpful if you
> could provide another use case that can make sense.

Well, we have two use cases that are more complex:

- an SQL-driven scheduler, similar to pgAgent, it's generic enough,
we might port it to this scheme and publish it
- a huge volume importer daemon, it was written for a very specific
purpose and for a single client, we cannot publish it.

Both need database connections, the second needs more than one,
so they need to link to the client side libpq, the way it was done for
walreceiver can be done here as well.

Best regards,
Zoltán Böszörményi

--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
http://www.postgresql.at/