开篇灵魂四问
-
你知道哪些GUC参数没有在postgresql.conf中显示吗?
-
你知道哪些GUC参数在show all时没有显示吗?
-
你知道哪些GUC参数在reset all时不会被重置吗?
-
你知道哪些GUC参数会显示在EXPLAIN时吗?
是否很熟悉的GUC参数,顷刻间也没那么熟悉了?
随着 PostgreSQL 15 的开发正在进行中,越来越多的功能被提交。今天我们将看一个新功能,关于GUC的相关标志, 一个值得拥有和了解的好东西。
如果你看一下截至今天 PostgreSQL 有多少个设置,有 358 个(这是 PostgreSQL 15dev):
mydb=# select count(*) from pg_settings ;
count
358
(1 row)
其中一些设置在由 initdb 创建的默认 postgresql.conf 中可用,其他设置不可用。
某些设置可以重新设置,而您不能为其他设置重新设置。
postgres=# select distinct flags
from (select pg_settings_get_flags(name)
as flags from pg_settings) as x;
flags
{EXPLAIN}
{}
{EXPLAIN,NOT_IN_SAMPLE}
{NOT_IN_SAMPLE,RUNTIME_COMPUTED}
{NOT_IN_SAMPLE}
{NO_RESET_ALL,NOT_IN_SAMPLE}
(6 rows)
该函数的返回类型是一个文本数组,这是我在我的实例上可以看到的。
标志的含义是:[(从这里偷来的)] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=d10e41d4238e7dcd23968230939c0c59cbcb41c2#patch1
EXPLAIN: EXPLAIN 命令中包含的参数
NO_SHOW_ALL: SHOW ALL 命令中排除的参数
NO_RESET_ALL: RESET ALL 命令中排除的参数
NOT_IN_SAMPLE: 默认情况下不包含在 postgresql.conf 中的参数
RUNTIME_COMPUTED: 运行时计算的参数
例如,如果您想知道默认 postgresql.conf 中不包含哪些设置,您可以使用如下新函数pg_settings_get_flags:
mydb=# with flags_all as (select name,pg_settings_get_flags(name) as flags from pg_settings)
select *
from flags_all
where flags_all.flags @> ARRAY[‘NOT_IN_SAMPLE’];
name | flags
----------------------------------±---------------------------------
allow_in_place_tablespaces | {NOT_IN_SAMPLE}
allow_system_table_mods | {NOT_IN_SAMPLE}
application_name | {NOT_IN_SAMPLE}
backtrace_functions | {NOT_IN_SAMPLE}
block_size | {NOT_IN_SAMPLE}
data_checksums | {NOT_IN_SAMPLE,RUNTIME_COMPUTED}
data_directory_mode | {NOT_IN_SAMPLE,RUNTIME_COMPUTED}
debug_assertions | {NOT_IN_SAMPLE}
debug_discard_caches | {NOT_IN_SAMPLE}
force_parallel_mode | {EXPLAIN,NOT_IN_SAMPLE}
ignore_checksum_failure | {NOT_IN_SAMPLE}
ignore_invalid_pages | {NOT_IN_SAMPLE}
ignore_system_indexes | {NOT_IN_SAMPLE}
in_hot_standby | {NOT_IN_SAMPLE}
integer_datetimes | {NOT_IN_SAMPLE}
jit_debugging_support | {NOT_IN_SAMPLE}
jit_dump_bitcode | {NOT_IN_SAMPLE}
jit_expressions | {NOT_IN_SAMPLE}
jit_profiling_support | {NOT_IN_SAMPLE}
jit_tuple_deforming | {NOT_IN_SAMPLE}
lc_collate | {NOT_IN_SAMPLE}
lc_ctype | {NOT_IN_SAMPLE}
max_function_args | {NOT_IN_SAMPLE}
max_identifier_length | {NOT_IN_SAMPLE}
max_index_keys | {NOT_IN_SAMPLE}
post_auth_delay | {NOT_IN_SAMPLE}
pre_auth_delay | {NOT_IN_SAMPLE}
remove_temp_files_after_crash | {NOT_IN_SAMPLE}
segment_size | {NOT_IN_SAMPLE}
server_encoding | {NOT_IN_SAMPLE}
server_version | {NOT_IN_SAMPLE}
server_version_num | {NOT_IN_SAMPLE}
shared_memory_size | {NOT_IN_SAMPLE,RUNTIME_COMPUTED}
shared_memory_size_in_huge_pages | {NOT_IN_SAMPLE,RUNTIME_COMPUTED}
ssl_library | {NOT_IN_SAMPLE}
trace_notify | {NOT_IN_SAMPLE}
trace_recovery_messages | {NOT_IN_SAMPLE}
trace_sort | {NOT_IN_SAMPLE}
transaction_deferrable | {NO_RESET_ALL,NOT_IN_SAMPLE}
transaction_isolation | {NO_RESET_ALL,NOT_IN_SAMPLE}
transaction_read_only | {NO_RESET_ALL,NOT_IN_SAMPLE}
wal_block_size | {NOT_IN_SAMPLE}
wal_consistency_checking | {NOT_IN_SAMPLE}
wal_segment_size | {NOT_IN_SAMPLE,RUNTIME_COMPUTED}
zero_damaged_pages | {NOT_IN_SAMPLE}
(45 rows)
所有这些主要是开发人员或跟踪设置,因此不包含在默认的 postgresql.conf 中。
可能会问:
如果您执行reset all,哪些参数不会被重置?
mydb=# with flags_all as (select name,pg_settings_get_flags(name) as flags from pg_settings)
select *
from flags_all
where flags_all.flags @> ARRAY[‘NO_RESET_ALL’];
name | flags
------------------------±-----------------------------
transaction_deferrable | {NO_RESET_ALL,NOT_IN_SAMPLE}
transaction_isolation | {NO_RESET_ALL,NOT_IN_SAMPLE}
transaction_read_only | {NO_RESET_ALL,NOT_IN_SAMPLE}
(3 rows)
… 或着在查询中组合两个或多个标志:
mydb=# with flags_all as (select name,pg_settings_get_flags(name) as flags from pg_settings)
select *
from flags_all
where flags_all.flags @> ARRAY[‘EXPLAIN’,‘NOT_IN_SAMPLE’];
name | flags
---------------------±------------------------
force_parallel_mode | {EXPLAIN,NOT_IN_SAMPLE}
(1 row)
非常不错的小功能!
作者原文地址:
https://daydayup863.github.io/PostgreSQL/PostgreSQL-15/GUC/GUC-flags/PostgreSQL_15_preview_flags_of_GUC/
PG考试相关详情:http://www.pgccc.com.cn/