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 {#pg12-2-1-improve-analytic-query-performance} The following features have been added that can be applied to large scale environments: #### Enhancement of Parallel Query {#pg12-p7-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 {#pg12-p7-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 {#pg12-2-2-improve-reliability} In PostgreSQL 12 the checking tool of integrity has been expanded. #### pg_checksums command {#pg12-p7-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 {#pg12-2-3-improve-maintenancibility} The following features that can improve the operability has been added. #### Abolition of recovery.conf file {#pg12-p7-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 {#pg12-p7-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 {#pg12-p8-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 {#pg12-p8-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 {#pg12-p8-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 {#pg12-2-4-preparing-for-future-new-features} PostgreSQL 12 is preparing for the features to be provided in future versions. #### Multiple storage engine {#pg12-p8-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 {#pg12-p8-64-bit-transaction-id} APIs that obtain 64-bit transaction IDs can now be used.PDF page 9
### 2.5 Incompatibility {#pg12-2-5-incompatibility} PostgreSQL 12 has the following specifications changed from PostgreSQL 11. #### 2.5.1 recovery.conf file {#pg12-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 {#pg12-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 {#pg12-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. **Example 1.** CREATE TABLE statement in PostgreSQL 12 ```text 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
**Example 2.** pg\_tablespace catalog in PostgreSQL 12 ```text 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 {#pg12-2-5-4-timetravel-contrib-module} The Contrib module timetravel has been removed. #### 2.5.5 Removed data type {#pg12-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 {#pg12-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 {#pg12-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
**Example 3.** FOREIGN KEY constraint name in PostgreSQL 12 ```text 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 {#pg12-2-5-8-functions} The specifications of the following functions have been changed. ##### Current_schema function and current_schemas function {#pg12-p11-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 {#pg12-p11-to-timestamp-function-and-to-date-function} Unnecessary space in the template is now ignored. **Example 4.** to\_date function in PostgreSQL 11 ```text postgres=> SELECT TO_DATE('2019/10/03', ' YYYY/MM/DD') ; to_date ------------ 0019-10-03 (1 row) ```PDF page 12
**Example 5.** to\_date function in PostgreSQL 12 ```text postgres=> SELECT TO_DATE('2019/10/03', ' YYYY/MM/DD') ; to_date ------------ 2019-10-03 (1 row) ``` ##### Substring function {#pg12-p12-substring-function} The pattern search specification in the escape string has been changed. **Example 6.** substring function in PostgreSQL 11 ```text postgres=> SELECT SUBSTRING('foobar' from '%#"oo*#"%' FOR '#') ; substring ----------- o (1 row) ``` **Example 7.** substring function in PostgreSQL 12 ```text postgres=> SELECT SUBSTRING('foobar' from '%#"oo*#"%' FOR '#') ; substring ----------- oo (1 row) ```PDF page 13