7 Contrib Modules

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

GASource pages: PDF 65-70Original PDF on GitHub

PDF page 65

Describes new features related to the Contrib modules.

3.5.1 Amcheck

A sequence checking is now supported.

Example 76 Sequence checking

postgres=# CREATE SEQUENCE seq1 ;
CREATE SEQUENCE
postgres=# SELECT verify_heapam(relation=>'seq1') ;
 verify_heapam
---------------
(0 rows)

3.5.2 Basebackup_to_shell

This module is used when “shell” is specified as the target of the base backup. When using this module, specify basebackup_to_shell in the parameter shared_preload_libraries or local_preload_libraries. The following parameters can be specified:

Parameter name	Description
basebackup_to_shell.command	Command path to be executed
basebackup_to_shell.required_role	Roles required for execution

Table 24 Available parameters

3.5.3 File_fdw

The MATCH value can now be specified in the HEADER option of the CREATE FOREIGN TABLE statement. This option checks for a match between the header line and column name in the text file.

PDF page 66

Example 77 Header match check

postgres=# \! cat /tmp/data1.csv
c1,c3
1,data1
2,data2
postgres=# CREATE EXTENSION file_fdw ;
CREATE EXTENSION
postgres=# CREATE SERVER filesvr1 FOREIGN DATA WRAPPER file_fdw ;
CREATE SERVER
postgres=# CREATE FOREIGN TABLE head1 (c1 int, c2 text) SERVER filesvr1
OPTIONS (format 'csv', filename '/tmp/data1.csv', delimiter ',',
header 'match') ;
CREATE FOREIGN TABLE
postgres=# SELECT * FROM head1 ;
ERROR:  column name mismatch in header line field 2: got "c3", expected "c2"
CONTEXT:  COPY head1, line 1: "c1,c3"

3.5.4 Pg_stat_statements

The pg_stat_statements view has additional items that can be monitored.

I/O information for temporary files

Read/write times for temporary files can now be monitored. The following columns have been added to the pg_stat_statements view.

Column name	Data type	Description
temp_blk_read_time	double precision	Temporary block read time
temp_blk_write_time	double precision	Temporary block write time

Table 25 Added column

JIT-related information can now be monitored; the following columns have been added to the pg_stat_statements view.

PDF page 67

Column name	Data type	Description
jit_functions	bigint	Number of JIT-compiled function executions
jit_generation_time	double precision	JIT code generation time (milliseconds)
jit_inlining_count	bigint	Number of executions of inlined function
jit_inlining_time	double precision	Execution time of inlined function (milliseconds)
jit_optimization_count	bigint	Optimized statement execution count
jit_optimization_time	double precision	Optimized statement execution time (milliseconds)
jit_emission_count	bigint	Number of times the code was issued
jit_emission_time	double precision	Time (milliseconds) the code was used to issue

Table 26 Added column

3.5.5 Pg_walinspect

The pg_walinspect module is a module for executing processing similar to the pg_waldump command with SQL statements. The following functions are provided. These functions can only be executed by users with the SUPERUSER attribute or the pg_read_server_files role.

Function name	Description
pg_get_wal_record_info	Return WAL information for the specified LSN
pg_get_wal_records_info	Return WAL information between specified LSNs
pg_get_wal_records_info_till_end_of_wal	Return WAL information from the specified LSN to the end
pg_get_wal_stats	Return WAL statistics between specified LSNs
pg_get_wal_stats_till_end_of_wal	Return WAL statistics from the specified LSN to the end

Table 27 Provided functions

PDF page 68

Example 78 Execute the pg_get_wal_stats function

postgres=# CREATE EXTENSION pg_walinspect ;
CREATE EXTENSION
postgres=# SELECT * FROM pg_get_wal_stats('0/B4BB822'::pg_lsn ,
 '0/B4BB828'::pg_lsn) ;
-[ RECORD 1 ]----------------+------------------
resource_manager/record_type | XLOG
count                        | 0
count_percentage             | 0
…

3.5.6 Postgres_fdw

The following enhancements have been implemented in postgres_fdw:

Specifying the application name

It is now possible to specify application_name for a remote connection. Change the postgres_fdw.application_name parameter.

Example 79 Specify application name

postgres=# SET postgres_fdw.application_name TO 'fdw_name1' ;
SET
postgres=# CREATE SERVER svr5432 FOREIGN DATA WRAPPER postgres_fdw OPTIONS
(host 'remhost1', port '5432', dbname 'postgres') ;
CREATE SERVER

The following escape sequences can be specified in the application name.

PDF page 69

Escape sequence	Description	Note
%a	Local application name
%c	Session ID
%C	Cluster name (cluster_name)
%d	Local database name
%p	Backend process ID
%u	Local user name
%%	% character

Table 28 Available escape sequences

Parallel_commit option

Added the parallel_commit option to control how remote transaction commits are executed. Setting this option to ‘on’ allows remote transaction commits in parallel. The default value is off, which causes commits to be executed serially.

Example 80 Specify parallel_commit option

postgres=# CREATE SERVER svr5433 FOREIGN DATA WRAPPER postgres_fdw OPTIONS
( host 'remhost1', port '5433', dbname 'postgres', parallel_commit 'true') ;
CREATE SERVER
postgres=# SELECT srvoptions FROM pg_foreign_server WHERE srvname='svr5433' ;
                           srvoptions
-----------------------------------------------------------------
 {host=localhost,port=5433,dbname=postgres,parallel_commit=true}
(1 row)

CASE expression

CASE expressions are now pushed to remote instances.

PDF page 70

Example 81 Pushed CASE expression

postgres=> EXPLAIN (ANALYZE, VERBOSE) SELECT COUNT(*) FROM remote1 WHERE
CASE WHEN c1 > 100 THEN c1 END < 100 ;
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Foreign Scan  (cost=102.84..164.07 rows=1 width=8) (actual time=1.364..1.365
rows=1 loops=1)
   Output: (count(*))
   Relations: Aggregate on (public.remote1)
   Remote SQL: SELECT count(*) FROM public.remote1 WHERE (((CASE WHEN (c1 >
100::numeric) THEN c1 ELSE NULL::numeric END) < 100::numeric))
 Planning Time: 0.077 ms
 Execution Time: 1.620 ms
(6 rows)

3.5.7 Sepgsql

The permissive/enforcing state has been added to the log, with permissive=0 or permissive=1 output at the end of the line.