PostgreSQL 15 New Features

Contents

Reference indexes

The opening chapter includes the first numbered table, Table 1. The indexes below collect the book’s other native numbered components.

Source edition

The source chapter order, wording, examples, and publication credits are retained. PDF page markers link the reading edition to the original document. Source identity and coverage · About the collection

1 About This Document

PDF page 5

1.1 Purpose

The purpose of this document is to provide information about the major new features of open-source RDBMS PostgreSQL 15 (15.0).

1.2 Audience

This document is written for engineers who already know PostgreSQL, such as installation, basic management, etc.

1.3 Scope

This document describes the major difference between PostgreSQL 14 (14.5) and PostgreSQL 15 (15.0). As a general rule, this document examines the features of behavior change. This document does not describe and verify all new features. In particular, the following new features are not included.

• Bugfix

• Performance improvement by changing internal behavior

• Improvement of regression test

• Operability improvement by psql command tab input

• Improvement of pgbench command

• Improvement of documentation, modify typo in the source code

• Refactoring without a change in behavior

1.4 Software Version

The contents of this document have been verified for the following versions and platforms.

PDF page 6

1.5 The Question, comment, and Responsibility

The contents of this document are not an official opinion of Hewlett Packard Enterprise Japan, G.K. The author and affiliation company do not take any responsibility for the problem caused by the mistake of contents. If you have any comments for this document, please contact Noriyoshi Shinoda (noriyoshi.shinoda@hpe.com), Hewlett Packard Enterprise Japan, LLC.

1.6 Notation

This document contains examples of the execution of the command or SQL statement. Execution examples are described according to the following rules:

The syntax is described in the following rules:

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

3 Architecture

PDF page 16

3.1.1 Modified System catalogs

The following catalogs have been changed.

PDF page 17

Among the modified system catalogs, the details of the major catalogs are described below.

Pg_ident_file_mappings catalog

This is a view for searching the contents of the pg_ident.conf file from SQL statements.

PDF page 18

postgres=# SELECT * FROM pg_ident_file_mapping ORDER BY 1 ;
line_number | map_name | sys_name | pg_username | error
-------------+----------+----------+-------------+-------
          43 | map1     | sysname1 | pgname1     |
          44 | map2     | sysname2 | pgname2     |
(2 rows)

Pg_parameter_acl catalog

The catalog records configuration parameters for which privileges have been granted to one or more roles.

Pg_publication_namespace

Information is stored when the FOR TABLE IN SCHEMA clause is specified in the CREATE PUBLICATION statement. Provides a mapping between PUBLICATION and SCHEMA.

PDF page 19

postgres=> SELECT * FROM pg_publication_namespace ;
  oid  | pnpubid | pnnspid |
-------+---------+---------+
 16411 |   16410 |    2200 |
(1 row)

Pg_stat_recovery_prefetch view

Provides prefetched block statistics during recovery. It is also possible to check the status of replication processing in the standby instance of the streaming replication environment.

Pg_stat_subscription_stats view

The pg_stat_subscription_stats view contains error information about logical replication subscription workers.

PDF page 20

postgres=# SELECT * FROM pg_stat_subscription_stats ;
-[ RECORD 1 ]-----+------
subid             | 16395
subname           | sub1
apply_error_count | 0
sync_error_count  | 23
stats_reset       |

Reference to memory related view

The pg_backend_memory_contexts view and pg_shmem_allocations view can now be referenced not only by users with the SUPERUSER attribute but also by users belonging to the pg_read_all_stats role.

postgres=# GRANT pg_read_all_stats TO demo ;
GRANT ROLE
postgres=# \connect postgres demo
You are now connected to database "postgres" as user "demo".
postgres=> SELECT COUNT(*) FROM pg_backend_memory_contexts ;
 count
-------
   122
(1 row)

PDF page 21

3.1.2 Logical Replication Enhancements

The following features have been added to Logical Replication.

Column-specific replication

It is now possible to replicate only specific columns in a table. Specify a column list for the table name in the CREATE PUBLICATION or ALTER PUBLICATION statement. The target column number is stored in the prattrs column of the pg_publication_rel catalog.

postgres=> CREATE TABLE repl1(c1 INT PRIMARY KEY, c2 INT, c3 VARCHAR(10)) ;
CREATE TABLE
postgres=> CREATE PUBLICATION pub1 FOR TABLE repl1(c1, c2) ;
CREATE PUBLICATION
postgres=> SELECT oid, prpubid, prrelid, prattrs FROM pg_publication_rel ;
-[ RECORD 1 ]--
oid     | 16394
prpubid | 16393
prrelid | 16388
prattrs | 1 2

When executing an UPDATE or DELETE statement, the column specification must include all
replica identities (primary key, etc.). In the example below, the UPDATE statement is in error by
specifying a column that does not include the primary key in the publication.

postgres=> CREATE TABLE repl2(c1 INT PRIMARY KEY, c2 VARCHAR(10)) ;
CREATE TABLE
postgres=> CREATE PUBLICATION pub2 FOR TABLE repl2(c2) ;
CREATE PUBLICATION
postgres=> UPDATE repl2 SET c2='update' WHERE c1=100 ;
ERROR:  cannot update table "repl2"
DETAIL:  Column list used by the publication does not cover the replica
identity.

Row filtered replication

By specifying the WHERE clause in the CREATE PUBLICATION statement or the ALTER

PDF page 22

PUBLICATION statement, the tuples in the table can be limited and replicated. Replication conditions are specified for each table. The WHERE clause description must be enclosed in parentheses.

postgres=> ALTER PUBLICATION pub1 SET TABLE repl1 WHERE (c1 < 1000) ;
ALTER PUBLICATION

In order to execute UPDATE or DELETE statements on the table to be replicated, the WHERE clause
must include a part of the replica identity (primary key, etc.).

postgres=> CREATE TABLE repl3(c1 INT PRIMARY KEY, c2 VARCHAR(10)) ;
CREATE TABLE
postgres=> CREATE PUBLICATION pub3 FOR TABLE repl3 WHERE (c2='update') ;
CREATE PUBLICATION
postgres=> UPDATE repl3 SET c2='modify' WHERE c1=1000 ;
ERROR:  cannot update table "repl3"
DETAIL:  Column used in the publication WHERE expression is not part of the
replica identity.
postgres=> DELETE FROM repl3 WHERE c1=1000 ;
ERROR:  cannot delete from table "repl3"
DETAIL:  Column used in the publication WHERE expression is not part of the
replica identity.

The conditional statement that can be specified in the WHERE clause must be statically determined.
Therefore, user-defined functions and MUTABLE-specified functions cannot be specified.

postgres=> CREATE PUBLICATION pub4 FOR TABLE repl1 WHERE (c1 = random()) ;
ERROR:  invalid publication WHERE expression
LINE 1: ...EATE PUBLICATION pub4 FOR TABLE repl1 WHERE (c1 = random());
                                                             ^
DETAIL:  User-defined or built-in mutable functions are not allowed.

