Обсуждение: Would a BGW need shmem_access or database_connection to enumeratedatabases?

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?

-Chap

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

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 +