Re: NOTIFY in Background Worker

On Sat, Aug 29, 2015 at 9:03 AM, Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com
> wrote:

> On Fri, Aug 28, 2015 at 10:30 PM, jacques klein <
> jacques(dot)klei(at)googlemail(dot)com> wrote:
>
>> Hello,
>>
>> I added a "NOFITY chan" to the SQL arg of an SPI_execute(), (I did it
>> also with just the NOTIFY statement),
>> but the listeners (other workers) don't get the notification until a
>> "NOTIFY chan" is done for example with pgadmin,
>>
>> They don't get lost, just not emited after the "not forgotten" call of
>> CommitTransactionCommand().
>>
>> Is this normal ( i.e. not supported (yet) ), a bug, or did I overlook
>> some doc. (or source code) ?.
>>
>> For now, I will try to "emit" the NOTIFY via libpq.
>>
>
> That's because ProcessCompletedNotifies isn't being called. For regular
> backends it is called inside the top level loop PostgresMain. I think you
> need to include "commands/async.h" and add a call to
> ProcessCompletedNotifies() after your background worker commits to make
> this work.
>

For the record, Jacques confirmed off-list that this worked, and I also did
a couple of tests.

Is this expected? If so, should it be documented -- perhaps with something
like the attached? Alternatively there may be some way to make
CommitTransactionCommand do it, though the comments in
ProcessCompletedNotifies explain why that was rejected, at least as far as
AtCommit_Notify goes.

This made me wonder what happens if a background worker calls LISTEN.
NotifyMyFrontEnd simply logs the notifications, since there is no remote
libpq to sent a message to. Perhaps a way of delivering to background
workers could be developed, though of course there are plenty of other
kinds of IPC available already.

--
Thomas Munro
http://www.enterprisedb.com