开篇灵魂四问:

你知道哪些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)

该函数的返回类型是一个文本数组，这是我在我的实例上可以看到的。标志的含义是（从这里偷来的）：

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)

非常不错的小功能.

转自

1	`https://blog.dbi-services.com/postgresql-15-new-function-to-list-flags-associated-to-gucs/`

daydayup863

PostgreSQL 15 preview - 你了解PostgreSQL GUC 参数吗?

转自