PDF page 70
Describe new features related to the Contrib modules. #### 3.5.1 adminpack {#pg13-3-5-1-adminpack} The pg_file_sync function has been added to the extension adminpack. This function guarantees a reliable write to the specified file or directory. **Example 76.** pg\_file\_sync function ```text postgres=# CREATE EXTENSION adminpack ; CREATE EXTENSION postgres=# SELECT pg_file_sync('postgresql-2020-09-24_120622.log') ; pg_file_sync -------------- (1 row) ``` #### 3.5.2 auto_explain {#pg13-3-5-2-auto-explain} The log_wal parameter to output WAL information has been added to the extension auto_explain. The default value is 'off', and WAL information is not output. If this parameter is set to 'on', auto_explain.log_analyze must also be set to 'on'.PDF page 71
**Example 77.** log\_wal parameter ```text postgres=# LOAD 'auto_explain' ; LOAD postgres=# SET auto_explain.log_wal = on ; SET postgres=# SET auto_explain.log_analyze = on ; SET postgres=# SET auto_explain.log_min_duration = 0 ; SET postgres=# DELETE FROM data1 WHERE c1=100 ; DELETE 1 postgres=# \! tail -7 log/postgres.log 2020-09-24 21:16:28.707 JST [4731] LOG: duration: 0.064 ms plan: Query Text: DELETE FROM data1 WHERE c1=100 ; Delete on data1 (cost=0.00..4.50 rows=2 width=6) (actual time=0.063..0.063 rows=0 loops=1) WAL: records=2 fpi=2 bytes=8966 -> Seq Scan on data1 (cost=0.00..4.50 rows=2 width=6) (actual time=0.018..0.026 rows=2 loops=1) Filter: (c1 = '100'::numeric) Rows Removed by Filter: 198 ``` #### 3.5.3 dict_int {#pg13-3-5-3-dict-int} The dict_int extension now has an ABSVAL attribute. If this attribute is specified, the sign specified for the integer value will be ignored.PDF page 72
**Example 78.** ABSVAL attribute ```text postgres=# CREATE EXTENSION dict_int ; CREATE EXTENSION postgres=# ALTER TEXT SEARCH DICTIONARY intdict (ABSVAL = true) ; ALTER TEXT SEARCH DICTIONARY postgres=# \dFd+ intdict List of text search dictionaries Schema | Name | Template | Init options | Description --------+---------+-------------------------+-----------------+------------- ``` ```text https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=806eb92c018102da9514443ad19160 ``` ```text public | intdict | public.intdict_template | absval = 'true' | dictionary ``` ```text c7b1841062 ``` ```text for integers (1 row) ``` #### 3.5.4 ltree {#pg13-3-5-4-ltree} The ltree, lquery, and ltxtquery types now support binary I/O. **Example 79.** information on the ltree data type ```text postgres=> SELECT typreceive, typsend FROM pg_type WHERE typname='ltree' ; -[ RECORD 1 ]---------- typreceive | ltree_recv typsend | ltree_send ``` #### 3.5.5 pageinspect {#pg13-3-5-5-pageinspect} The following enhancements have been implemented in pageinspect extension. ##### Heap_tuple_infomask_flags function {#pg13-p72-heap-tuple-infomask-flags-function} This function converts the values of t_infomask, t_infomask2 into a human-readable form.PDF page 73
**Example 80.** heap\_tuple\_infomask\_flags function ```text postgres=# SELECT t_infomask, t_infomask2, raw_flags, combined_flags FROM heap_page_items(get_raw_page('data1', 0)), LATERAL heap_tuple_infomask_flags(t_infomask, t_infomask2) m(raw_flags, combined_flags) ; -[ RECORD 1 ]--+--------------------------------------------------------- t_infomask | 2306 t_infomask2 | 2 raw_flags | {HEAP_HASVARWIDTH,HEAP_XMIN_COMMITTED,HEAP_XMAX_INVALID} combined_flags | {} ``` ##### Bt_metap function {#pg13-p73-bt-metap-function} An allequalimage column has been added to the output of the bt_metap function to indicate the status of duplicate data in the index. **Example 81.** Output of bt\_metap function ```text postgres=# SELECT * FROM bt_metap('idx1_data1') ; -[ RECORD 1 ]-----------+------- magic | 340322 version | 4 root | 3 level | 1 fastroot | 3 fastlevel | 1 oldest_xact | 0 last_cleanup_num_tuples | -1 allequalimage | t ``` ##### Bt_page_items function {#pg13-p73-bt-page-items-function} The dead, htid, and tids columns have been added to the output of the bt_page_items function.PDF page 74
**Example 82.** Output of bt\_page\_items function ```text postgres=# SELECT * FROM bt_page_items('idx1_data1', 1) ; -[ RECORD 1 ]----------------------- itemoffset | 1 ctid | (0,1) itemlen | 16 nulls | f vars | t data | 0b 00 80 01 00 00 00 00 dead | f htid | (0,101) tids | ``` **Table 28.** Added columns | Column name | Description | | --- | --- | | dead | A boolean value indicating the value of the LP_DEAD bit. | | htid | Indicates a single heap TID value for each tuple. | | tids | An array of TID. | #### 3.5.6 pg_stat_statements {#pg13-3-5-6-pg-stat-statements} The features to output execution plan and WAL information have been added to the pg_stat_statements extension. ##### Track_planning parameter {#pg13-p74-track-planning-parameter} This parameter specifies whether to track the execution plan. The default value is 'off'. If this parameter is specified, information about the execution plan will be added to the pg_stat_statements view. ##### Pg_stat_statements view {#pg13-p74-pg-stat-statements-view} The following columns have been added to the pg_stat_statements view:PDF page 75
**Table 29.** Added columns | Column name | Data type | Description | | --- | --- | --- | | plans | bigint | Number of times the statement was planned. | | total_plan_time | double precision | Total time spent planning the statement. | | min_plan_time | double precision | Minimum time spent planning the statement, in milliseconds. | | max_plan_time | double precision | Maximum time spent planning the statement, in milliseconds. | | mean_plan_time | double precision | Meantime spent planning the statement, in milliseconds. | | stddev_plan_time | double precision | Population standard deviation of time spent planning the statement, in milliseconds. | | wal_records | bigint | Total count of WAL records. | | wal_fpi | bigint | Total number of WAL full-page writes. | | wal_bytes | numeric | Total amount of WAL bytes. | The column names in the pg_stat_statements view have been changed as follows. **Table 30.** Modified columns | PostgreSQL 12 | PostgreSQL 13 | Description | | --- | --- | --- | | total_time | total_exec_time | Total time spent executing the statement. | | min_time | min_exec_time | Minimum time spent executing the statement. | | max_time | max_exec_time | Maximum time spent executing the statement. | | mean_time | mean_exec_time | Mean time spent executing the statement. | | stddev_time | stddev_exec_time | Population standard deviation of time spent executing the statement. |PDF page 76
**Example 83.** Refer the pg\_stat\_statements view ```text postgres=# SELECT queryid, plans, total_plan_time, min_plan_time FROM pg_stat_statements WHERE plans != 0; queryid | plans | total_plan_time | min_plan_time ----------------------+-------+----------------------+--------------- 1809546128015582813 | 1 | 0.9533 | 0.9533 1580010417825738145 | 900 | 29.697499999999998 | 0.0194 726988806503882440 | 1 | 0.0889 | 0.0889 6547867665616429211 | 1 | 0.3364 | 0.3364 1518237015596383425 | 1 | 0.0522 | 0.0522 6090531631178874043 | 900 | 51.09360000000001 | 0.0221 -8944905077122340531 | 6 | 0.22340000000000002 | 0.0333 -261743227044297956 | 10 | 0.061900000000000004 | 0.0054 … ``` ##### SQL statement normalization {#pg13-p76-sql-statement-normalization} The SELECT statement with the FOR UPDATE clause specified and the SELECT statement without it are now separated. Previously, the presence or absence of the FOR UPDATE clause was ignored. #### 3.5.7 postgres_fdw {#pg13-3-5-7-postgres-fdw} The following options have been added to the postgres_fdw extension: ##### Password_required option {#pg13-p76-password-required-option} By default, general users are refused connections without a password. This behavior can now be changed by specifying the password_required attribute when creating/changing a user mapping. Only superusers can change this option. **Example 84.** Create USER MAPPING object without a password ```text postgres=# CREATE USER MAPPING FOR demo SERVER remhost1 OPTIONS (user 'demo') ; CREATE USER MAPPING ```PDF page 77
**Example 85.** Creation of external table by general user and error ```text postgres=> CREATE FOREIGN TABLE data1(c1 INT, c2 VARCHAR(10)) SERVER remhost1 ; CREATE FOREIGN TABLE postgres=> SELECT * FROM data1 ; ERROR: password is required DETAIL: Non-superusers must provide a password in the user mapping. ``` ```text By setting the option password_required to 'false', password setting for user mapping can be avoided. ``` **Example 86.** Setting to allow password omission ```text postgres=# ALTER USER MAPPING FOR demo SERVER remhost1 OPTIONS (password_required 'false'); ALTER USER MAPPING ``` ##### Additional authentication options {#pg13-p77-additional-authentication-options} The sslkey and sslcert options can be specified as options when creating a user mapping. These options can only be specified if the superuser is creating/modifying USER MAPPING. #### 3.5.8 bool_plperl {#pg13-3-5-8-bool-plperl} The extension bool_plperl was added. Use this extension with PL/Perl extension. Used to correctly transfer the value of a bool value to a Perl program.PDF page 78
**Example 87.** bool\_plperl extension ```text postgres=> CREATE EXTENSION plperl ; CREATE EXTENSION postgres=> CREATE EXTENSION bool_plperl ; CREATE EXTENSION postgres=> CREATE FUNCTION perl_and(bool, bool) RETURNS bool TRANSFORM FOR TYPE bool AS $$ my ($a, $b) = @_; return $a && $b; $$ LANGUAGE plperl ; CREATE FUNCTION ```