Thread: NOTIFY in Background Worker

Hello,<br /><br /> I added a "NOFITY chan" to the SQL arg of an SPI_execute(), (I did it also with just the NOTIFY
statement),<br/> but the listeners (other workers) don't get the notification until a "NOTIFY chan" is done for example
withpgadmin,<br /><br /> They don't get lost, just not emited after the "not forgotten" call of
CommitTransactionCommand().<br/><br /> Is this normal ( i.e. not supported (yet) ), a bug, or did I overlook some doc.
(orsource code) ?.<br /><br /> For now, I will try to "emit" the NOTIFY via libpq.<br /><br /> Jacques K.<br /><br /> 

On Sat, Aug 29, 2015 at 12:55 PM, Thomas Munro
<thomas.munro@enterprisedb.com> wrote:
> On Sat, Aug 29, 2015 at 9:03 AM, Thomas Munro
> <thomas.munro@enterprisedb.com> wrote:
>>
>> On Fri, Aug 28, 2015 at 10:30 PM, jacques klein
>> <jacques.klei@googlemail.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.

With this commit - bde39eed0cafb82bc94c40e95d96b5cf47b6f719, it is not possible
to execute Notify commands inside a parallel worker. Can't we change
it as disable
both listen and notify commands inside a background worker?

Regards,
Hari Babu
Fujitsu Australia

On 2015-11-03 17:19:43 +1100, Haribabu Kommi wrote:
> On Sat, Aug 29, 2015 at 12:55 PM, Thomas Munro
> <thomas.munro@enterprisedb.com> wrote:
> > On Sat, Aug 29, 2015 at 9:03 AM, Thomas Munro
> > <thomas.munro@enterprisedb.com> wrote:
> > 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.
> 
> With this commit - bde39eed0cafb82bc94c40e95d96b5cf47b6f719, it is not possible
> to execute Notify commands inside a parallel worker. Can't we change
> it as disable both listen and notify commands inside a background worker?

Well, parallel workers are something different from general background
workers. I don't see why it'd make sense to allow listen/notify there,
given the rest of the restrictions?

Andres

On 2015-11-03 09:52:26 +0100, Pavel Stehule wrote:
> 2015-11-03 9:35 GMT+01:00 Andres Freund <andres@anarazel.de>:
> 
> > On 2015-11-03 17:19:43 +1100, Haribabu Kommi wrote:
> > > With this commit - bde39eed0cafb82bc94c40e95d96b5cf47b6f719, it is not
> > possible
> > > to execute Notify commands inside a parallel worker. Can't we change
> > > it as disable both listen and notify commands inside a background worker?
> >
> > Well, parallel workers are something different from general background
> > workers. I don't see why it'd make sense to allow listen/notify there,
> > given the rest of the restrictions?
> >
> 
> I though about this possibility and I am thinking, so NOTIFY can be pretty
> useful there.
> 
> The background workers can be used for running AT TIME tasks - run import
> every 10 minutes. The notification can be useful for starting AFTER tasks
> where the required previous steps should be committed.
> 
> If we use workers more for execution custom code (PLpgSQL, PLPython, ...)
> then notification mechanism can be interesting (both directions).

Did you actually read what I wrote above? paralell workers != general
background workers.

On 2015-11-03 09:52:34 +0100, Simon Riggs wrote:
> On 3 November 2015 at 09:35, Andres Freund <andres@anarazel.de> wrote:
> 
> 
> > > With this commit - bde39eed0cafb82bc94c40e95d96b5cf47b6f719, it is not
> > possible
> > > to execute Notify commands inside a parallel worker. Can't we change
> > > it as disable both listen and notify commands inside a background worker?
> >
> > Well, parallel workers are something different from general background
> > workers. I don't see why it'd make sense to allow listen/notify there,
> > given the rest of the restrictions?
> >
> 
> What are the restrictions and/or where are they documented? Thanks

There's a section in README.parallel:
> Instead, we take a more pragmatic approach. First, we try to make as many of
> the operations that are safe outside of parallel mode work correctly in
> parallel mode as well.  Second, we try to prohibit common unsafe operations
> via suitable error checks.  These checks are intended to catch 100% of
> unsafe things that a user might do from the SQL interface, but code written
> in C can do unsafe things that won't trigger these checks.  The error checks
> are engaged via EnterParallelMode(), which should be called before creating
> a parallel context, and disarmed via ExitParallelMode(), which should be
> called after all parallel contexts have been destroyed.  The most
> significant restriction imposed by parallel mode is that all operations must
> be strictly read-only; we allow no writes to the database and no DDL.  We
> might try to relax these restrictions in the future.

Basically the restriction is that, for now, while a parallelized
statement is in progress you can't write data and no DDL is possible.

Andres

On Sat, Aug 29, 2015 at 12:55 PM, Thomas Munro
<thomas.munro@enterprisedb.com> wrote:
> On Sat, Aug 29, 2015 at 9:03 AM, Thomas Munro
> <thomas.munro@enterprisedb.com> wrote:
>>
>> On Fri, Aug 28, 2015 at 10:30 PM, jacques klein
>> <jacques.klei@googlemail.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

On Thu, Nov 5, 2015 at 12:34 AM, Haribabu Kommi
<kommi.haribabu@gmail.com> wrote:
> I marked this patch as ready for committer.

The patch says:

If a background worker registers to receive asynchronous notifications
with the <command>LISTEN</command> through <acronym>SPI</acronym>,
there is currently no way for incoming notifications to be received.

But wouldn't it be more correct to say:

If a background worker registers to receive asynchronous notifications
with the <command>LISTEN</command> through <acronym>SPI</acronym>, the
worker will log those notifications, but there is no programmatic way
for the worker to intercept and respond to those notifications.

Or something like that?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Fri, Nov 6, 2015 at 4:57 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Thu, Nov 5, 2015 at 12:34 AM, Haribabu Kommi
> <kommi.haribabu@gmail.com> wrote:
>> I marked this patch as ready for committer.
>
> The patch says:
>
> If a background worker registers to receive asynchronous notifications
> with the <command>LISTEN</command> through <acronym>SPI</acronym>,
> there is currently no way for incoming notifications to be received.
>
> But wouldn't it be more correct to say:
>
> If a background worker registers to receive asynchronous notifications
> with the <command>LISTEN</command> through <acronym>SPI</acronym>, the
> worker will log those notifications, but there is no programmatic way
> for the worker to intercept and respond to those notifications.

Yes, the above description is good.

Regards,
Hari Babu
Fujitsu Australia

On Thu, Nov 5, 2015 at 11:46 PM, Thomas Munro
<thomas.munro@enterprisedb.com> wrote:
>> Yes, the above description is good.
>
> +1

Committed that way, then.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company