Possible problem with pg_reload_conf() and view pg_settings

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 or terminate 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