7 Contrib Modules

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

GASource pages: PDF 95-105Original PDF on GitHub

PDF page 95

Describes new features related to the Contrib modules.

3.5.1 Amcheck

The bt_index_check function now has a checkunique parameter to check the integrity of unique constraints. The following example shows the error when corruption is found in the index data1_pkey for the primary key. [5ae2087]

Example 104 Specify checkunique parameter

postgres=# SELECT bt_index_check('data1_pkey', true, true) ;
ERROR:  index uniqueness is violated for index "data1_pkey"
DETAIL:  Index tid=(1,84) and tid=(1,85) (point to heap tid=(0,84) and
tid=(0,85)) page lsn=0/1573AB8.

The --checkunique option has been added to the pg_amcheck command to do the same.

Example 105 Specify --checkunique option

$ pg_amcheck --checkunique -d postgres
btree index "postgres.public.data1_pkey":
    ERROR:  index uniqueness is violated for index "data1_pkey"
    DETAIL:  Index tid=(1,84) and tid=(1,85) (point to heap tid=(0,84) and
tid=(0,85)) page lsn=0/1573AB8.

3.5.2 Pg_buffercache

The following functions have been added to the pg_buffercache module. [13453ee]

Function name	Description	Note
pg_buffercache_evict Remove any block from the buffer pool.	pg_buffercache_evict Remove any block from the buffer pool.	SUPERUSER only

Table 42 Added function

PDF page 96

3.5.3 Pg_stat_statements

The following features have been added to the pg_stat_statements module.

Enhancements of the pg_stat_statements view

The pg_stat_statements view has been modified with I/O-related columns and JIT-related and timestamp-related columns added. [13d0072, 5147ab1, dc9f8a7, 5a3423a]

Column Name	Data Type	Description
local_blk_read_time	double precision	Local block read time
local_blk_write_time	double precision	Local block write time
shared_blk_read_time	double precision	Shared block read time
shared_blk_write_time double precision	shared_blk_write_time double precision	Shared block write time
jit_deform_count	bigint	JIT deform processing count
jit_deform_time	double precision	JIT deform processing time
stats_since	timestamp with time zone	Statement statistics collection time
minmax_stats_since	timestamp with time zone	Statement min/max statistics collection time

Table 43 Added columns

Column Name	Description
blk_read_time	Split into shared_blk_read_time and local_blk_read_time
blk_write_time	Split into shared_blk_write_time and local_blk_write_time

Table 44 Removed column

Constants for Execution Statements

The SAVEPOINT, ROLLBACK TO, RELEASE, COMMIT PREPARED, PREPARE TRANSACTION, ROLLBACK PREPARED, DEALLOCATE, and overloaded CALL statements are saved in the pg_stat_statements table as constants with parameter symbols. Previously, statements with different specified names were saved separately. [31de7e6, 638d42a, bb45156, 11c34b3]

PDF page 97

Example 106 Constant SAVEPOINT statement

postgres=> BEGIN ;
BEGIN
postgres=*> SAVEPOINT sp1 ;
SAVEPOINT
postgres=*> SAVEPOINT sp2 ;
SAVEPOINT
postgres=*> SAVEPOINT sp3 ;
SAVEPOINT
postgres=*> COMMIT ;
COMMIT
postgres=> SELECT calls, rows, query FROM pg_stat_statements WHERE
query LIKE 'SAVEPOINT%' ;
 calls | rows |    query
-------+------+--------------
     3 |    0 | SAVEPOINT $1
(1 row)

Example 107 Constant CALL statement

postgres=> CALL overload(1) ;
CALL
postgres=> CALL overload('A') ;
CALL
postgres=> CALL in_out(1, NULL, 1) ;
CALL
postgres=> CALL in_out(2, 1, 2) ;
CALL
postgres=> SELECT calls, rows, query FROM pg_stat_statements ;
 calls | rows |          query
-------+------+--------------------------
     1 |    1 | <insufficient privilege>
     1 |    0 | CALL overload($1)
     1 |    0 | CALL overload($1)
     2 |    0 | CALL in_out($1, $2, $3)
(4 rows)

PDF page 98

Example 108 Constant PREPARE statement and COMMIT statement

