Re: worker_spi.naptime in worker_spi example

On Thu, Mar 15, 2018 at 3:54 PM, Chapman Flack <chap(at)anastigmatix(dot)net>
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,