2 New Features Summary

PDF page 7

More than 200 new features have been added to PostgreSQL 14. Here are some major new features and benefits.

2.1 Improve analytic query performance

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

Logical Streaming Replication enhancements

Logical replication can now transfer decoded updates to a standby instance without waiting for the transaction to complete. In addition, it is now possible to send transfer data that was previously converted to text in binary.

Parallel query enhancement

Parallel queries now work for REFRESH MATERIALIZED VIEW statements.

LZ4 compression

LZ4 compression is now available for TOAST columns.

BRIN index

Bloom filters are now available.

2.2 Improve reliability

PostgreSQL 14 implements the following enhancements to improve reliability.

Enhanced data structure checking

Contrib module amcheck now includes a function to check object structure. Also, add pg_amcheck command to run checks by the amcheck module from the command line

Elimination of password length restrictions

The restriction on password length has been removed, and very long passwords can now be used.

PDF page 8

2.3 Improved maintainability

The following features that can improve operability have been added.

Enhancements to the REINDEX statement

It is now possible to execute a REINDEX statement on a partitioned table. This eliminates the need to run REINDEX on each partition. In addition, it will be able to move the index to be re-created with the TABLESPACE clause.

Enhancement of monitoring feature

A real-time view of COPY command execution status has been added. A view has also been added to check WAL usage and memory status of backend processes. In the Contrib module pg_stat_statements, it is possible to acquire information on utility statements such as REFRESH MATERIALIZED VIEW statement, and the statistical information that can be acquired has increased significantly.

2.4 Preparing for future new features

PostgreSQL 14 is now ready for features that will be provided in future versions.

Data Access by Subscripts

The infrastructure for subscript data access to new data types has been implemented, and the hstore module can take advantage of this feature.

2.5 Incompatibility

In PostgreSQL 14, the following specifications have been changed from PostgreSQL 13.

2.5.1 Functions

The following functions have been changed.

LEAD / LAG

The data type of some function return values has been changed from anyelement type to anycompatible type.

PDF page 9

EXTRACT

The return data type of the EXTRACT function has been changed from double precision to numeric. In previous versions, the EXTRACT function internally called the DATE_PART function, but now it’s independent. The data type of the return value of the DATE_PART function remains double precision.

VAR_SAMP, STDDEV_SAMP

The return value specified as a NaN value of type numeric has changed from NaN to NULL.

2.5.2 Regular Expression

In newline-sensitive mode, the escape \W and \D now match the linefeed code.

postgres=> SELECT regexp_match(E'A\nC', '\D\D\D', 'n') ;
 regexp_match
--------------
 null
(1 row)

postgres=> SELECT regexp_match(E'A\nC', '\D\D\D', 'n') ;
 regexp_match
--------------
 {"A         +
 C"}
(1 row)

2.5.3 Factorial calculation

The factorial calculation result and operator have been changed.

Factorial of negative value

Factorial calculation of negative values will result in an error.

PDF page 10

postgres=> SELECT factorial(-4) ;
factorial
-----------
         1
(1 row)

postgres=> SELECT factorial(-4) ;
ERROR:  factorial of a negative number is undefined

Removed operator

The factorial operator (! and !!) has been removed.

postgres=> SELECT 4! ;
RROR:  syntax error at or near ";"
LINE 1: SELECT 4! ;
                  ^

Removed function

The numeric_fac function has been removed.

2.5.4 Operators

The deprecated operators @ and ~ have been removed from the Contrib modules cube, hstore, intarray and seg.

2.5.5 Manuals

The Default Roles in the manual and the program source have been changed to Predefined Roles.

2.5.6 Transaction wraparound warning

The threshold points of the remaining transactions for the wraparound of the transaction ID have been changed as follows.

PDF page 11

2.5.7 Start-up options

The -o option, which specifies additional options when starting postmaster, has been removed.

2.5.8 Extended Statistics

CREATE STATISTICS statement for system catalogs can no longer be executed. This limit is backported to older versions.

postgres=# CREATE STATISTICS stat1_collation ON collname,
collowner FROM pg_collation ;
ERROR:  permission denied: "pg_collation" is a system catalog

2.5.9 Protocol

The version 2 client-backend communication protocol has been removed. This protocol has been used since PostgreSQL 7.4.

2.5.10 ALTER OPERATOR

Removed support for postfix (right-unary) operators on ALTER OPERATOR statement and DROP OPERATOR statement.

2.5.11 ALTER TABLE

The DROP EXPRESSION clause can no longer be specified in the ALTER TABLE ONLY statement.

PDF page 12

2.5.12 CREATE LANGUAGE

Specifying a name in single quotes will result in an error in the CREATE LANGUAGE statement or DROP LANGUAGE statement.

postgres=# CREATE LANGUAGE 'plperl' ;
ERROR:  syntax error at or near "'plperl'"
LINE 1: CREATE LANGUAGE 'plperl';

2.5.13 LIBPQ connection string

The connection strings authtype and tty have been removed. The client connection setting sslcompression for SSL compression is ignored by the backend.

2.5.14 Findoidjoins

The findoidjoins command has been removed.

2.5.15 Intarray

Prevent the containment operators (<@ and @>) for intarray module from using GiST indexes.

2.5.16 Pg_hba.conf file

Values “0”, “1”, and “no-verify” are no longer supported of the clientcert option in the pg_hba.conf file.

2.5.17 Pg_standby

The pg_standby command has been removed.

2.5.18 Tablefunc

Negative values can no longer be specified for the first parameter of the normal_rand function.

PDF page 13

postgres=# CREATE EXTENSION tablefunc ;
CREATE EXTENSION
postgres=# SELECT normal_rand(-1, 1.2, 1.3) ;
ERROR:  number of rows cannot be negative

PDF page 14