postgres=> BEGIN ;
BEGIN
postgres=*> PREPARE TRANSACTION 'Transaction#1' ;
PREPARE TRANSACTION
postgres=> COMMIT PREPARED 'Transaction#1' ;
COMMIT PREPARED
postgres=> BEGIN ;
BEGIN
postgres=*> PREPARE TRANSACTION 'Transaction#2' ;
PREPARE TRANSACTION
postgres=> COMMIT PREPARED 'Transaction#2' ;
COMMIT PREPARED
postgres=> SELECT calls, query FROM pg_stat_statements ORDER BY query ;
 calls |          query
-------+--------------------------
     1 | <insufficient privilege>
     2 | BEGIN
     2 | COMMIT PREPARED $1
     2 | PREPARE TRANSACTION $1
(4 rows)

Pg_stat_statements_reset function

The parameter minmax_only has been added to the pg_stat_statements_reset function. Setting this parameter to true will reset the maximum/minimum value (min_plan_time column, min_exec_time column, etc.) information. [43cbeda]

PDF page 99

Example 109 Reset min/max values

postgres=# SELECT query, calls, min_exec_time FROM pg_stat_statements
WHERE queryid=228406429875636958 ;
           query            | calls | min_exec_time
----------------------------+-------+---------------
 SELECT COUNT(*) FROM data1 |     2 |        20.938
(1 row)
postgres=# SELECT pg_stat_statements_reset(0, 0, 228406429875636958, true) ;
   pg_stat_statements_reset
-------------------------------
 2024-09-27 15:35:14.552288+09
(1 row)
postgres=# SELECT query, calls, min_exec_time FROM pg_stat_statements
WHERE queryid=228406429875636958 ;
           query            | calls | min_exec_time
----------------------------+-------+---------------
 SELECT COUNT(*) FROM data1 |     2 |             0
(1 row)

3.5.4 Postgres_fdw

The following features have been added to the postgres_fdw module.

Increase tuple cost

The default cost per tuple to access a remote table has been changed from 0.01 to 0.2. [cac169d]

De-parsing Semi-Join

The EXISTS clause used between tables in the same FOREIGN SERVER can now be executed remotely. [824dbea]

PDF page 100

Example 110 Execution Plan on PostgreSQL 17

postgres=> EXPLAIN (VERBOSE, COSTS OFF) SELECT COUNT(*) FROM data1 d1
WHERE EXISTS (SELECT * FROM data2 d2 WHERE d1.c1 = d2.c1) ;
                               QUERY PLAN
------------------------------------------------------------------------------
 Foreign Scan
   Output: (count(*))
   Relations: Aggregate on ((public.data1 d1) SEMI JOIN (public.data2 d2))
   Remote SQL: SELECT count(*) FROM public.data1 r1 WHERE EXISTS (SELECT NULL
FROM public.data2 r2 WHERE ((r1.c1 = r2.c1)))
Query Identifier: -2590308894092843219
(5 rows)

Example 111 Execution Plan on PostgreSQL 16

postgres=> EXPLAIN (VERBOSE, COSTS OFF) SELECT COUNT(*) FROM data1 d1
WHERE EXISTS (SELECT * FROM data2 d2 WHERE d1.c1 = d2.c1) ;
                         QUERY PLAN
-------------------------------------------------------------
 Aggregate
   Output: count(*)
   ->  Hash Semi Join
         Hash Cond: (d1.c1 = d2.c1)
         ->  Foreign Scan on public.data1 d1
               Output: d1.c1
               Remote SQL: SELECT c1 FROM public.data1
         ->  Hash
               Output: d2.c1
               ->  Foreign Scan on public.data2 d2
                     Output: d2.c1
                     Remote SQL: SELECT c1 FROM public.data2
(12 rows)

PDF page 101

3.5.5 Ltree

The hash indexes are now accessible for match searches. [485f0aa]

Example 112 Using hash indexes for ltree types

postgres=> \d ltree1
              Table "public.ltree1"
 Column | Type  | Collation | Nullable | Default
--------+-------+-----------+----------+---------
 path   | ltree |           |          |
Indexes:
    "path_hash_idx" hash (path)
postgres=> EXPLAIN (COSTS OFF)
SELECT * FROM ltree1 WHERE path='Top.Hobbies.Amateurs_Astronomy' ;
                           QUERY PLAN
----------------------------------------------------------------
 Index Scan using path_hash_idx on ltree1
   Index Cond: (path = 'Top.Hobbies.Amateurs_Astronomy'::ltree)
(2 rows)

3.5.6 Injection_points

The extension module injection_points has been added to the src/test/modules/injection_points directory. This module provides the infrastructure to utilize basic injection points. The injection_points module provides the following functions. [49cd2b9, f587338]

