PDF page 57
In PostgreSQL 12 the following parameters have been changed. #### 3.3.1 Added parameters {#pg12-3-3-1-added-parameters} The following parameters have been added. **Table 18.** Added parameters | Parameter name | Description (context) | Default Val | | --- | --- | --- | | archive_cleanup_command | Migrate from recovery.conf (sighup) | - | | data_sync_retry | Behavior when fsync system call fails | off | | (Also added to 11.2) | (postmaster) | | | default_table_access_method Default storage engine name (user) | default_table_access_method Default storage engine name (user) | heap | | log_statement_sample_rate | Rate of output of SQL statement to log (superuser) | 1 | | plan_cache_mode | Changed the behavior of caching execution plans (user) | auto | | primary_conninfo | Migration from recovery.conf file (postmaster) | - | | primary_slot_name | Migrate from recovery.conf (postmaster) | - | | promote_trigger_file | Migrate from recovery.conf (sighup) | - | | recovery_end_command | Migrate from recovery.conf (sighup) | - | | recovery_min_apply_delay | Migrate from recovery.conf (sighup) | 0 | | recovery_target | Migrate from recovery.conf (postmaster) | - | | recovery_target_action | Migrate from recovery.conf (postmaster) | pause | | recovery_target_inclusive | Migrate from recovery.conf (postmaster) | on | | recovery_target_lsn | Migrate from recovery.conf (postmaster) | - | | recovery_target_name | Migrate from recovery.conf (postmaster) | - | | recovery_target_time | Migrate from recovery.conf (postmaster) | - | | recovery_target_timeline | Migrate from recovery.conf (postmaster) | latest | | recovery_target_xid | Migrate from recovery.conf (postmaster) | - | | restore_comand | Migrate from recovery.conf (postmaster) | - | | shared_memory_type | Type of shared memory (postmaster) | OS depend | | ssl_library | Name of library providing SSL function (internal) | - | | ssl_max_protocol_version | Maximum version of SSL protocol to support (sighup) | - | | ssl_min_protocol_version | Minimum version of SSL protocol to support (sighup) | TLSv1 | | tcp_user_timeout | TCP timeout specification (user) | 0 | | wal_init_zero | Fill-in WAL file to zero (superuser) | on | | wal_recycle | Recycle WAL File (superuser) | on |PDF page 58
##### Primary_conninfo parameter {#pg12-p58-primary-conninfo-parameter} Specifies the connection string for the primary instance. Previously this was specified in the recovery.conf file. The default value for the application_name entry has traditionally been "walreceiver", but when the cluster_name parameter is specified, the value for cluster_name has been changed to the default value. The value of the application_name column in the pg_stat_replication catalog changes. ##### Ssl_library parameter {#pg12-p58-ssl-library-parameter} This parameter indicates the name of the library that provides the SSL feature. The parameter value is "OpenSSL" when --with-openssl is specified when executing configure command in Red Hat Enterprise Linux environment. **Example 67.** ssl\_library parameter ```text postgres=# SHOW ssl_library ; ssl_library ------------- OpenSSL (1 row) ``` ##### Shared_memory_type parameter {#pg12-p58-shared-memory-type-parameter} This parameter specifies the type of shared memory (such as shared_buffers).PDF page 59
**Table 19.** shared\_memory\_type parameter value | Setting | Description | System call | | --- | --- | --- | | mmap | Use an anonymous memory map. | mmap | | sysv | Use System V shared memory. | shmget | | windows | Use Windows shared memory. | CreateFileMapping | The default value for this parameter on Linux is mmap. This is the same behavior as PostgreSQL ### 9.3 and later. Very small System V shared memory and most shared memory are configured using {#pg12-9-3-and-later-very-small-system-v-shared-memory-and-most-shared-memory-are-configured-using} memory map file (mmap). Setting this parameter to sysv allows you to revert to the pre-PostgreSQL ### 9.2 behavior. In this case, configure all shared memory using System V shared memory. {#pg12-9-2-behavior-in-this-case-configure-all-shared-memory-using-system-v-shared-memory} #### Plancache_mode parameter {#pg12-p59-plancache-mode-parameter} This parameter sets the method for caching the execution plan of a prepared statement (created by the PREPARE statement). The default value is auto, which is the same behavior as previous versions. Normally, an execution plan is generated each time the SQL statement created by the PREPARE statement is executed by the EXECUTE statement. In the following example, it can be seen that the execution plan changes depending on the parameters specified in the EXECUTE statement. It indicates that the data stored in column C2 is uneven, there is little plan0 data (does an index search), and there is a lot of plan1 data (a whole table is searched).PDF page 60
**Example 68.** Execution plan changes with each execution of EXECUTE statement ```text postgres=> PREPARE sel1(VARCHAR) AS SELECT * FROM plan1 WHERE c2=$1 ; PREPARE postgres=> EXPLAIN EXECUTE sel1('plan0') ; QUERY PLAN ----------------------------------------------------------------------- Index Scan using idx1_plan1 on plan1 (cost=0.42..4.44 rows=1 width=12) Index Cond: ((c2)::text = 'plan0'::text) (2 rows) postgres=> EXPLAIN EXECUTE sel1('plan1') ; QUERY PLAN ---------------------------------------------------------------- Seq Scan on plan1 (cost=0.00..17906.01 rows=1000001 width=12) Filter: ((c2)::text = 'plan1'::text) (2 rows) ``` ```sql If the same SQL statement is executed 5 times or more, the execution plan may be cached, and the cached execution plan (general execution plan) may be used even if the parameter is changed from the next time. In the example below, the display in the execution plan changes from a literal value to $1 at the execution of the sixth EXPLAIN statement. ``` **Example 69.** Execution plan is cached ```text postgres=> EXPLAIN ANALYZE EXECUTE sel1('plan1') ; -- Repeat 5 times QUERY PLAN ---------------------------------------------------------------- Seq Scan on plan1 (cost=0.00..23311.01 rows=1000001 width=12) Filter: ((c2)::text = 'plan1'::text) (2 rows) postgres=> EXPLAIN ANALYZE EXECUTE sel1('plan1') ; -- Sixth (execution plan changed) QUERY PLAN ---------------------------------------------------------------- Seq Scan on plan1 (cost=0.00..23311.01 rows=1000001 width=12) Filter: ((c2)::text = ($1)::text) (2 rows) ```PDF page 61
The newly added parameter plan_cache_mode changes this behavior. Setting the parameter value to "force_custom_plan" turns off execution plan caching. On the other hand, setting the parameter value to "force_generic_plan" immediately enables execution plan caching. **Example 70.** Setting value force\_generic\_plan ```text postgres=> SET plan_cache_mode = force_generic_plan ; SET postgres=> PREPARE sel1(VARCHAR) AS SELECT * FROM plan1 WHERE c2=$1 ; PREPARE postgres=> EXPLAIN EXECUTE sel1('plan0') ; QUERY PLAN ---------------------------------------------------------------- Seq Scan on plan1 (cost=0.00..23312.50 rows=1000120 width=12) Filter: ((c2)::text = ($1)::text) (2 rows) ``` #### Data_sync_retry parameter {#pg12-p61-data-sync-retry-parameter} This parameter determines what happens if the fsync system call issued during a checkpoint fails. In previous versions, the fsync function was re-executed (data_sync_retry = "on"), and the default behavior of the new version (data_sync_retry = "off") caused an instance stoppage by PANIC if the fsync system call failed. This parameter has been added since PostgreSQL 11.2. #### 3.3.2 Changed parameters {#pg12-3-3-2-changed-parameters} The setting range and options were changed for the following configuration parameters.PDF page 62
**Table 20.** Changed parameters | dynamic_shared_memory_type | setting value "none" has been deleted. | | --- | --- | | log_autovacuum_min_duration | Log output contents now change according to VACUUM execution status. | | log_connections | Application_name information has been added to the log. | | plpgsql.extra_warnings | The following parameter values have been added: too_many_rows strict_multi_assignment | | trace_sort | The log output message has been changed. | | wal_level | It is now checked for proper level at startup. | | wal_sender_timeout | The context has been changed from sighup to user. | | default_with_oids | It cannot be set to "on". | | recovery_target_timeline | "Current" has been added as a setting value, the default value has been changed to "latest". | | autovacuum_vacuum_cost_delay Data type changed from integer to real. | autovacuum_vacuum_cost_delay Data type changed from integer to real. | Parameter name Changes client_min_messages It is no longer possible to set it to a higher level than ERROR. The default_with_oids parameter cannot be referenced from the pg_settings catalog.PDF page 63
**Example 71.** default\_with\_oids parameter ```text postgres=> SHOW default_with_oids ; default_with_oids ------------------- off (1 row) postgres=> SET default_with_oids = on ; psql: ERROR: tables declared WITH OIDS are not supported postgres=> SELECT COUNT(*) FROM pg_settings WHERE name='default_with_oids' ; count ------- 0 (1 row) ``` ##### Wal_sender_timeout parameter {#pg12-p63-wal-sender-timeout-parameter} This parameter can now be changed by the user on a per session basis. This allows changing parameters on a per-connection basis from slave instances in a streaming replication environment. **Example 72.** wal\_sender\_timeout parameter ```bash $ grep primary_conninfo data/postgresql.conf primary_conninfo = 'host=svrhost1 port=5432 user=postgres password=password options=''-c wal_sender_timeout=5000''' ``` ##### Log_connections parameter {#pg12-p63-log-connections-parameter} The value of the application_name parameter is added to the log that is output when this parameter is set to 'on'.PDF page 64
##### Connection from psql command {#pg12-p64-connection-from-psql-command} ```text LOG: connection authorized: user=postgres database=postgres application_name=psql ``` ##### Connection from pg_basebackup command {#pg12-p64-connection-from-pg-basebackup-command} ```text LOG: replication connection authorized: user=postgres application_name=pg_baseback ``` ##### Connection from Streaming Replication slave instance {#pg12-p64-connection-from-streaming-replication-slave-instance} ```text LOG: replication connection authorized: user=postgres application_name=walreceiver ``` ##### Trace_sort parameter {#pg12-p64-trace-sort-parameter} The output log format has been changed when this parameter is set to 'on'. **Example 74.** PostgreSQL 11 log \(part\) ```text LOG: -1 switching to external sort with 16 tapes: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s LOG: -1 using 3951 KB of memory for read buffers among 15 input tapes LOG: performsort of -1 done (except 15-way final merge): CPU: user: 0.15 s, system: 0.01 s, elapsed: 0.16 s ``` **Example 75.** PostgreSQL 12 log \(part\) ```text LOG: performsort of worker -1 starting: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s LOG: internal sort of worker -1 ended, 25 KB used: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s ``` ##### Wal_level parameter {#pg12-p64-wal-level-parameter} When a replication slot is used, it became to be checked whether the wal_level parameter is an appropriate value during the instance startup. If the required level is not set, instance startup will fail.PDF page 65
In the following example, wal_level is changed to "replica" and the instance is restarted in the Logical Replication environment. **Example 76.** wal\_level value check ```text postgres=# ALTER SYSTEM SET wal_level=minimal ; ALTER SYSTEM postgres=# \q $ $ pg_ctl -D data restart waiting for server to shut down.... done server stopped waiting for server to start....2019-10-04 00:52:19.890 EDT [22682] FATAL: WAL archival cannot be enabled when wal_level is "minimal" stopped waiting pg_ctl: could not start server Examine the log output. $ ``` #### 3.3.3 Parameters with default values changed {#pg12-3-3-3-parameters-with-default-values-changed} The default values of the following configuration parameters have been changed. **Table 21.** Parameters that default value has been changed | Parameter name | PostgreSQL 11 | PostgreSQL 12 | Note | | --- | --- | --- | --- | | autovacuum_vacuum_cost_delay | 20 | 2 | | | extra_float_digitsjit | 0 | 1 | | | | off | on | | | recovery_target_timelineserver_version | current | latest | | | | 11.5 | 12.0 | | | server_version_num | 110005 | 120000 | |