On Sat, Mar 15, 2014 at 11:39 PM, Olivier Chaussavoine <olivier.chaussavoine@xxxxxxxxx> wrote: > Applications using postgres frequently require central process serializing > specific tasks. We usually implement these with external programs but the > reliability of such solution depends on a complexity that require carefull > monitoring. > I imagne an extension that would lauch some bg_workers waiting for notify > statements like: > NOTIFY <channel> <event>; > and for each <event> catched on the <channel>, calling calling a pl/pgsql > function with a single parameter <event>. > A predetermined list of bg_workers would be described in a public table in > the postgres database, and red at postgres startup. I wonder how to code a > bg_worker waiting for notifications on a channel. Have you any idea on such > implementation? There are two things to take into account: - registration of the backend to a channel - Manage NOTIFY message reception Registering a backend to a given channel is not complicated. You could use the SPI interface to do that or even call directly Async_Listen, and be sure that the bgworker is connected to a given database, a NOTIFY is ignored when it comes from another database. Then comes the tricky part: NOTIFY reception is done by handling SIGUSR1. Roughly, you would need to call EnableNotifyInterrupt and enable SIGUSR1 reception by doing that when the bgworker starts: pqsignal(SIGUSR1, procsignal_sigusr1_handler); // or something similar with PROCSIG_NOTIFY_INTERRUPT processing on MyProcSignalSlot Then, new notify messages are sent by NotifyMyFrontEnd:async.c, which will actually log it the NOTIFY content each time a notify is received except if you use (whereToSendOutput == DestRemote) (case where a pq_* message is sent back to frontend, but in the case of a bgworker this is not interesting because it *is* a server backend). It logs an elog INFO message... You might perhaps be happy with only the INFO message in your server logs, or you could use emit_log_hook in the bgworker to perform other actions like storing the string of the elog message and save it in a queue that your bgworker could analyze and fetch each time it begins a new process loop. That's the idea... If you do an implementation of that and have time to write a patch, it might be worth adding it to the following catalog for future reference (catalog that I actually maintain): https://github.com/michaelpq/pg_workers Here is for example one bgworker sending NOTIFY messages automatically, this might help you as well: https://github.com/michaelpq/pg_workers/tree/master/hello_notify Regards, -- Michael -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