Function Name	Description	Note
injection_points_attach	Create Injection Point
injection_points_detach	Delete Injection Point
injection_points_run	Execute Injection Point
injection_points_set_local	Limit injection point execution to the current process	Limit injection point execution to the current process
injection_points_wakeup	Wakeup Injection Point

Table 45 Added functions

PDF page 102

3.5.7 Test_radixtree

The extension test_radixtree has been added to the directory src/test/modules/test_radixtree. This extension is a test implementation of the paper “The Adaptive Radix Tree: ARTful Indexing for Main- Memory Databases” published by Viktor Leis, Alfons Kemper and Thomas Neumann in 2013 (https://ieeexplore.ieee.org/document/6544812). [ee1b30f]

Function name	Description	Note
test_radixtree	Execute the Radix Tree test.

Table 46 Added function

Example 113 Using the test_radixtree extension

postgres=# CREATE EXTENSION test_radixtree ;
CREATE EXTENSION
postgres=# SELECT test_radixtree() ;
NOTICE:  testing node node-4 with shift 0 and ascending keys
NOTICE:  testing node node-4 with shift 0 and descending keys
NOTICE:  testing node node-4 with shift 8 and ascending keys
NOTICE:  testing node node-4 with shift 8 and descending keys
NOTICE:  testing node node-4 with shift 56 and ascending keys
NOTICE:  testing node node-4 with shift 56 and descending keys
NOTICE:  testing node node-16-lo with shift 0 and ascending keys
…
NOTICE:  testing node node-256 with shift 56 and ascending keys
NOTICE:  testing node node-256 with shift 56 and descending keys
 test_radixtree
----------------
(1 row)

3.5.8 Test_tidstore

The extension module test_tidstore has been added to the src/test/modules/test_tidstore directory. The test_tidstore module is a test module for efficiently storing large sets of TIDs. [30e1442]

PDF page 103

Function Name	Description	Note
test_create	Creating a test
check_set_block_offsetstest_is_fulltest_destroy	Validate TID in store against array Check for memory overflow Discarding the test
do_set_block_offset	Set block offset value

Table 47 Added functions

3.5.9 Xid_wraparound

The extension module xid_wraparound has been added to the src/test/modules/xid_wraparound directory. This module provides functions to quickly burn through lots of transaction IDs. [e255b64]

Function Name	Description	Note
consume_xids	To proceed to the specified value for the transaction ID.
consume_xids_until	To Proceed to the specified value of Transaction ID.

Table 48 Added functions

Example 114 Burn through lots of transaction ID

postgres=# CREATE EXTENSION xid_wraparound ;
CREATE EXTENSION
postgres=# SELECT txid_current() ;
 txid_current
--------------
          753
(1 row)
postgres=# SELECT consume_xids('100000000') ;
NOTICE:  consumed 10000055 / 100000000 XIDs, latest 0:10000809
NOTICE:  consumed 20000864 / 100000000 XIDs, latest 0:20001618
…
NOTICE:  consumed 90004750 / 100000000 XIDs, latest 0:90005504
 consume_xids
--------------
    100000754
(1 row)

PDF page 104

3.5.10 Miscellaneous

In addition to those described above, the following test extension modules have been added under the src/test/modules directory.

Module Name	Description
test_dsa	Test module for Dynamic Shared Area (DSA). [325f540]
test_dsatest_dsm_registory	Test module for Dynamic Shared Area (DSA). [325f540] Test module for Dynamic Shared Memory (DSM) registration. [8b2bcf3]
test_dsm_registorytest_json_parser	Test module for Dynamic Shared Memory (DSM) registration. [8b2bcf3] Test module for JSON parsing. [3311ea8]
test_json_parsertest_resowner	Test module for JSON parsing. [3311ea8] Test module for resource owner feature. [b8bff07]

Table 49 Added modules

PDF page 105

Reverted New Features This section describes major new features that were once committed but not adopted after the beta version. These features may be implemented in the next version.

Merge feature for multiple partitions [1adf16b]
Feature to split partitions [87c21bb]
Named NOT NULL constraints [b0e96f3]
WITHOUT OVERLAPS clause for primary/foreign keys [46a0cd4]
Automatic backtrace output feature [a740b21]
Self-join elimination feature [d3d55ce]
Add stratnum GiST support function [6db4598]
pg_wal_replay_wait function [06c418e]
Transform OR clause to ANY expression [72bd38c]