On Thu, Mar 15, 2018 at 3:54 PM, Chapman Flack <c...@anastigmatix.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,
