7 Contrib Modules

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

GASource pages: PDF 81-85Original PDF on GitHub

PDF page 81

Describes new features related to the Contrib module.

3.5.1 auto_explain

Auto_explain.log_level parameter

The parameter auto_explain.log_level (default value LOG) has been added. This parameter can specify the level of log output by the auto_explain module. Previously, it was fixed at log level LOG.

Example 99 log_level parameter

postgres=# LOAD 'auto_explain' ;
LOAD
postgres=# SET auto_explain.log_level = NOTICE ;
SET
postgres=# SET auto_explain.log_min_duration = 0 ;
SET
postgres=# SELECT * FROM data1 WHERE c1=1000 ;
NOTICE:  duration: 146.234 ms  plan:
Query Text: SELECT * FROM data1 WHERE c1=1000 ;
Index Scan using data1_pkey on data1  (cost=0.42..8.44 rows=1
width=12)
  Index Cond: (c1 = '1000'::numeric)
  c1  |  c2
------+-------
 1000 | data1
(1 row)

Auto_explain.log_settings parameter

Determines whether changed optimizer-related parameters are output to the log. The default value is ‘off’, so no log is output.

PDF page 82

Example 100 log_settings parameter

2019-10-05 11:10:02.465 JST [28847] LOG:  duration: 62.630 ms  plan:
        Query Text: SELECT COUNT(*) FROM data1 ;
        Finalize Aggregate  (cost=11604.55..11604.56 rows=1 width=8)
          ->  Gather  (cost=11604.33..11604.54 rows=2 width=8)
                Workers Planned: 2
                ->  Partial Aggregate  (cost=10604.33..10604.34 rows=1 width=8)
                      ->  Parallel Seq Scan on data1  (cost=0.00..9562.67
rows=416667 width=0)
        Settings: random_page_cost = '1'

Auto_explain.sample_rate parameter

Specify the sampling rate for log output. The default value is 1, which outputs all output SQL statements.

Add JIT compilation information

JIT compilation information is now output to the log (backported to PostgreSQL 11.2).

Example 101 JIT compilation information log

2019-10-04 22:25:11.027 JST [17749] LOG:  duration: 47.828 ms  plan:
        Query Text: SELECT COUNT(*) FROM data1 ;
        Aggregate  (cost=17906.00..17906.01 rows=1 width=8)
          ->  Seq Scan on data1  (cost=0.00..15406.00 rows=1000000 width=0)
        JIT:
          Functions: 2
          Options: Inlining false, Optimization false, Expressions true,
Deforming true

3.5.2 citext

Added citext_hash_extended to calculate 64-bit hash value. The second parameter specifies the SEED.

PDF page 83

Example 102 citext_hash_extended function

postgres=> INSERT INTO hash1 VALUES ('abc') ;
INSERT 0 1
postgres=> SELECT citext_hash_extended(c1, 0) FROM hash1 ;
 citext_hash_extended
----------------------
 -6747756470228489321
(1 row)
postgres=> SELECT citext_hash_extended(c1, 1) FROM hash1 ;
 citext_hash_extended
----------------------
  2125675926272891485
(1 row)

3.5.3 hstore

Hstore_hash_extended has been added to calculate 64-bit hash values. The second parameter specifies the SEED.

PDF page 84

Example 103 hstore_hash_extended function

postgres=> INSERT INTO hstore1 VALUES (hstore('key1', 'val1')) ;
INSERT 0 1
postgres=> SELECT hstore_hash_extended(c1, 0) FROM hstore1 ;
 hstore_hash_extended
----------------------
  4863461342754690677
(1 row)
postgres=> SELECT hstore_hash_extended(c1, 1) FROM hstore1 ;
 hstore_hash_extended
----------------------
  1673650576531873159
(1 row)

3.5.4 pg_stat_statements

The pg_stat_statements_reset function of the pg_stat_statements module has added a parameter that limits the deletion range of statistical information. Statistical information can be deleted by specifying database ID, user ID, and query ID. If these parameters are omitted (the default value is 0), all statistics will be discarded as before.

Example 104 pg_stat_statements_reset function

postgres=> \dfS pg_stat_statements_reset
List of functions
-[ RECORD 1 ]-------+--------------------------------------------
-------------
Schema              | public
Name                | pg_stat_statements_reset
Result data type    | void
Argument data types | userid oid DEFAULT 0, dbid oid DEFAULT 0,
queryid bigint DEFAULT 0
Type                | func

 Also, this function cannot be executed by users who hold the pg_read_all_stats role.

PDF page 85

3.5.5 postgres_fdw

The postgres_fdw module adds an “options” option that specifies the GUC options that can be run in a remote session. For example, settings of work_mem parameter and geqo parameter can be changed. Set values are described in the “-c parameter name = value” format. When specifying multiple parameters, specify the entire “-c parameter_name = value” separated by a space.

Example 105 options parameter

postgres=# CREATE SERVER remsvr1 FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'remhost1', options '-c work_mem=4MB') ;
CREATE SERVER
postgres=# ALTER SERVER remsvr1 OPTIONS
(SET options '-c work_mem=16MB -c geqo=off') ;
ALTER SERVER
postgres=# SELECT * FROM pg_foreign_server ;
-[ RECORD 1 ]-------------------------------------------------------
srvname    | remsvr1
srvowner   | 10
srvfdw     | 16412
srvtype    |
srvversion |
srvacl     |
srvoptions | {host=remhost1, "options=-c work_mem=16MB -c geqo=off"}