2 New Features Summary

PDF page 7

More than 180 new features have been added to PostgreSQL 12. Here are some typical new features and benefits. This version focuses to enhance various new features added in PostgreSQL 11.

2.1 Improve analytic query performance

The following features have been added that can be applied to large scale environments:

Enhancement of Parallel Query

The scope to which parallel queries are applied has been expanded. Even when the transaction isolation level is SERIALIZABLE, parallel queries may be performed.

Enhancement of Partition Table

It is now possible to specify computed values instead of fixed values as partition key values. Partition tables can now be used as foreign key references.

2.2 Improve reliability

In PostgreSQL 12 the checking tool of integrity has been expanded.

pg_checksums command

The pg_verify_checksums command added in PostgreSQL 11 has been changed to the pg_checksums command. In addition to checking the integrity, it is now possible to change the enable/disable of the checksum function with the command.

2.3 Improve maintenancibility

The following features that can improve the operability has been added.

Abolition of recovery.conf file

The recovery.conf file, used for streaming replication standby instances and recovery, have been consolidated into the postgresql.conf file.

Enhancement of REINDEX statement

The CONCURRENTLY clause was added to the REINDEX statement, and the lock range when rebuilding the index became very small.

PDF page 8

Enhancement of monitoring features

Catalogs have been added that allow you to check the execution status of CLUSTER statement, VACUUM FULL statement and CREATE INDEX statement in real time.

Increase in waiting for event type

Several wait events have been added. It can be checked in the pg_stat_activity catalog.

pg_promote function

A function pg_promote has been provided to perform promotion from the standby instance to the primary instance.

2.4 Preparing for future new features

PostgreSQL 12 is preparing for the features to be provided in future versions.

Multiple storage engine

The basic specification of PLUGGABLE STORAGE ENGINE has been decided that can use multiple storage engines simultaneously. It is expected that the corresponding such as zheap is carried out in the future.

64-bit transaction ID

APIs that obtain 64-bit transaction IDs can now be used.

PDF page 9

2.5 Incompatibility

PostgreSQL 12 has the following specifications changed from PostgreSQL 11.

2.5.1 recovery.conf file

Recovery.conf files created in database recovery and standby instances in a streaming replication environment have been discontinued. Various parameters in the recovery.conf file have been integrated into the postgresql.conf file. Details are explained in “3.1.2. Deprecation of recovery.conf file”.

2.5.2 Pg_checksums command

The pg_verify_checksums command added in PostgreSQL 11 has been renamed to pg_checksums and the new feature has been added.

2.5.3 WITH OIDS clause deleted

The WITH OIDS clause of the CREATE TABLE statement is prohibited, and a table with OID cannot be created. Along with this change, the oid column in the system catalog is no longer a hidden column. The WITHOUT OIDS clause can continue to be used. Along with this change, the default_with_oids parameter cannot be changed to “on”. With this specification change, the –oids option (-o option) has been removed from the pg_dump command.

postgres=> CREATE TABLE oid1(c1 INT) WITH OIDS ;
psql: ERROR:  syntax error at or near "OIDS"
LINE 1: CREATE TABLE oid1(c1 INT) WITH OIDS ;

PDF page 10

postgres=> \d pg_tablespace
            Table "pg_catalog.pg_tablespace"
   Column   |   Type    | Collation | Nullable | Default
------------+-----------+-----------+----------+---------
 oid        | oid       |           | not null |
 spcname    | name      |           | not null |
 spcowner   | oid       |           | not null |
 spcacl     | aclitem[] |           |          |
 spcoptions | text[]    |           |          |
Indexes:
    "pg_tablespace_oid_index"
UNIQUE,
btree
(oid),
tablespace
"pg_global"
    "pg_tablespace_spcname_index"
UNIQUE,
btree
(spcname),
tablespace "pg_global"
Tablespace: "pg_global"

2.5.4 Timetravel Contrib module

The Contrib module timetravel has been removed.

2.5.5 Removed data type

The datatypes abstime, reltime, and tinterval have been removed. As a result, the data type of column valutil in the system catalog pg_shadow has been changed to “timestamp with time zone”.

2.5.6 COPY FREEZE for the partitioned table

The COPY FREEZE statement for partitioned tables has been prohibited. This specification has also been backported to PostgreSQL 11.2 and later.

2.5.7 Change foreign key constraint name

Auto-generated foreign key names are now generated including all the columns contained in the foreign key.

PDF page 11

postgres=> CREATE TABLE ftable2(c1 INT, c2 INT, c3 VARCHAR(10),
FOREIGN KEY (c1, c2) REFERENCES ftable1(c1, c2)) ;
CREATE TABLE
postgres=> \d ftable2
                     Table "public.ftable2"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 c1     | integer               |           |          |
 c2     | integer               |           |          |
 c3     | character varying(10) |           |          |
Foreign-key constraints:
    "ftable2_c1_c2_fkey" FOREIGN KEY (c1, c2) REFERENCES ftable1(c1,
c2)

2.5.8 Functions

The specifications of the following functions have been changed.

Current_schema function and current_schemas function

Current_schema and current_schemas functions were set to be unsafe for the parallel query (Parallel Unsafe).

To_timestamp function and to_date function

Unnecessary space in the template is now ignored.

postgres=> SELECT TO_DATE('2019/10/03', ' YYYY/MM/DD') ;
  to_date
------------
 0019-10-03
(1 row)

PDF page 12

postgres=> SELECT TO_DATE('2019/10/03', ' YYYY/MM/DD') ;
  to_date
------------
 2019-10-03
(1 row)

Substring function

The pattern search specification in the escape string has been changed.

postgres=> SELECT SUBSTRING('foobar' from '%#"oo*#"%' FOR '#') ;
 substring
-----------
 o
(1 row)

postgres=> SELECT SUBSTRING('foobar' from '%#"oo*#"%' FOR '#') ;
 substring
-----------
 oo
(1 row)

PDF page 13