PG生效參數的演進過程
- 2019 年 12 月 10 日
- 筆記
版權聲明：本文為博主原創文章，遵循 CC 4.0 BY-SA 版權協議，轉載請附上原文出處鏈接和本聲明。
本文鏈接：https://blog.csdn.net/bisal/article/details/103415242
我們用的EDB數據庫，其實就是商業版PostgreSQL，用的9.2版本，測試有個需求，需要修改deadlock_timeout參數，但是不知道需不需要重啟。
這個參數的含義，並不是重點，從官方手冊，可以檢索到，
deadlock_timeout (integer) This is the amount of time, in milliseconds, to wait on a lock before checking to see if there is a deadlock condition. The check for deadlock is relatively expensive, so the server doesn't run it every time it waits for a lock. We optimistically assume that deadlocks are not common in production applications and just wait on the lock for a while before checking for a deadlock. Increasing this value reduces the amount of time wasted in needless deadlock checks, but slows down reporting of real deadlock errors. The default is one second (1s), which is probably about the smallest value you would want in practice. On a heavily loaded server you might want to raise it. Ideally the setting should exceed your typical transaction time, so as to improve the odds that a lock will be released before the waiter decides to check for deadlock. Only superusers can change this setting. When log_lock_waits is set, this parameter also determines the length of time to wait before a log message is issued about the lock wait. If you are trying to investigate locking delays you might want to set a shorter than normal deadlock_timeout.
從數據庫看，當前deadlock_timeout值是1秒，
edb=# show deadlock_timeout; deadlock_timeout ------------------ 1s (1 row)
如果執行set指令，確實更改了，
edb=# set deadlock_timeout='2s'; SET edb=# show deadlock_timeout; deadlock_timeout ------------------ 2s (1 row)
但是換個會話，發現值沒變，還是1秒，
edb=# show deadlock_timeout; deadlock_timeout ------------------ 1s (1 row)
這是因為PG中set操作，相當於Oracle中alter session操作，讓當前會話生效，退出重登錄，或者換個會話，參數值未變，如果在9.5之前，需要永久生效，唯一的做法，就是改postgresql.conf配置文件，
vi postgresql.conf ... #deadlock_timeout = 1s 改為2s ...
再使用pg_ctl指令，重新加載生效，
-bash-4.1$ pg_ctl reload server signaled
此時檢索參數，值改成了2秒，
edb=# show deadlock_timeout; deadlock_timeout ------------------ 2s (1 row)
這種操作其實很不方便，需要改配置文件，重新加載，而且需要show指令，確認更改效果。
從PG 9.5以後，增加了alter system命令，並且在pg_settings視圖中增加了pending_restart(boolean)列，該列標識了是否在重啟之後才生效，使得修改參數和查看參數是否生效都方便了很多。
alter system語法，
ALTER SYSTEM SET configuration_parameter { TO | = } { value | 'value' | DEFAULT } ALTER SYSTEM RESET configuration_parameter ALTER SYSTEM RESET ALL
例如，檢索pg_settings，pending_restart是f，表示不用重啟，即可生效，
edb=# select name, unit, context, pending_restart from pg_settings where name='deadlock_timeout'; name | setting | unit | context | pending_restart | ------------------+---------+------+-----------+------------------ deadlock_timeout | 1000 | ms | superuser f (1 row)
使用alter system，修改deadlock_timeout值，
edb=# alter system set deadlock_timeout='2s'; ALTER SYSTEM
此時，使用pg_ctl reload或SELECT pg_reload_conf();，就可以生效，
edb=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row)
通過ALTER SYSTEM配置的參數都會保存在postgresql.auto.conf文件中，因此，需要清除這些配置的時候，只需要一條指令就可以很方便的處理完成，
edb=# alter system reset all; ALTER SYSTEM
其實各種數據庫、各種技術之間，是可以相互借鑒，互相促進的，設計好的地方，吸收進來，取長補短，但之所以滯後，很可能是產品需求有限度的問題，或者技術實現的問題，沒毛病。
參考，
https://www.postgresql.org/docs/current/sql-altersystem.html
https://blog.hackroad.com/operations-engineer/linux_server/13212.html
