7 Contrib Modules

Changes to contrib extensions and the modules covered by the source document.

GASource pages: PDF 70-78Original PDF on GitHub

PDF page 70

Describe new features related to the Contrib modules.

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

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

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

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

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

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
--------+---------+-------------------------+-----------------+-------------

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=806eb92c018102da9514443ad19160

 public | intdict | public.intdict_template | absval = 'true' | dictionary

c7b1841062

for integers
(1 row)

3.5.4 ltree

The ltree, lquery, and ltxtquery types now support binary I/O.

Example 79 information on the ltree data type

postgres=> SELECT typreceive, typsend FROM pg_type WHERE typname='ltree' ;
-[ RECORD 1 ]----------
typreceive | ltree_recv
typsend    | ltree_send

3.5.5 pageinspect

The following enhancements have been implemented in pageinspect extension.

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

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

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

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

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

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       |

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.

Table 28 Added columns

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

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

The following columns have been added to the pg_stat_statements view:

PDF page 75

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.

Table 29 Added columns

The column names in the pg_stat_statements view have been changed as follows.

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.

Table 30 Modified columns

PDF page 76

Example 83 Refer the pg_stat_statements view

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

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

The following options have been added to the postgres_fdw extension:

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

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

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.

By setting the option password_required to 'false', password setting for user mapping can be avoided.

Example 86 Setting to allow password omission

postgres=# ALTER USER MAPPING FOR demo SERVER remhost1 OPTIONS
(password_required 'false');
ALTER USER MAPPING

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

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

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