# Contrib Modules

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

---

LLMS index: [llms.txt](/en/llms.txt)

---

<div class="feature-source-note"><span class="feature-edition-badge">Beta 1</span><span><strong>Source pages:</strong> PDF 105-119</span><span><a href="https://github.com/nori-shinoda/documents/blob/1773200c5362476c4a549222b7d17fa91f546cae/postgresql19_beta1_new_features_en_20260606-1.pdf">Original PDF on GitHub</a></span></div>

<p class="feature-page-marker" id="pgfeat-19-modules-pdf-page-105">PDF page 105</p>

This section describes new features related to the Contrib module.

#### 3.5.1 Auto_explain {#pg19-3-5-1-auto-explain}

The following parameters have been added to the auto_explain module.

##### Parameter log_extension_options {#pg19-p105-parameter-log-extension-options}

The auto_explain.log_extension_options parameter has been added. This parameter specifies the string to be passed to the EXPLAIN statement in additional modules such as pg_overexplain.

**Example 125.** Specifying the log\_extension\_options option

```text
postgres=# LOAD 'auto_explain' ;
LOAD
postgres=# LOAD 'pg_overexplain' ;
LOAD
postgres=# SET auto_explain.log_extension_options = 'DEBUG' ;
SET
```

##### Parameter log_io {#pg19-p105-parameter-log-io}

The parameter auto_explain.log_io has been added. The default value is "off". If this parameter is set to "on", I/O statistics for executed SQL statements will be logged. This feature is effective only when the parameter auto_explain.log_analyze is set to "on". [[61c36a3](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=61c36a34a4196bfe1ce80b376e5979020bc571b2)]

##### Parameter log_extension_options {#pg19-p105-parameter-log-extension-options-2}

This includes additional options from external modules such as the pg_overexplain module and the pg_plan_adice module in the log.

<p class="feature-page-marker" id="pgfeat-19-modules-pdf-page-106">PDF page 106</p>

**Example 126.** Specifying the log\_io option

```text
postgres=# SET auto_explain.log_analyze = on ;
SET
postgres=# SET auto_explain.log_io = on ;
SET
postgres=# SELECT COUNT(*) FROM data1 ;
  count
----------
 10000000
(1 row)
postgres=# \! tail -6 data/log/postgresql.log
LOG:  duration: 341.638 ms  plan:
        Query Text: SELECT COUNT(*) FROM data1;
        Aggregate  (cost=179055.00..179055.01 rows=1 width=8) (actual time=…
          ->  Seq Scan on data1  (cost=0.00..154055.00 rows=10000000 width=…
                Prefetch: avg=46.37 max=91 capacity=94
                I/O: count=2397 waits=11 size=15.89 in-progress=4.27
```

#### 3.5.2 Basic_archive {#pg19-3-5-2-basic-archive}

The system no longer checks for the existence of the directory specified by the basic_archive.archive_directory parameter at startup. This change will be backported to PostgreSQL 15 and later. [[cc774c5](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=cc774c543b23e91175642a65559aae456f17d89e)]

#### 3.5.3 File_fdw {#pg19-3-5-3-file-fdw}

The "header" option, which specifies the number of header records in the data file, has been added. [[26cb14a](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=26cb14aea12a0f0c2f9a49de3865721936b711a7)]

<p class="feature-page-marker" id="pgfeat-19-modules-pdf-page-107">PDF page 107</p>

**Example 127.** Reading the CSV file with a two\-line header

```text
postgres=# \! cat /tmp/multi1.csv
HEADER1-1,HEADER1-2
HEADER2-1,HEADER2-2
100,data1
200,data2
postgres=# CREATE EXTENSION file_fdw ;
CREATE EXTENSION
postgres=# CREATE SERVER filesvr1 FOREIGN DATA WRAPPER file_fdw ;
CREATE SERVER
postgres=# CREATE FOREIGN TABLE multi1(col1 INT, col2 VARCHAR(10)) SERVER
filesvr1 OPTIONS (filename '/tmp/multi1.csv', HEADER '2', format 'csv') ;
CREATE FOREIGN TABLE
postgres=# SELECT * FROM multi1 ;
 col1 | col2
------+-------
  100 | data1
  200 | data2
(2 rows)
```

#### 3.5.4 Injection_points {#pg19-3-5-4-injection-points}

The following new features have been added to the injection_points module.

##### Function injection_points_attach {#pg19-p107-function-injection-points-attach}

The injection_points_attach function has been updated to include additional arguments. [[16a2f70](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=16a2f706951edc1b284a66902c9e582217d37d31)]

##### Function injection_points_list {#pg19-p107-function-injection-points-list}

