7 Contrib Modules

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

GASource pages: PDF 89-95Original PDF on GitHub

PDF page 89

Describes new features related to the Contrib modules.

3.5.1 Auto_explain

The following features have been added to the auto_explain: [d4bfe41, 9d2d972]

Parameter log_parameter_max_length

Add the log_parameter_max_length parameter. This parameter limits the maximum log output width. The default value is -1, which imposes no limit. Specifying 0 for this parameter disables logging.

Output of the query ID

The query ID is output if the parameter auto_explain.log_verbose is set to ‘on’ and compute_query_id is set to ‘on’.

Example 103 Output of the query ID

$ tail -5 data/log/ postgresql-2023-09-16_135719.log
LOG:  duration: 0.005 ms  plan:
        Query Text: SELECT 1;
        Result  (cost=0.00..0.01 rows=1 width=4)
          Output: 1
        Query Identifier: -1801652217649936326

3.5.2 Fuzzystrmatch

The daitch_mokotoff function has been added to the fuzzystrmatch module. This function is superior to the SOUNDEX function for non-English languages. [a290378]

Example 104 Execute the daitch_mokotoff function

postgres=> SELECT daitch_mokotoff('Müller') ;
daitch_mokotoff
-----------------
 {479000}
(1 row)

PDF page 90

3.5.3 Ltree

Hyphens (-) are now allowed in labels. Previously, only letters, numbers, and underlines (_) were permitted. The maximum label size has been increased from 255 to 1,000 characters. [b1665bf]

3.5.4 Pageinspect

The following features have been added to the pageinspect: [1fd3dd2, 3581cbd, 428c0ca]

Bt_multi_page_stats function

Add function bt_multi_page_stats to print B-Tree index page summaries. This function is the multipage version of the bt_page_stats function. Specify the first block number and number of pages.

Example 105 Execute the bt_multi_page_stats function

postgres=# SELECT * FROM bt_multi_page_stats('idx1_data1', 2, 1) ;
-[ RECORD 1 ]-+-----
blkno         | 2
type          | l
live_items    | 367
dead_items    | 0
avg_item_size | 16
page_size     | 8192
free_size     | 808
btpo_prev     | 1
btpo_next     | 4
btpo_level    | 0
btpo_flags    | 1

Brin_page_items function

The “empty” column has been added to the output of the brin_page_items function to indicate free blocks.

3.5.5 Pg_buffercache

The following functions have been added to the pg_buffercache module. [f3fa313, f3fa313]

PDF page 91

Pg_buffercache_summary function

Add the pg_buffercache_summary function that can check buffer cache summary by SQL statement.

Example 106 Execute the pg_buffercache_summary function

postgres=# SELECT * FROM pg_buffercache_summary() ;
-[ RECORD 1 ]--+-------------------
buffers_used   | 251
buffers_unused | 16133
buffers_dirty  | 62

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=2589434ae0fbfe08e46b6a4ffba4001

buffers_pinned | 0

40b636074

usagecount_avg | 3.3346613545816735

Column name	Description	Note
buffers_usedbuffers_unusedbuffers_dirtybuffers_pinnedusagecount_avg	Total number of buffers used Number of free buffers Number of dirty buffers Number of pinned buffers Average amount of dirty buffers used

Table 35 Results of the pg_buffercache_summary function

Pg_buffercache_usage_counts function

Returns a summary of the state of all shared buffers. It is easier to check the information than by aggregating the pg_buffercache view.

Example 107 Execute the pg_buffercache_usage_counts function

postgres=# SELECT * FROM pg_buffercache_usage_counts() ;
 usage_count | buffers | dirty | pinned
-------------+---------+-------+--------
           0 |   16035 |     0 |      0
           1 |      76 |     6 |      0
           2 |      31 |     1 |      0
           3 |      13 |     6 |      0
           4 |       8 |     3 |      0
           5 |     221 |    48 |      0
(6 rows)

PDF page 92

3.5.6 Pg_stat_statements

Updated how SQL statements are generalized. SET statements, CHECKPOINT statements, CREATE statements, etc. are collected as the same query ID regardless of case. [9ba37b2, daa8365]

Example 108 Select the pg_stat_statements view

postgres=> SET enable_seqscan = off ;
SET
postgres=> set enable_seqscan = off ;
SET
postgres=> CHECKPOINT ;
CHECKPOINT
postgres=> checkpoint ;
CHECKPOINT
postgres=> SELECT queryid, query FROM pg_stat_statements WHERE userid=16384 ;
       queryid        |          query
----------------------+--------------------------
 -4537744274534392533 | SET enable_seqscan = off
 -7184411855217750948 | CHECKPOINT
