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.