2 New Features Summary

PDF page 7

More than 200 new features have been added to PostgreSQL 15. This chapter provides an overview of typical new features and benefits. Details of the new features will be explained in “3. New Feature Detail”.

2.1 Improvements to adapt to large scale environments

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

Parallel Query Improvements

Parallel queries now work for SELECT DISTINCT statements.

Improved Compression Method

LZ4 and Zstandard are now available for compressing WALs and base backups. pg_receivewal command compression method has also been added.

Improvements to utilization statistics

The stats collector process has been deprecated, and performance statistics have been changed from communicating using UDP to using a shared memory area.

2.2 Improve reliability

PostgreSQL 15 implements the following enhancements to improve reliability.

Addition of archive library

Archive logging can now be performed by a shared library. The Contrib module basic_archive has been added as a reference implementation.

Checkpoint log

The default value for the log_checkpoints parameter has changed to “on”. The checkpoint execution log will be output by default.

PDF page 8

2.3 Improved maintainability

The following features that can improve operability have been added.

Improvements in logical Replication

Logical Replication has been greatly enhanced in PostgreSQL 15. Logical Replication can now only replicate tuples that meet certain conditions. The conditions for which tuples can be replicated are specified in the WHERE clause of the CREATE PUBLICATION or ALTER PUBLICATION statement. Replication columns can now be selected by specifying a list of columns in addition to the table in the CREATE PUBLICATION statement. Also, updates to specified LSNs can now be skipped.

Monitoring Enhancements

The pg_stat_subscription_stats view has been added to check the status of errors that occur on the subscription worker in Logical Replication environment. Pg_stat_statements module provides information on temporary file I/O times and JITs. information can now be monitored.

Log File Format

Log files can now be output in JSON format.

2.4 Improved in Programming

Added MERGE statement to SQL syntax. The MERGE statement can execute INSERT / UPDATE / DELETE processing at once depending on whether the join conditions of the tables match or not match.

2.5 Preparing for future new features

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

Change table access method

Added syntax to modify table access methods in the ALTER TABLE and ALTER MATERIALIZED VIEW statements.

PDF page 9

2.6 Incompatibility

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

2.6.1 Access privileges to the PUBLIC schema

Previously, the public schema had CREATE and USAGE privileges for all users (PUBLIC). In PostgreSQL 15, access to the public schema is limited to the database owner (pg_database_owner role). The owner of the public schema has also changed to pg_database_owner. With this change, it will be in the recommended state after CVE-2018-1058.

postgres=# CREATE USER demo PASSWORD 'demo' ;
CREATE ROLE
postgres=# \connect postgres demo
You are now connected to database "postgres" as user "demo".
postgres=> CREATE TABLE public.data1(c1 INT, c2 VARCHAR(10)) ;
ERROR:  permission denied for schema public
LINE 1: CREATE TABLE public.data1(c1 INT, c2 VARCHAR(10)) ;
                     ^
postgres=> CREATE VIEW public.view1 AS SELECT 1 ;
ERROR:  permission denied for schema public
postgres=> CREATE SEQUENCE public.seq1 ;
ERROR:  permission denied for schema public

2.6.2 Exclusive backup mode

The deprecated exclusive backup mode has been removed. Exclusive backup prohibits multiple simultaneous backups. To start an exclusive backup, set the exclusive parameter of the pg_start_backup function to true (default). The function name used to start/end online backup has changed due to the deletion of exclusive backup mode.

PDF page 10

postgres=# SELECT pg_backup_start('start online backup#1', false) ;
 pg_backup_start
-----------------
 0/4000028
(1 row)
postgres=# \! cp -r data backup
postgres=# SELECT pg_backup_stop(true) ;
NOTICE:  all required WAL segments have been archived
                              pg_backup_stop
---------------------------------------------------------------------------
 (0/4000138,"START WAL LOCATION: 0/4000028 (file 000000010000000000000004)+
 CHECKPOINT LOCATION: 0/4000060                                           +
 BACKUP METHOD: streamed                                                  +
 BACKUP FROM: primary                                                     +
START TIME: 2022-10-13 23:03:20 JST                                      +
 LABEL: start online backup#1                                             +
 START TIMELINE: 1                                                        +
 ","")
(1 row)

2.6.3 Psql

The psql command has the following changes related to compatibility.

Supported version of the connected instance

Connections to servers prior to PostgreSQL 9.2 are no longer supported. A warning message will be output when connecting to a server with PostgreSQL 9.2 or earlier.

PDF page 11

Password change

The behavior of the \password command has been changed. Previously, the password of the user specified at login was changed, but the user is determined based on the result of the execution of the SELECT CURRENT_USER statement. This changes the behavior of the SET SESSION AUTHORIZATION statement. In addition, the name of the user to be changed is now displayed at the prompt. This specification change will be backported to the previous version.

postgres=# SET SESSION AUTHORIZATION demo ;
SET
postgres=> \password
Enter new password: <<PASSWORD>> <- Password of postgres user
Enter it again: <<PASSWORD>> <- Password of postgres user
ERROR:  must be superuser to alter superuser roles or change superuser attribute
postgres=>

postgres=# SET SESSION AUTHORIZATION demo ;
SET
postgres=> \password
Enter new password for user "demo": <<PASSWORD>> <- Password of demo user
Enter it again: <<PASSWORD>> <- Password of demo user
postgres=>

Displaying Multiple Results

In previous versions, the psql command would print only the last result if multiple result sets were returned; PostgreSQL 15 prints all results. To change to the same behavior as in the previous version, execute the command \set SHOW_ALL_RESULTS off.

postgres=> SELECT 1 \; SELECT 2 ;
 ?column?
----------
        2
(1 row)

PDF page 12

postgres=> SELECT 1 \; SELECT 2 ;
 ?column?
----------
        1
(1 row)
 ?column?
----------
        2
(1 row)
postgres=> \set SHOW_ALL_RESULTS off
postgres=> SELECT 1 \; SELECT 2 ;
 ?column?
----------
        2
(1 row)

–Single-transaction option behavior

If the –single-transaction option is specified and an error occurs with the ON_ERROR_STOP variable defined, a ROLLBACK statement is automatically executed.

2.6.4 Literal

Literal strings that begin with a number were previously split into a number part and a string part, but PostgreSQL 15 causes an error.

postgres=> SELECT 123abc ;
abc
-----
 123
(1 row)

PDF page 13

postgres=> SELECT 123abc ;
ERROR:  trailing junk after numeric literal at or near "123a"
LINE 1: SELECT 123abc;
               ^

2.6.5 ANALYZE

The ANALYZE statement has been changed to use maintenance_io_concurrency instead of effective_io_concurrency for execution. This specification will be backported to PostgreSQL 14.

2.6.6 Python 2

Python 2 support in PL/Python has been removed due to the expiration of Python 2.7 support; Python 2 is rejected when running the ‘configure’ command. Python 3.2 and above is supported.

$ ./configure --with-python
checking build system type... x86_64-pc-linux-gnu
…
configure: using perl 5.26.3
checking for python3... no
checking for python... /usr/bin/python
configure: using python 2.7.18 (default, Feb 10 2022, 14:26:12)
configure: error: Python version 2.7 is too old (version 3 or later is required)

The extensions such as hstore_plpython2u, jsonb_plpython2u, and ltree_plpython2u have also been
removed.

2.6.7 Date_bin

Negative values can no longer be specified for the interval of the date_bin function. This specification change will be backported to the previous version.

PDF page 14

postgres=> SELECT date_bin('-2'::interval, timestamp '1970-01-01 01:00:00',
timestamp '1970-01-01 00:00:00') ;
ERROR:  stride must be greater than zero

2.6.8 Pg_amcheck

The –quiet option (short -q) of the pg_amcheck command has been removed. This fix will also be backported to PostgreSQL 14.

2.6.9 Pgcrypto

The proprietary built-in encryption algorithm has been removed and OpenSSL is now required.

2.6.10 Pg_dump / pg_dumpall

Removed the upgrade feature from versions prior to PostgreSQL 9.2. Also, the –no-synchronizedsnapshots option has been removed.

2.6.11 Pg_upgrade

Removed the upgrade feature from versions prior to PostgreSQL 9.2.

2.6.12 Postmaster

The postmaster startup option –forkboot has been changed to –forkaux.

2.6.13 Array_to_tsvector

Disallow making an empty lexeme via array_to_tsvector function.

postgres=> SELECT array_to_tsvector(ARRAY['base','hidden','rebel', '']) ;
ERROR:  lexeme array may not contain empty strings

PDF page 15

2.6.14 Xml2

The xml_is_well_formed function has been removed from the Contrib module xml2.

2.6.15 Pipeline mode

Removed support for PQsendQuery function in pipeline mode. Use the PQsendQueryParams function instead.

2.6.16 Extension

CREATE OR REPLACE statements for existing objects are disallowed unless they already belong to the extension.

PDF page 16