[译文] PostgreSQL 15：列出与 GUC 关联的标志的新功能

今天我们一起看一个 PostgreSQL 15 的新功能，它不是锤子功能，而是一个值得拥有和了解的好东西。

如果你看一下截至今天 PostgreSQL 有多少个设置，有 353 个（这是 PostgreSQL 15dev）：

postgres=# select count(*) from pg_settings ;
 count
-------
   353
(1 row)

其中一些设置在由initdb创建的默认 postgresql.conf 中可用，其他设置不可用。某些设置可以重新设置，而您不能为其他设置重新设置。

新功能 pg_settings_get_flags 让您对此有更多了解。首先，让我们看看现在有多少不同的标志可用：

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 中不包含哪些设置，您可以使用新功能：

postgres=# 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 中。您可能会问：如果您执行全部重置，哪些参数不会被重置：

postgres=# 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)

…或者在您的查询中组合两个或多个标志：

postgres=# 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)

不错的小功能。

作者：Daniel Westermann
文章来源：https://blog.dbi-services.com/postgresql-15-new-function-to-list-flags-associated-to-gucs/