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
