PDF page 8
More than 200 new features have been added to PostgreSQL 18. 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 {#pg18-2-1-improvements-to-adapt-to-large-scale-environments} The following features have been added that can be applied to large scale environments: #### Creating Virtual Columns {#pg18-p8-creating-virtual-columns} Allows the creation of virtual columns without data storage as columns of a table. #### VACUUM / ANALYZE statements for partitioned tables {#pg18-p8-vacuum-analyze-statements-for-partitioned-tables} The VACUUM and ANALYZE statements can now specify an ONLY clause that does not recursively process partitions. ### 2.2 Improved reliability {#pg18-2-2-improved-reliability} PostgreSQL 18 implements the following enhancements to improve reliability. #### Tar format support for pg_verifybackup command {#pg18-p8-tar-format-support-for-pg-verifybackup-command} The pg_verifybackup command can now be executed for tar format backups ### 2.3 Improved maintainability {#pg18-2-3-improved-maintainability} The following features that can improve operability have been added. #### Statistics Migration {#pg18-p8-statistics-migration} Optimizer statistics can now be included in dump files for the pg_dump and pg_dumpall commands. Functions have also been added to set and remove optimizer statistics for tables and columns. #### OAUTH SASL {#pg18-p8-oauth-sasl} Supports authentication by OAUTHBearer/SASL.PDF page 9
#### EXPLAIN statement {#pg18-p9-explain-statement} Many options have been added to the EXPLAIN statement. ### 2.4 Improvements in Programming {#pg18-2-4-improvements-in-programming} The following functions have been added to SQL statements. #### Access to Before Update Data {#pg18-p9-access-to-before-update-data} The RETURNING clause of the update DML can now specify an identifier that indicates the data before (OLD) and after (NEW) the update. #### UUID v7 function {#pg18-p9-uuid-v7-function} A function is provided that returns a UUID v7 value with a fixed leading portion. ### 2.5 Preparing for future new features {#pg18-2-5-preparing-for-future-new-features} PostgreSQL 18 is now ready for features that will be provided in future versions. #### Asynchronous I/O Infrastructure- {#pg18-p9-asynchronous-i-o-infrastructure} The infrastructure for using asynchronous I/O has been implemented. The parameter io_method specifies the asynchronous I/O method to be used. #### NUMA support {#pg18-p9-numa-support} Functions and views to use the basic NUMA environment have been added.PDF page 10
### 2.6 Incompatibility {#pg18-2-6-incompatibility} In PostgreSQL 18, the following specifications have been changed from PostgreSQL 17. #### 2.6.1 Support Information {#pg18-2-6-1-support-information} PostgreSQL 18 changes the supported versions for the following platforms and tools The following platforms and tools are no longer supported. [[edadeb0](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=edadeb0710e838f6ce1f95973fae683a56fdba06), [972c2cd](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=972c2cd2882b6dd7a2059d030d03e89dae47ede7), [6c66b74](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=6c66b7443cebf3ff09ea76416a20fb6bb1d32a52), [45363fc](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=45363fca6372a04e90d1c2628c3cbfe8894b811b)] - PA-RISC processor - LLVM 13 and below - OpenSSL 1.1.0 and below - Python 3.2 The changes in component support versions required to build PostgreSQL 18 are as follows. [[a70e01d](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a70e01d4306fdbcd5fbedb4ca97e5c21c995da60), [6c66b74](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=6c66b7443cebf3ff09ea76416a20fb6bb1d32a52)] - OpenSSL 1.1.1 or higher required - Python 3.6.8 or higher required The following components are now supported in PostgreSQL 18. - Tcl 9 support added [[32a2aa7](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=32a2aa77ef7eec5ca46cfdbe6b175435cac1af61)] #### 2.6.2 MD5 Password {#pg18-2-6-2-md5-password} MD5 passwords are now deprecated. Generating an MD5 password will output a warning by default. This warning can be suppressed by setting the parameter md5_password_warnings to 'off'. [[db6a4a98](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=db6a4a985bc09d260d5c29848e3c97f080646a53)]PDF page 11
**Example 1.** MD5 Password Warning ```text postgres=# SET password_encryption = md5 ; SET postgres=# SHOW md5_password_warnings ; md5_password_warnings ----------------------- on (1 row) postgres=# CREATE USER user1 PASSWORD 'user1' ; WARNING: setting an MD5-encrypted password DETAIL: MD5 password support is deprecated and will be removed in a future release of PostgreSQL. HINT: Refer to the PostgreSQL documentation for details about migrating to another password type. CREATE ROLE ``` #### 2.6.3 Configure command {#pg18-2-6-3-configure-command} The configure command has been changed as follows. ##### Option --disable-spin-locks {#pg18-p11-option-disable-spin-locks} This option has been removed. [[e256266](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e25626677f8076eb3ce94586136c5464ee154381)] ##### Option --disable-atomics {#pg18-p11-option-disable-atomics} This option has been removed. [[8138526](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=81385261362962deb9861b39b509aeffe213721d)] ##### Command flex {#pg18-p11-command-flex} ```text Version checks have been removed. [0869ea4] ``` #### 2.6.4 Initdb command {#pg18-2-6-4-initdb-command} The data checksum feature is enabled by default. To disable checksums, specify the --no-datachecksums option. [[04bec89](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=04bec894a04cb0d32533f1522ab81b7016141ff1)]PDF page 12
**Example 2.** Checksum enabled by default ```bash $ initdb data The files belonging to this database system will be owned by user "postgres". … The default text search configuration will be set to "simple". Data page checksums are enabled. … ``` #### 2.6.5 COPY statement {#pg18-2-6-5-copy-statement} The following changes have been implemented for the COPY statement. ##### Prohibition of the COPY FREEZE statement for external tables {#pg18-p12-prohibition-of-the-copy-freeze-statement-for-external-tables} The COPY FREEZE statement for external tables is now explicitly prohibited. [[401a695](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=401a6956fa69c9202cbc14c09ba8a9c430b90cac)] **Example 3.** Prohibition of COPY FREEZE statement for external tables ```text postgres=# CREATE FOREIGN TABLE data1(col1 INT, col2 VARCHAR(10)) SERVER remsvr1 ; CREATE FOREIGN TABLE postgres=# COPY data1 FROM stdin FREEZE ; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself, or an EOF signal. >> 1,data1 >> \. ERROR: cannot perform COPY FREEZE because the table was not created or truncated in the current subtransaction ``` ##### CSV mode specification change {#pg18-p12-csv-mode-specification-change} When executing the COPY statement in CSV mode and reading data from a source other than STDIN, '\.' is no longer considered data completion. [[7702337](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=7702337489810f645b3501d99215c2b525c5abca)]PDF page 13
**Example 4.** Contents of CSV file data1\.csv ```text Data1 Data2 \. Data3 ``` **Example 5.** PostgreSQL 17 behavior ```text postgres=> COPY data1 FROM '/home/postgres/data1.csv' CSV ; COPY 2 postgres=> SELECT * FROM copy1 ; col1 ------- Data1 Data2 (2 rows) ``` **Example 6.** PostgreSQL 17 behavior ```text postgres=> COPY data1 FROM '/home/postgres/data1.csv' CSV ; COPY 4 postgres=> SELECT * FROM data1 ; col1 ------- Data1 Data2 \. Data3 (4 rows) ``` #### 2.6.6 CREATE OPERATOR CLASS statement {#pg18-2-6-6-create-operator-class-statement} The RECHECK clause in the CREATE OPERATOR CLASS and ALTER OPERATOR FAMILY statements is no longer accepted. The RECHECK clause in these statements was valid prior to PostgreSQL 8.4 and did not cause an error if specified until PostgreSQL 17. [[7da1bdc](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=7da1bdc2c2f17038f2ae1900be90a0d7b5e361e0)]PDF page 14
#### 2.6.7 CREATE SUBSCRIPTION statement {#pg18-2-6-7-create-subscription-statement} The default value of the 'streaming' option when creating SUBSCRIPTION has been changed from 'off' to 'parallel'. [[1bf1140](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1bf1140be87230c71d0e7b29939f7e2b3d073aa1)] **Example 7.** Change default value when creating SUBSCRIPTION ```text postgres=# CREATE SUBSCRIPTION sub1 CONNECTION 'host=dbsvr1 port=5432 dbname=postgres' PUBLICATION pub1 ; NOTICE: created replication slot "sub1" on publisher CREATE SUBSCRIPTION postgres=# SELECT subname, substream FROM pg_subscription ; subname | substream ---------+----------- sub1 | p (1 row) ``` #### 2.6.8 CREATE TABLE statement {#pg18-2-6-8-create-table-statement} Partition tables can no longer be created as UNLOGGED tables. Partitions can be created as UNLOGGED tables. [[e2bab2d](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e2bab2d792044b55dd092bf1c2be0d570ccb9401)] **Example 8.** Error creating UNLOGGED partition table ```text postgres=> CREATE UNLOGGED TABLE part1(col1 INT, col2 VARCHAR(10)) PARTITION BY RANGE(col1) ; ERROR: partitioned tables cannot be unlogged postgres=> CREATE TABLE part1(col1 INT, col2 VARCHAR(10)) PARTITION BY RANGE(col1) ; CREATE TABLE postgres=> CREATE UNLOGGED TABLE part1v1 PARTITION OF part1 FOR VALUES FROM (0) TO (10) PARTITION BY LIST(col2) ; ERROR: partitioned tables cannot be unlogged ``` #### 2.6.9 EXPLAIN statement {#pg18-2-6-9-explain-statement} The BUFFERS clause is now enabled by default. [[c2a4078](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c2a4078ebad71999dd451ae7d4358be3c9290b07)]PDF page 15
**Example 9.** PostgreSQL 17 behavior ```text postgres=> EXPLAIN ANALYZE SELECT * FROM data1 WHERE col1=1000 ; QUERY PLAN ----------------------------------------------------------------------------… Index Scan using data1_pkey on data1 (cost=0.42..8.44 rows=1 width=10) (act… Index Cond: (col1 = 1000) Planning Time: 0.062 ms Execution Time: 0.018 ms (4 rows) ``` **Example 10.** PostgreSQL 18 behavior ```text postgres=> EXPLAIN ANALYZE SELECT * FROM data1 WHERE col1=1000 ; QUERY PLAN ----------------------------------------------------------------------------… Index Scan using data1_pkey on data1 (cost=0.42..8.44 rows=1 width=10) (act… Index Cond: (col1 = 1000) Buffers: shared hit=4 Planning Time: 0.065 ms Execution Time: 0.023 ms (5 rows) ``` #### 2.6.10 GRANT statement {#pg18-2-6-10-grant-statement} The RULE privilege on objects has been removed since PostgreSQL 8.2, but the GRANT statement did not raise an error. The RULE privilege has been completely removed from this version. [[fefa76f](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=fefa76f70fdc75c91f80bddce2df7a8825205962)] **Example 11.** Remove RULE privileges ```text postgres=> CREATE TABLE data1(col1 INT PRIMARY KEY, col2 VARCHAR(10)) ; CREATE TABLE postgres=> GRANT RULE ON data1 TO PUBLIC ; ERROR: unrecognized privilege type "rule" ```PDF page 16
#### 2.6.11 Psql {#pg18-2-6-11-psql} The output format of the \conninfo metacommand has been changed. [[bba2fbc](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=bba2fbc6238b2a0a7f348fbbb5c31ffa7623bc39)] **Example 12.** PostgreSQL 17 psql command output ```text postgres=> \conninfo You are connected to database "postgres" as user "demo" on host "dbsvr1" (address "192.168.1.100") at port "5432". ``` **Example 13.** PostgreSQL 18 psql command output \(without SSL\) ```text postgres=# \conninfo Connection Information Parameter | Value ----------------------+---------- Database | postgres Client User | postgres Socket Directory | /tmp Server Port | 5432 Options | Protocol Version | 3.0 Password Used | false GSSAPI Authenticated | false Backend PID | 27757 SSL Connection | false Superuser | on Hot Standby | off (12 rows) ``` #### 2.6.12 Pg_backend_memory_contexts view {#pg18-2-6-12-pg-backend-memory-contexts-view} The starting number of the level column in the pg_backend_memory_contexts view has been changed from zero to one. The same change has been implemented for the output of the pg_log_backend_memory_contexts function. [[d9e0386](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d9e03864b6b4c7fe2988393c22e2355786c4c1f7)]PDF page 17
**Example 14.** PostgreSQL 17 pg\_backend\_memory\_contexts view ```text postgres=# SELECT level,name FROM pg_backend_memory_contexts order by 1 LIMIT 2 ; level | name -------+----------------------- 0 | TopMemoryContext 1 | TopTransactionContext (2 rows) ``` **Example 15.** PostgreSQL 18 pg\_backend\_memory\_contexts view ```text postgres=# SELECT level,name FROM pg_backend_memory_contexts order by 1 LIMIT 2 ; level | name -------+------------------------------------------------ 1 | TopMemoryContext 2 | Map from relid to OID of cached composite type (2 rows) ```PDF page 18