Re: NOTIFY in Background Worker

On Sat, Aug 29, 2015 at 12:55 PM, Thomas Munro
<thomas(dot)munro(at)enterprisedb(dot)com> wrote:
> 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.

Yes, the Notify command execution is possible with call to
ProcessCompletedNotifies
function in a background worker and the Listen command is not possible in
background worker because of no client associated with it.

The documentation patch provides a better understanding to the user regarding
notify and listen commands.

I marked this patch as ready for committer.

Regards,
Hari Babu
Fujitsu Australia