PDF page 76
Describes new features related to the Contrib modules. #### 3.5.1 Amcheck {#pg14-3-5-1-amcheck} The verify_heapam function has been added to check the structure of the table. **Example 90.** Verify\_heapam function ```text postgres=# CREATE EXTENSION amcheck ; CREATE EXTENSION postgres=# SELECT verify_heapam('data1') ; verify_heapam --------------- (0 rows) ``` #### 3.5.2 Btree_gist {#pg14-3-5-2-btree-gist} All functions provided by the btree_gist module is now parallel-safe. #### 3.5.3 Cube {#pg14-3-5-3-cube} The cube type now supports binary I/O. **Example 91.** Cube type binary access ```text 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 {#pg14-3-5-4-hstore} It is now possible to access columns of type hstore using subscripts. **Example 92.** Access using subscripts ```text 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 {#pg14-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 ```text 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 {#pg14-3-5-6-pageinspect} The following functions for GiST indexes have been added. **Table 28.** Added functions | Function name | Description | | --- | --- | | gist_page_opaque_info | Returns information about the opaque area. | | gist_page_items | Returns the data in the page as record type. | | gist_page_items_bytea | Returns the data in the page in bytea type. |PDF page 79
**Example 94.** Information of GiST index ```text 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 {#pg14-p79-output-of-bt-metap-function} Additional information "last cleanup num del pages" is now output. **Example 95.** Output of bt\_metap function ```text 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 {#pg14-3-5-7-passwordcheck} The reason is now output in the output of the check using the Cracklib library. **Example 96.** Password check ```text 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 {#pg14-3-5-8-pgstattuple} The pgstattuple_approx function can now be executed on TOAST tables. **Example 97.** Pgstattuple\_approx function ```text 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 {#pg14-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 {#pg14-p80-pg-stat-statements-view} The following columns have been added. **Table 29.** Added columns | Column name | Data type | Description | | --- | --- | --- | | toplevel | boolean | True if the query was executed as a top level. |PDF page 81
##### Track utility commands {#pg14-p81-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 ```text 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 {#pg14-p81-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 ```text 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 {#pg14-3-5-10-pg-trgm} Equality operators are now supported on GiST / GIN indexes. **Example 100.** Index equation operator ```text 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 {#pg14-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 ```text 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 {#pg14-3-5-12-postgres-fdw} The following enhancements have been implemented for the postgres_fdw module. ##### Functions {#pg14-p83-functions} The following functions have been added to control the active session.PDF page 84
**Table 30.** Added functions | Function name | Description | | --- | --- | | postgres_fdw_disconnect | Disconnect a specified active session. | | postgres_fdw_disconnect_all | Disconnect all active sessions. | | postgres_fdw_get_connections | postgres_fdw_get_connections Return all active sessions. | **Example 102.** Execution of postgres\_fdw related functions ```text 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 {#pg14-p84-truncate-statement} The TRUNCATE statement can now be executed on an foreign table. ##### Supports Bulk Insert {#pg14-p84-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 ```text 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') ``` ```sql The following interfaces have been added to the Foreign Data Wrapper for the bulk insert feature. ``` **Table 31.** Added Interface functions | Interface name | Description | | --- | --- | | ExecForeignBatchInsert | Execution of batch transmission process. | | GetForeignModifyBatchSize | Acquisition of batch size. | ##### keep_connections option {#pg14-p85-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 {#pg14-p85-import-foreign-schema-statement} Partitions can now be specified in the LIMIT TO and EXCEPT clauses. ##### Reconnect {#pg14-p85-reconnect} If the session with the remote instance is found to be broken, it will now be reconnected.