The injection_points_list function, which returns a list of injection points, has been added. [[4eca711](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=4eca711bc991954613261b7a314b1e8f5963815c)]

##### Removed Functions {#pg19-p107-removed-functions}

The following functions related to statistics information have been removed. [[d52c24b](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d52c24b0f808a6644fd839c507625eafd6bb9f12)]

<p class="feature-page-marker" id="pgfeat-19-modules-pdf-page-108">PDF page 108</p>

- injection_points_stats_numcalls

- injection_points_stats_drop

- injection_points_stats_fixed

#### 3.5.5 Pg_buffercache {#pg19-3-5-5-pg-buffercache}

The following new features have been implemented in the pg_buffercache module.

##### Add Views {#pg19-p108-add-views}

The following views have been added. [[4b203d4](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=4b203d499c610160e9867e6add2366780429344c)]

**Table 37.** Added Views

| View name | Description |
| --- | --- |
| pg_buffercache_os_pages | Mapping between the buffer pool and OS pages |
| pg_buffercache_numa | Mapping of the buffer pool and NUMA nodes |

##### Add Functions {#pg19-p108-add-functions}

The following functions have been added. [[9ccc049](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=9ccc049dfe655ca9927f7c62559ec32f4d1f94dd;hp=d167c19295da46be1998dd474841f94170e32597), [4b203d4](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=4b203d499c610160e9867e6add2366780429344c)]

**Table 38.** Added Functions

| Function name | Description |
| --- | --- |
| pg_buffercache_os_pages | Provides a mapping between the buffer pool and OS pages |
| pg_buffercache_mark_dirty | Sets the specified buffer to the dirty state |
| pg_buffercache_mark_dirty_relation | Sets the buffers for the specified relation in the buffer pool to the dirty state |

pg_buffercache_mark_dirty_all Sets all buffers in the buffer pool to the dirty state

**Example 128.** Executing of the pg\_buffercache\_mark\_dirty\_all function

```text
postgres=# SELECT * FROM pg_buffercache_mark_dirty_all() ;
-[ RECORD 1 ]---------+------
buffers_dirtied       | 5527
buffers_already_dirty | 10857
buffers_skipped       | 0
```

<p class="feature-page-marker" id="pgfeat-19-modules-pdf-page-109">PDF page 109</p>

#### 3.5.6 Pg_plan_advice {#pg19-3-5-6-pg-plan-advice}

The new Contrib module pg_plan_advice has been added to PostgreSQL 19. This module provides features for changing execution plans and verifying the modified execution plans. [[5883ff3](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5883ff30b02ceed3c5eabba4d9c09a7766f9a8fc), [6455e55](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=6455e55b0da47255f332a96f005ba0dd1c7176c2), [26255a3](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=26255a320733de2d91a30bd6ae529dd01e7f3409)]

##### Basic Usage {#pg19-p109-basic-usage}

To use this module, follow the steps below. 1. Specify pg_plan_advice in the LOAD statement or in the shared_preload_libraries parameter. 2. Verify the execution plan using an EXPLAIN statement with the PLAN_ADICE option. 3. Information for changing the execution plan is output in the "Generated Plan Advice:" section. 4. Specify information regarding changes to the execution plan in the pg_plan_advice.advice parameter. 5. The execution plan will be changed. In the following example, the join order of tables data1 and data2 is changed to verify that the execution plan changes.

<p class="feature-page-marker" id="pgfeat-19-modules-pdf-page-110">PDF page 110</p>

**Example 129.** Verifying the Method for Modifying the Execution Plan

```text
postgres=# LOAD 'pg_plan_advice' ;
LOAD
postgres=# EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM data1 AS d1
INNER JOIN data2 AS d2 ON d1.col1 = d2.col1 WHERE d1.col1 < 1000 ;
                       QUERY PLAN
---------------------------------------------------------
 Nested Loop
   ->  Index Scan using data1_pkey on data1 d1
         Index Cond: (col1 < 1000)
   ->  Index Scan using data2_pkey on data2 d2
         Index Cond: (col1 = d1.col1)
 Generated Plan Advice:
   JOIN_ORDER(d1 d2)
   NESTED_LOOP_PLAIN(d2)
   INDEX_SCAN(d1 public.data1_pkey d2 public.data2_pkey)
   NO_GATHER(d1 d2)
(10 rows)
```

<p class="feature-page-marker" id="pgfeat-19-modules-pdf-page-111">PDF page 111</p>

**Example 130.** Configure Execution Plan Changes

```text
postgres=# SET pg_plan_advice.advice = 'JOIN_ORDER(d2 d1)' ;
SET
postgres=# EXPLAIN (COSTS OFF) SELECT * FROM data1 AS d1 INNER JOIN data2 AS
d2 ON d1.col1 = d2.col1 WHERE d1.col1 < 1000 ;
                        QUERY PLAN
-----------------------------------------------------------
 Gather
   Workers Planned: 2
   ->  Hash Join
         Hash Cond: (d2.col1 = d1.col1)
         ->  Parallel Seq Scan on data2 d2
         ->  Hash
               ->  Index Scan using data1_pkey on data1 d1
                     Index Cond: (col1 < 1000)
 Supplied Plan Advice:
   JOIN_ORDER(d2 d1) /* matched */
(10 rows)
```

```text
Specify the changes to the execution plan in the pg_plan_advice.advice parameter. The major settings
are listed below.
```

**Table 39.** Typical \&quot\;advice\&quot\; parameter settings

| Setting value | Description |
| --- | --- |
| SEQ_SCAN(target) | Use the sequential scan (Seq Scan) |
| INDEX_SCAN(target) | Use the specified index |
| JOIN_ORDER(item item…) | Specify the join order |
| HASH_JOIN(target) | Execute the hash join |
| NESTED_LOOP_PLAN(target) | Execute the nested loop join |
| GATHER(item) | Specify the parallel query |
| NO_GATHER(item) | Do not use a parallel query |
| DO_NOT_SCAN(item) | Stop scanning the specified relation |

<p class="feature-page-marker" id="pgfeat-19-modules-pdf-page-112">PDF page 112</p>

##### Parameters {#pg19-p112-parameters}

The pg_plan_advice module provides the following custom parameters.

**Table 40.** 追加されたパラメーター

| Parameter name | Description | Default value |
| --- | --- | --- |
| advice | Advice on changing the execution plan | '' |
| always_explain_supplied_advice | Always include output indicating which child has had its execution plan changed in the results of the EXPLAIN statement | on |
| always_store_advice_details | Whether to output feedback for prepared statements | off |
| feedback_warnings | Whether to issue a warning if the advice was not successfully applied | off |
| trace_mask | Whether to output debug traces | off |

If the always_explain_supplied_advice parameter is set to "on" (the default value), the EXPLAIN statement will always output information about any changes to the execution plan (under the heading "Supplied Plan Advice:"). If this parameter is set to "off", specifying the PLAN_ADVICE option in the EXPLAIN statement is required to output information about changes to the execution plan.

#### 3.5.7 Pg_stash_advice {#pg19-3-5-7-pg-stash-advice}

The new Contrib module pg_stash_advice has been added to PostgreSQL 19. The pg_stash_advice module applies execution plan advice to query IDs. By default, the generated advice is stored in memory and is not persisted. To persist the advice stash data, set the pg_stash_advice.persist parameter to "true". The persisted data is created as the file pg_stash_advice.tsv in the database cluster. [[e8ec19a](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e8ec19aa321abc89fb4fd277c994f14680ba17db), [c10edb1](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c10edb102ada607eb054bc9e23690109d86849ef)]

**Table 41.** Added Functions

| Function name | Description | Note |
| --- | --- | --- |
| pg_create_advice_stash | Creating the Advice Stash |  |
| pg_drop_advice_stash | Dropping the Advice Stash |  |
| pg_set_stashed_advice | Setting advice for a query |  |
| pg_get_advice_stashes | List of Advice Stashes |  |
| pg_get_advice_stash_contents | Viewing the contents of the Advice Stash |  |

<p class="feature-page-marker" id="pgfeat-19-modules-pdf-page-113">PDF page 113</p>

The following parameters are defined in this module.

**Table 42.** Added Parameters

| Parameter name | Description | Default value |
| --- | --- | --- |
| pg_stash_advice.persist | Whether to persist advice | false |
| pg_stash_advice.stash_name | Name of the active advice stash | '' |
| pg_stash_advice.persist_interval | Persistence check interval | 30s |

##### Preparation {#pg19-p113-preparation}

Set the shared_preload_libraries parameter to pg_stash_advice. Set the compute_query_id parameter to "auto" (default value) or "on". Next, enable the module using the CREATE EXTENSION statement.

**Example 131.** Preparing pg\_stash\_advice module

```text
postgres=# SHOW shared_preload_libraries ;
 shared_preload_libraries
--------------------------
 pg_stash_advice
(1 row)
postgres=# SHOW compute_query_id ;
 compute_query_id
------------------
 on
(1 row)
postgres=# CREATE EXTENSION pg_stash_advice ;
CREATE EXTENSION
```

```sql
Create the advice stash. Specify the name for the pg_create_advice_stash function, and set the name
of the advice just created to the pg_advice_stash.stash_name parameter.
```

<p class="feature-page-marker" id="pgfeat-19-modules-pdf-page-114">PDF page 114</p>

**Example 132.** Creating and Configuring a Advice Stash

```text
postgres=# SELECT pg_create_advice_stash('stash1') ;
pg_create_advice_stash
------------------------
(1 row)
postgres=# SET pg_stash_advice.stash_name = 'stash1' ;
SET
```

```text
Identify the query ID and execution plan of the query for which the execution plan needs to be
changed.
```

**Example 133.** Checking the Query ID

```text
postgres=# EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM data1 AS d1 INNER
 JOIN data2 AS d2 ON d1.col1 = d2.col1 WHERE d1.col1 = 10000 ;
                      QUERY PLAN
------------------------------------------------------
 Nested Loop
   Output: d1.col1, d1.col2, d2.col1, d2.col2
   ->  Index Scan using data1_pkey on public.data1 d1
         Output: d1.col1, d1.col2
         Index Cond: (d1.col1 = 10000)
   ->  Index Scan using data2_pkey on public.data2 d2
         Output: d2.col1, d2.col2
         Index Cond: (d2.col1 = 10000)
 Query Identifier: 1524396781679940627
(9 rows)
```

```text
Specify the advice to change the execution plan for the query ID. In the following example, the join
order is changed. For instructions on changing the execution plan, refer to the pg_plan_advice Contrib
module.
```

<p class="feature-page-marker" id="pgfeat-19-modules-pdf-page-115">PDF page 115</p>

**Example 134.** Specify advice

```text
postgres=#  SELECT pg_set_stashed_advice('stash1', 1524396781679940627,
'JOIN_ORDER(d2 d1)') ;
 pg_set_stashed_advice
-----------------------
(1 row)
```

```text
Check the execution plan. The "Supplied Plan Advice" entry is output, indicating that advice for
changing the execution plan has been specified.
```

**Example 135.** Check the Execution Plan

```text
postgres=# EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM data1 AS d1 INNER JOIN
data2 AS d2 ON d1.col1 = d2.col1 WHERE d1.col1 = 10000 ;
                      QUERY PLAN
------------------------------------------------------
 Nested Loop
   Output: d1.col1, d1.col2, d2.col1, d2.col2
   ->  Index Scan using data2_pkey on public.data2 d2
         Output: d2.col1, d2.col2
         Index Cond: (d2.col1 = 10000)
   ->  Index Scan using data1_pkey on public.data1 d1
         Output: d1.col1, d1.col2
         Index Cond: (d1.col1 = 10000)
 Query Identifier: 1524396781679940627
 Supplied Plan Advice:
   JOIN_ORDER(d2 d1) /* matched */
(11 rows)
```

#### 3.5.8 Pg_stat_statements {#pg19-3-5-8-pg-stat-statements}

The following new features have been added to the pg_stat_statements module.

<p class="feature-page-marker" id="pgfeat-19-modules-pdf-page-116">PDF page 116</p>

##### Changes in the pg_stat_statements view {#pg19-p116-changes-in-the-pg-stat-statements-view}

The following columns have been added to the pg_stat_statements view. [[3357471](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=3357471cf9f5e470dfed0c7919bcf31c7efaf2b9;hp=e125e360020a7b0affd5bea938b749e85d8999d3)]

**Table 43.** Added Columns

| Column name | Data type | Description |
| --- | --- | --- |
| generic_plan_calls | bigint | Number of executions of the generic execution plan |
| custom_plan_calls | bigint | Number of executions of the custom execution plan |

**Example 136.** Number of Execution Plans Executed

```text
postgres=> SELECT query, generic_plan_calls, custom_plan_calls FROM
pg_stat_statements WHERE query LIKE 'PREPARE%' ;
-[ RECORD 1 ]------+-----------------------------------
query              | PREPARE prepare1(int) AS SELECT $1
generic_plan_calls | 4
custom_plan_calls  | 4
```

##### Constancy of FETCH Statements {#pg19-p116-constancy-of-fetch-statements}

The number of rows specified in the FETCH statement is now treated as the constant and assigned the single query ID. [[bee23ea](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=bee23ea4ddc46198c95a4e73a83f453c09e04bf8)]

<p class="feature-page-marker" id="pgfeat-19-modules-pdf-page-117">PDF page 117</p>

**Example 137.** Constancy of FETCH Statements

```text
postgres=> BEGIN ;
BEGIN
postgres=*> DECLARE c1 CURSOR FOR SELECT * FROM data1 ;
DECLARE CURSOR
postgres=*> FETCH 1 FROM c1 ;
 c1 |  c2
----+-------
  1 | data1
(1 row)
postgres=*> FETCH 2 FROM c1 ;
 c1 |  c2
----+-------
  2 | data2
  3 | data3
(2 rows)
postgres=*> SELECT query, calls FROM pg_stat_statements WHERE query LIKE
'FETCH%' ;
      query       | calls
------------------+-------
 FETCH $1 FROM c1 |     2
(1 row)
```

#### 3.5.9 Postgres_fdw {#pg19-3-5-9-postgres-fdw}

The following features have been added to the postgres_fdw module.

##### Option restore_stats {#pg19-p117-option-restore-stats}

The Foreign Data Wrapper now supports importing remote statistics via the callback. The postgres_fdw module implements this callback, and the restore_stats option can be specified for both the FOREIGN SERVER and the FOREIGN TABLE. [[28972b6](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=28972b6fc3dcd1296e844246b635eddfa29c38e1)]

<p class="feature-page-marker" id="pgfeat-19-modules-pdf-page-118">PDF page 118</p>

**Example 138.** Specifying the restore\_stats option

```text
postgres=> CREATE FOREIGN TABLE data1(col1 INT, col2 VARCHAR(10)) SERVER dbsvr1
OPTIONS (restore_stats 'true') ;
CREATE FOREIGN TABLE
postgres=> ANALYZE (VERBOSE) data1 ;
INFO:  importing statistics for foreign table "public.data1"
INFO:  finished importing statistics for foreign table "public.data1"
ANALYZE
```

##### Pushdown of ARRAY Operations {#pg19-p118-pushdown-of-array-operations}

When using a generic plan, the ANY clause in the PREPARE statement can now be pushed down. [[62c3b4c](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=62c3b4cd9ddc6d3066e3f6e43b68fd00c620d9ad)]

**Example 139.** SQL statement to execute

```text
postgres=> SET plan_cache_mode = force_generic_plan ;
SET
postgres=> PREPARE array_stmt(VARCHAR[]) AS SELECT * FROM data1 WHERE
col2 = ANY($1) ;
PREPARE
postgres=> EXPLAIN (VERBOSE, COSTS OFF) EXECUTE
array_stmt(ARRAY['data1', 'data2']) ;
```

**Example 140.** PostgreSQL 18 Execution Plan

```text
postgres=> EXPLAIN (VERBOSE, COSTS OFF) EXECUTE
array_stmt(ARRAY['data1', 'data2']) ;
                     QUERY PLAN
-----------------------------------------------------
 Foreign Scan on public.data1
   Output: col1, col2
   Filter: ((data1.col2)::text = ANY (($1)::text[]))
   Remote SQL: SELECT col1, col2 FROM public.data1
(4 rows)
```

<p class="feature-page-marker" id="pgfeat-19-modules-pdf-page-119">PDF page 119</p>

**Example 141.** PostgreSQL 19 Execution Plan

```text
postgres=> EXPLAIN (VERBOSE, COSTS OFF) EXECUTE
array_stmt(ARRAY['data1', 'data2']) ;
                                            QUERY PLAN
----------------------------------------------------------------------------…
 Foreign Scan on public.data1
   Output: col1, col2
   Remote SQL: SELECT col1, col2 FROM public.data1 WHERE ((col2 = ANY
($1::character varying[])))
(3 rows)
```

##### Transaction State Propagation {#pg19-p119-transaction-state-propagation}

The status of local READ ONLY and DEFERRABLE transactions is now pushed down to remote connections. [[de28140](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=de28140ded8d4ba00faf905ec3530ffeb8a34a53)]

#### 3.5.10 Miscellaneous {#pg19-3-5-10-miscellaneous}

In addition to what was described above, the following test extension modules have been added to the src/test/modules directory. [[31280d9](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=31280d96a64850f5a9a924088890ab43a2905237), [e0e4c13](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e0e4c132ef2b59f42b9d01872c69607983f3ef31)]

**Table 44.** Added extension modules

| Module name | Description | Note |
| --- | --- | --- |
| test_binaryheaptest_bitmapsettest_checksums | Binary heap testing Bitmapset testing Checksum behavior testing |  |
| test_cplusplusext | C++ header compilation testing |  |
| test_custom_stats | Fixed- and variable-size custom statistics testing |  |
| test_custom_types | Custom type testing |  |
| test_int128 | 128-bit int type testing |  |
| test_lwlock_tranchestest_plan_advicetest_saslpreptest_shmem | LWLock Tranche testing pg_plan_advice module testing SASLprep implementation testing Shared memory testing |  |