Re: pg_worker and notify - Mailing list pgsql-general

On Sat, Mar 15, 2014 at 11:39 PM, Olivier Chaussavoine
<olivier.chaussavoine@gmail.com> 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