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 {#pg14-2-1-improve-analytic-query-performance} ```text The following features have been added that can be applied to large scale environments: ``` #### Logical Streaming Replication enhancements {#pg14-p7-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 {#pg14-p7-parallel-query-enhancement} Parallel queries now work for REFRESH MATERIALIZED VIEW statements. #### LZ4 compression {#pg14-p7-lz4-compression} LZ4 compression is now available for TOAST columns. #### BRIN index {#pg14-p7-brin-index} Bloom filters are now available. ### 2.2 Improve reliability {#pg14-2-2-improve-reliability} PostgreSQL 14 implements the following enhancements to improve reliability. #### Enhanced data structure checking {#pg14-p7-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 {#pg14-p7-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 {#pg14-2-3-improved-maintainability} The following features that can improve operability have been added. #### Enhancements to the REINDEX statement {#pg14-p8-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 {#pg14-p8-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 {#pg14-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 {#pg14-p8-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 {#pg14-2-5-incompatibility} In PostgreSQL 14, the following specifications have been changed from PostgreSQL 13. #### 2.5.1 Functions {#pg14-2-5-1-functions} The following functions have been changed. ##### LEAD / LAG {#pg14-p8-lead-lag} The data type of some function return values has been changed from anyelement type to anycompatible type.PDF page 9
##### EXTRACT {#pg14-p9-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 {#pg14-p9-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 {#pg14-2-5-2-regular-expression} In newline-sensitive mode, the escape \W and \D now match the linefeed code. **Example 1.** Match newline \(PostgreSQL 13\) ```text postgres=> SELECT regexp_match(E'A\nC', '\D\D\D', 'n') ; regexp_match -------------- null (1 row) ``` **Example 2.** Match newline \(PostgreSQL 14\) ```text postgres=> SELECT regexp_match(E'A\nC', '\D\D\D', 'n') ; regexp_match -------------- {"A + C"} (1 row) ``` #### 2.5.3 Factorial calculation {#pg14-2-5-3-factorial-calculation} The factorial calculation result and operator have been changed. ##### Factorial of negative value {#pg14-p9-factorial-of-negative-value} Factorial calculation of negative values will result in an error.PDF page 10
**Example 3.** Factorial calculation \(PostgreSQL 13\) ```text postgres=> SELECT factorial(-4) ; factorial ----------- 1 (1 row) ``` **Example 4.** Factorial calculation \(PostgreSQL 14\) ```text postgres=> SELECT factorial(-4) ; ERROR: factorial of a negative number is undefined ``` ##### Removed operator {#pg14-p10-removed-operator} The factorial operator (! and !!) has been removed. **Example 5.** Factorial calculation \(PostgreSQL 14\) ```text postgres=> SELECT 4! ; RROR: syntax error at or near ";" LINE 1: SELECT 4! ; ^ ``` ##### Removed function {#pg14-p10-removed-function} The numeric_fac function has been removed. #### 2.5.4 Operators {#pg14-2-5-4-operators} The deprecated operators @ and ~ have been removed from the Contrib modules cube, hstore, intarray and seg. #### 2.5.5 Manuals {#pg14-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 {#pg14-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
**Table 4.** Warning of transaction ID wraparound | Comparison | PostgreSQL 13 | PostgreSQL 14 | Note | | --- | --- | --- | --- | | Warning | 11 million | 40 million | | | Shutdown | One million | 3 million | | #### 2.5.7 Start-up options {#pg14-2-5-7-start-up-options} The -o option, which specifies additional options when starting postmaster, has been removed. #### 2.5.8 Extended Statistics {#pg14-2-5-8-extended-statistics} CREATE STATISTICS statement for system catalogs can no longer be executed. This limit is backported to older versions. **Example 6.** CREATE STATISTICS statement for system catalog ```text postgres=# CREATE STATISTICS stat1_collation ON collname, collowner FROM pg_collation ; ERROR: permission denied: "pg_collation" is a system catalog ``` #### 2.5.9 Protocol {#pg14-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 {#pg14-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 {#pg14-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 {#pg14-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. **Example 7.** CREATE LANGUAGE statement \(PostgreSQL 14\) ```text postgres=# CREATE LANGUAGE 'plperl' ; ERROR: syntax error at or near "'plperl'" LINE 1: CREATE LANGUAGE 'plperl'; ``` #### 2.5.13 LIBPQ connection string {#pg14-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 {#pg14-2-5-14-findoidjoins} The findoidjoins command has been removed. #### 2.5.15 Intarray {#pg14-2-5-15-intarray} Prevent the containment operators (<@ and @>) for intarray module from using GiST indexes. #### 2.5.16 Pg_hba.conf file {#pg14-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 {#pg14-2-5-17-pg-standby} The pg_standby command has been removed. #### 2.5.18 Tablefunc {#pg14-2-5-18-tablefunc} Negative values can no longer be specified for the first parameter of the normal_rand function.PDF page 13
**Example 8.** Normal\_rand function ```text 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