Re: [HACKERS] NOTIFY in Background Worker

[Robert Haas]

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

The patch says:

If a background worker registers to receive asynchronous notifications
with the LISTEN through SPI,
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 LISTEN through SPI, 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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] NOTIFY in Background Worker

[Haribabu Kommi]

On Fri, Nov 6, 2015 at 4:57 AM, Robert Haas  wrote:
> On Thu, Nov 5, 2015 at 12:34 AM, Haribabu Kommi
>  wrote:
>> I marked this patch as ready for committer.
>
> The patch says:
>
> If a background worker registers to receive asynchronous notifications
> with the LISTEN through SPI,
> 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 LISTEN through SPI, 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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] NOTIFY in Background Worker

[Thomas Munro]

On Fri, Nov 6, 2015 at 12:08 PM, Haribabu Kommi 
wrote:

> On Fri, Nov 6, 2015 at 4:57 AM, Robert Haas  wrote:
> > On Thu, Nov 5, 2015 at 12:34 AM, Haribabu Kommi
> >  wrote:
> >> I marked this patch as ready for committer.
> >
> > The patch says:
> >
> > If a background worker registers to receive asynchronous notifications
> > with the LISTEN through SPI,
> > 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 LISTEN through SPI, 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.


+1

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

Re: [HACKERS] NOTIFY in Background Worker

[Robert Haas]

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

Committed that way, then.

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] NOTIFY in Background Worker

[Haribabu Kommi]

On Sat, Aug 29, 2015 at 12:55 PM, Thomas Munro
 wrote:
> On Sat, Aug 29, 2015 at 9:03 AM, Thomas Munro
>  wrote:
>>
>> On Fri, Aug 28, 2015 at 10:30 PM, jacques klein
>>  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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] NOTIFY in Background Worker

[Andres Freund]

On 2015-11-03 09:52:34 +0100, Simon Riggs wrote:
> On 3 November 2015 at 09:35, Andres Freund  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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] NOTIFY in Background Worker

[Pavel Stehule]

2015-11-03 9:54 GMT+01:00 Andres Freund :

> On 2015-11-03 09:52:26 +0100, Pavel Stehule wrote:
> > 2015-11-03 9:35 GMT+01:00 Andres Freund :
> >
> > > 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.
>

I miss it, I am sorry. My notice is related to background workers

Regards

Pavel


>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Re: [HACKERS] NOTIFY in Background Worker

[Andres Freund]

On 2015-11-03 09:52:26 +0100, Pavel Stehule wrote:
> 2015-11-03 9:35 GMT+01:00 Andres Freund :
> 
> > 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.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] NOTIFY in Background Worker

[Andres Freund]

On 2015-11-03 17:19:43 +1100, Haribabu Kommi wrote:
> On Sat, Aug 29, 2015 at 12:55 PM, Thomas Munro
>  wrote:
> > On Sat, Aug 29, 2015 at 9:03 AM, Thomas Munro
> >  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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] NOTIFY in Background Worker

[Pavel Stehule]

2015-11-03 9:35 GMT+01:00 Andres Freund :

> On 2015-11-03 17:19:43 +1100, Haribabu Kommi wrote:
> > On Sat, Aug 29, 2015 at 12:55 PM, Thomas Munro
> >  wrote:
> > > On Sat, Aug 29, 2015 at 9:03 AM, Thomas Munro
> > >  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?
>

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).

Regards

Pavel


Andres
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Re: [HACKERS] NOTIFY in Background Worker

[Simon Riggs]

On 3 November 2015 at 09:35, Andres Freund  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

-- 
Simon Riggshttp://www.2ndQuadrant.com/

PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: [HACKERS] NOTIFY in Background Worker

[Haribabu Kommi]

On Sat, Aug 29, 2015 at 12:55 PM, Thomas Munro
 wrote:
> On Sat, Aug 29, 2015 at 9:03 AM, Thomas Munro
>  wrote:
>>
>> On Fri, Aug 28, 2015 at 10:30 PM, jacques klein
>>  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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] NOTIFY in Background Worker

[Thomas Munro]

On Sat, Aug 29, 2015 at 9:03 AM, Thomas Munro thomas.mu...@enterprisedb.com
 wrote:

 On Fri, Aug 28, 2015 at 10:30 PM, jacques klein 
 jacques.k...@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.

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


bgworker-notify-doc.patch
Description: Binary data

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] NOTIFY in Background Worker

[Thomas Munro]

On Fri, Aug 28, 2015 at 10:30 PM, jacques klein jacques.k...@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.

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