PDF page 95
Describes new features related to the Contrib modules. #### 3.5.1 Amcheck {#pg17-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5ae2087202af9fd804c8b8d76954bdd8bdf31e51)] **Example 104.** Specify checkunique parameter ```text 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. ``` ```text The --checkunique option has been added to the pg_amcheck command to do the same. ``` **Example 105.** Specify \-\-checkunique option ```bash $ 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 {#pg17-3-5-2-pg-buffercache} The following functions have been added to the pg_buffercache module. [[13453ee](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=13453eedd3f692f8dcf8e334396eee84f00fdde2)] **Table 42.** Added function | 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 |PDF page 96
#### 3.5.3 Pg_stat_statements {#pg17-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 {#pg17-p96-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=13d00729d422c84b1764c24251abcc785ea4adb1), [5147ab1](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5147ab1dd34ad81c169c85fa3c7d11a782a1555b), [dc9f8a7](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=dc9f8a798307244d791b71f666f50de319799e7c), [5a3423a](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5a3423ad8ee171fbf08317917981effe47d211eb)] **Table 43.** Added columns | 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 44.** Removed column | 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 | ##### Constants for Execution Statements {#pg17-p96-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=31de7e60da34761365f94dc76cc2c1bf2172d1bc), [638d42a](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=638d42a3c52081cf4882520f0622161bad69b40f), [bb45156](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=bb45156f342c2cdb88cdd7f9efdc4541e910ec86), [11c34b3](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=11c34b342bd77a292b625c32b70b44c02db8f3ed)]PDF page 97
**Example 106.** Constant SAVEPOINT statement ```text 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 ```text 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 |PDF page 98
**Example 108.** Constant PREPARE statement and COMMIT statement ```text 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 |PDF page 99
**Example 109.** Reset min\/max values ```text 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 {#pg17-3-5-4-postgres-fdw} The following features have been added to the postgres_fdw module. ##### Increase tuple cost {#pg17-p99-increase-tuple-cost} The default cost per tuple to access a remote table has been changed from 0.01 to 0.2. [[cac169d](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=cac169d686eddb277880a0d8a760ac3007b4846a)] ##### De-parsing Semi-Join {#pg17-p99-de-parsing-semi-join} The EXISTS clause used between tables in the same FOREIGN SERVER can now be executed remotely. [[824dbea](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=824dbea3e41efa3b35094163c834988dea7eb139)]PDF page 100
**Example 110.** Execution Plan on PostgreSQL 17 ```text 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 ```text 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 {#pg17-3-5-5-ltree} The hash indexes are now accessible for match searches. [[485f0aa](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=485f0aa85995340fb62113448c992ee48dc6fff1)] **Example 112.** Using hash indexes for ltree types ```text 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 {#pg17-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=49cd2b93d7dbceefdf9a71cc301d284a2dd234c3), [f587338](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f587338dec87d3c35b025e131c5977930ac69077)] **Table 45.** Added functions | 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 | |PDF page 102
#### 3.5.7 Test_radixtree {#pg17-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](https://ieeexplore.ieee.org/document/6544812)). [[ee1b30f](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ee1b30f128d8f63a5184d2bcf1c48a3efc3fcbf9)] **Table 46.** Added function | Function name | Description | Note | | --- | --- | --- | | test_radixtree | Execute the Radix Tree test. | | **Example 113.** Using the test\_radixtree extension ```text 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 {#pg17-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=30e144287a72529c9cd9fd6b07fe96eb8a1e270e)]PDF page 103
**Table 47.** Added functions | 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 | | #### 3.5.9 Xid_wraparound {#pg17-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e255b646a16b45823c338dadf787813fc9e191dc)] **Table 48.** Added functions | 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. | | **Example 114.** Burn through lots of transaction ID ```text 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 {#pg17-3-5-10-miscellaneous} In addition to those described above, the following test extension modules have been added under the src/test/modules directory. **Table 49.** Added modules | Module Name | Description | | --- | --- | | test_dsa | Test module for Dynamic Shared Area (DSA). [[325f540](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=325f54033e59499cfc087932f68eec5356061640)] | | test_dsatest_dsm_registory | Test module for Dynamic Shared Area (DSA). [[325f540](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=325f54033e59499cfc087932f68eec5356061640)] Test module for Dynamic Shared Memory (DSM) registration. [[8b2bcf3](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=8b2bcf3f287c79eaebf724cba57e5ff664b01e06)] | | test_dsm_registorytest_json_parser | Test module for Dynamic Shared Memory (DSM) registration. [[8b2bcf3](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=8b2bcf3f287c79eaebf724cba57e5ff664b01e06)] Test module for JSON parsing. [[3311ea8](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3311ea86edc7a689614bad754e17371865cdc11f)] | | test_json_parsertest_resowner | Test module for JSON parsing. [[3311ea8](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3311ea86edc7a689614bad754e17371865cdc11f)] Test module for resource owner feature. [[b8bff07](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b8bff07daa85c837a2747b4d35cd5a27e73fb7b2)] |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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1adf16b8fba45f77056d91573cd7138ed9da4ebf)] - Feature to split partitions [[87c21bb](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=87c21bb9412c8ba2727dec5ebcd74d44c2232d11)] - Named NOT NULL constraints [[b0e96f3](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b0e96f311985bceba79825214f8e43f65afa653a)] - WITHOUT OVERLAPS clause for primary/foreign keys [[46a0cd4](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=46a0cd4cefb4d9b462d8cc4df5e7ecdd190bea92)] - Automatic backtrace output feature [[a740b21](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a740b213d4b4d3360ad0cac696e47e5ec0eb8864)] - Self-join elimination feature [[d3d55ce](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d3d55ce571369dad6e1d582f1655e5a3fbd8594a)] - Add stratnum GiST support function [[6db4598](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=6db4598fcb82a87a683c4572707e522504830a2b)] - pg_wal_replay_wait function [[06c418e](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=06c418e163e913966e17cb2d3fb1c5f8a8d58308)] - Transform OR clause to ANY expression [[72bd38c](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=72bd38cc99a15da6f97373fae98027c908c398ea)]