Would a BGW need shmem_access or database_connection to enumerate databases?

On Thu, Nov 30, 2017 at 7:48 AM, Chapman Flack <chap@anastigmatix.net> wrote:

For the "master" one, what capabilities will it need to simply
enumerate the current names of known databases? I suppose I could
have it connect to the null dbname and query pg_database. Would
that be the civilized way to do it, or am I missing a simpler way?

Yes. That's actually what the autovacuum launcher does. It connects
using InitPostgres(NULL, InvalidOid, NULL, NULL), and then scans
pg_database to fetch a list (see get_database_list).
--
Michael

On 11/29/2017 05:54 PM, Michael Paquier wrote:

Yes. That's actually what the autovacuum launcher does. It connects
using InitPostgres(NULL, InvalidOid, NULL, NULL), and then scans
pg_database to fetch a list (see get_database_list).

Thanks! It looks like if get_database_list were not static, it
would be just the thing I'm looking for.

Would an SPI query of pg_database also work, in the
bgw-connected-to-null-dbname context? I'm just wondering if
that might be clearer/fewer LOC than just copying the lower-level
approach from get_database_list.

-Chap

On 2017-11-29 18:23:40 -0500, Chapman Flack wrote:

On 11/29/2017 05:54 PM, Michael Paquier wrote:

Yes. That's actually what the autovacuum launcher does. It connects
using InitPostgres(NULL, InvalidOid, NULL, NULL), and then scans
pg_database to fetch a list (see get_database_list).

Thanks! It looks like if get_database_list were not static, it
would be just the thing I'm looking for.

Would an SPI query of pg_database also work, in the
bgw-connected-to-null-dbname context? I'm just wondering if
that might be clearer/fewer LOC than just copying the lower-level
approach from get_database_list.

SQL won't really work in a non-database connected context.

On 11/29/2017 05:48 PM, Chapman Flack wrote:

I'm thinking of writing a background worker that will enumerate
the databases present, and spin off, for each one, another BGW
that will establish a connection and do stuff.

Can I even do this?

"Unlike RegisterBackgroundWorker, which can only be called
from within the postmaster, RegisterDynamicBackgroundWorker
must be called from a regular backend."

Can I call RegisterDynamicBackgroundWorker when not in the postmaster,
but also not in a "regular backend", but rather another BGW?

Put another way, can a BGW be regarded as a "regular backend" for the
purpose of this rule?

-Chap

On Fri, Dec 1, 2017 at 10:04 AM, Chapman Flack <chap@anastigmatix.net> wrote:

Can I call RegisterDynamicBackgroundWorker when not in the postmaster,
but also not in a "regular backend", but rather another BGW?

I believe that doing it from another BGW works fine.

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

On Sat, Dec 2, 2017 at 12:41 AM, Robert Haas <robertmhaas@gmail.com> wrote:

On Fri, Dec 1, 2017 at 10:04 AM, Chapman Flack <chap@anastigmatix.net> wrote:

Can I call RegisterDynamicBackgroundWorker when not in the postmaster,
but also not in a "regular backend", but rather another BGW?

I believe that doing it from another BGW works fine.

I think we are already doing it that way in autoprewarm
(src/contrib/pg_prewarm/autoprewarm.c).

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

On 30 November 2017 at 06:48, Chapman Flack <chap@anastigmatix.net> wrote:

I'm thinking of writing a background worker that will enumerate
the databases present, and spin off, for each one, another BGW
that will establish a connection and do stuff.

For the "master" one, what capabilities will it need to simply
enumerate the current names of known databases? I suppose I could
have it connect to the null dbname and query pg_database. Would
that be the civilized way to do it, or am I missing a simpler way?

pglogical does exactly this. Take a look at start_manager_workers in
pglogical.c

https://github.com/2ndQuadrant/pglogical/blob/REL2_x_STABLE/pglogical.c#L594

and the caller pglogical_supervisor_main .

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 1 December 2017 at 23:04, Chapman Flack <chap@anastigmatix.net> wrote:

On 11/29/2017 05:48 PM, Chapman Flack wrote:

I'm thinking of writing a background worker that will enumerate
the databases present, and spin off, for each one, another BGW
that will establish a connection and do stuff.

Can I even do this?

"Unlike RegisterBackgroundWorker, which can only be called
from within the postmaster, RegisterDynamicBackgroundWorker
must be called from a regular backend."

Can I call RegisterDynamicBackgroundWorker when not in the postmaster,
but also not in a "regular backend", but rather another BGW?

Yes. BDR does it a lot.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 12/04/2017 09:13 AM, Craig Ringer wrote:

On 1 December 2017 at 23:04, Chapman Flack <chap@anastigmatix.net> wrote:

