Thread: Possible problem with pg_reload_conf() and view pg_settings
Hi, I have been playing around with pg_reload_conf() and the pg_settings view. I understand that the pg_settings view, if updated, applies to the current session only. However I was under the impression that if I did a pg_reload_conf(), the pg_settings view would be updated at that time, but that does not seem to happen. I am running on win32, but the same thing happens on Linux. If I restart the Postgresql service then the pg_settings view contains the changes I made to the postgresql.conf file. Any ideas, does this seem like a possible bug? It just seems to me that pg_settings should be updated if a pg_reload_conf() is executed. Thanks, Tony
Tony Caduto <tony_caduto@amsoftwaredesign.com> writes:
> However I was under the impression that if I did a pg_reload_conf(), the
> pg_settings view would be updated at that time, but that does not seem to
> happen.
It works for me ...
regression=# select setting from pg_settings where name = 'constraint_exclusion';setting
---------off
(1 row)
-- edit postgresql.conf in another window, set constraint_exclusion = on
regression=# select pg_reload_conf();pg_reload_conf
----------------t
(1 row)
regression=# select setting from pg_settings where name = 'constraint_exclusion';setting
---------on
(1 row)
regards, tom lane
"Tom Lane" <tgl@sss.pgh.pa.us> wrote in message news:18807.1131149902@sss.pgh.pa.us... > Tony Caduto <tony_caduto@amsoftwaredesign.com> writes: >> However I was under the impression that if I did a pg_reload_conf(), the >> pg_settings view would be updated at that time, but that does not seem to >> happen. I repeated Tony's result (Win32): Welcome to psql 8.1RC1, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g orterminate with semicolon to execute query \q to quit test=# select setting from pg_settings where name = 'constraint_exclusion';setting ---------off (1 row) test=# select pg_reload_conf();pg_reload_conf ----------------t (1 row) test=# select setting from pg_settings where name = 'constraint_exclusion';setting ---------off (1 row) test=# LOG: received SIGHUP, reloading configuration files test=# select setting from pg_settings where name = 'constraint_exclusion';setting ---------off (1 row) test=# select setting from pg_settings where name = 'constraint_exclusion';setting ---------on (1 row) -- Seems that's due to delay of process SIGHUP ...
Qingqing Zhou wrote: > >test=# LOG: received SIGHUP, reloading configuration files > >test=# select setting from pg_settings where name = 'constraint_exclusion'; > setting >--------- > off >(1 row) > >test=# select setting from pg_settings where name = 'constraint_exclusion'; > setting >--------- > on >(1 row) > > >-- Seems that's due to delay of process SIGHUP ... > > > > What's the delay? 1s? 5? 10? cheers andrew
> > What's the delay? 1s? 5? 10? > Delay is the time difference we kill a signal and the time we really process it. We kill at once, but only process it at proper idle time. In my test, the delay is 2 seconds or so. I am looking into the problem - not sure the exact details now though ... Regards, Qingqing
"Qingqing Zhou" <zhouqq@cs.toronto.edu> writes:
> I repeated Tony's result (Win32):
Hmm, some delay in the signal being recognized in our Win32 signal
implementation? Why would that be?
regards, tom lane
"Tom Lane" <tgl@sss.pgh.pa.us> wrote > > Hmm, some delay in the signal being recognized in our Win32 signal > implementation? Why would that be? > I believe this is a disease for all platforms, not only Windows. This is because the signals are asynchoronized. Think when you returned from kill(SIGHUP), you can't suppose that Postmaster already got it. Now when you do your next command very fast, then there is a chance you didn't receive the forwarded SIGHUP from Postmaster. But windows does make the disease easier to be seen. Regards, Qingqing
Andrew Dunstan wrote: > > > Qingqing Zhou wrote: > >> >> test=# LOG: received SIGHUP, reloading configuration files >> >> test=# select setting from pg_settings where name = >> 'constraint_exclusion'; >> setting >> --------- >> off >> (1 row) >> >> test=# select setting from pg_settings where name = >> 'constraint_exclusion'; >> setting >> --------- >> on >> (1 row) >> >> >> -- Seems that's due to delay of process SIGHUP ... >> >> >> >> > > What's the delay? 1s? 5? 10? > > cheers > > andrew > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > > hmm, I waited for at least 1 minute after doing the reload and it was never updated. It seemed at the time that the only way to get a updated pg_settings view was to actually restart the server. I plan on doing some more testing on Saturday. Tony
On Fri, 4 Nov 2005, Tony Caduto wrote: > > hmm, I waited for at least 1 minute after doing the reload and it was > never updated. It seemed at the time that the only way to get a updated > pg_settings view was to actually restart the server. I plan on doing > some more testing on Saturday. > Did you set "constraint_exclusion = on", then comment it "#constraint_exclusion = on" in the .conf file? If so, then ProcessConfigFile() will ignore this line since it is a comment, instead of setting back its value to "off". Try to do set "constraint_exclusion = off" explicitely, see if long delay ever happens. Btw, is this considered a bug of ProcessConfigFile()? To fix it, we can call InitializeGUCOptions() somewhere but I am not sure which values should be untouched, like ConfigFileName ... Regards, Qingqing
I have tested on pgsql-8.1-beta3 on Windows 2003. It works fine. I changed the line in postmaster.conf between "on" and "off". (Remember to save it each time). And paste the two lines in psql to see the results. select pg_reload_conf(); select setting from pg_settings where name = 'constraint_exclusion'; "Tony Caduto" <tony_caduto@amsoftwaredesign.com> wrote > Hi, > I have been playing around with pg_reload_conf() and the pg_settings view. > > I understand that the pg_settings view, if updated, applies to the current > session only. > However I was under the impression that if I did a pg_reload_conf(), the > pg_settings view would be updated at that time, but that does not seem to > happen. I am running on win32, but the same thing happens on Linux. > > If I restart the Postgresql service then the pg_settings view contains the > changes I made to the postgresql.conf file. > > Any ideas, does this seem like a possible bug? It just seems to me that > pg_settings should be updated if a pg_reload_conf() is executed. > > > Thanks, > > Tony > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster >