Skip to content

7 Contrib Modules

Changes to contrib extensions and the modules covered by the source document.
GASource pages: PDF 76-85Original PDF on GitHub

PDF page 76

Describes new features related to the Contrib modules.

3.5.1 Amcheck

The verify_heapam function has been added to check the structure of the table.

Example 90 Verify_heapam function
postgres=# CREATE EXTENSION amcheck ;
CREATE EXTENSION
postgres=# SELECT verify_heapam('data1') ;
 verify_heapam
---------------
(0 rows)

3.5.2 Btree_gist

All functions provided by the btree_gist module is now parallel-safe.

3.5.3 Cube

The cube type now supports binary I/O.

Example 91 Cube type binary access
postgres=> CREATE EXTENSION cube ;
CREATE EXTENSION
postgres=> SELECT typname, typreceive, typsend FROM pg_type
WHERE typname='cube' ;
 typname | typreceive |  typsend
---------+------------+-----------
 cube    | cube_recv  | cube_send
(1 row)

PDF page 77

3.5.4 Hstore

It is now possible to access columns of type hstore using subscripts.

Example 92 Access using subscripts
postgres=> CREATE EXTENSION hstore ;
CREATE EXTENSION
postgres=> CREATE TABLE hstore1 (id INT, attr hstore) ;
CREATE TABLE
postgres=>  INSERT INTO hstore1 VALUES (1, '"attr1"=>"val1",
"attr2"=>"val2", "attr3"=>"val3"') ;
INSERT 0 1
postgres=> SELECT id, attr['attr1'], attr->'attr2' FROM hstore1 ;
 id | attr | ?column?
----+------+----------
  1 | val1 | val2
(1 row)

3.5.5 Old_snapshot

This is a newly added Contrib module. This module defines the pg_old_snapshot_time_mapping function. When the parameter old_snapshot_threshold is set to something other than -1, the mapping between XIDs and timestamps can be displayed.

PDF page 78

Example 93 Pg_old_snapshot_time_mapping function
postgres=# SHOW old_snapshot_threshold ;
 old_snapshot_threshold
------------------------
 90min
(1 row)
postgres=# CREATE EXTENSION old_snapshot ;
CREATE EXTENSION
postgres=# SELECT * FROM pg_old_snapshot_time_mapping() ;
 array_offset |     end_timestamp      | newest_xmin
--------------+------------------------+-------------
            0 | 2021-10-01 10:13:00+09 |         533
            1 | 2021-10-01 10:14:00+09 |         534
            2 | 2021-10-01 10:15:00+09 |         535
            3 | 2021-10-01 10:16:00+09 |         535
(4 rows)

3.5.6 Pageinspect

The following functions for GiST indexes have been added.

Function nameDescription
gist_page_opaque_infoReturns information about the opaque area.
gist_page_itemsReturns the data in the page as record type.
gist_page_items_byteaReturns the data in the page in bytea type.
Table 28 Added functions

PDF page 79

Example 94 Information of GiST index
postgres=# SELECT * FROM
 gist_page_opaque_info(get_raw_page('idx1_gist1', 1)) ;
    lsn    |    nsn    | rightlink | flags
-----------+-----------+-----------+--------
 0/2216510 | 0/2216510 |       473 | {leaf}
(1 row)
postgres=# SELECT * FROM
 gist_page_items(get_raw_page('idx1_gist1', 1), 'idx1_gist1') ;
 itemoffset |    ctid     | itemlen |     keys
------------+-------------+---------+--------------
          1 | (457,65535) |     144 | (c2)=('' '')
          2 | (249,65535) |     144 | (c2)=('' '')
          3 | (487,65535) |     144 | (c2)=('' '')
…
Output of bt_metap function

Additional information “last cleanup num del pages” is now output.

Example 95 Output of bt_metap function
postgres=# SELECT * FROM bt_metap('idx1_data1') ;
-[ RECORD 1 ]-------------+-------
magic                     | 340322
version                   | 4
root                      | 290
level                     | 2
fastroot                  | 290
fastlevel                 | 2
last_cleanup_num_delpages | 0
last_cleanup_num_tuples   | -1
allequalimage             | t

PDF page 80

3.5.7 Passwordcheck

The reason is now output in the output of the check using the Cracklib library.

Example 96 Password check
postgres=# CREATE USER demo PASSWORD 'password123' ;
ERROR:  password is easily cracked
postgres=# \! tail -3 data/log/postgresql-2020-10-01_151319.log
ERROR:  password is easily cracked
DETAIL:  cracklib diagnostic: it is based on a dictionary word
STATEMENT:  CREATE USER demo PASSWORD 'password123';

3.5.8 Pgstattuple

The pgstattuple_approx function can now be executed on TOAST tables.

Example 97 Pgstattuple_approx function
postgres=#  SELECT pgstattuple_approx((SELECT reltoastrelid FROM
pg_class WHERE relname='data1')) ;
  pgstattuple_approx
-----------------------
 (0,0,0,0,0,0,0,0,0,0)
(1 row)

3.5.9 Pg_stat_statements

The following features have been extended to the pg_stat_statements module. When using pg_stat_statements, compute_query_id parameter must be specified as ‘on’ or ‘auto’.

Pg_stat_statements view

The following columns have been added.

Column nameData typeDescription
toplevelbooleanTrue if the query was executed as a top level.
Table 29 Added columns

PDF page 81

Track utility commands

Tracking is now available for REFRESH MATERIALIZED VIEW statement, CREATE TABLE AS statement, SELECT INTO statement, CREATE MATERIALIZED VIEW statement, etc.

Example 98 Tracking REFRESH MATERIALIZED VIEW statement
postgres=# REFRESH MATERIALIZED VIEW mview1 ;
REFRESH MATERIALIZED VIEW
postgres=#
postgres=# SELECT query, rows FROM pg_stat_statements WHERE
query LIKE 'REFRESH%';
              query               |  rows
----------------------------------+---------
 REFRESH MATERIALIZED VIEW mview1 | 1000000
(1 row)
Pg_stat_statements_info view

The pg_stat_statements_info view has been added. This view can be used to check the operational status of the pg_stat_statements module. Currently, the pg_stat_statements view only provides a dealloc column showing the number of SQL statements deleted and a stats_reset column showing the date and time the statistics were reset. The ‘dealloc’ column is used to validate the pg_stat_statements.max parameter.

Example 99 Pg_stat_statements_info view
postgres=# \d pg_stat_statements_info
       View "public.pg_stat_statements_info"
 Column      |  Type                    | Collation | Nullable | Default
-------------+--------------------------+-----------+----------+---------
 dealloc     | bigint                   |           |          |
 stats_reset | timestamp with time zone |           |          |

PDF page 82

3.5.10 Pg_trgm

Equality operators are now supported on GiST / GIN indexes.

Example 100 Index equation operator
postgres=> CREATE INDEX trgm_idx ON test_trgm
USING gist (t gist_trgm_ops) ;
CREATE INDEX
postgres=> EXPLAIN ANALYZE SELECT * FROM test_trgm
WHERE t='100000' ;
                              QUERY PLAN
-----------------------------------------------------------------
 Index Scan using trgm_idx on test_trgm  (cost=0.28..8.30 rows=1
width=5) (actual time=0.424..0.732 rows=1 loops=1)
   Index Cond: (t = '100000'::text)
   Rows Removed by Index Recheck: 2
 Planning Time: 0.038 ms
 Execution Time: 0.744 ms
(5 rows)

3.5.11 Pg_surgery

A new Contrib module, pg_surgery, has been added. It provides a function heap_force_freeze to force a tuple to freeze, and a function heap_force_kill to force it to be deleted.

PDF page 83

Example 101 Pg_surgery Contrib module
postgres=# SELECT xmin, ctid, c1 FROM data1 ;
 xmin | ctid  | c1
------+-------+-----
  517 | (0,1) | 100
(1 row)
postgres=# SELECT heap_force_freeze('data1'::regclass,
ARRAY['(0, 1)']::tid[]) ;
 heap_force_freeze
-------------------
(1 row)
postgres=# SELECT xmin, ctid, c1 FROM data1 ;
 xmin | ctid  | c1
------+-------+-----
    2 | (0,1) | 100
(1 row)
postgres=# SELECT heap_force_kill('data1'::regclass,
ARRAY['(0, 1)']::tid[]) ;
 heap_force_kill
-----------------
(1 row)
postgres=# SELECT xmin, ctid, c1 FROM data1 ;
 xmin | ctid | c1
------+------+----
(0 rows)

3.5.12 Postgres_fdw

The following enhancements have been implemented for the postgres_fdw module.

Functions

The following functions have been added to control the active session.

PDF page 84

Function nameDescription
postgres_fdw_disconnectDisconnect a specified active session.
postgres_fdw_disconnect_allDisconnect all active sessions.
postgres_fdw_get_connectionspostgres_fdw_get_connections Return all active sessions.
Table 30 Added functions
Example 102 Execution of postgres_fdw related functions
postgres=> CREATE FOREIGN TABLE remote1(c1 NUMERIC,
c2 VARCHAR(10)) SERVER remsvr1 ;
CREATE FOREIGN TABLE
postgres=> SELECT COUNT(*) FROM remote1 ;
  count
---------
 1000000
(1 row)
postgres=> SELECT * FROM postgres_fdw_get_connections() ;
 server_name | valid
-------------+-------
 remsvr1      | t
(1 row)
postgres=> SELECT postgres_fdw_disconnect_all() ;
 postgres_fdw_disconnect_all
-----------------------------
 t
(1 row)
TRUNCATE statement

The TRUNCATE statement can now be executed on an foreign table.

Supports Bulk Insert

In response to the addition of the Bulk Insert API to the Foreign Data Wrapper, batch_size can now be specified as an option for FOREIGN SERVER and FOREIGN TABLE that use postgres_fdw.

PDF page 85

Example 103 Batch_size option setting
postgres=> CREATE FOREIGN TABLE data1(c1 NUMERIC, c2 VARCHAR(10)) SERVER svr1
OPTIONS (batch_size '1000') ;
CREATE FOREIGN TABLE
postgres=> \d data1
                         Foreign table "public.data1"
 Column |         Type          | Collation | Nullable | Default | FDW options
--------+-----------------------+-----------+----------+---------+----------
 c1     | numeric               |           |          |         |
 c2     | character varying(10) |           |          |         |
Server: svr1
FDW options: (batch_size '1000')
The following interfaces have been added to the Foreign Data Wrapper for the bulk insert feature.
Interface nameDescription
ExecForeignBatchInsertExecution of batch transmission process.
GetForeignModifyBatchSizeAcquisition of batch size.
Table 31 Added Interface functions
keep_connections option

The option keep_connections controls remote connections after a transaction completes. The default value is ‘on’, which keeps the connection after the transaction is completed. Setting this option to ‘off’ closes the connection when the transaction completes.

IMPORT FOREIGN SCHEMA statement

Partitions can now be specified in the LIMIT TO and EXCEPT clauses.

Reconnect

If the session with the remote instance is found to be broken, it will now be reconnected.