PG生效参数的演进过程

缺乏、安全感 2023-06-19 11:05 1阅读 0赞

我们用的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

EDB相关历史文章，

《[EDB官方网站和文档的介绍][EDB]》

《[EDB无法删除分区子表的错误][EDB 1]》

《[Oracle/EDB/达梦，对同字段多索引的支持][Oracle_EDB]》

《[EDB索引坏块的问题][EDB 2]》  
《[EDB(/PG)对于执行计划的解读][EDB_PG]》

[EDB]: https://blog.csdn.net/bisal/article/details/88265950
[EDB 1]: https://blog.csdn.net/bisal/article/details/87941847
[Oracle_EDB]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea81578dddf6de9b33b1630dc52d464cd565324ef4f46ca63fb523077bc7c36453fd548c623d&idx=1&mid=2247488126&scene=21&sn=7f076fbb210425d5de07969062e283b0#wechat_redirect
[EDB 2]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea8157f4ddf6dee261c90364475f3fc88bb4bc9840ee541bb4333d229cfc745b6a6ee5311345&idx=1&mid=2247488007&scene=21&sn=5d481ce9a37b2ad49de1460defe6137d#wechat_redirect
[EDB_PG]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea814c5addf6c54c519aafcb8c24437b5d84828dbbf12af6f6135745f648baeba60a3c73a2e4&idx=1&mid=2247485865&scene=21&sn=40a2d34afcca8dc05111c4f05be8d1d5#wechat_redirect