Skip to content

14 Contrib Modules

Source chapter 3.12 for PostgreSQL 10.
Beta 1Source pages: PDF 94-98Original PDF on GitHub

PDF page 94

This section describes the new features of the Contrib module.

3.12.1 postgres_fdw

The following enhancements have been added to the postgres_fdw module.

Push-down of aggregation processing

It is now possible to push down of FULL JOIN between remote tables.

Example 104 SQL for local execution
postgres=# SELECT COUNT(*), AVG(c1), SUM(c1) FROM datar1 ;
count |         avg          |  sum
-------+----------------------+--------
  1000 | 500.5000000000000000 | 500500
(1 row)
The above SQL statement is converted to the following SQL statement at FOREIGN SERVER.
Example 105 Remote execution SQL (from log_statement = 'all' log)
statement: START TRANSACTION ISOLATION LEVEL REPEATABLE READ
execute <unnamed>: DECLARE c1 CURSOR FOR
        SELECT count(*), avg(c1), sum(c1) FROM public.datar1
statement: FETCH 100 FROM c1
statement: CLOSE c1
statement: COMMIT TRANSACTION
Push down of FULL JOIN

Pushdown is now done when doing FULL JOIN between remote tables.

PDF page 95

Example 106 FULL JOIN between remote tables
postgres=> EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM (SELECT * FROM remote1 WHERE
c1 < 10000) r1 FULL JOIN (SELECT * FROM remote2 WHERE c1 < 10000) r2 ON (TRUE) LIMIT
10 ;
                            QUERY PLAN
------------------------------------------------------------------------------
 Limit
   Output: remote1.c1, remote1.c2, remote2.c1, remote2.c2
   ->  Foreign Scan
         Output: remote1.c1, remote1.c2, remote2.c1, remote2.c2
         Relations: (public.remote1) FULL JOIN (public.remote2)
         Remote SQL: SELECT s4.c1, s4.c2, s5.c1, s5.c2 FROM ((SELECT c1, c2 FROM
public.remote1 WHERE ((c1 < 10000::numeric))) s4(c1, c2) FULL JOIN (SELECT c1, c2
FROM public.remote2 WHERE ((c1 < 10000::numeric))) s5(c1, c2) ON (TRUE))
(6 rows)

3.12.2 file_fdw

A “program” option to run the application has been added. The program option is specified instead of the filename option indicating the file name. When the SELECT statement for FOREIGN TABLE is executed, the specified program is automatically executed. The content that the executed program outputs to the standard output is returned to the application as tuples.

Example 107 Program specified file_fdw module
postgres=# CREATE EXTENSION file_fdw ;
CREATE EXTENSION
postgres=# CREATE SERVER fs FOREIGN DATA WRAPPER file_fdw ;
CREATE SERVER
postgres=# CREATE FOREIGN TABLE tfile1 (id NUMERIC, val VARCHAR(10)) SERVER fs
OPTIONS (program '/home/postgres/bin/file_fdw.py', delimiter ',') ;
CREATE FOREIGN TABLE
postgres=# SELECT * FROM tfile1 ;

PDF page 96

In the above example, when FOREIGN TABLE tfile1 is searched, the program file_fdw.py is executed. The program uses standard output as follows.

Example 108 Examples of programs executed
#!/bin/python
for x in range(1000):
    print x,",test"
The end of the program is regarded as the end of the SELECT statement. Internally, the program is
passed to popen(3) function and executed (OpenPipeStream function in src/backend/storage/file/fd.c
file).

3.12.3 amcheck

The amcheck module to check the consistency of the BTree index has been added to the Contrib module. The following functions have been added to this module.

  • bt_index_check(index regclass) Checks the integrity of the specified BTree index

  • bt_index_parent_check(index regclass) Checks the consistency of parent-child index In the example below, the bt_index_check function is executed for the index (idx1_check1) where some data is corrupted.

Example 109 Execution of bt_index_check function
postgres=# CREATE EXTENSION amcheck ;
CREATE EXTENSION
postgres=# SELECT bt_index_check('idx1_check1') ;
ERROR:  item order invariant violated for index "idx1_check1"
DETAIL:  Lower index tid=(1,2) (points to heap tid=(0,2)) higher index
tid=(1,3) (points to heap tid=(0,3)) page lsn=0/7EFE4638.

3.12.4 pageinspect

The following functions corresponding to Hash Index have been added.

PDF page 97

  • hash_page_type

  • hash_page_stats

  • hash_page_items

  • hash_metapage_info

  • page_checksum

  • bt_page_items(IN page bytea)

3.12.5 pgstattuple

The following functions have been added.

  • pgstathashindex Provides information on Hash Index.
Example 110 pgstathashindex function
postgres=# SELECT * FROM pgstathashindex('idx1_hash1') ;
-[ RECORD 1 ]--+----------------
version        | 3
bucket_pages   | 33
overflow_pages | 15
bitmap_pages   | 1
unused_pages   | 32
live_items     | 13588
dead_items     | 0
free_percent   | 58.329244357213

3.12.6 btree_gist / btree_gin

GiST indexes can now be created in UUID type columns and ENUM type columns. GIN index can be created in ENUM type columns.

PDF page 98

Example 111 GiST index creation for columns of UUID type and ENUM type
postgres=# CREATE EXTENSION btree_gist ;
CREATE EXTENSION
postgres=> CREATE TYPE type1 AS ENUM ('typ1', 'typ2', 'typ3') ;
CREATE TYPE
postgres=> CREATE TABLE gist1(c1 UUID, c2 type1) ;
CREATE TABLE
postgres=> CREATE INDEX idx1_gist1 ON gist1 USING gist (c1) ;
CREATE INDEX
postgres=> CREATE INDEX idx2_gist1 ON gist1 USING gist (c2) ;
CREATE INDEX

3.12.7 pg_stat_statements

The format of the SQL statement stored in the query column of the pg_stat_statements view has been changed. The literal value of the WHERE clause was conventionally output as a question mark (?), but it has been changed to $ {N} (N = 1, 2, …).

Example 112 pg_stat_statements view
postgres=> SELECT query FROM pg_stat_statements WHERE query LIKE '%part1%' ;
                      query
--------------------------------------------------
SELECT COUNT(*) FROM part1 WHERE c1=$1
SELECT COUNT(*) FROM part1 WHERE c1=$1 AND c2=$2
(2 rows)

3.12.8 tsearch2

The tsearch2 module has been deleted.