PDF page 23

The conditions specified in the WHERE clause are converted and stored in the prqual column of the pg_publication_rel catalog. The psql command can be checked with the \d command or the \dRp+ command.

postgres=> \d repl1
                      Table "public.repl1"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 c1     | integer               |           | not null |
 c2     | character varying(10) |           |          |
Indexes:
    "repl1_pkey" PRIMARY KEY, btree (c1)
Publications:
    "pub1" WHERE (c1 < 1000)

Specify all tables in the schema

The syntax is now available to register all tables in a particular schema with a PUBLICATION object. Specify the FOR TABLES IN SCHEMA clause and schema name in the CREATE PUBLICATION statement (or ALTER PUBLICATION statement). This syntax can only be executed by users with the SUPERUSER attribute. In the case of the FOR ALL TABLE clause that was available in the past, tables of all schemas were registered. Syntax

CREATE PUBLICATION publication_name FOR TABLES IN SCHEMA schema_name
ALTER PUBLICATION publication_name ADD TABLES IN SCHEMA schema_name

PDF page 24

postgres=# CREATE PUBLICATION pub5 FOR TABLES IN SCHEMA schema1 ;
CREATE PUBLICATION
postgres=# \dRp
                               List of publications
 Name |  Owner   | All tables | Inserts | Updates | Deletes | Truncates | …
------+----------+------------+---------+---------+---------+-----------+-…
pub5 | postgres | f          | t       | t       | t       | t         | f
(1 row)

The pg_publication_namespace catalog has been added with this implementation.

Skip LSN

It is now possible to specify the SKIP clause in the ALTER SUBSCRIPTION statement. Specify LSN in the SKIP clause. Execution of this statement allows the SUBSCRIPTION side of logical replication to skip transactions that caused synchronization errors. Syntax

ALTER SUBSCRIPTION subscription_name SKIP (LSN='lsn_value')

The skipped LSN can be obtained in the subskiplsn column of the pg_subscription catalog. The psql command added a Skip LSN entry to the output of the \dRs + command.

postgres=# ALTER SUBSCRIPTION sub1 SKIP (LSN = '0/30B51E0') ;
ALTER SUBSCRIPTION
postgres=# SELECT subskiplsn FROM pg_subscription ;
 subskiplsn
------------
 0/30B51E0
(1 row)

PDF page 25

Disable SUBSCRIPTION when an error occurs

The option disable_on_error can now be specified to disable SUBSCRIPTION if a replication error occurs. The default is false and no disabling is performed. The column subdisableonerr has been added to the pg_subscription catalog to indicate this option value.

postgres=# CREATE SUBSCRIPTION sub1 CONNECTION 'host=remsvr1, dbname=postgres'
PUBLICATION pub1 WITH (disable_on_error=true, two_phase=true) ;
NOTICE: created replication slot "sub1" on publisher
CREATE SUBSCRIPTION

TWO_PHASE option

The replication protocol command CREATE_REPILICATION_SLOT has been expanded and the TWO_PHASE option has been added. This makes it possible to decode PREPARE TRANSACTION, COMMIT PREPARED, ROLLBACK PREPARED, etc.

3.1.3 Parallel Query Enhancements

The SELECT DISTINCT statement can now work with parallel queries.

postgres=> EXPLAIN SELECT DISTINCT c2 FROM data1 ;
                                 QUERY PLAN
------------------------------------------------------------------------------
 Unique  (cost=11614.55..11614.56 rows=1 width=6)
   ->  Sort  (cost=11614.55..11614.56 rows=2 width=6)
         Sort Key: c2
         ->  Gather  (cost=11614.33..11614.54 rows=2 width=6)
               Workers Planned: 2
               ->  HashAggregate  (cost=10614.33..10614.34 rows=1 width=6)
                     Group Key: c2
                     ->  Parallel Seq Scan on data1  (cost=0.00..9572.67 ro…
(8 rows)

PDF page 26

3.1.4 WAL compression

It is now possible to specify LZ4 and Zstandard as the compression method for the full-page image output to WAL. Change the parameter wal_compression to perform WAL compression. The setting value on, which is the conventional compression method, is an alias for the setting value pglz. The default value remains off without any change.

postgres=# SELECT name, setting, vartype, enumvals FROM pg_settings
WHERE name='wal_compression' ;
-[ RECORD 1 ]---------------
name     | wal_compression
setting  | off
vartype  | enum
enumvals | {pglz,lz4,zstd,on,off}

To use value lz4 or zstd, it must be compiled with --with-lz4 and --with-zstd specified when executing
'configure' command.

3.1.5 Archive Library

It is now possible to use a shared library in addition to the traditional command (archive_command parameter) as a way to create an archive of WAL files. The archive_library parameter has been added to specify the archive library name. If archive_library is not set, the archive_command parameter can still be used. The Contrib module basic_archive has been added as a standard archive library. This Contrib module does not require the execution of the CREATE EXTENSION statement. The basic_archive.archive_directory parameter can be specified as the archive destination directory.

PDF page 27

postgres=# SELECT * FROM pg_settings WHERE name LIKE 'archive_library' ;
-[ RECORD 1 ]---+-------------------------------------------------------------
name            | archive_library
setting         | basic_archive
unit            |
category        | Write-Ahead Log / Archiving
short_desc      | Sets the library that will be called to archive a WAL file.
extra_desc     | An empty string indicates that "archive_command" should be
used.
context         | sighup
vartype         | string
source          | configuration file
min_val         |
max_val         |
enumvals        |
boot_val        |
reset_val       | basic_archive
sourcefile      | /home/postgres/data/postgresql.conf
sourceline      | 255
pending_restart | f
postgres=# SHOW basic_archive.archive_directory ;
-[ RECORD 1 ]-------------------+----------------------
basic_archive.archive_directory | /home/postgres/arch

If both archive_library and archive_command are specified, archive_library is used.

3.1.6 Global Locale Provider

It is now possible to specify an ICU as the global locale provider. Allows ICU to be used as the default locale provider for the entire database cluster or database. You must use a binary compiled with the –with-icu option to use this feature. The –locale-provider option that specifies the locale provider name can be specified for the initdb command and createdb command. Specify libc or icu for this option. The –icu-locale option allows you to specify the ICU locale name.

PDF page 28

$ initdb --locale-provider=icu --icu-locale=en --encoding=utf8 -D data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with this locale configuration:
  provider:    icu
  ICU locale:  en
  LC_COLLATE:  en_US.utf8
  LC_CTYPE:    en_US.utf8
  LC_MESSAGES: en_US.utf8
  LC_MONETARY: en_US.utf8
  LC_NUMERIC:  en_US.utf8
  LC_TIME:     en_US.utf8
The default text search configuration will be set to "english".
…

Added LOCALE_PROVIDER to specify the locale provider and ICU_LOCALE to specify the locale
name to the options of the CREATE DATABASE statement.

postgres=# CREATE DATABASE icudb1 LOCALE_PROVIDER=icu ICU_LOCALE=en
TEMPLATE=template0 ;
CREATE DATABASE
postgres=# SELECT datname, daticulocale FROM pg_database
WHERE datname='icudb1' ;
 datname | daticulocale
---------+--------------
 icudb1  | en
(1 row)

The psql command outputs ICU Locale and Locale Provider in the output of the \l command.

PDF page 29

postgres=> \l
                                                List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    | ICU Locale | Locale Provider…
-----------+----------+----------+------------+------------+------------+--------------- …
postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | en         | icu             …
template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | en         | icu             …
template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | en         | icu             …
(3 rows)

3.1.7 Statistics Information

In previous versions, the stats collector process received UDP protocol packets and periodically wrote them to temporary files. PostgreSQL 15 now stores statistics in shared memory. Information on the shared memory area for statistics can be obtained by searching the pg_shmem_allocations view and pg_backend_memory_contexts view. The stats collector process has been deprecated as a result of this specification change.

postgres=# SELECT * FROM pg_shmem_allocations WHERE name LIKE 'Shared%Stats' ;
        name         |    off    |  size  | allocated_size
---------------------+-----------+--------+----------------
 Shared Memory Stats | 147331584 | 263312 |         263424
(1 row)
postgres=# SELECT name, total_bytes, free_bytes, used_bytes FROM
pg_backend_memory_contexts WHERE name LIKE 'PgStat%' ;
          name          | total_bytes | free_bytes | used_bytes
------------------------+-------------+------------+------------
 PgStat Shared Ref Hash |        7224 |        680 |       6544
 PgStat Shared Ref      |        4096 |       1432 |       2664
 PgStat Pending         |        4096 |       2168 |       1928
(3 rows)

PDF page 30

3.1.8 GiST Index

The following enhancements have been implemented to the GiST index:

Index statistics

Access to the SP-GiST index is now counted in the pg_stat_ * views.

BOOL type

It is now possible to create an index for a Boolean type.

postgres=> CREATE EXTENSION btree_gist ;
CREATE EXTENSION
postgres=> CREATE TABLE gist1(c1 INT, c2 BOOL, c3 VARCHAR(10)) ;
CREATE TABLE
postgres=> CREATE INDEX idx1_gist1 ON gist1 USING gist (c2) ;
CREATE INDEX

3.1.9 Wait Events

The following wait events have been added.

PDF page 31

The wait events PgStatMain, LogicalChangesRead, LogicalChangesWrite, LogicalSubxactRead, and LogicalSubxactWrite have been removed.

3.1.10 Role

The predefined role pg_checkpoint has been added. This role holder can execute the CHECKPOINT statement.

postgres=# CREATE USER check1 PASSWORD 'check1' ;
CREATE ROLE
postgres=# \connect postgres check1
You are now connected to database "postgres" as user "check1".
postgres=> CHECKPOINT ;
ERROR:  must be superuser or have privileges of pg_checkpoint to do CHECKPOINT
postgres=> \connect postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# GRANT pg_checkpoint TO check1 ;
GRANT ROLE
postgres=# \connect postgres check1
You are now connected to database "postgres" as user "check1".
postgres=> CHECKPOINT ;
CHECKPOINT

3.1.11 Libpq

The following enhancements have been implemented in the Libpq API:

Environment variable PG_COLORS

It is now possible to specify ’note’ in the color specification item of the terminal output.

PQsslAttribute

The PQsslAttribute(NULL, “library”) function can now be executed to obtain the name of the library in use.

PDF page 32

PQcancel

The connection string tcp_user_timeout and keepalives are now available for executing the PQcancel API.

Allow root-owned SSL private keys in libpq

Libpq applies the same private key file ownership and permission checks as used on the backend. The private key can be owned by either the current user or the root

3.1.12 Custom WAL resource manager

The custom WAL resource managers can now be registered for the shared_preload_libraries parameter. The pg_get_wal_resource_managers function has been added to check the current WAL resource managers.

postgres=# SELECT * FROM pg_get_wal_resource_managers() ;
 rm_id |      rm_name      | rm_builtin
-------+-------------------+------------
     0 | XLOG              | t
     1 | Transaction       | t
     2 | Storage           | t
     3 | CLOG              | t
…

3.1.13 Hook

The following hooks have been added.

String object access

Added an access hook for string objects. Executed when accessing the GUC string.

PDF page 33

typedef void (*object_access_hook_type_str) (ObjectAccessType access,
                                        Oid classId,
                                        const char *objectStr,
                                        int subId,
                                        void *arg);
extern PGDLLIMPORT object_access_hook_type_str object_access_hook_str;

Shared memory request

It must be specified from within the _PG_init function when the extension module gets the shared memory.

typedef void (*shmem_request_hook_type) (void);
extern PGDLLIMPORT shmem_request_hook_type shmem_request_hook;
extern PGDLLIMPORT bool process_shmem_requests_in_progress;

Object access hook

Execution of the ALTER TABLE SET {LOGGED, UNLOGGED, ACCESS METHOD} statement now executes an object access hook.

3.1.14 Custom scan providers

The custom scan providers can now choose to support projection. In previous versions, all custom scan providers needed projection support. Control is performed by the CUSTOMPATH_SUPPORT_PROJECTION macro.

3.1.15 Build target

The target name of Makefile to be specified when building from source code has been added. The following targets do not build or install documentation.

• world-bin

• install-world-bin

PDF page 34

This specification will also be backported to older versions.

3.1.16 Dynamic Shared Memory

Dynamic Shared Memory (DSM) is now also available in single user mode.

4 SQL Statements

PDF page 35

This section explains new features related to SQL statements.

3.2.1 NUMERIC data type

Negative numbers can now be specified for NUMERIC type scales. If a negative number is specified, it will be rounded to the specified digit. Also, the range of values that can be specified for the scale has been extended from -1,000 to 1,000.

postgres=> CREATE TABLE type1( c1 NUMERIC(5, -2) ) ;
CREATE TABLE
postgres=> INSERT INTO type1 VALUES (12345.678) ;
INSERT 0 1
postgres=> INSERT INTO type1 VALUES (1234567.89) ;
INSERT 0 1
postgres=> INSERT INTO type1 VALUES (12345678.901) ;
ERROR:  numeric field overflow
DETAIL:  A field with precision 5, scale -2 must round to an absolute value
less than 10^7.
postgres=> INSERT INTO type1 VALUES (-1234567.89) ;
INSERT 0 1
postgres=> SELECT * FROM type1 ;
    c1
----------
    12300
  1234600

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=085f931f52494e1f304e35571924efa

 -1234600

6fcdc2b44

(3 rows)

PDF page 36

3.2.2 ALTER DATABASE

The syntax for tracking the version of a collation has been added to the ALTER DATABASE statement. Syntax

ALTER DATABASE database_name REFRESH COLLATION VERSION

Added a new function pg_database_collation_actual_version to get the collation version and a datcollversion column for the version in the pg_database view. If it detects that the collation version of the database is different from the operating system, a warning will be output when connecting to the corresponding database. Below is an example of migration from Red Hat Enterprise Linux 7.8 to Red Hat Enterprise Linux 8.6.

$ psql demodb
WARNING:  database "demodb" has a collation version mismatch
DETAIL:  The database was created using collation version 58.0.6.50, but the
operating system provides version 153.80.
HINT:  Rebuild all objects in this database that use the default collation and
run ALTER DATABASE demodb REFRESH COLLATION VERSION, or build PostgreSQL with
the right library version.
psql (15.0)
Type "help" for help.
demodb=# ALTER DATABASE demodb REFRESH COLLATION VERSION ;
NOTICE:  changing version from 58.0.6.50 to 153.80
ALTER DATABASE

3.2.3 ALTER TABLE

The SET ACCESS METHOD clause can now be specified in ALTER TABLE statements that change the access method for a table; the SET ACCESS METHOD clause can also be specified in ALTER MATERIALIZED VIEW statements.

PDF page 37

Syntax

• ALTER TABLE table_name SET ACCESS METHOD method_name

• ALTER MATERIALIZED VIEW view_name SET ACCESS METHOD method_name

postgres=> ALTER TABLE data1 SET ACCESS METHOD heap ;
ALTER TABLE

A table_rewrite trigger is fired when you execute an ALTER TABLE SET ACCESS METHOD
statement on a materialized view.

3.2.4 COPY

The following enhancements have been added to the COPY statement.

COPY TO statement

The format ’text’ and header ’true’ can now be used simultaneously in the COPY TO statement options. At the same time, the file_fdw extension can now enable headers in text format.

postgres=# CREATE EXTENSION file_fdw ;
CREATE EXTENSION
postgres=# CREATE SERVER textsvr FOREIGN DATA WRAPPER file_fdw ;
CREATE SERVER
postgres=# CREATE FOREIGN TABLE text1(c1 INT, c2 VARCHAR(10)) SERVER textsvr
OPTIONS (filename '/tmp/data.txt', format 'text', header 'true') ;
CREATE FOREIGN TABLE
postgres=# COPY data1 TO '/home/postgres/data1.txt' (FORMAT text, HEADER true) ;
COPY 100
postgres=# \! cat data1.txt
c1      c2
1       data1
…

PDF page 38

COPY FROM statement

The HEADER option can now be specified as ‘match’. When this option is specified, COPY FROM checks that the header row and column names match when the COPY FROM statement is executed. In the example below, the first row of the CSV file and the column names in the table are different, resulting in an error.

postgres=# \! cat /tmp/csvlog1.csv
c1,c3
1,data1
2,data2
3,data3
postgres=# COPY csvlog1 FROM '/tmp/csvlog1.csv' WITH
(format 'csv', header 'match') ;
ERROR:  column name mismatch in header line field 2: got "c3", expected "c2"
CONTEXT:  COPY csvlog1, line 1: "c1,c3"

3.2.5 CLUSTER

The CLUSTER statements can now be executed on partitioned tables.

postgres=> CREATE TABLE part1 (c1 INT PRIMARY KEY, c2 VARCHAR(10))
PARTITION BY LIST(c1) ;
CREATE TABLE
postgres=> CLUSTER part1 USING part1_pkey ;
CLUSTER

3.2.6 CREATE DATABASE

The following enhancements have been implemented for the CREATE DATABASE statement

ICU Locale

The CREATE DATABASE statement now includes a LOCALE_PROVIDER clause to specify the locale provider, an ICU_LOCALE clause to specify the ICU locale, and a COLLATION_VERSION clause to specify the collation version.

PDF page 39

postgres=# CREATE DATABASE icudb1 LOCALE_PROVIDER=icu ICU_LOCALE=en
COLLATION_VERSION='58.0.6.50' TEMPLATE=template0 ;
CREATE DATABASE

STRATEGY clause

The STRATEGY clause has been added to the CREATE DATABASE statement to specify how the database should be created; specifying WAL_LOG in the STRATEGY clause will cause the new database to be copied from the template block by block, with information for each block output to WAL. This is an effective method when the template is small and is the default value. Specify FILE_COPY to perform the behavior of the previous version.

postgres=# CREATE DATABASE strategydb1 STRATEGY WAL_LOG ;
CREATE DATABASE

The option --strategy, which corresponds to the STRATEGY clause, has also been added to the
createdb command.

OID clause

The object ID (OID) of the database to be created can now be specified; the OID can be any free number greater than 16384.

postgres=# CREATE DATABASE oiddb1 OID=17000 ;
CREATE DATABASE

3.2.7 CREATE TABLE

Column lists can now be specified in the ON DELETE SET NULL and SET DEFAULT clauses of foreign key definitions in CREATE TABLE and ALTER TABLE statements. In the previous version, it was not possible to specify columns. The confdelsetcols column has been added to the pg_constraint catalog.

PDF page 40

postgres=> CREATE TABLE pktable1 (
   tid INT,
   id INT,
   name1 VARCHAR(10), PRIMARY KEY (tid, id)) ;
CREATE TABLE
postgres=> CREATE TABLE fktable1 (
   tid INT,
   id INT,
   fk_id_del_set_null INT,
   fk_id_del_set_default INT DEFAULT 0,
   FOREIGN KEY (tid, fk_id_del_set_null) REFERENCES pktable1
ON DELETE SET NULL (fk_id_del_set_null),
   FOREIGN KEY (tid, fk_id_del_set_default) REFERENCES pktable1
ON DELETE SET DEFAULT (fk_id_del_set_default)
) ;
CREATE TABLE

3.2.8 CREATE UNIQUE INDEX

It is now possible to specify NULLS DISTINCT and NULLS NOT DISTINCT for the unique index attribute. Multiple null values can be stored in a unique index with the NULLS DISTINCT clause. If nothing is specified, the NULLS DISTINCT specification is the default. An index with the NULLS NOT DISTINCT clause cannot contain multiple null values. An indnullsnotdistinct column has been added to the pg_index catalog to indicate this attribute. Syntax

• CREATE UNIQUE INDEX index_name ON … NULLS [NOT] DISTINCT

• CONSTRAINT constraint_name NULLS [NOT] DISTINCT

PDF page 41

postgres=> CREATE TABLE data1(c1 INT, c2 INT, c3 VARCHAR(10)) ;
CREATE TABLE
postgres=> CREATE UNIQUE INDEX idx1_data1 ON data1(c1) NULLS DISTINCT ;
CREATE INDEX
postgres=> CREATE UNIQUE INDEX idx2_data1 ON data1(c2) NULLS NOT DISTINCT ;
CREATE INDEX
postgres=> INSERT INTO data1 VALUES (1, 1, 'data1') ;
INSERT 0 1
postgres=> INSERT INTO data1 VALUES (2, NULL, 'data2') ;
INSERT 0 1
postgres=> INSERT INTO data1 VALUES (NULL, 3, 'data3') ;
INSERT 0 1
postgres=> INSERT INTO data1 VALUES (NULL, NULL, 'data4') ;
ERROR:  duplicate key value violates unique constraint "idx2_data1"
DETAIL:  Key (c2)=(null) already exists.
postgres=> INSERT INTO data1 VALUES (NULL, 5, 'data5') ;
INSERT 0 1

Similar specifications can be made for the unique constraints of the CREATE TABLE and ALTER
TABLE statements.

postgres=> CREATE TABLE const1(c1 INT UNIQUE NULLS NOT DISTINCT, c2 INT) ;
CREATE TABLE
postgres=> ALTER TABLE const1 ADD CONSTRAINT uniq1 UNIQUE NULLS DISTINCT (c2) ;
ALTER TABLE

3.2.9 CREATE SEQUENCE

The UNLOGGED clause can now be specified in the CREATE SEQUENCE statement to suppress WAL output. The sequence created by the CREATE UNLOGGED SEQUENCE statement has the repersistence column in the pg_class catalog as ‘u’. The attributes of the sequence can be changed with the ALTER SEQUENCE SET LOGGED statement or the ALTER SEQUENCE SET UNLOGGED statement.

PDF page 42

postgres=> CREATE UNLOGGED SEQUENCE seq1 ;
CREATE SEQUENCE
postgres=> \d seq1
                       Unlogged sequence "public.seq1"
  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
 bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1
postgres=> SELECT relname, relpersistence FROM pg_class WHERE relname = 'seq1' ;
 relname | relpersistence
---------+----------------
 seq1    | u
(1 row)
postgres=> ALTER SEQUENCE seq1 SET LOGGED ;
ALTER SEQUENCE

If the instance crashes, UNLOGGED SEQUENCE will be reset to its default value.

3.2.10 CREATE VIEW

The security_invoker option has been added to the CREATE VIEW statement. By default, access to the table from which the view is based is enforced with the privileges of the view owner. When the security_invoker option is set to True for a view, access to the table is subject to the privileges of the user accessing the view. This option can also be specified in the ALTER VIEW statement. Syntax

• CREATE VIEW view_name WITH (security_invoker = true|false) AS sql_clause

• ALTER VIEW view_name SET (security_invoker = true|false)

• ALTER VIEW view_name RESET (security_invoker)

postgres=> CREATE VIEW view1 WITH(security_invoker) AS SELECT * FROM data1 ;
CREATE VIEW

PDF page 43

3.2.11 EXPLAIN

I/O time for temporary files is now output when BUFFERS and VERBOSE are specified in JSON format.

postgres=# SET track_io_timing = on ;
SET
postgres=# EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON)
SELECT * FROM data1 ;
                     QUERY PLAN
----------------------------------------------------
 [                                                 +
…
       "I/O Write Time": 0.000,     +
       "Temp I/O Read Time": 0.000, +
       "Temp I/O Write Time": 0.000 +
}
…

3.2.12 GRANT

Roles can now be granted permission to change specific parameters through the ALTER SYSTEM statement. Syntax

GRANT {ALTER SYSTEM | SET} ON PARAMETER parameter_name TO role_spec
REVOKE {ALTER SYSTEM | SET} ON PARAMETER parameter_name FROM role_spec

PDF page 44

postgres=# GRANT ALTER SYSTEM ON PARAMETER log_statement TO demo ;
GRANT
postgres=# SELECT * FROM pg_parameter_acl ;
-[ RECORD 1 ]-----------------------------------
oid     | 16385
parname | log_statement
paracl  | {postgres=sA/postgres,demo=A/postgres}
postgres=# \connect postgres demo
You are now connected to database "postgres" as user "demo".
postgres=> ALTER SYSTEM SET log_statement = 'all' ;
ALTER SYSTEM

3.2.13 MERGE

The MERGE statement that simultaneously executes INSERT / DELETE / UPDATE statements on a table from a join condition is now supported. Tuples matching the join condition can be specified in the WHEN MATCHED clause to update (UPDATE) or delete (DELETE) or do nothing (DO NOTHING). Additional conditions may be specified. The behavior of tuples that do not match the condition is specified in the WHEN NOT MATCHED clause. Syntax

MERGE INTO target_table [AS alias]
USING source_table [AS alias]
ON join_clause
WHEN MATCHED [AND condition] THEN
   UPDATE SET … | DELETE | DO NOTHING
WHEN NOT MATCHED
INSERT VALUES … | DO NOTHING

PDF page 45

postgres=> MERGE INTO dst1 AS d USING src1 AS s ON d.c1 = s.c1
WHEN MATCHED THEN
UPDATE SET c2 = s.c2
WHEN NOT MATCHED THEN
INSERT VALUES (s.c1, s.c2) ;
MERGE 2
postgres=> MERGE INTO dst1 AS d USING src1 AS s ON d.c1 = s.c1
WHEN MATCHED AND s.c1 < 1000 THEN
DELETE
WHEN NOT MATCHED THEN
DO NOTHING ;
MERGE 2

The MERGE statement cannot be used in the WITH clause or at the same time as the COPY statement.

postgres=> WITH data1 AS (
MERGE INTO dst1 USING src1 ON (true)
WHEN MATCHED THEN DELETE
) SELECT * FROM data1 ;
ERROR:  MERGE not supported in WITH query
LINE 1: WITH data1 AS (
^

3.2.14 VACUUM

The execution log of the VACUUM VERBOSE statement has changed significantly. Average read rate, buffer usage, WAL output information, etc. have been added. Details on how pg_class.relfrozenxid and pg_class.relminmxid have progressed are also now reported.

PDF page 46

postgres=> VACUUM VERBOSE data1 ;
INFO:  vacuuming "postgres.public.data1"
INFO:  finished vacuuming "postgres.public.data1": index scans: 1
pages: 0 removed, 5406 remain, 5406 scanned (100.00% of total)
tuples: 500000 removed, 500000 remain, 0 are dead but not yet removable
removable cutoff: 757, which was 0 XIDs old when operation ended
…
avg read rate: 0.000 MB/s, avg write rate: 0.068 MB/s
buffer usage: 18975 hits, 0 misses, 1 dirtied
WAL usage: 18953 records, 0 full page images, 4007392 bytes
system usage: CPU: user: 0.11 s, system: 0.00 s, elapsed: 0.11 s
INFO:  vacuuming "postgres.pg_toast.pg_toast_32787"
…

3.2.15 Functions

The following functions have been added/extended.

MAX / MIN

MAX function and MIN function for xid8 type have been implemented.

Replication related functions

The following function to get replication information has been added. These functions can be executed by a user with the SUPERUSER attribute or the pg_monitor role.

PDF page 47

postgres=# SELECT pg_create_physical_replication_slot('slot1') ;
 pg_create_physical_replication_slot
-------------------------------------
 (slot1,)
(1 row)
postgres=# SELECT * FROM pg_ls_replslotdir('slot1') ;
 name  | size |      modification
-------+------+------------------------
 state |  200 | 2022-10-14 09:52:04+09
(1 row)
postgres=# SELECT * FROM pg_ls_logicalsnapdir() ;
      name      | size |      modification
----------------+------+------------------------
 0-3005558.snap |  128 | 2022-10-14 00:01:29+09
(1 row1)
postgres=# SELECT * FROM pg_ls_logicalmapdir() ;
 name | size | modification
------+------+--------------
(0 rows)

Internally, it uses the READ_REPLICATION_SLOT command to read the replication slot
information.

Pg_log_backend_memory_contexts

The check for SUPERUSER permission has been removed, and it can now be used by general users by granting permission using the GRANT statement.

Regular expression functions

The regexp_count, regexp_instr, and regexp_substr have been added as functions that handle regular expressions. Parameters that can be specified have been added to the regexp_replace function.

PDF page 48

Syntax

• integer REGEXP_COUNT (string text, pattern text [, start integer [, flags

text ]])

• integer REGEXP_INSTR (string text, pattern text [, start integer [, N integer

[, endpos integer [, flags text [, subexpr integer ]]]]])

• boolean REGEXP_LIKE (string text, pattern text [, flags text ])

• text REGEXP_REPLACE (string text, pattern text, replace text start integer,

N integer [, flags text])

• text REGEXP_SUBSTR (string text, pattern text [, start integer [, N intger

[, flags text [, subexpr integer ]]]])

PDF page 49

postgres=> SELECT regexp_count('ABCABC', 'Abc', 1, 'i') ;
-[ RECORD 1 ]+--
regexp_count | 2
postgres=> SELECT regexp_like('PostgreSQL', 'Postgre(s|S)QL') ;
-[ RECORD 1 ]--
regexp_like | t
postgres=> SELECT regexp_instr('ABCDEFGHT', 'D.F') ;
-[ RECORD 1 ]+--
regexp_instr | 4
postgres=> SELECT regexp_replace('PostgreSQL', 'a|e|i|o|u', 'X', 1, 2) ;
-[ RECORD 1 ]--+-----------
regexp_replace | PostgrXSQL
postgres=> SELECT regexp_substr('ABCDEFGHT', 'D.F', 2) ;
-[ RECORD 1 ]-+----
regexp_substr | DEF

Unnest

The MULTIRANGE type can now be specified for UNNEST functions. Syntax

setof anyrange UNNNEST(anymultirange)

postgres=> SELECT unnest(int4multirange(int4range('5', '6'), int4range('1',
'2'))) ;
 unnest
--------
 [1,2)
 [5,6)
(2 rows)

PDF page 50

Pg_size_bytes / pg_size_pretty

It now supports up to petabytes.

postgres=> SELECT pg_size_pretty(pg_size_bytes('10.5 PB')) ;
 pg_size_pretty
----------------
 11 PB
(1 row)

Starts_with

The starts_with function can now use BTREE indexes. At the same time, the ^@ operator will do the same.

postgres=> EXPLAIN SELECT * FROM data1 WHERE c2 ^@ '999' ;
                                  QUERY PLAN
------------------------------------------------------------------------------
 Index Scan using idx2_data1 on data1  (cost=0.42..1337.98 rows=100 width=12)
   Index Cond: ((c2 >= '999'::text) AND (c2 < '99:'::text))
   Filter: (c2 ^@ '999'::text)
(3 rows)
postgres=> EXPLAIN SELECT * FROM data1 WHERE starts_with(c2, '999') ;
                                  QUERY PLAN
------------------------------------------------------------------------------
 Index Scan using idx2_data1 on data1  (cost=0.42..1337.98 rows=100 width=12)
   Index Cond: ((c2 >= '999'::text) AND (c2 < '99:'::text))
   Filter: starts_with(c2, '999'::text)
(3 rows)

Pg_settings_get_flags

Attribute information of GUC is returned as an array of text. If a non-existent GUC name is specified, NULL is returned.

PDF page 51

postgres=> SELECT * FROM pg_settings_get_flags('session_authorization') ;
          pg_settings_get_flags
------------------------------------------
 {NO_RESET_ALL,NO_SHOW_ALL,NOT_IN_SAMPLE}
(1 row)
postgres=> SELECT * FROM pg_settings_get_flags('bad_guc_name') ;
 pg_settings_get_flags
-----------------------
null
(1 row)

Time specified character string

OF, TZH and TZM can now be used in lowercase.

postgres=> SELECT to_char(current_date, 'of / tzh / tzm') ;
    to_char
----------------
 +09 / +09 / 00
(1 row)

Poly_distance

The poly_distance function has been implemented. This function existed in previous versions, but when executed it returned an ERRCODE_FEATURE_NOT_SUPPORTED error.

postgres=> SELECT
poly_distance('((10, 10),(20,20))'::polygon, '((30,30),(40,40))'::polygon) ;
   poly_distance
--------------------
 14.142135623730951
(1 row)

5 Configuration Parameters

PDF page 52

In PostgreSQL 15 the following parameters have been changed.

3.3.1 Added parameters

The following parameters have been added.

allow_in_place_tablespaces

Tablespaces can be created in a database cluster by setting this parameter to on and specifying an empty string (’’) in the LOCATION clause of the CREATE TABLESPACE statement.

PDF page 53

postgres=# SET allow_in_place_tablespaces = on ;
SET
postgres=# CREATE TABLESPACE ts1 LOCATION '' ;
CREATE TABLESPACE
postgres=# \! ls -l data/pg_tblspc/
total 0
drwx------. 3 postgres 29 Oct 14 14:16 17153

3.3.2 Modified Parameters

The following parameters have been changed in terms of setting range and choices.

JSON format log file

If jsonlog is specified for the parameter log_destination, the log file will be output in JSON format. The file extension is .json. A file with a .log extension is also created that outputs only some information.

PDF page 54

postgres=> SHOW log_destination ;
 log_destination
-----------------
 jsonlog
(1 row)
postgres=> \! cat postgresql-2022-10-14_141810.json
"timestamp":"2022-10-14
14:18:10.657
JST","pid":14876,"session_id":"6348f112.3a1c","line_num":1,"session_start":"20
22-10-14 14:18:10 JST","txid":0,"error_severity":"LOG","message":"ending log
output to stderr","hint":"Future log output will go to log destination
\"jsonlog\".","backend_type":"postmaster","query_id":0}
{"timestamp":"2022-10-14
14:18:10.657
JST","pid":14876,"session_id":"6348f112.3a1c","line_num":2,"session_start":"20
22-10-14
14:18:10
JST","txid":0,"error_severity":"LOG","message":"starting
PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514
(Red Hat 8.5.0-10), 64-bit","backend_type":"postmaster","query_id":0}

3.3.3 Parameters with default values changed

The following parameters have changed default values.

3.3.4 Removed parameter

The following parameter have been removed.

PDF page 55

3.3.5 Error when changing parameters

Some extension modules (auto_explain, pg_prewarm, pg_stat_statements, plperl, plpgsql) now generate errors when attempting to set parameters that do not exist after loading the module.

postgres=> CREATE FUNCTION getwork() RETURNS TEXT AS $$
BEGIN
  RETURN current_setting('work_mem') ;
END ;
$$ LANGUAGE plpgsql ;
CREATE FUNCTION
postgres=> SET plpgsql.bad_parameter_name TO false ;
ERROR:  invalid configuration parameter name "plpgsql.bad_parameter_name"
DETAIL:  "plpgsql" is a reserved prefix.

6 Utilities

PDF page 56

Describes the major enhancements of utility commands.

3.4.1 Configure

Added –with-zstd option to support Zstandard compression method. Zstandard 1.4.0 and above are supported.

$ ./configure --help | grep -i zstd
  --with-zstd             build with ZSTD support
  ZSTD_CFLAGS C compiler flags for ZSTD, overriding pkg-config
  ZSTD_LIBS   linker flags for ZSTD, overriding pkg-config

3.4.2 Psql

The following enhancements have been implemented in the psql command.

Environment variable PSQL_WATCH_PAGER

The environment variable PSQL_WATCH_PAGER, which specifies the pager to be displayed when the \watch command is executed, is now available.

\Dconfig command

The \dconfig command can display the parameter settings of the instance. If no option is specified, a list of parameters that have changed from the default values is displayed. If a parameter name is specified, the specific parameter setting value can be checked. Wildcards can be used for parameter names. Syntax

\dconfig [parameter_name]

PDF page 57

postgres=> \dconfig work_mem
List of configuration parameters
 Parameter | Value
-----------+-------
 work_mem  | 4MB
(1 row)
postgres=> \dconfig log_*connect*
List of configuration parameters
     Parameter      | Value
--------------------+-------
 log_connections    | off
 log_disconnections | off
(2 rows)
postgres=> \dconfig+ port
               List of configuration parameters
 Parameter | Value |  Type   |  Context   | Access privileges
-----------+-------+---------+------------+-------------------
 port      | 5432  | integer | postmaster |
(1 row)

\Getenv command

Added the \getenv command to get environment variables. Specify the psql variable name and environment variable name to store the variable value. Specifying an environment variable name that does not exist does not cause an error. Syntax

\getenv psql_variable_name Environment_variable_name

PDF page 58

postgres=> \getenv home
postgres=> \echo :home
/home/postgres
postgres=> \getenv val BADENV
postgres=> \echo :val
:val

Comments in SQL statements

Comments with two hyphen (-) in SQL statements are now sent to the backend. Previously it was removed before sending to the backend.

Large Object output

Access privileges are now output to the \dl+ command and \lo_list+ command.

postgres=> \lo_import 'test.dat'
lo_import 16409
postgres=> GRANT ALL ON LARGE OBJECT 16409 TO PUBLIC ;
GRANT
postgres=> \dl+
                  Large objects
  ID   | Owner | Access privileges | Description
-------+-------+-------------------+-------------
 16409 | demo  | demo=rw/demo     +|
       |       | =rw/demo          |
(1 row)
postgres=> \lo_list+
                  Large objects
  ID   | Owner | Access privileges | Description
-------+-------+-------------------+-------------
 16409 | demo  | demo=rw/demo     +|
       |       | =rw/demo          |
(1 row)

PDF page 59

Specifying a database name

When specifying a table name with the \d command, etc., use “database_name.schema_name.table_name” format is allowed. However, the “database_name” part is valid only for the currently connected database. If other database names are specified, an error message will be displayed.

postgres=> CREATE TABLE public.data1(c1 INT, c2 VARCHAR(10)) ;
CREATE TABLE
postgres=> \d postgres.public.data1
                      Table "public.data1"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 c1     | integer               |           |          |
 c2     | character varying(10) |           |          |
postgres=> \d demodb.public.data1
cross-database references are not implemented: demodb.public.data1
postgres=>

Output of multiple SQL

PostgreSQL 15 displays all results if the server returns multiple result sets. This behavior can be changed with the “\set SHOW_ALL_RESULTS off” command.

3.4.3 Pg_amcheck

The –install-missing option has been added. If this option is specified, the amcheck extension will be installed automatically if it is not installed.

3.4.4 Pg_basebackup

The following options have been added / extended to the pg_basebackup command.

–Target option

The –target option to specify the output destination has been added. This option uses a new data transfer protocol (BASE_BACKUP TARGET). The following formats can be specified Specifying

PDF page 60

server as the output destination allows base backups to be output to a server running a PostgreSQL instance. Server-side backups can be performed by a user with the SUPERUSER or pg_write_server_files roles.

Target Description

The –wal-method option must be set to “fetch” or “none” to make this specification. When “shell” is specified in the –target option, it is necessary to set the Contrib module basebackup_to_shell.

–Compress option

The –compress option can now specify the compression method and compression level, whereas the previous version specified the compression level. The compression level is specified by specifying “level=number” after a colon (:); for Zstandard compression, the “workers=number” option for parallel processing can also be set. The same notation as in previous versions is also available for compatibility. If server-side compression (server-gzip) is specified for plain format (-Fp), only the transferred data will be compressed and decompressed and stored on the client side. This setting is useful when network bandwidth is small.

PDF page 61

$ pg_basebackup --compress=none --format=tar -D back.1
$ pg_basebackup --compress=zstd:level=9 --format=tar -D back.2

3.4.5 Pg_dump

The following enhancements have been implemented for the pg_dump command.

–No-table-access-method option

The –no-table-access-method option can now be specified to remove table access methods from the output DDL. This option is ignored if the dump file is output in non-text format. The same option can also be specified for the pg_dumpall and pg_restore commands.

Consideration of TOAST data volume

The pg_dump command takes into account the table size when running in parallel mode; PostgreSQL 15 now also takes into account the amount of TOAST data.

3.4.6 Pg_recvlogical

The –two-phase (or -t) option has been added to the pg_recvlogical command to support the addition of the TWO_PHASE option to the CREATE_REPILICATION_SLOT command for the replication protocol.

$ pg_recvlogical --create-slot --slot=slot1 --two-phase --dbname=postgres
$ psql
postgres=# SELECT two_phase FROM pg_replication_slots WHERE slot_name='slot1' ;
 two_phase
-----------
 t

3.4.7 Pg_receivewal

The –compress option, which specifies how to compress the logs, has changed. Compression method, level, etc. can be specified.

PDF page 62

Syntax

pg_receivewal --compress=METHOD[:DETAIL]
METHOD = {'gzip', 'none', 'lz4'}
DETAIL = level=[1-9]

Valid values for compression are gzip, lz4 or none. An optional compression level (level) can be specified. The range of compression levels is 1-9.

$ pg_receivewal -D wal --compress=lz4:level=9
^Cpg_receivewal: not renaming "000000010000000000000028.lz4.partial", segment
is not complete
$ ls wal
000000010000000000000022.lz4  000000010000000000000026.lz4
000000010000000000000023.lz4  000000010000000000000027.lz4
000000010000000000000024.lz4  000000010000000000000028.lz4.partial
000000010000000000000025.lz4

Must be compiled with the --with-lz4 option to perform LZ4 compression.

3.4.8 Pg_resetwal

The –oldest-transaction-id option (short form -u) has been added to specify the oldest transaction ID.

3.4.9 Pg_rewind

Added the –config-file option to specify the path to the server configuration file (postgresql.conf). This option is useful when the configuration file is outside the data directory.

3.4.10 Pg_upgrade

The following enhancements have been implemented for the pg_upgrade command

PDF page 63

–No-sync option

The –no-sync option (short form -N) has been added. When this option is specified, it does not wait for the completion of file writing. This is not to be used in a production environment.

Temporary Directories

Temporary files created during the upgrade will be created in the pg_upgrade_output.d directory of the new database cluster.

3.4.11 Pg_waldump

  The following enhancements have been added to the pg_waldump command

Options

The following options have been added Multiple –rmgr options can now be specified.

Signal

When the SIGINT signal is received and the process ends, summary information is output.

Addition of output information

The remote_apply information of transaction commit and the replication origin information of PREPARE TRANSACTION are added to the command output.

PDF page 64

$ pg_waldump data/pg_wal/000000010000000000000008 | grep origin
rmgr: Transaction len (rec/tot):    117/   117, tx:        775, lsn:
0/080025F0, prev 0/080025C0, desc: COMMIT 2022-10-14 10:01:46.390500 JST; inval
msgs: catcache 57 catcache 56 catcache 66; origin: node 2, lsn 0/0, at 2022-10-
14 10:01:46.382305 JST
rmgr: Transaction len (rec/tot):     85/    85, tx:        776, lsn:
0/08002770, prev 0/08002740, desc: COMMIT 2022-10-14 10:01:46.391231 JST; inval
msgs: catcache 66; origin: node 2, lsn 0/0, at 2022-10-14 10:01:46.382305 JST
…

7 Contrib Modules

PDF page 65

Describes new features related to the Contrib modules.

3.5.1 Amcheck

A sequence checking is now supported.

postgres=# CREATE SEQUENCE seq1 ;
CREATE SEQUENCE
postgres=# SELECT verify_heapam(relation=>'seq1') ;
 verify_heapam
---------------
(0 rows)

3.5.2 Basebackup_to_shell

This module is used when “shell” is specified as the target of the base backup. When using this module, specify basebackup_to_shell in the parameter shared_preload_libraries or local_preload_libraries. The following parameters can be specified:

3.5.3 File_fdw

The MATCH value can now be specified in the HEADER option of the CREATE FOREIGN TABLE statement. This option checks for a match between the header line and column name in the text file.

PDF page 66

postgres=# \! cat /tmp/data1.csv
c1,c3
1,data1
2,data2
postgres=# CREATE EXTENSION file_fdw ;
CREATE EXTENSION
postgres=# CREATE SERVER filesvr1 FOREIGN DATA WRAPPER file_fdw ;
CREATE SERVER
postgres=# CREATE FOREIGN TABLE head1 (c1 int, c2 text) SERVER filesvr1
OPTIONS (format 'csv', filename '/tmp/data1.csv', delimiter ',',
header 'match') ;
CREATE FOREIGN TABLE
postgres=# SELECT * FROM head1 ;
ERROR:  column name mismatch in header line field 2: got "c3", expected "c2"
CONTEXT:  COPY head1, line 1: "c1,c3"

3.5.4 Pg_stat_statements

The pg_stat_statements view has additional items that can be monitored.

I/O information for temporary files

Read/write times for temporary files can now be monitored. The following columns have been added to the pg_stat_statements view.

Addition of JIT-related information

JIT-related information can now be monitored; the following columns have been added to the pg_stat_statements view.

PDF page 67

3.5.5 Pg_walinspect

The pg_walinspect module is a module for executing processing similar to the pg_waldump command with SQL statements. The following functions are provided. These functions can only be executed by users with the SUPERUSER attribute or the pg_read_server_files role.

PDF page 68

postgres=# CREATE EXTENSION pg_walinspect ;
CREATE EXTENSION
postgres=# SELECT * FROM pg_get_wal_stats('0/B4BB822'::pg_lsn ,
 '0/B4BB828'::pg_lsn) ;
-[ RECORD 1 ]----------------+------------------
resource_manager/record_type | XLOG
count                        | 0
count_percentage             | 0
…

3.5.6 Postgres_fdw

The following enhancements have been implemented in postgres_fdw:

Specifying the application name

It is now possible to specify application_name for a remote connection. Change the postgres_fdw.application_name parameter.

postgres=# SET postgres_fdw.application_name TO 'fdw_name1' ;
SET
postgres=# CREATE SERVER svr5432 FOREIGN DATA WRAPPER postgres_fdw OPTIONS
(host 'remhost1', port '5432', dbname 'postgres') ;
CREATE SERVER

The following escape sequences can be specified in the application name.

PDF page 69

Parallel_commit option

Added the parallel_commit option to control how remote transaction commits are executed. Setting this option to ‘on’ allows remote transaction commits in parallel. The default value is off, which causes commits to be executed serially.

postgres=# CREATE SERVER svr5433 FOREIGN DATA WRAPPER postgres_fdw OPTIONS
( host 'remhost1', port '5433', dbname 'postgres', parallel_commit 'true') ;
CREATE SERVER
postgres=# SELECT srvoptions FROM pg_foreign_server WHERE srvname='svr5433' ;
                           srvoptions
-----------------------------------------------------------------
 {host=localhost,port=5433,dbname=postgres,parallel_commit=true}
(1 row)

CASE expression

CASE expressions are now pushed to remote instances.

PDF page 70

postgres=> EXPLAIN (ANALYZE, VERBOSE) SELECT COUNT(*) FROM remote1 WHERE
CASE WHEN c1 > 100 THEN c1 END < 100 ;
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Foreign Scan  (cost=102.84..164.07 rows=1 width=8) (actual time=1.364..1.365
rows=1 loops=1)
   Output: (count(*))
   Relations: Aggregate on (public.remote1)
   Remote SQL: SELECT count(*) FROM public.remote1 WHERE (((CASE WHEN (c1 >
100::numeric) THEN c1 ELSE NULL::numeric END) < 100::numeric))
 Planning Time: 0.077 ms
 Execution Time: 1.620 ms
(6 rows)

3.5.7 Sepgsql

The permissive/enforcing state has been added to the log, with permissive=0 or permissive=1 output at the end of the line.

8 URL List

The links in this chapter are preserved from the source PDF’s URL list.

PDF page 71

• Release Notes

• Commitfests

• PostgreSQL 15 Manual

• Git

• GitHub

• Announce of PostgreSQL 15

• Postgres Professional

• PostgreSQL 15 Open Items

• Qiita (Nuko@Yokohama)

• pgsql-hackers Mailing list

• PostgreSQL Developer Information

• pgPedia

• SQL Notes

• Slack - postgresql-jp

9 Change History

This chapter preserves the source document’s revision history and publication credits.

PDF page 72

Change history