(2 rows)

3.5.7 Pg_walinspect

Add the pg_get_wal_block_info function. This function extracts a full-page image between specified LSNs. [c31cf1c, 9ecb134, 122376f]

Syntax 17 - Pg_get_wal_block_info function

record pg_get_wal_block_info(start_lsn pg_lsn, end_lsn pg_lsn, show_data
boolean)

PDF page 93

Example 109 Execute the pg_get_wal_block_info function

postgres=# SELECT start_lsn, relfilenode, relblocknumber, record_type FROM
pg_get_wal_block_info('1/7E25EA98', '1/7E25EB98', false) ;
 start_lsn  | relfilenode | relblocknumber | record_type
------------+-------------+----------------+-------------
 1/7E25EAA0 |       33253 |           5405 | INSERT
 1/7E25EAE8 |       33254 |           2744 | INSERT_LEAF
 1/7E25EB28 |       33255 |            788 | INSERT_LEAF
(3 rows)

Executing this function will return the following information:

Column name	Data type	Description
start_lsn	pg_lsn	Start LSN
end_lsn	pg_lsn	End LSN
prev_lsn	pg_lsn	Previous LSN
block_id	smallint	Block ID
reltablespacereldatabase	oid	OID of the tablespace
	oid	OID of the database
relfilenoderelforknumber	oid	OID of the relation file node
	smallint	Relation fork number
relblocknumberxid	bigint	Block number of the table
	xid	Transaction ID
resource_managerrecord_type	text	Resource manager name
	text	WAL record type
record_lengthmain_data_length	integer	WAL record length
	integer	Main data length
block_data_lengthblock_fpi_length	integer	Block data length
	integer	Block FPI data length
block_fpi_infodescription	text[]	Block FPI information
	text	Description
block_datablock_fpi_data	bytea	Block data
	bytea	FPI data

Table 36 Return value of the pg_get_wal_block_info function

PDF page 94

3.5.8 Postgres_fdw

The following enhancements have been implemented in the postgres_fdw module. [97da482, 8ad51b5, 983ec23]

COPY FROM statement

The COPY FROM statement now uses the foreign table’s batch_size option. Below is the log using the log_statement parameter for the instance that owns the table. It can be confirmed that multiple records are inserted in batch.

Example 110 Execution log on the external table side

LOG:  statement: START TRANSACTION ISOLATION LEVEL REPEATABLE READ
LOG:  statement: TRUNCATE public.data1 CONTINUE IDENTITY RESTRICT
LOG:  statement: COMMIT TRANSACTION
LOG:  statement: START TRANSACTION ISOLATION LEVEL REPEATABLE READ
LOG:  execute pgsql_fdw_prep_7: INSERT INTO public.data1(c1, c2) VALUES ($1,
$2), ($3, $4), ($5, $6), ($7, $8)
DETAIL:  parameters: $1 = '1', $2 = 'data1', $3 = '2', $4 = 'data2', $5 = '3',
$6 = 'data3', $7 = '4', $8 = 'data4'
LOG:  statement: DEALLOCATE pgsql_fdw_prep_7
LOG:  statement: COMMIT TRANSACTION

Analyze_sampling option

The analyze_sampling option has been added to FOREIGN SERVER and FOREIGN TABLE. Previous versions fetched the entire foreign table record for statistics. The load on the remote database can be reduced by using the TABLESAMPLE clause when retrieving information from external tables. The default value is auto, which is assumed to be random for PostgreSQL < 9.5 and bernoulli for PostgreSQL 9.5 and higher. The values that can be set are as follows.

auto	version (default value)
random	Sampling is done using the random function.
system	Sampling is done using the TABLESAMPLE SYSTEM clause.
bernoulli	Sampling is done using the TABLESAMPLE BERNOULLI clause.

Table 37 Possible values for the analyze_sampling option

Value Description Off Do not sample.

PDF page 95

Example 111 Execute the ANALYZE statement

postgres=> CREATE FOREIGN TABLE foreign1(c1 INT, c2 VARCHAR(10)) SERVER remsvr1
            OPTIONS (analyze_sampling 'bernoulli', table_name 'data1') ;
CREATE FOREIGN TABLE
postgres=> ANALYZE VERBOSE foreign1 ;
INFO:  analyzing "public.foreign1"
INFO:  "foreign1": table contains 1000000 rows, 30000 rows in sample
ANALYZE

No sampling is performed if no ANALYZE statement is executed on the source table of the external
table.

Parallel_abort option

Postgres_fdw aborts remote transactions one at a time if it aborts the local transaction. Setting the option parallel_abort to ’true’ aborts remote transactions in parallel, improving performance. The default value for this option is ‘false’.