5 Configuration Parameters

PDF page 61

In PostgreSQL 14 the following parameters have been changed.

3.3.1 Added parameters

The following parameters have been added.

PDF page 62

Compute_query_id

If the parameter value is set to on, a unique ID will be calculated for the query being executed. The query ID will be the same value for all SQLs that have the same meaning in the database. The query ID can be displayed by EXPLAIN VERBOSE statement, queryid column in pg_stat_activity view, %Q in log_line_prefix parameter, and so on. This is an adaptation of the functionality previously used by the pg_stat_statements module. The default value is ‘auto’, which is automatically enabled if modules such as pg_stat_statements require this parameter.

postgres=# SET compute_query_id = on ;
SET
postgres=# EXPLAIN VERBOSE SELECT * FROM data1 ;
                              QUERY PLAN
-----------------------------------------------------------------
 Seq Scan on public.data1  (cost=0.00..15406.00 rows=1000000
width=12)
   Output: c1, c2
 Query Identifier: 3365166609774651210
(3 rows)

Idle_session_timeout

Specifies the timeout value in milliseconds for forcibly terminating idle sessions. The default value is 0, which means that no timeout will occur. During transaction execution, idle_in_transaction_session_timeout is used and idle_session_timeout is ignored.

PDF page 63

postgres=> \set VERBOSITY verbose
postgres=> SET idle_session_timeout = 1000 ;
SET
postgres=> -- Wait one second.
postgres=> SELECT 1 ;
FATAL:  57P05: terminating connection due to idle-session timeout
LOCATION:  ProcessInterrupts, postgres.c:3356
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
postgres=>

Log_recovery_conflict_waits

In an environment where the log_recovery_conflict_waits parameter is enabled on the standby instance, conflict information is output to the log when a conflict occurs when deadlock_timeout is exceeded. Also, if the conflict is resolved, it will be output to the log. The following is the output conflict information.

-- When conflict occurs
LOG:  recovery still waiting after 1031.776 ms: recovery conflict
on snapshot
DETAIL:  Conflicting process: 53647.
 [49948] CONTEXT:  WAL redo at 1/8ECE0138 for Heap2/PRUNE:
latestRemovedXid 895 nredirected 0 ndead 167; blkref #0: rel
1663/14892/17264, blk 108
-- When conflict is resolved
LOG:  recovery finished waiting after 26073.257 ms: recovery
conflict on snapshot
CONTEXT:  WAL redo at 1/8ECE0138 for Heap2/PRUNE: latestRemovedXid
895 nredirected 0 ndead 167; blkref #0: rel 1663/14892/17264, blk
108

PDF page 64

Remove_temp_files_after_crash

Determines whether to remove the temporary files that were created when the backend process terminated abnormally. Conventionally, these files are not removed until the instance is restarted. The default value is ‘on’, which means that the temporary files will be deleted if the backend process terminates abnormally.

3.3.2 Changed parameters

The setting range and options were changed for the following configuration parameters.

3.3.3 Removed parameters

The following parameters have been removed. The parameter vacuum_cleanup_index_scale_factor is disabled since PostgreSQL 13.3.

PDF page 65

3.3.4 Parameters with default values changed

The following parameters have changed default values.