Re: worker_spi.naptime in worker_spi example

[Jeremy Finzel]

On Thu, Mar 15, 2018 at 3:54 PM, Chapman Flack 
wrote:

> On 03/15/2018 04:19 PM, Jeremy Finzel wrote:
>
> > Thank you.  I did see worker_spi_naptime definition, but I wanted to
> > pass worker_spi_naptime
> > as an argument instead into worker_spi_main so that I can control via SQL
> > interface how frequently the worker SQL executes.  Is this possible?
>
> Are you writing a background worker that is started by the postmaster
> at system startup, or one that you dynamically launch from a regular
> session later?
>
> In the first case, your background worker is not a child of any
> session you directly control with SQL. It is a child of the postmaster.
>
> It has to get the initial value of worker_spi.naptime from the
> configuration sources that are available at the time the postmaster
> starts it (most commonly, the configuration files).
>
> Once it is running, it will only notice a change to worker_spi.naptime
> if it responds to a HUP signal by doing a new ProcessConfigFile(). The
> example code does exactly this, so you can see how it's done.
>
> That means you do have a way to control it "via SQL" if:
>
> - you use ALTER SYSTEM to set the value of worker_spi.naptime, and
> - if you change the value with ALTER SYSTEM and want the new value
>   to take effect while the worker is already running, you do
>   SELECT pg_reload_conf()
>
> In the second, dynamic-launch case (which is also illustrated in
> worker_spi.c, as it contains code for both ways of launching), you can
> see that you end up writing an SQL-callable function (worker_spi_launch
> in the example), which you can define to take whatever arguments you
> want it to.
>
> How to get those arguments to the background worker is the next
> question. You can stash one value in bgw_main_arg (as a Datum),
> and have it passed as the only argument to your main function
> in the worker.
>
> Or, if you want to pass it more information, you can pass up to
> BGW_EXTRALEN bytes of stuff of your choice in bgw_extra, and
> have the worker retrieve it via MyBgworkerEntry.
>
> -Chap
>

This was extremely helpful.  I didn't see how the example is supporting
both dynamic and shared_preload_libraries ways of loading bgworkers.  I
just tried both ways and can see how this works.  Before I couldn't see the
GUC configs in my database, but when I added to shared_preload_libraries I
can now.  Awesome.

To answer your question, I was more looking at dynamic workers.  In our
environment, cluster restarts are expensive, so the dynamic loading of
workers is a super awesome feature of Postgres that we are hoping to use.

Will let you know if I have more questions, but I will look more into what
you suggest.

Thanks,

Re: worker_spi.naptime in worker_spi example

[Chapman Flack]

On 03/15/2018 04:19 PM, Jeremy Finzel wrote:

> Thank you.  I did see worker_spi_naptime definition, but I wanted to
> pass worker_spi_naptime
> as an argument instead into worker_spi_main so that I can control via SQL
> interface how frequently the worker SQL executes.  Is this possible?

Are you writing a background worker that is started by the postmaster
at system startup, or one that you dynamically launch from a regular
session later?

In the first case, your background worker is not a child of any
session you directly control with SQL. It is a child of the postmaster.

It has to get the initial value of worker_spi.naptime from the
configuration sources that are available at the time the postmaster
starts it (most commonly, the configuration files).

Once it is running, it will only notice a change to worker_spi.naptime
if it responds to a HUP signal by doing a new ProcessConfigFile(). The
example code does exactly this, so you can see how it's done.

That means you do have a way to control it "via SQL" if:

- you use ALTER SYSTEM to set the value of worker_spi.naptime, and
- if you change the value with ALTER SYSTEM and want the new value
  to take effect while the worker is already running, you do
  SELECT pg_reload_conf()

In the second, dynamic-launch case (which is also illustrated in
worker_spi.c, as it contains code for both ways of launching), you can
see that you end up writing an SQL-callable function (worker_spi_launch
in the example), which you can define to take whatever arguments you
want it to.

How to get those arguments to the background worker is the next
question. You can stash one value in bgw_main_arg (as a Datum),
and have it passed as the only argument to your main function
in the worker.

Or, if you want to pass it more information, you can pass up to
BGW_EXTRALEN bytes of stuff of your choice in bgw_extra, and
have the worker retrieve it via MyBgworkerEntry.

-Chap

Re: worker_spi.naptime in worker_spi example

[Jeremy Finzel]

On Thu, Mar 15, 2018 at 12:21 PM, Chapman Flack 
wrote:

> On 03/15/2018 10:59 AM, Jeremy Finzel wrote:
>
> > WaitLatch function the same variable worker_spi_naptime without
> referencing
> > the configuration created in _PG_init.  Am I missing something here?
>
> You can see the declaration of that variable up on line 56.
> It's just an int variable static to worker_spi.c.
>
> The code you saw in _PG_init registers that variable to the
> Grand Unified Configuration system (you can see where the address
> of the variable, _spi_naptime, is passed in that registration),
> so the GUC system can handle setting or querying the value from SQL
> (and config files, etc.).
>
> You can read more about the GUC system in
> src/backend/utils/misc/README, src/backend/utils/misc/guc.c, and
> src/include/utils/guc.h.
>
> -Chap
>

Thank you.  I did see worker_spi_naptime definition, but I wanted to
pass worker_spi_naptime
as an argument instead into worker_spi_main so that I can control via SQL
interface how frequently the worker SQL executes.  Is this possible?

Thanks,
Jeremy

Re: worker_spi.naptime in worker_spi example

[Chapman Flack]

On 03/15/2018 10:59 AM, Jeremy Finzel wrote:

> WaitLatch function the same variable worker_spi_naptime without referencing
> the configuration created in _PG_init.  Am I missing something here?

You can see the declaration of that variable up on line 56.
It's just an int variable static to worker_spi.c.

The code you saw in _PG_init registers that variable to the
Grand Unified Configuration system (you can see where the address
of the variable, _spi_naptime, is passed in that registration),
so the GUC system can handle setting or querying the value from SQL
(and config files, etc.).

You can read more about the GUC system in
src/backend/utils/misc/README, src/backend/utils/misc/guc.c, and
src/include/utils/guc.h.

-Chap

worker_spi.naptime in worker_spi example

[Jeremy Finzel]

Hello!

I am trying to create a background worker where I can pass in via SQL
interface the naptime.  I'm not sure how to do this.  In the worker_spi
example, _PG_init creates a configuration using the static variable
worker_spi_naptime, but then it appears to call in worker_spi_main in the
WaitLatch function the same variable worker_spi_naptime without referencing
the configuration created in _PG_init.  Am I missing something here?

I could see how to pass in an int for naptime via the SQL interface but I'm
not sure what the function is of this configuration.

Thanks!
Jeremy