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