NOTIFY in Background Worker

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.

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

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.

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

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

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

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

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

2015-11-03 9:35 GMT+01:00 Andres Freund <andres@anarazel.de>:

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?

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

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

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/&gt;
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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.

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

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

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

2015-11-03 9:54 GMT+01:00 Andres Freund <andres@anarazel.de>:

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.

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

Regards

Pavel

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

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

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

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

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

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

On Fri, Nov 6, 2015 at 12:08 PM, Haribabu Kommi <kommi.haribabu@gmail.com>
wrote:

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.

+1

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

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

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