Describes the new features of the Contrib modules.
3.9.1 adminpack
Previously, functions included in this module required SUPERUSER privilege. But now, general users are also executable if allowed by the GRANT statement.
3.9.2 amcheck
A new Contrib module amcheck has been added. The only B-Tree index can be checked. In the following example, the index idx1_data1 whose integrity has been destroyed is checked. Also, check the HASH index and an error has occurred.
Example 94Use amcheck module
postgres=# CREATE EXTENSION amcheck ;
CREATE EXTENSION
postgres=# SELECT bt_index_check('idx1_data1') ;
ERROR: invalid page in block 0 of relation base/16385/16479
postgres=# CREATE INDEX idxh1_data1 ON data1 USING hash (c1) ;
CREATE INDEX
postgres=# SELECT bt_index_check('idxh1_data1') ;
ERROR: only B-Tree indexes are supported as targets for verification
DETAIL: Relation "idxh1_data1" is not a B-Tree index.
Function name
Description
bt_index_check
Consistency check for B-Tree index
bt_index_parent_check
Consistency check of B-Tree index including parentage relationship
Table 26Provided functions
PDF page 77
3.9.3 btree_gin
B-Tree GIN indexes can now be created for bool, bpchar, and uuid columns.
Example 95Create B-Tree Gin index
postgres=# CREATE EXTENSION btree_gin ;
CREATE EXTENSION
postgres=> CREATE TABLE gintbl1(c1 bool, c2 bpchar(10), c3 uuid) ;
CREATE TABLE
postgres=> CREATE INDEX idx1_gintbl1 ON gintbl1 USING gin(c1) ;
CREATE INDEX
postgres=> CREATE INDEX idx2_gintbl1 ON gintbl1 USING gin(c2) ;
CREATE INDEX
postgres=> CREATE INDEX idx3_gintbl1 ON gintbl1 USING gin(c3) ;
CREATE INDEX
3.9.4 citext
Operator class citext_pattern_ops for the index has been added. Operators <, <=, >, >= can be used for the citext type similarly to text type. Conventionally, it was converted to text type and executed.
Example 96Execution plan on PostgreSQL 10
postgres=> EXPLAIN ANALYZE SELECT * FROM citext1 WHERE c2 ~<~ '111' ;
QUERY PLAN
---------------------------------------------------------------------
Seq Scan on citext1 (cost=0.00..17906.00 rows=11574 width=12) (actual
time=0.011..76.417 rows=12225 loops=1)
Filter: ((c2)::text ~<~ '111'::text)
Rows Removed by Filter: 987775
Planning time: 0.046 ms
Execution time: 76.881 ms
(5 rows)
PDF page 78
Example 97Execution plan on PostgreSQL 11
postgres=> CREATE INDEX idx1_citext1 ON
citext1(c2 citext_pattern_ops) ;
CREATE INDEX
postgres=> EXPLAIN ANALYZE SELECT * FROM citext1 WHERE c2 ~<~ '111' ;
QUERY PLAN
---------------------------------------------------------------------
Bitmap Heap Scan on citext1 (cost=44.03..603.77 rows=1499 width=11)
(actual time=0.146..0.243 rows=1225 loops=1)
Recheck Cond: (c2 ~<~ '111'::citext)
Heap Blocks: exact=10
-> Bitmap Index Scan on idx1_citext1 (cost=0.00..43.66 rows=1499
width=0) (actual time=0.142..0.142 rows=1225 loops=1)
Index Cond: (c2 ~<~ '111'::citext)
Planning time: 0.107 ms
Execution time: 0.309 ms
(7 rows)
3.9.5 cube / seg
Index Only Scan is now executable with the GiST index.
3.9.6 jsonb_plpython
A new Contrib module jsonb_plpython has been added. The –with-python option must be specified in the configure command for installation. The module name specified in the CREATE EXTENSION statement is “jsonb_plpythonu”, “jsonb_plpython2u”, or “jsonb_plpython3u”. Jsonb can be specified in the TRANSFORM clause of the CREATE FUNCTION statement.
PDF page 79
Example 98jsonb_plpython module
postgres=# CREATE EXTENSION jsonb_plpythonu CASCADE ;
NOTICE: installing required extension "plpythonu"
CREATE EXTENSION
postgres=# CREATE OR REPLACE FUNCTION fpython(val jsonb)
RETURNS jsonb
TRANSFORM FOR TYPE jsonb
LANGUAGE plpythonu
AS $$
return (val) ;
$$ ;
CREATE FUNCTION
postgres=# SELECT fpython('{"1":1,"example": null}'::jsonb) ;
fpython
---------------------------
{"1": 1, "example": null}
(1 row)
3.9.7 jsonb_plperl
A new Contrib module jsonb_plperl has been added. The –with-perl option must be specified in the “configure” command for installation. To use it, specify jsonb in the TRANSFORM clause of the CREATE FUNCTION statement.
PDF page 80
Example 99jsonb_plperl module
postgres=# CREATE EXTENSION jsonb_plperl CASCADE ;
NOTICE: installing required extension "plperl"
CREATE EXTENSION
postgres=> CREATE OR REPLACE FUNCTION fperl(val jsonb)
RETURNS jsonb
TRANSFORM FOR TYPE jsonb
LANGUAGE plperl
AS $$
return $_[0] ;
$$ ;
CREATE FUNCTION
postgres=> SELECT fperl('{"1":1,"example": null}'::jsonb) ;
fperl
---------------------------
{"1": 1, "example": null}
(1 row)
3.9.8 pageinspect
The last_cleanup_num_tuples column was added to the output of bt_metap function.
The feature which automatically loads pages that existed in shared memory when the instance was running into shared memory at instance startup has been added. When “pg_prewarm” is specified for the parameter shared_preload_libraries and the instance is started, the background worker process “postgres: autoprewarm master” is started automatically. The autoprewarm master process loads the blocks previously stored in the shared memory into the shared memory at instance startup. After that, this process saves the block information on the shared memory in a file at regular intervals. If the parameter max_worker_processes is 0, the background worker process will not start.
File
The file to which the autoprewarm process saves the block information loaded on shared memory is {PGDATA}/autoprewarm.blocks. This file is in text format and saves information such as a database, tablespace, filenode, block etc… While updating the file, it writes to the {PGDATA}/autoprewarm.blocks.tmp file and the file name is changed.
Parameter pg_prewarm.autoprewarm
Specifying the default value “on” for this parameter enables the automatic prewarm feature.
Parameter pg_prewarm.autoprewarm_interval
Specify the minimum interval, in seconds, at which block information on shared memory is saved periodically. The default value is 300 seconds (5 minutes). When this parameter is set to 0, periodic shared memory saving processing is not performed. This process will be performed only when the instance is stopped.
3.9.10 pg_trgm
The function strict_word_similarity has been added. This function is similar to the word_similarity function, but it matches the extent boundary to the word boundary.
The following enhancements have been added to the postgres_fdw module.
Update remote partition
Even if the partition is FOREIGN TABLE, it is now possible to update tuples via partition table. It is also possible to insert tuples by COPY statement.
Example 102Partition with FOREIGN TABLE
postgres=> CREATE TABLE part1(c1 INT, c2 VARCHAR(10)) PARTITION BY RANGE(c1) ;
CREATE TABLE
postgres=> CREATE FOREIGN TABLE part1v1 PARTITION OF part1 FOR VALUES FROM (0)
TO (1000000) SERVER remhost1 ;
CREATE FOREIGN TABLE
postgres=> CREATE FOREIGN TABLE part1v2 PARTITION OF part1 FOR VALUES FROM
(1000000) TO (2000000) SERVER remhost1 ;
Example 103The behavior of PostgreSQL 10
postgres=> INSERT INTO part1 VALUES (100, 'data1') ;
ERROR: cannot route inserted tuples to a foreign table