Can I call RegisterDynamicBackgroundWorker when not in the postmaster,
but also not in a "regular backend", but rather another BGW?

Yes. BDR does it a lot.

Would this doc patch be acceptable to clarify that, in case
I'm not the last person who might wonder?

-Chap

On Fri, Dec 15, 2017 at 8:12 AM, Chapman Flack <chap@anastigmatix.net> wrote:

On 12/04/2017 09:13 AM, Craig Ringer wrote:

On 1 December 2017 at 23:04, Chapman Flack <chap@anastigmatix.net> wrote:

Can I call RegisterDynamicBackgroundWorker when not in the postmaster,
but also not in a "regular backend", but rather another BGW?

Yes. BDR does it a lot.

Would this doc patch be acceptable to clarify that, in case
I'm not the last person who might wonder?

This doc addition looks like a good idea to me.
--
Michael

On Thu, Dec 14, 2017 at 06:12:35PM -0500, Chapman Flack wrote:

On 12/04/2017 09:13 AM, Craig Ringer wrote:

On 1 December 2017 at 23:04, Chapman Flack <chap@anastigmatix.net> wrote:

Can I call RegisterDynamicBackgroundWorker when not in the postmaster,
but also not in a "regular backend", but rather another BGW?

Yes. BDR does it a lot.

Would this doc patch be acceptable to clarify that, in case
I'm not the last person who might wonder?

Thanks, patch applied to head.

---------------------------------------------------------------------------

From 3308ef5647e8ce4a84855b4d0cdddda09ba6aeb7 Mon Sep 17 00:00:00 2001

From: Chapman Flack <chap@anastigmatix.net>
Date: Thu, 14 Dec 2017 18:09:14 -0500
Subject: [PATCH] Clarify that a BGW can register a dynamic BGW.

---
doc/src/sgml/bgworker.sgml | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)

diff --git a/doc/src/sgml/bgworker.sgml b/doc/src/sgml/bgworker.sgml
index 4bc2b69..e490bb8 100644
--- a/doc/src/sgml/bgworker.sgml
+++ b/doc/src/sgml/bgworker.sgml
@@ -41,7 +41,7 @@
*worker, BackgroundWorkerHandle **handle</type>)</function>.  Unlike
<function>RegisterBackgroundWorker</function>, which can only be called from within
the postmaster, <function>RegisterDynamicBackgroundWorker</function> must be
-  called from a regular backend.
+  called from a regular backend, possibly another background worker.
</para>

<para>
--
1.8.3.1

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

Thanks! I had actually registered that one (with a related one)
for CF 2018-03, having missed the deadline for -01:

https://commitfest.postgresql.org/17/1467/

-Chap

On Wed, Jan 24, 2018 at 01:48:05PM -0500, Chapman Flack wrote:

Thanks! I had actually registered that one (with a related one)
for CF 2018-03, having missed the deadline for -01:

https://commitfest.postgresql.org/17/1467/

OK, thanks. I added a commitfest item annotiation to say that the doc
part of this entry has been applied.

---------------------------------------------------------------------------

-Chap

On 01/24/2018 01:20 PM, Bruce Momjian wrote:

On Thu, Dec 14, 2017 at 06:12:35PM -0500, Chapman Flack wrote:

On 12/04/2017 09:13 AM, Craig Ringer wrote:

On 1 December 2017 at 23:04, Chapman Flack <chap@anastigmatix.net> wrote:

Can I call RegisterDynamicBackgroundWorker when not in the postmaster,
but also not in a "regular backend", but rather another BGW?

Yes. BDR does it a lot.

Would this doc patch be acceptable to clarify that, in case
I'm not the last person who might wonder?

Thanks, patch applied to head.

---------------------------------------------------------------------------

From 3308ef5647e8ce4a84855b4d0cdddda09ba6aeb7 Mon Sep 17 00:00:00 2001

From: Chapman Flack <chap@anastigmatix.net>
Date: Thu, 14 Dec 2017 18:09:14 -0500
Subject: [PATCH] Clarify that a BGW can register a dynamic BGW.

---
doc/src/sgml/bgworker.sgml | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)

diff --git a/doc/src/sgml/bgworker.sgml b/doc/src/sgml/bgworker.sgml
index 4bc2b69..e490bb8 100644
--- a/doc/src/sgml/bgworker.sgml
+++ b/doc/src/sgml/bgworker.sgml
@@ -41,7 +41,7 @@
*worker, BackgroundWorkerHandle **handle</type>)</function>.  Unlike
<function>RegisterBackgroundWorker</function>, which can only be called from within
the postmaster, <function>RegisterDynamicBackgroundWorker</function> must be
-  called from a regular backend.
+  called from a regular backend, possibly another background worker.
</para>

<para>
--
1.8.3.1

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +