Upgrading from 14.17 to 17.4 gives you 3.4 years worth of changes and fixes (737 of them)

List of changes:

• ⇑ Upgrade to 15 released on 2022-10-13 - docs

  • Remove PUBLIC creation permission on the public schema (Noah Misch)

    The new default is one of the secure schema usage patterns that Section 5.9.6 has recommended since the security release for CVE-2018-1058. The change applies to new database clusters and to newly-created databases in existing clusters. Upgrading a cluster or restoring a database dump will preserve public's existing permissions.

    For existing databases, especially those having multiple users, consider revoking CREATE permission on the public schema to adopt this new default. For new databases having no need to defend against insider threats, granting CREATE permission will yield the behavior of prior releases.

  • Change the owner of the public schema to be the new pg_database_owner role (Noah Misch)

    This allows each database's owner to have ownership privileges on the public schema within their database. Previously it was owned by the bootstrap superuser, so that non-superuser database owners could not do anything with it.

    This change applies to new database clusters and to newly-created databases in existing clusters. Upgrading a cluster or restoring a database dump will preserve public's existing ownership specification.

  • Remove long-deprecated exclusive backup mode (David Steele, Nathan Bossart)

    If the database server stops abruptly while in this mode, the server could fail to start. The non-exclusive backup mode is considered superior for all purposes. Functions pg_start_backup()/pg_stop_backup() have been renamed to pg_backup_start()/pg_backup_stop(), and the functions pg_backup_start_time() and pg_is_in_backup() have been removed.

  • Increase hash_mem_multiplier default to 2.0 (Peter Geoghegan)

    This allows query hash operations to use more work_mem memory than other operations.

  • Remove server-side language plpython2u and generic Python language plpythonu (Andres Freund)

    Python 2.x is no longer supported. While the original intent of plpythonu was that it could eventually refer to plpython3u, changing it now seems more likely to cause problems than solve them, so it's just been removed.

  • Generate an error if array_to_tsvector() is passed an empty-string array element (Jean-Christophe Arnu)

    This is prohibited because lexemes should never be empty. Users of previous Postgres releases should verify that no empty lexemes are stored because they can lead to dump/restore failures and inconsistent results.

  • Generate an error when chr() is supplied with a negative argument (Peter Eisentraut)

  • Prevent CREATE OR REPLACE VIEW from changing the collation of an output column (Tom Lane)

  • Disallow zero-length Unicode identifiers, e.g., U&"" (Peter Eisentraut)

    Non-Unicode zero-length identifiers were already disallowed.

  • Prevent numeric literals from having non-numeric trailing characters (Peter Eisentraut)

    Previously, query text like 123abc would be interpreted as 123 followed by a separate token abc.

  • Adjust JSON numeric literal processing to match the SQL/JSON-standard (Peter Eisentraut)

    This accepts numeric formats like .1 and 1., and disallows trailing junk after numeric literals, like 1.type().

  • When interval input provides a fractional value for a unit greater than months, round to the nearest month (Bruce Momjian)

    For example, convert 1.99 years to 2 years, not 1 year 11 months as before.

  • Improve consistency of interval parsing with trailing periods (Tom Lane)

    Numbers with trailing periods were rejected on some platforms.

  • Mark the interval output function as stable, not immutable, since it depends on IntervalStyle (Tom Lane)

    This will, for example, cause creation of indexes relying on the text output of interval values to fail.

  • Detect integer overflow in interval justification functions (Joe Koshakow)

    The affected functions are justify_interval(), justify_hours(), and justify_days().

  • Change the I/O format of type "char" for non-ASCII characters (Tom Lane)

    Bytes with the high bit set are now output as a backslash and three octal digits, to avoid encoding issues.

  • Remove the default ADMIN OPTION privilege a login role has on its own role membership (Robert Haas)

    Previously, a login role could add/remove members of its own role, even without ADMIN OPTION privilege.

  • Allow logical replication to run as the owner of the subscription (Mark Dilger)

    Because row-level security policies are not checked, only superusers, roles with bypassrls, and table owners can replicate into tables with row-level security policies.

  • Prevent UPDATE and DELETE logical replication operations on tables where the subscription owner does not have SELECT permission on the table (Jeff Davis)

    UPDATE and DELETE commands typically involve reading the table as well, so require the subscription owner to have table SELECT permission.

  • When EXPLAIN references the session's temporary object schema, refer to it as pg_temp (Amul Sul)

    Previously the actual schema name was reported, leading to inconsistencies across sessions.

  • Fix pg_statio_all_tables to sum values for the rare case of TOAST tables with multiple indexes (Andrei Zubkov)

    Previously such cases would show one row for each index.

  • Disallow setting custom options that match the name of an installed extension, but are not one of the extension's declared variables (Florin Irion, Tom Lane)

    This change causes any such pre-existing variables to be deleted during extension load, and then prevents new ones from being created later in the session. The intent is to prevent confusion about whether a variable is associated with an extension or not.

  • Remove obsolete server variable stats_temp_directory (Andres Freund, Kyotaro Horiguchi)

  • Improve the algorithm used to compute random() (Fabien Coelho)

    This will cause random()'s results to differ from what was emitted by prior versions, even for the same seed value.

  • libpq's PQsendQuery() function is no longer supported in pipeline mode (Álvaro Herrera)

    Applications that are using that combination will need to be modified to use PQsendQueryParams() instead.

  • On non-Windows platforms, consult the HOME environment variable to find the user's home directory (Anders Kaseorg)

    If HOME is empty or unset, fall back to the previous method of checking the <pwd.h> database. This change affects libpq (for example, while looking up ~/.pgpass) as well as various client application programs.

  • Remove pg_dump's --no-synchronized-snapshots option (Tom Lane)

    All still-supported server versions support synchronized snapshots, so there's no longer a need for this option.

  • After an error is detected in psql's --single-transaction mode, change the final COMMIT command to ROLLBACK only if ON_ERROR_STOP is set (Michael Paquier)

  • Avoid unnecessary casting of constants in queries sent by postgres_fdw (Dian Fay)

    When column types are intentionally different between local and remote databases, such casts could cause errors.

  • Remove xml2's xml_is_well_formed() function (Tom Lane)

    This function has been implemented in the core backend since Postgres 9.1.

  • Allow custom scan providers to indicate if they support projections (Sven Klemm)

    The default is now that custom scan providers are assumed to not support projections; those that do will need to be updated for this release.

  • Record and check the collation version of each database (Peter Eisentraut)

    This feature is designed to detect collation version changes to avoid index corruption. Function pg_database_collation_actual_version() reports the underlying operating system collation version, and ALTER DATABASE ... REFRESH sets the recorded database collation version to match the operating system collation version.

  • Allow ICU collations to be set as the default for clusters and databases (Peter Eisentraut)

    Previously, only libc-based collations could be selected at the cluster and database levels. ICU collations could only be used via explicit COLLATE clauses.

  • Add system view pg_ident_file_mappings to report pg_ident.conf information (Julien Rouhaud)

  • Improve planning time for queries referencing partitioned tables (David Rowley)

    This change helps when only a few of many partitions are relevant.

  • Allow ordered scans of partitions to avoid sorting in more cases (David Rowley)

    Previously, a partitioned table with a DEFAULT partition or a LIST partition containing multiple values could not be used for ordered partition scans. Now they can be used if such partitions are pruned during planning.

  • Improve foreign key behavior of updates on partitioned tables that move rows between partitions (Amit Langote)

    Previously, such updates ran a delete action on the source partition and an insert action on the target partition. PostgreSQL will now run an update action on the partition root, providing cleaner semantics.

  • Allow CLUSTER on partitioned tables (Justin Pryzby)

  • Fix ALTER TRIGGER RENAME on partitioned tables to properly rename triggers on all partitions (Arne Roland, Álvaro Herrera)

    Also prohibit cloned triggers from being renamed.

  • Allow btree indexes on system and TOAST tables to efficiently store duplicates (Peter Geoghegan)

    Previously de-duplication was disabled for these types of indexes.

  • Improve lookup performance of GiST indexes that were built using sorting (Aliaksandr Kalenik, Sergei Shoulbakov, Andrey Borodin)

  • Allow unique constraints and indexes to treat NULL values as not distinct (Peter Eisentraut)

    Previously NULL entries were always treated as distinct values, but this can now be changed by creating constraints and indexes using UNIQUE NULLS NOT DISTINCT.

  • Allow the ^@ starts-with operator and the starts_with() function to use btree indexes if using the C collation (Tom Lane)

    Previously these could only use SP-GiST indexes.

  • Allow extended statistics to record statistics for a parent with all its children (Tomas Vondra, Justin Pryzby)

    Regular statistics already tracked parent and parent-plus-all-children statistics separately.

  • Add server variable recursive_worktable_factor to allow the user to specify the expected size of the working table of a recursive query (Simon Riggs)

  • Allow hash lookup for NOT IN clauses with many constants (David Rowley, James Coleman)

    Previously the code always sequentially scanned the list of values.

  • Allow SELECT DISTINCT to be parallelized (David Rowley)

  • Speed up encoding validation of UTF-8 text by processing 16 bytes at a time (John Naylor, Heikki Linnakangas)

    This will improve text-heavy operations like COPY FROM.

  • Improve performance for sorts that exceed work_mem (Heikki Linnakangas)

    When the sort data no longer fits in work_mem, switch to a batch sorting algorithm that uses more output streams than before.

  • Improve performance and reduce memory consumption of in-memory sorts (Ronan Dunklau, David Rowley, Thomas Munro, John Naylor)

  • Allow WAL full page writes to use LZ4 and Zstandard compression (Andrey Borodin, Justin Pryzby)

    This is controlled by the wal_compression server setting.

  • Add support for writing WAL using direct I/O on macOS (Thomas Munro)

    This only works if max_wal_senders = 0 and wal_level = minimal.

  • Allow vacuum to be more aggressive in setting the oldest frozen and multi transaction id (Peter Geoghegan)

  • Allow a query referencing multiple foreign tables to perform parallel foreign table scans in more cases (Andrey Lepikhov, Etsuro Fujita)

  • Improve the performance of window functions that use row_number(), rank(), dense_rank() and count() (David Rowley)

  • Improve the performance of spinlocks on high-core-count ARM64 systems (Geoffrey Blake)

  • Enable default logging of checkpoints and slow autovacuum operations (Bharath Rupireddy)

    This changes the default of log_checkpoints to on and that of log_autovacuum_min_duration to 10 minutes. This will cause even an idle server to generate some log output, which might cause problems on resource-constrained servers without log file rotation. These defaults should be changed in such cases.

  • Generate progress messages in the server log during slow server starts (Nitin Jadhav, Robert Haas)

    The messages report the cause of the delay. The time interval for notification is controlled by the new server variable log_startup_progress_interval.

  • Store cumulative statistics system data in shared memory (Kyotaro Horiguchi, Andres Freund, Melanie Plageman)

    Previously this data was sent to a statistics collector process via UDP packets, and could only be read by sessions after transferring it via the file system. There is no longer a separate statistics collector process.

  • Add additional information to VACUUM VERBOSE and autovacuum logging messages (Peter Geoghegan)

  • Add EXPLAIN (BUFFERS) output for temporary file block I/O (Masahiko Sawada)

  • Allow log output in JSON format (Sehrope Sarkuni, Michael Paquier)

    The new setting is log_destination = jsonlog.

  • Allow pg_stat_reset_single_table_counters() to reset the counters of relations shared across all databases (Sadhuprasad Patro)

  • Add wait events for local shell commands (Fujii Masao)

    The new wait events are used when calling archive_command, archive_cleanup_command, restore_command and recovery_end_command.

  • Allow table accesses done by a view to optionally be controlled by privileges of the view's caller (Christoph Heiss)

    Previously, view accesses were always treated as being done by the view's owner. That's still the default.

  • Allow members of the pg_write_server_files predefined role to perform server-side base backups (Dagfinn Ilmari Mannsåker)

    Previously only superusers could perform such backups.

  • Allow GRANT to grant permissions to change individual server variables via SET and ALTER SYSTEM (Mark Dilger)

    The new function has_parameter_privilege() reports on this privilege.

  • Add predefined role pg_checkpoint that allows members to run CHECKPOINT (Jeff Davis)

    Previously checkpoints could only be run by superusers.

  • Allow members of the pg_read_all_stats predefined role to access the views pg_backend_memory_contexts and pg_shmem_allocations (Bharath Rupireddy)

    Previously these views could only be accessed by superusers.

  • Allow GRANT to grant permissions on pg_log_backend_memory_contexts() (Jeff Davis)

    Previously this function could only be run by superusers.

  • Add server variable shared_memory_size to report the size of allocated shared memory (Nathan Bossart)

  • Add server variable shared_memory_size_in_huge_pages to report the number of huge memory pages required (Nathan Bossart)

    This is only supported on Linux.

  • Honor server variable shared_preload_libraries in single-user mode (Jeff Davis)

    This change supports use of shared_preload_libraries to load custom access methods and WAL resource managers, which would be essential for database access even in single-user mode.

  • On Solaris, make the default setting of dynamic_shared_memory_type be sysv (Thomas Munro)

    The previous default choice, posix, can result in spurious failures on this platform.

  • Allow postgres -C to properly report runtime-computed values (Nathan Bossart)

    Previously runtime-computed values data_checksums, wal_segment_size, and data_directory_mode would report values that would not be accurate on the running server. However, this does not work on a running server.

  • Add support for LZ4 and Zstandard compression of server-side base backups (Jeevan Ladhe, Robert Haas)

  • Run the checkpointer and bgwriter processes during crash recovery (Thomas Munro)

    This helps to speed up long crash recoveries.

  • Allow WAL processing to pre-fetch needed file contents (Thomas Munro)

    This is controlled by the server variable recovery_prefetch.

  • Allow archiving via loadable modules (Nathan Bossart)

    Previously, archiving was only done by calling shell commands. The new server variable archive_library can be set to specify a library to be called for archiving.

  • No longer require IDENTIFY_SYSTEM to be run before START_REPLICATION (Jeff Davis)

  • Allow publication of all tables in a schema (Vignesh C, Hou Zhijie, Amit Kapila)

    For example, this syntax is now supported: CREATE PUBLICATION pub1 FOR TABLES IN SCHEMA s1,s2. ALTER PUBLICATION supports a similar syntax. Tables added later to the listed schemas will also be replicated.

  • Allow publication content to be filtered using a WHERE clause (Hou Zhijie, Euler Taveira, Peter Smith, Ajin Cherian, Tomas Vondra, Amit Kapila)

    Rows not satisfying the WHERE clause are not published.

  • Allow publication content to be restricted to specific columns (Tomas Vondra, Álvaro Herrera, Rahila Syed)

  • Allow skipping of transactions on a subscriber using ALTER SUBSCRIPTION ... SKIP (Masahiko Sawada)

  • Add support for prepared (two-phase) transactions to logical replication (Peter Smith, Ajin Cherian, Amit Kapila, Nikhil Sontakke, Stas Kelvich)

    The new CREATE_REPLICATION_SLOT option is called TWO_PHASE. pg_recvlogical now supports a new --two-phase option during slot creation.

  • Prevent logical replication of empty transactions (Ajin Cherian, Hou Zhijie, Euler Taveira)

    Previously, publishers would send empty transactions to subscribers if subscribed tables were not modified.

  • Add SQL functions to monitor the directory contents of logical replication slots (Bharath Rupireddy)

    The new functions are pg_ls_logicalsnapdir(), pg_ls_logicalmapdir(), and pg_ls_replslotdir(). They can be run by members of the predefined pg_monitor role.

  • Allow subscribers to stop the application of logical replication changes on error (Osumi Takamichi, Mark Dilger)

    This is enabled with the subscriber option disable_on_error and avoids possible infinite error loops during stream application.

  • Adjust subscriber server variables to match the publisher so datetime and float8 values are interpreted consistently (Japin Li)

    Some publishers might be relying on inconsistent behavior.

  • Add system view pg_stat_subscription_stats to report on subscriber activity (Masahiko Sawada)

    The new function pg_stat_reset_subscription_stats() allows resetting these statistics counters.

  • Suppress duplicate entries in the pg_publication_tables system view (Hou Zhijie)

    In some cases a partition could appear more than once.

  • Add SQL MERGE command to adjust one table to match another (Simon Riggs, Pavan Deolasee, Álvaro Herrera, Amit Langote)

    This is similar to INSERT ... ON CONFLICT but more batch-oriented.

  • Add support for HEADER option in COPY text format (Rémi Lapeyre)

    The new option causes the column names to be output, and optionally verified on input.

  • Add new WAL-logged method for database creation (Dilip Kumar)

    This is the new default method for copying the template database, as it avoids the need for checkpoints during database creation. However, it might be slow if the template database is large, so the old method is still available.

  • Allow CREATE DATABASE to set the database OID (Shruthi Gowda, Antonin Houska)

  • Prevent DROP DATABASE, DROP TABLESPACE, and ALTER DATABASE SET TABLESPACE from occasionally failing during concurrent use on Windows (Thomas Munro)

  • Allow foreign key ON DELETE SET actions to affect only specified columns (Paul Martinez)

    Previously, all of the columns in the foreign key were always affected.

  • Allow ALTER TABLE to modify a table's ACCESS METHOD (Justin Pryzby, Jeff Davis)

  • Properly call object access hooks when ALTER TABLE causes table rewrites (Michael Paquier)

  • Allow creation of unlogged sequences (Peter Eisentraut)

  • Track dependencies on individual columns in the results of functions returning composite types (Tom Lane)

    Previously, if a view or rule contained a reference to a specific column within the result of a composite-returning function, that was not noted as a dependency; the view or rule was only considered to depend on the composite type as a whole. This meant that dropping the individual column would be allowed, causing problems in later use of the view or rule. The column-level dependency is now also noted, so that dropping such a column will be rejected unless the view is changed or dropped.

  • Allow the scale of a numeric value to be negative, or greater than its precision (Dean Rasheed, Tom Lane)

    This allows rounding of values to the left of the decimal point, e.g., '1234'::numeric(4, -2) returns 1200.

  • Improve overflow detection when casting values to interval (Joe Koshakow)

  • Change the I/O format of type "char" for non-ASCII characters (Tom Lane)

  • Update the display width information of modern Unicode characters, like emojis (Jacob Champion)

    Also update from Unicode 5.0 to 14.0.0. There is now an automated way to keep Postgres updated with Unicode releases.

  • Add multirange input to range_agg() (Paul Jungwirth)

  • Add MIN() and MAX() aggregates for the xid8 data type (Ken Kato)

  • Add regular expression functions for compatibility with other relational systems (Gilles Darold, Tom Lane)

    The new functions are regexp_count(), regexp_instr(), regexp_like(), and regexp_substr(). Some new optional arguments were also added to regexp_replace().

  • Add the ability to compute the distance between polygons (Tom Lane)

  • Add to_char() format codes of, tzh, and tzm (Nitin Jadhav)

    The upper-case equivalents of these were already supported.

  • When applying AT TIME ZONE to a time with time zone value, use the transaction start time rather than wall clock time to determine whether DST applies (Aleksander Alekseev, Tom Lane)

    This allows the conversion to be considered stable rather than volatile, and it saves a kernel call per invocation.

  • Ignore NULL array elements in ts_delete() and setweight() functions with array arguments (Jean-Christophe Arnu)

    These functions effectively ignore empty-string array elements (since those could never match a valid lexeme). It seems consistent to let them ignore NULL elements too, instead of failing.

  • Add support for petabyte units to pg_size_pretty() and pg_size_bytes() (David Christensen)

  • Change pg_event_trigger_ddl_commands() to output references to other sessions' temporary schemas using the actual schema name (Tom Lane)

    Previously this function reported all temporary schemas as pg_temp, but it's misleading to use that for any but the current session's temporary schema.

  • Fix enforcement of PL/pgSQL variable CONSTANT markings (Tom Lane)

    Previously, a variable could be used as a CALL output parameter or refcursor OPEN variable despite being marked CONSTANT.

  • Allow IP address matching against a server certificate's Subject Alternative Name (Jacob Champion)

  • Allow PQsslAttribute() to report the SSL library type without requiring a libpq connection (Jacob Champion)

  • Change query cancellations sent by the client to use the same TCP settings as normal client connections (Jelte Fennema)

    This allows configured TCP timeouts to apply to query cancel connections.

  • Prevent libpq event callback failures from forcing an error result (Tom Lane)

  • Allow pgbench to retry after serialization and deadlock failures (Yugo Nagata, Marina Polyakova)

  • Improve performance of psql's \copy command, by sending data in larger chunks (Heikki Linnakangas)

  • Add \dconfig command to report server variables (Mark Dilger, Tom Lane)

    This is similar to the server-side SHOW command, but it can process patterns to show multiple variables conveniently.

  • Add \getenv command to assign the value of an environment variable to a psql variable (Tom Lane)

  • Add + option to the \lo_list and \dl commands to show large-object privileges (Pavel Luzanov)

  • Add a pager option for the \watch command (Pavel Stehule, Thomas Munro)

    This is only supported on Unix and is controlled by the PSQL_WATCH_PAGER environment variable.

  • Make psql include intra-query double-hyphen comments in queries sent to the server (Tom Lane, Greg Nancarrow)

    Previously such comments were removed from the query before being sent. Double-hyphen comments that are before any query text are not sent, and are not recorded as separate psql history entries.

  • Adjust psql so that Readline's meta-# command will insert a double-hyphen comment marker (Tom Lane)

    Previously a pound marker was inserted, unless the user had taken the trouble to configure a non-default comment marker.

  • Make psql output all results when multiple queries are passed to the server at once (Fabien Coelho)

    Previously, only the last query result was displayed. The old behavior can be restored by setting the SHOW_ALL_RESULTS psql variable to off.

  • After an error is detected in --single-transaction mode, change the final COMMIT command to ROLLBACK only if ON_ERROR_STOP is set (Michael Paquier)

    Previously, detection of an error in a -c command or -f script file would lead to issuing ROLLBACK at the end, regardless of the value of ON_ERROR_STOP.

  • Improve psql's tab completion (Shinya Kato, Dagfinn Ilmari Mannsåker, Peter Smith, Koyu Tanigawa, Ken Kato, David Fetter, Haiying Tang, Peter Eisentraut, Álvaro Herrera, Tom Lane, Masahiko Sawada)

  • Limit support of psql's backslash commands to servers running PostgreSQL 9.2 or later (Tom Lane)

    Remove code that was only used when running with an older server. Commands that do not require any version-specific adjustments compared to 9.2 will still work.

  • Make pg_dump dump public schema ownership changes and security labels (Noah Misch)

  • Improve performance of dumping databases with many objects (Tom Lane)

    This will also improve the performance of pg_upgrade.

  • Improve parallel pg_dump's performance for tables with large TOAST tables (Tom Lane)

  • Add dump/restore option --no-table-access-method to force restore to only use the default table access method (Justin Pryzby)

  • Limit support of pg_dump and pg_dumpall to servers running PostgreSQL 9.2 or later (Tom Lane)

  • Add new pg_basebackup option --target to control the base backup location (Robert Haas)

    The new options are server to write the backup locally and blackhole to discard the backup (for testing).

  • Allow pg_basebackup to do server-side gzip, LZ4, and Zstandard compression and client-side LZ4 and Zstandard compression of base backup files (Dipesh Pandit, Jeevan Ladhe)

    Client-side gzip compression was already supported.

  • Allow pg_basebackup to compress on the server side and decompress on the client side before storage (Dipesh Pandit)

    This is accomplished by specifying compression on the server side and plain output format.

  • Allow pg_basebackup's --compress option to control the compression location (server or client), compression method, and compression options (Michael Paquier, Robert Haas)

  • Add the LZ4 compression method to pg_receivewal (Georgios Kokolatos)

    This is enabled via --compress=lz4 and requires binaries to be built using --with-lz4.

  • Add additional capabilities to pg_receivewal's --compress option (Georgios Kokolatos)

  • Improve pg_receivewal's ability to restart at the proper WAL location (Ronan Dunklau)

    Previously, pg_receivewal would start based on the WAL file stored in the local archive directory, or at the sending server's current WAL flush location. With this change, if the sending server is running Postgres 15 or later, the local archive directory is empty, and a replication slot is specified, the replication slot's restart point will be used.

  • Add pg_rewind option --config-file to simplify use when server configuration files are stored outside the data directory (Gunnar Bluth)

  • Store pg_upgrade's log and temporary files in a subdirectory of the new cluster called pg_upgrade_output.d (Justin Pryzby)

    Previously such files were left in the current directory, requiring manual cleanup. Now they are automatically removed on successful completion of pg_upgrade.

  • Disable default status reporting during pg_upgrade operation if the output is not a terminal (Andres Freund)

    The status reporting output can be enabled for non-tty usage by using --verbose.

  • Make pg_upgrade report all databases with invalid connection settings (Jeevan Ladhe)

    Previously only the first database with an invalid connection setting was reported.

  • Make pg_upgrade preserve tablespace and database OIDs, as well as relation relfilenode numbers (Shruthi Gowda, Antonin Houska)

  • Add a --no-sync option to pg_upgrade (Michael Paquier)

    This is recommended only for testing.

  • Limit support of pg_upgrade to old servers running PostgreSQL 9.2 or later (Tom Lane)

  • Allow pg_waldump output to be filtered by relation file node, block number, fork number, and full page images (David Christensen, Thomas Munro)

  • Make pg_waldump report statistics before an interrupted exit (Bharath Rupireddy)

    For example, issuing a control-C in a terminal running pg_waldump --stats --follow will report the current statistics before exiting. This does not work on Windows.

  • Improve descriptions of some transaction WAL records reported by pg_waldump (Masahiko Sawada, Michael Paquier)

  • Allow pg_waldump to dump information about multiple resource managers (Heikki Linnakangas)

    This is enabled by specifying the --rmgr option multiple times.

  • Add documentation for pg_encoding_to_char() and pg_char_to_encoding() (Ian Lawrence Barwick)

  • Document the ^@ starts-with operator (Tom Lane)

  • Add support for continuous integration testing using cirrus-ci (Andres Freund, Thomas Munro, Melanie Plageman)

  • Add configure option --with-zstd to enable Zstandard builds (Jeevan Ladhe, Robert Haas, Michael Paquier)

  • Add an ABI identifier field to the magic block in loadable libraries, allowing non-community PostgreSQL distributions to identify libraries that are not compatible with other builds (Peter Eisentraut)

    An ABI field mismatch will generate an error at load time.

  • Create a new pg_type.typcategory value for "char" (Tom Lane)

    Some other internal-use-only types have also been assigned to this category.

  • Add new protocol message TARGET to specify a new COPY method to be used for base backups (Robert Haas)

    pg_basebackup now uses this method.

  • Add new protocol message COMPRESSION and COMPRESSION_DETAIL to specify the compression method and options (Robert Haas)

  • Remove server support for old BASE_BACKUP command syntax and base backup protocol (Robert Haas)

  • Add support for extensions to set custom backup targets (Robert Haas)

  • Allow extensions to define custom WAL resource managers (Jeff Davis)

  • Add function pg_settings_get_flags() to get the flags of server variables (Justin Pryzby)

  • On Windows, export all the server's global variables using PGDLLIMPORT markers (Robert Haas)

    Previously, only specific variables were accessible to extensions on Windows.

  • Require GNU make version 3.81 or later to build PostgreSQL (Tom Lane)

  • Require OpenSSL to build the pgcrypto extension (Peter Eisentraut)

  • Require Perl version 5.8.3 or later (Dagfinn Ilmari Mannsåker)

  • Require Python version 3.2 or later (Andres Freund)

  • Allow amcheck to check sequences (Mark Dilger)

  • Improve amcheck sanity checks for TOAST tables (Mark Dilger)

  • Add new module basebackup_to_shell as an example of a custom backup target (Robert Haas)

  • Add new module basic_archive as an example of performing archiving via a library (Nathan Bossart)

  • Allow btree_gist indexes on boolean columns (Emre Hasegeli)

    These can be used for exclusion constraints.

  • Fix pageinspect's page_header() to handle 32-kilobyte page sizes (Quan Zongliang)

    Previously, improper negative values could be returned in certain cases.

  • Add counters for temporary file block I/O to pg_stat_statements (Masahiko Sawada)

  • Add JIT counters to pg_stat_statements (Magnus Hagander)

  • Add new module pg_walinspect (Bharath Rupireddy)

    This gives SQL-level output similar to pg_waldump.

  • Indicate the permissive/enforcing state in sepgsql log messages (Dave Page)

  • Allow postgres_fdw to push down CASE expressions (Alexander Pyhalov)

  • Add server variable postgres_fdw.application_name to control the application name of postgres_fdw connections (Hayato Kuroda)

    Previously the remote session's application_name could only be set on the remote server or via a postgres_fdw connection specification. postgres_fdw.application_name supports some escape sequences for customization, making it easier to tell such connections apart on the remote server.

  • Allow parallel commit on postgres_fdw servers (Etsuro Fujita)

    This is enabled with the CREATE SERVER option parallel_commit.

• ⇑ Upgrade to 15.1 released on 2022-11-10 - docs

  • Fix failure to remove non-first segments of large tables (Tom Lane)

    PostgreSQL splits large tables into multiple files (normally with 1GB per file). The logic for dropping a table was broken and would miss removing all but the first such file, in two cases: drops of temporary tables and WAL replay of drops of regular tables. Applications that routinely create multi-gigabyte temporary tables could suffer significant disk space leakage.

    Orphaned temporary-table files are removed during postmaster start, so the mere act of updating to 15.1 is sufficient to clear any leaked temporary-table storage. However, if you suffered any database crashes while using 15.0, and there might have been large tables dropped just before such crashes, it's advisable to check the database directories for files named according to the pattern NNNN.NN. If there is no matching file named just NNNN (without the .NN suffix), these files should be removed manually.

  • Prevent use of MERGE on a partitioned table with foreign-table partitions (Álvaro Herrera)

    The case isn't supported, and previously threw an incomprehensible error.

  • Fix planner failure with extended statistics on partitioned or inherited tables (Richard Guo, Justin Pryzby)

    Some cases failed with “cache lookup failed for statistics object”.

  • Avoid double call of the shutdown callback of an archiver module (Nathan Bossart, Bharath Rupireddy)

  • Fix psql's exit status when a command-line query is canceled (Peter Eisentraut)

    psql -c query would exit successfully if the query was canceled. Fix it to exit with nonzero status, as in other error cases.

  • Fix pg_dump's failure to dump comments attached to some CHECK constraints (Tom Lane)

  • Fix CREATE DATABASE to allow its oid parameter to exceed 2³¹ (Tom Lane)

    This oversight prevented pg_upgrade from succeeding when the source installation contained databases with OIDs larger than that.

• ⇑ Upgrade to 15.2 released on 2023-02-09 - docs

  • Fix possible failure of MERGE to compute GENERATED columns (Dean Rasheed)

    When the first row-level action of the MERGE was an UPDATE, any subsequent INSERT actions would fail to compute GENERATED columns that were deemed unnecessary to compute for the UPDATE action (due to not depending on any of the UPDATE target columns).

  • Fix MERGE's check for unreachable WHEN clauses (Dean Rasheed)

    A WHEN clause following an unconditional WHEN clause should be rejected as unreachable, but this case was not always detected.

  • Fix MERGE's rule-detection test (Dean Rasheed)

    MERGE is not supported on tables with rules; but it also failed on tables that once had rules but no longer do.

  • In MERGE, don't count a DO NOTHING action as a processed tuple (Álvaro Herrera)

    This makes the code's behavior match the documentation.

  • Remove arbitrary limit on number of elements in int2vector and oidvector (Tom Lane)

    The input functions for these types previously rejected more than 100 elements. With the introduction of the logical replication column list feature, it's necessary to accept int2vectors having up to 1600 columns, otherwise long column lists cause logical-replication failures.

  • Fix planner preprocessing oversights for window function run-condition expressions (Richard Guo, David Rowley)

    This could lead to planner errors such as “WindowFunc not found in subplan target lists”.

  • Fix possible dangling-pointer access during execution of window function run-condition expressions (David Rowley)

    In practice, because the run-condition optimization is only applied to certain window functions that happen to all return int8, this only manifested as a problem on 32-bit builds.

  • Prevent the statistics machinery from getting confused when a relation's relkind changes (Andres Freund)

    Converting a table to a view could lead to crashes or assertion failures.

  • Acquire spinlock while updating shared state during logical decoding context creation (Masahiko Sawada)

    We neglected to acquire the appropriate lock while updating data about two-phase transactions, potentially allowing other processes to see inconsistent data.

  • Fix pgoutput replication plug-in to not send columns not listed in a table's replication column list (Hou Zhijie)

    UPDATE and DELETE events did not pay attention to the configured column list, thus sending more data than expected. This did not cause a problem when the receiver is our built-in logical replication code, but it might confuse other receivers, and in any case it wasted network bandwidth.

  • Fix possible corruption of very large tablespace map files in pg_basebackup (Antonin Houska)

  • Avoid harmless warning from pg_dump in --if-exists mode (Tom Lane)

    If the public schema has a non-default owner then use of pg_dump's --if-exists option resulted in a warning message “warning: could not find where to insert IF EXISTS in statement "-- *not* dropping schema, since initdb creates it"”. The dump output was okay, though.

  • Update contrib/pageinspect to mark its disk-accessing functions as PARALLEL RESTRICTED (Tom Lane)

    This avoids possible failure if one of these functions is used to examine a temporary table, since a session's temporary tables are not accessible from parallel workers.

• ⇑ Upgrade to 15.3 released on 2023-05-11 - docs

  • Fix potential corruption of the template (source) database after CREATE DATABASE with the STRATEGY WAL_LOG option (Nathan Bossart, Ryo Matsumura)

    Improper buffer handling created a risk that any later modification of the template's pg_class catalog would be lost.

  • Fix memory leakage and unnecessary disk reads during CREATE DATABASE with the STRATEGY WAL_LOG option (Andres Freund)

  • Fix various planner failures with MERGE commands (Tom Lane)

    Planning could fail with errors like “variable not found in subplan target list” or “PlaceHolderVar found where not expected”.

  • Fix the row count reported by MERGE for some corner cases (Dean Rasheed)

    The row count reported in the command tag counted rows that actually hadn't been modified due to a BEFORE ROW trigger returning NULL. This is inconsistent with what happens in plain UPDATE or DELETE, so change it to not count such rows. Also, avoid counting a row twice when MERGE moves it into a different partition of a partitioned table.

  • Fix MERGE problems with concurrent updates (Dean Rasheed, Álvaro Herrera)

    Some cases misbehaved if a row to be updated or deleted by MERGE had just been updated by a concurrent transaction. This could lead to a crash, or the wrong merge action being executed, or no action at all.

  • Add support for decompiling MERGE commands (Álvaro Herrera)

    This was overlooked when MERGE was added, but it's essential support for MERGE in new-style SQL functions.

  • Fix enabling/disabling of foreign-key triggers in partitioned tables (Tom Lane)

    ALTER TABLE ... ENABLE/DISABLE TRIGGER failed if applied to a partitioned table's foreign-key enforcement triggers, because it tried to locate the clone triggers for the partitions by name, and they do not have the same name. Locate them by parent-trigger OID instead.

  • Adjust text-search-related character classification logic to correctly detect whether the prevailing locale is C (Jeff Davis)

    This code got confused if the database's default collation uses ICU.

  • Avoid possible crash on empty input for type interval (Tom Lane)

  • Re-allow exponential notation in ISO-8601 interval fields (Tom Lane)

    Interval input like P0.1e10D isn't officially sanctioned by ISO-8601, but we accepted it for a long time before version 15, so re-allow it.

  • Fix incorrect tests for whether a qual clause applied to a subquery can be transformed into a window aggregate “run condition” within the subquery (David Rowley)

    A SubPlan within such a clause would cause assertion failures or incorrect answers, as would some other unusual cases.

  • Disable startup progress reporting overhead in standby mode (Bharath Rupireddy)

    In standby mode, we don't actually report progress of recovery, but we were doing work to track it anyway.

  • Fix list_copy_head() to work correctly on an empty List (David Rowley)

    This case is not known to be reached by any core PostgreSQL code, but extensions might rely on it working.

  • Fix assertion failure for MERGE into a partitioned table with row-level security enabled (Dean Rasheed)

  • In contrib/basebackup_to_shell, properly detect failure to open a pipe (Robert Haas)

  • Fix handling of escape sequences in contrib/postgres_fdw's application_name parameter (Kyotaro Horiguchi, Michael Paquier)

    The code to expand these could fail if executed in a background process, as for example during auto-analyze of a foreign table.

  • In contrib/pg_walinspect, limit memory usage of pg_get_wal_records_info() (Bharath Rupireddy)

• ⇑ Upgrade to 15.4 released on 2023-08-10 - docs

  • Fix MERGE to enforce row security policies properly (Dean Rasheed)

    When MERGE performs an UPDATE action, it should enforce any UPDATE or SELECT RLS policies defined on the target table, to be consistent with the way that a plain UPDATE with a WHERE clause works. Instead it was enforcing INSERT RLS policies for both INSERT and UPDATE actions.

    In addition, when MERGE performs a DO NOTHING action, it applied the target table's DELETE RLS policies to existing rows, even though those rows are not being deleted. While it's not a security problem, this could result in unwanted errors.

    The PostgreSQL Project thanks Dean Rasheed for reporting this problem. (CVE-2023-39418)

  • Fix tracking of tables' access method dependencies (Michael Paquier)

    ALTER TABLE ... SET ACCESS METHOD failed to update relevant pg_depend entries when changing a table's access method. When using non-built-in access methods, this creates a risk that an access method could be dropped even though tables still depend on it. This fix corrects the logic in ALTER TABLE, but it will not adjust any already-missing pg_depend entries.

  • Ensure that all existing placeholders are checked for matches when an extension declares its GUC prefix to be reserved (Karina Litskevich, Ekaterina Sokolova)

    Faulty loop logic could cause some entries to be skipped.

  • Avoid leaking a stats entry for a subscription when it is dropped (Masahiko Sawada)

  • Silence bogus “missing contrecord” errors (Thomas Munro)

    Treat this case as plain end-of-WAL to avoid logging inaccurate complaints from pg_waldump and walsender.

  • Avoid assertion failure when the stats_fetch_consistency setting is changed intra-transaction (Kyotaro Horiguchi)

  • In psql, ignore the PSQL_WATCH_PAGER environment variable when stdin/stdout are not a terminal (Tom Lane)

    This corresponds to the treatment of PSQL_PAGER in commands besides \watch.

  • Improve pg_dump's display of details about dependency-loop problems (Tom Lane)

  • Avoid crash in pgbench with an empty pipeline and prepared mode (Álvaro Herrera)

  • Fix make_etags script to work with non-Exuberant ctags (Masahiko Sawada)

• ⇑ Upgrade to 16 released on 2023-09-14 - docs

  • Change assignment rules for PL/pgSQL bound cursor variables (Tom Lane)

    Previously, the string value of such variables was set to match the variable name during cursor assignment; now it will be assigned during OPEN, and will not match the variable name. To restore the previous behavior, assign the desired portal name to the cursor variable before OPEN.

  • Disallow NULLS NOT DISTINCT indexes for primary keys (Daniel Gustafsson)

  • Change REINDEX DATABASE and reindexdb to not process indexes on system catalogs (Simon Riggs)

    Processing such indexes is still possible using REINDEX SYSTEM and reindexdb --system.

  • Tighten GENERATED expression restrictions on inherited and partitioned tables (Amit Langote, Tom Lane)

    Columns of parent/partitioned and child/partition tables must all have the same generation status, though now the actual generation expressions can be different.

  • Remove pg_walinspect functions pg_get_wal_records_info_till_end_of_wal() and pg_get_wal_stats_till_end_of_wal() (Bharath Rupireddy)

  • Rename server variable force_parallel_mode to debug_parallel_query (David Rowley)

  • Remove the ability to create views manually with ON SELECT rules (Tom Lane)

  • Remove the server variable vacuum_defer_cleanup_age (Andres Freund)

    This has been unnecessary since hot_standby_feedback and replication slots were added.

  • Remove server variable promote_trigger_file (Simon Riggs)

    This was used to promote a standby to primary, but is now easier accomplished with pg_ctl promote or pg_promote().

  • Remove read-only server variables lc_collate and lc_ctype (Peter Eisentraut)

    Collations and locales can vary between databases so having them as read-only server variables was unhelpful.

  • Role inheritance now controls the default inheritance status of member roles added during GRANT (Robert Haas)

    The role's default inheritance behavior can be overridden with the new GRANT ... WITH INHERIT clause. This allows inheritance of some roles and not others because the members' inheritance status is set at GRANT time. Previously the inheritance status of member roles was controlled only by the role's inheritance status, and changes to a role's inheritance status affected all previous and future member roles.

  • Restrict the privileges of CREATEROLE and its ability to modify other roles (Robert Haas)

    Previously roles with CREATEROLE privileges could change many aspects of any non-superuser role. Such changes, including adding members, now require the role requesting the change to have ADMIN OPTION permission. For example, they can now change the CREATEDB, REPLICATION, and BYPASSRLS properties only if they also have those permissions.

  • Remove symbolic links for the postmaster binary (Peter Eisentraut)

  • Allow incremental sorts in more cases, including DISTINCT (David Rowley)

  • Add the ability for aggregates having ORDER BY or DISTINCT to use pre-sorted data (David Rowley)

    The new server variable enable_presorted_aggregate can be used to disable this.

  • Allow memoize atop a UNION ALL (Richard Guo)

  • Allow anti-joins to be performed with the non-nullable input as the inner relation (Richard Guo)

  • Allow parallelization of FULL and internal right OUTER hash joins (Melanie Plageman, Thomas Munro)

  • Improve the accuracy of GIN index access optimizer costs (Ronan Dunklau)

  • Allow more efficient addition of heap and index pages (Andres Freund)

  • During non-freeze operations, perform page freezing where appropriate (Peter Geoghegan)

    This makes full-table freeze vacuums less necessary.

  • Allow window functions to use the faster ROWS mode internally when RANGE mode is active but unnecessary (David Rowley)

  • Allow optimization of always-increasing window functions ntile(), cume_dist() and percent_rank() (David Rowley)

  • Allow aggregate functions string_agg() and array_agg() to be parallelized (David Rowley)

  • Improve performance by caching RANGE and LIST partition lookups (Amit Langote, Hou Zhijie, David Rowley)

  • Allow control of the shared buffer usage by vacuum and analyze (Melanie Plageman)

    The VACUUM/ANALYZE option is BUFFER_USAGE_LIMIT, and the vacuumdb option is --buffer-usage-limit. The default value is set by server variable vacuum_buffer_usage_limit, which also controls autovacuum.

  • Support wal_sync_method=fdatasync on Windows (Thomas Munro)

  • Allow HOT updates if only BRIN-indexed columns are updated (Matthias van de Meent, Josef Simanek, Tomas Vondra)

  • Improve the speed of updating the process title (David Rowley)

  • Allow xid/subxid searches and ASCII string detection to use vector operations (Nathan Bossart, John Naylor)

    ASCII detection is particularly useful for COPY FROM. Vector operations are also used for some C array searches.

  • Reduce overhead of memory allocations (Andres Freund, David Rowley)

  • Add system view pg_stat_io view to track I/O statistics (Melanie Plageman)

  • Record statistics on the last sequential and index scans on tables (Dave Page)

    This information appears in pg_stat_*_tables and pg_stat_*_indexes.

  • Record statistics on the occurrence of updated rows moving to new pages (Corey Huinker)

    The pg_stat_*_tables column is n_tup_newpage_upd.

  • Add speculative lock information to the pg_locks system view (Masahiko Sawada, Noriyoshi Shinoda)

    The transaction id is displayed in the transactionid column and the speculative insertion token is displayed in the objid column.

  • Add the display of prepared statement result types to the pg_prepared_statements view (Dagfinn Ilmari Mannsåker)

  • Create subscription statistics entries at subscription creation time so stats_reset is accurate (Andres Freund)

    Previously entries were created only when the first statistics were reported.

  • Correct the I/O accounting for temp relation writes shown in pg_stat_database (Melanie Plageman)

  • Add function pg_stat_get_backend_subxact() to report on a session's subtransaction cache (Dilip Kumar)

  • Have pg_stat_get_backend_idset(), pg_stat_get_backend_activity(), and related functions use the unchanging backend id (Nathan Bossart)

    Previously the index values might change during the lifetime of the session.

  • Report stand-alone backends with a special backend type (Melanie Plageman)

  • Add wait event SpinDelay to report spinlock sleep delays (Andres Freund)

  • Create new wait event DSMAllocate to indicate waiting for dynamic shared memory allocation (Thomas Munro)

    Previously this type of wait was reported as DSMFillZeroWrite, which was also used by mmap() allocations.

  • Add the database name to the process title of logical WAL senders (Tatsuhiro Nakamori)

    Physical WAL senders do not display a database name.

  • Add checkpoint and REDO LSN information to log_checkpoints messages (Bharath Rupireddy, Kyotaro Horiguchi)

  • Provide additional details during client certificate failures (Jacob Champion)

  • Add predefined role pg_create_subscription with permission to create subscriptions (Robert Haas)

  • Allow subscriptions to not require passwords (Robert Haas)

    This is accomplished with the option password_required=false.

  • Simplify permissions for LOCK TABLE (Jeff Davis)

    Previously a user's ability to perform LOCK TABLE at various lock levels was limited to the lock levels required by the commands they had permission to execute on the table. For example, someone with UPDATE permission could perform all lock levels except ACCESS SHARE, even though it was a lesser lock level. Now users can issue lesser lock levels if they already have permission for greater lock levels.

  • Allow GRANT group_name TO user_name to be performed with ADMIN OPTION (Robert Haas)

    Previously CREATEROLE permission was required.

  • Allow GRANT to use WITH ADMIN TRUE/FALSE syntax (Robert Haas)

    Previously only the WITH ADMIN OPTION syntax was supported.

  • Allow roles that create other roles to automatically inherit the new role's rights or the ability to SET ROLE to the new role (Robert Haas, Shi Yu)

    This is controlled by server variable createrole_self_grant.

  • Prevent users from changing the default privileges of non-inherited roles (Robert Haas)

    This is now only allowed for inherited roles.

  • When granting role membership, require the granted-by role to be a role that has appropriate permissions (Robert Haas)

    This is a requirement even when a non-bootstrap superuser is granting role membership.

  • Allow non-superusers to grant permissions using a granted-by user that is not the current user (Robert Haas)

    The current user still must have sufficient permissions given by the specified granted-by user.

  • Add GRANT to control permission to use SET ROLE (Robert Haas)

    This is controlled by a new GRANT ... SET option.

  • Add dependency tracking to roles which have granted privileges (Robert Haas)

    For example, removing ADMIN OPTION will fail if there are privileges using that option; CASCADE must be used to revoke dependent permissions.

  • Add dependency tracking of grantors for GRANT records (Robert Haas)

    This guarantees that pg_auth_members.grantor values are always valid.

  • Allow multiple role membership records (Robert Haas)

    Previously a new membership grant would remove a previous matching membership grant, even if other aspects of the grant did not match.

  • Prevent removal of superuser privileges for the bootstrap user (Robert Haas)

    Restoring such users could lead to errors.

  • Allow makeaclitem() to accept multiple privilege names (Robins Tharakan)

    Previously only a single privilege name, like SELECT, was accepted.

  • Add support for Kerberos credential delegation (Stephen Frost)

    This is enabled with server variable gss_accept_delegation and libpq connection parameter gssdelegation.

  • Allow the SCRAM iteration count to be set with server variable scram_iterations (Daniel Gustafsson)

  • Improve performance of server variable management (Tom Lane)

  • Tighten restrictions on which server variables can be reset (Masahiko Sawada)

    Previously, while certain variables, like transaction_isolation, were not affected by RESET ALL, they could be individually reset in inappropriate situations.

  • Move various postgresql.conf items into new categories (Shinya Kato)

    This also affects the categories displayed in the pg_settings view.

  • Prevent configuration file recursion beyond 10 levels (Julien Rouhaud)

  • Allow autovacuum to more frequently honor changes to delay settings (Melanie Plageman)

    Rather than honor changes only at the start of each relation, honor them at the start of each block.

  • Remove restrictions that archive files be durably renamed (Nathan Bossart)

    The archive_command command is now more likely to be called with already-archived files after a crash.

  • Prevent archive_library and archive_command from being set at the same time (Nathan Bossart)

    Previously archive_library would override archive_command.

  • Allow the postmaster to terminate children with an abort signal (Tom Lane)

    This allows collection of a core dump for a stuck child process. This is controlled by send_abort_for_crash and send_abort_for_kill. The postmaster's -T switch is now the same as setting send_abort_for_crash.

  • Remove the non-functional postmaster -n option (Tom Lane)

  • Allow the server to reserve backend slots for roles with pg_use_reserved_connections membership (Nathan Bossart)

    The number of reserved slots is set by server variable reserved_connections.

  • Allow huge pages to work on newer versions of Windows 10 (Thomas Munro)

    This adds the special handling required to enable huge pages on newer versions of Windows 10.

  • Add debug_io_direct setting for developer usage (Thomas Munro, Andres Freund, Bharath Rupireddy)

    While primarily for developers, wal_sync_method=open_sync/open_datasync has been modified to not use direct I/O with wal_level=minimal; this is now enabled with debug_io_direct=wal.

  • Add function pg_split_walfile_name() to report the segment and timeline values of WAL file names (Bharath Rupireddy)

  • Add support for regular expression matching on database and role entries in pg_hba.conf (Bertrand Drouvot)

    Regular expression patterns are prefixed with a slash. Database and role names that begin with slashes need to be double-quoted if referenced in pg_hba.conf.

  • Improve user-column handling of pg_ident.conf to match pg_hba.conf (Jelte Fennema)

    Specifically, add support for all, role membership with +, and regular expressions with a leading slash. Any user name that matches these patterns must be double-quoted.

  • Allow include files in pg_hba.conf and pg_ident.conf (Julien Rouhaud)

    These are controlled by include, include_if_exists, and include_dir. System views pg_hba_file_rules and pg_ident_file_mappings now display the file name.

  • Allow pg_hba.conf tokens to be of unlimited length (Tom Lane)

  • Add rule and map numbers to the system view pg_hba_file_rules (Julien Rouhaud)

  • Determine the default encoding from the locale when using ICU (Jeff Davis)

    Previously the default was always UTF-8.

  • Have CREATE DATABASE and CREATE COLLATION's LOCALE options, and initdb and createdb --locale options, control non-libc collation providers (Jeff Davis)

    Previously they only controlled libc providers.

  • Add predefined collations unicode and ucs_basic (Peter Eisentraut)

    This only works if ICU support is enabled.

  • Allow custom ICU collation rules to be created (Peter Eisentraut)

    This is done using CREATE COLLATION's new RULES clause, as well as new options for CREATE DATABASE, createdb, and initdb.

  • Allow Windows to import system locales automatically (Juan José Santamaría Flecha)

    Previously, only ICU locales could be imported on Windows.

  • Allow logical decoding on standbys (Bertrand Drouvot, Andres Freund, Amit Khandekar)

    Snapshot WAL records are required for logical slot creation but cannot be created on standbys. To avoid delays, the new function pg_log_standby_snapshot() allows creation of such records.

  • Add server variable to control how logical decoding publishers transfer changes and how subscribers apply them (Shi Yu)

    The variable is debug_logical_replication_streaming.

  • Allow logical replication initial table synchronization to copy rows in binary format (Melih Mutlu)

    This is only possible for subscriptions marked as binary.

  • Allow parallel application of logical replication (Hou Zhijie, Wang Wei, Amit Kapila)

    The CREATE SUBSCRIPTION STREAMING option now supports parallel to enable application of large transactions by parallel workers. The number of parallel workers is controlled by the new server variable max_parallel_apply_workers_per_subscription. Wait events LogicalParallelApplyMain, LogicalParallelApplyStateChange, and LogicalApplySendData were also added. Column leader_pid was added to system view pg_stat_subscription to track parallel activity.

  • Improve performance for logical replication apply without a primary key (Onder Kalaci, Amit Kapila)

    Specifically, REPLICA IDENTITY FULL can now use btree indexes rather than sequentially scanning the table to find matches.

  • Allow logical replication subscribers to process only changes that have no origin (Vignesh C, Amit Kapila)

    This can be used to avoid replication loops. This is controlled by the new CREATE SUBSCRIPTION ... ORIGIN option.

  • Perform logical replication SELECT and DML actions as the table owner (Robert Haas)

    This improves security and now requires subscription owners to be either superusers or to have SET ROLE permission on all roles owning tables in the replication set. The previous behavior of performing all operations as the subscription owner can be enabled with the subscription run_as_owner option.

  • Have wal_retrieve_retry_interval operate on a per-subscription basis (Nathan Bossart)

    Previously the retry time was applied globally. This also adds wait events >LogicalRepLauncherDSA and LogicalRepLauncherHash.

  • Add EXPLAIN option GENERIC_PLAN to display the generic plan for a parameterized query (Laurenz Albe)

  • Allow a COPY FROM value to map to a column's DEFAULT (Israel Barth Rubio)

  • Allow COPY into foreign tables to add rows in batches (Andrey Lepikhov, Etsuro Fujita)

    This is controlled by the postgres_fdw option batch_size.

  • Allow the STORAGE type to be specified by CREATE TABLE (Teodor Sigaev, Aleksander Alekseev)

    Previously only ALTER TABLE could control this.

  • Allow truncate triggers on foreign tables (Yugo Nagata)

  • Allow VACUUM and vacuumdb to only process TOAST tables (Nathan Bossart)

    This is accomplished by having VACUUM turn off PROCESS_MAIN or by vacuumdb using the --no-process-main option.

  • Add VACUUM options to skip or update all frozen statistics (Tom Lane, Nathan Bossart)

    The options are SKIP_DATABASE_STATS and ONLY_DATABASE_STATS.

  • Change REINDEX DATABASE and REINDEX SYSTEM to no longer require an argument (Simon Riggs)

    Previously the database name had to be specified.

  • Allow CREATE STATISTICS to generate a statistics name if none is specified (Simon Riggs)

  • Allow non-decimal integer literals (Peter Eisentraut)

    For example, 0x42F, 0o273, and 0b100101.

  • Allow NUMERIC to process hexadecimal, octal, and binary integers of any size (Dean Rasheed)

    Previously only unquoted eight-byte integers were supported with these non-decimal bases.

  • Allow underscores in integer and numeric constants (Peter Eisentraut, Dean Rasheed)

    This can improve readability for long strings of digits.

  • Accept the spelling +infinity in datetime input (Vik Fearing)

  • Prevent the specification of epoch and infinity together with other fields in datetime strings (Joseph Koshakow)

  • Remove undocumented support for date input in the form YyearMmonthDday (Joseph Koshakow)

  • Add functions pg_input_is_valid() and pg_input_error_info() to check for type conversion errors (Tom Lane)

  • Allow subqueries in the FROM clause to omit aliases (Dean Rasheed)

  • Add support for enhanced numeric literals in SQL/JSON paths (Peter Eisentraut)

    For example, allow hexadecimal, octal, and binary integers and underscores between digits.

  • Add SQL/JSON constructors (Nikita Glukhov, Teodor Sigaev, Oleg Bartunov, Alexander Korotkov, Amit Langote)

    The new functions JSON_ARRAY(), JSON_ARRAYAGG(), JSON_OBJECT(), and JSON_OBJECTAGG() are part of the SQL standard.

  • Add SQL/JSON object checks (Nikita Glukhov, Teodor Sigaev, Oleg Bartunov, Alexander Korotkov, Amit Langote, Andrew Dunstan)

    The IS JSON checks include checks for values, arrays, objects, scalars, and unique keys.

  • Allow JSON string parsing to use vector operations (John Naylor)

  • Improve the handling of full text highlighting function ts_headline() for OR and NOT expressions (Tom Lane)

  • Add functions to add, subtract, and generate timestamptz values in a specified time zone (Przemyslaw Sztoch, Gurjeet Singh)

    The functions are date_add(), date_subtract(), and generate_series().

  • Change date_trunc(unit, timestamptz, time_zone) to be an immutable function (Przemyslaw Sztoch)

    This allows the creation of expression indexes using this function.

  • Add server variable SYSTEM_USER (Bertrand Drouvot)

    This reports the authentication method and its authenticated user.

  • Add functions array_sample() and array_shuffle() (Martin Kalcher)

  • Add aggregate function ANY_VALUE() which returns any value from a set (Vik Fearing)

  • Add function random_normal() to supply normally-distributed random numbers (Paul Ramsey)

  • Add error function erf() and its complement erfc() (Dean Rasheed)

  • Improve the accuracy of numeric power() for integer exponents (Dean Rasheed)

  • Add XMLSERIALIZE() option INDENT to pretty-print its output (Jim Jones)

  • Change pg_collation_actual_version() to return a reasonable value for the default collation (Jeff Davis)

    Previously it returned NULL.

  • Allow pg_read_file() and pg_read_binary_file() to ignore missing files (Kyotaro Horiguchi)

  • Add byte specification (B) to pg_size_bytes() (Peter Eisentraut)

  • Allow to_reg* functions to accept numeric OIDs as input (Tom Lane)

  • Add the ability to get the current function's OID in PL/pgSQL (Pavel Stehule)

    This is accomplished with GET DIAGNOSTICS variable = PG_ROUTINE_OID.

  • Add libpq connection option require_auth to specify a list of acceptable authentication methods (Jacob Champion)

    This can also be used to disallow certain authentication methods.

  • Allow multiple libpq-specified hosts to be randomly selected (Jelte Fennema)

    This is enabled with load_balance_hosts=random and can be used for load balancing.

  • Add libpq option sslcertmode to control transmission of the client certificate (Jacob Champion)

    The option values are disable, allow, and require.

  • Allow libpq to use the system certificate pool for certificate verification (Jacob Champion, Thomas Habets)

    This is enabled with sslrootcert=system, which also enables sslmode=verify-full.

  • Allow ECPG variable declarations to use typedef names that match unreserved SQL keywords (Tom Lane)

    This change does prevent keywords which match C typedef names from being processed as keywords in later EXEC SQL blocks.

  • Allow psql to control the maximum width of header lines in expanded format (Platon Pronko)

    This is controlled by xheader_width.

  • Add psql command \drg to show role membership details (Pavel Luzanov)

    The Member of output column has been removed from \du and \dg because this new command displays this informaion in more detail.

  • Allow psql's access privilege commands to show system objects (Nathan Bossart)

    The options are \dpS and \zS.

  • Add FOREIGN designation to psql \d+ for foreign table children and partitions (Ian Lawrence Barwick)

  • Prevent \df+ from showing function source code (Isaac Morland)

    Function bodies are more easily viewed with \sf.

  • Allow psql to submit queries using the extended query protocol (Peter Eisentraut)

    Passing arguments to such queries is done using the new psql \bind command.

  • Allow psql \watch to limit the number of executions (Andrey Borodin)

    The \watch options can now be named when specified.

  • Detect invalid values for psql \watch, and allow zero to specify no delay (Andrey Borodin)

  • Allow psql scripts to obtain the exit status of shell commands and queries (Corey Huinker, Tom Lane)

    The new psql control variables are SHELL_ERROR and SHELL_EXIT_CODE.

  • Various psql tab completion improvements (Vignesh C, Aleksander Alekseev, Dagfinn Ilmari Mannsåker, Shi Yu, Michael Paquier, Ken Kato, Peter Smith)

  • Add pg_dump control of dumping child tables and partitions (Gilles Darold)

    The new options are --table-and-children, --exclude-table-and-children, and --exclude-table-data-and-children.

  • Add LZ4 and Zstandard compression to pg_dump (Georgios Kokolatos, Justin Pryzby)

  • Allow pg_dump and pg_basebackup to use long mode for compression (Justin Pryzby)

  • Improve pg_dump to accept a more consistent compression syntax (Georgios Kokolatos)

    Options like --compress=gzip:5.

  • Add initdb option to set server variables for the duration of initdb and all future server starts (Tom Lane)

    The option is -c name=value.

  • Add options to createuser to control more user options (Shinya Kato)

    Specifically, the new options control the valid-until date, bypassing of row-level security, and role membership.

  • Deprecate createuser option --role (Nathan Bossart)

    This option could be easily confused with new createuser role membership options, so option --member-of has been added with the same functionality. The --role option can still be used.

  • Allow control of vacuumdb schema processing (Gilles Darold)

    These are controlled by options --schema and --exclude-schema.

  • Use new VACUUM options to improve the performance of vacuumdb (Tom Lane, Nathan Bossart)

  • Have pg_upgrade set the new cluster's locale and encoding (Jeff Davis)

    This removes the requirement that the new cluster be created with the same locale and encoding settings.

  • Add pg_upgrade option to specify the default transfer mode (Peter Eisentraut)

    The option is --copy.

  • Improve pg_basebackup to accept numeric compression options (Georgios Kokolatos, Michael Paquier)

    Options like --compress=server-5 are now supported.

  • Fix pg_basebackup to handle tablespaces stored in the PGDATA directory (Robert Haas)

  • Add pg_waldump option --save-fullpage to dump full page images (David Christensen)

  • Allow pg_waldump options -t/--timeline to accept hexadecimal values (Peter Eisentraut)

  • Add support for progress reporting to pg_verifybackup (Masahiko Sawada)

  • Allow pg_rewind to properly track timeline changes (Heikki Linnakangas)

    Previously if pg_rewind was run after a timeline switch but before a checkpoint was issued, it might incorrectly determine that a rewind was unnecessary.

  • Have pg_receivewal and pg_recvlogical cleanly exit on SIGTERM (Christoph Berg)

    This signal is often used by systemd.

  • Build ICU support by default (Jeff Davis)

    This removes build flag --with-icu and adds flag --without-icu.

  • Add support for SSE2 (Streaming SIMD Extensions 2) vector operations on x86-64 architectures (John Naylor)

  • Add support for Advanced SIMD (Single Instruction Multiple Data) (NEON) instructions on ARM architectures (Nathan Bossart)

  • Have Windows binaries built with MSVC use RandomizedBaseAddress (ASLR) (Michael Paquier)

    This was already enabled on MinGW builds.

  • Prevent extension libraries from exporting their symbols by default (Andres Freund, Tom Lane)

    Functions that need to be called from the core backend or other extensions must now be explicitly marked PGDLLEXPORT.

  • Require Windows 10 or newer versions (Michael Paquier, Juan José Santamaría Flecha)

    Previously Windows Vista and Windows XP were supported.

  • Require Perl version 5.14 or later (John Naylor)

  • Require Bison version 2.3 or later (John Naylor)

  • Require Flex version 2.5.35 or later (John Naylor)

  • Require MIT Kerberos for GSSAPI support (Stephen Frost)

  • Remove support for Visual Studio 2013 (Michael Paquier)

  • Remove support for HP-UX (Thomas Munro)

  • Remove support for HP/Intel Itanium (Thomas Munro)

  • Remove support for M68K, M88K, M32R, and SuperH CPU architectures (Thomas Munro)

  • Remove libpq support for SCM credential authentication (Michael Paquier)

    Backend support for this authentication method was removed in PostgresSQL 9.1.

  • Add meson build system (Andres Freund, Nazir Bilal Yavuz, Peter Eisentraut)

    This eventually will replace the Autoconf and Windows-based MSVC build systems.

  • Allow control of the location of the openssl binary used by the build system (Peter Eisentraut)

    Make finding openssl program a configure or meson option

  • Add build option to allow testing of small WAL segment sizes (Andres Freund)

    The build options are --with-segsize-blocks and -Dsegsize_blocks.

  • Add pgindent options (Andrew Dunstan)

    The new options are --show-diff, --silent-diff, --commit, and --help, and allow multiple --exclude options. Also require the typedef file to be explicitly specified. Options --code-base and --build were also removed.

  • Add pg_bsd_indent source code to the main tree (Tom Lane)

  • Improve make_ctags and make_etags (Yugo Nagata)

  • Adjust pg_attribute columns for efficiency (Peter Eisentraut)

  • Improve use of extension-based indexes on boolean columns (Zongliang Quan, Tom Lane)

  • Add support for Daitch-Mokotoff Soundex to fuzzystrmatch (Dag Lem)

  • Allow auto_explain to log values passed to parameterized statements (Dagfinn Ilmari Mannsåker)

    This affects queries using server-side PREPARE/EXECUTE and client-side parse/bind. Logging is controlled by auto_explain.log_parameter_max_length; by default query parameters will be logged with no length restriction.

  • Have auto_explain's log_verbose mode honor the value of compute_query_id (Atsushi Torikoshi)

    Previously even if compute_query_id was enabled, log_verbose was not showing the query identifier.

  • Change the maximum length of ltree labels from 256 to 1000 and allow hyphens (Garen Torikian)

  • Have pg_stat_statements normalize constants used in utility commands (Michael Paquier)

    Previously constants appeared instead of placeholders, e.g., $1.

  • Add pg_walinspect function pg_get_wal_block_info() to report WAL block information (Michael Paquier, Melanie Plageman, Bharath Rupireddy)

  • Change how pg_walinspect functions pg_get_wal_records_info() and pg_get_wal_stats() interpret ending LSNs (Bharath Rupireddy)

    Previously ending LSNs which represent nonexistent WAL locations would generate an error, while they will now be interpreted as the end of the WAL.

  • Add detailed descriptions of WAL records in pg_walinspect and pg_waldump (Melanie Plageman, Peter Geoghegan)

  • Add pageinspect function bt_multi_page_stats() to report statistics on multiple pages (Hamid Akhtar)

    This is similar to bt_page_stats() except it can report on a range of pages.

  • Add empty range output column to pageinspect function brin_page_items() (Tomas Vondra)

  • Redesign archive modules to be more flexible (Nathan Bossart)

    Initialization changes will require modules written for older versions of Postgres to be updated.

  • Correct inaccurate pg_stat_statements row tracking extended query protocol statements (Sami Imseih)

  • Add pg_buffercache function pg_buffercache_usage_counts() to report usage totals (Nathan Bossart)

  • Add pg_buffercache function pg_buffercache_summary() to report summarized buffer statistics (Melih Mutlu)

  • Allow the schemas of required extensions to be referenced in extension scripts using the new syntax @extschema:referenced_extension_name@ (Regina Obe)

  • Allow required extensions to be marked as non-relocatable using no_relocate (Regina Obe)

    This allows @extschema:referenced_extension_name@ to be treated as a constant for the lifetime of the extension.

  • Allow postgres_fdw to do aborts in parallel (Etsuro Fujita)

    This is enabled with postgres_fdw option parallel_abort.

  • Make ANALYZE on foreign postgres_fdw tables more efficient (Tomas Vondra)

    The postgres_fdw option analyze_sampling controls the sampling method.

  • Restrict shipment of reg* type constants in postgres_fdw to those referencing built-in objects or extensions marked as shippable (Tom Lane)

  • Have postgres_fdw and dblink handle interrupts during connection establishment (Andres Freund)

• ⇑ Upgrade to 16.1 released on 2023-11-09 - docs

  • Fix inconsistent rechecking of concurrently-updated rows during MERGE (Dean Rasheed)

    In READ COMMITTED mode, an update that finds that its target row was just updated by a concurrent transaction will recheck the query's WHERE conditions on the updated row. MERGE failed to ensure that the proper rows of other joined tables were used during this recheck, possibly resulting in incorrect decisions about whether the newly-updated row should be updated again by MERGE.

  • Fix improper sharing of origin filter condition across successive pg_logical_slot_get_changes() calls (Hou Zhijie)

    The origin condition set by one call of this function would be re-used by later calls that did not specify the origin argument. This was not intended.

  • Fix ALTER SUBSCRIPTION so that a commanded change in the run_as_owner option is actually applied (Hou Zhijie)

  • Fix bulk table insertion into partitioned tables (Andres Freund)

    Improper sharing of insertion state across partitions could result in failures during COPY FROM, typically manifesting as “could not read block NNNN in file XXXX: read only 0 of 8192 bytes” errors.

  • In COPY FROM, avoid evaluating column default values that will not be needed by the command (Laurenz Albe)

    This avoids a possible error if the default value isn't actually valid for the column, or if the default's expression would fail in the current execution context. Such edge cases sometimes arise while restoring dumps, for example. Previous releases did not fail in this situation, so prevent v16 from doing so.

  • Fix “could not find pathkey item to sort” errors occurring while planning aggregate functions with ORDER BY or DISTINCT options (David Rowley)

  • Fix confusion about forced-flush behavior in pgstat_report_wal() (Ryoga Yoshida, Michael Paquier)

    This could result in some statistics about WAL I/O being forgotten in a shutdown.

  • Fix statistics tracking of temporary-table extensions (Karina Litskevich, Andres Freund)

    These were counted as normal-table writes when they should be counted as temp-table writes.

  • When track_io_timing is enabled, include the time taken by relation extension operations as write time (Nazir Bilal Yavuz)

  • Avoid a possible pfree-a-NULL-pointer crash after an error in OpenSSL connection setup (Sergey Shinderuk)

  • Fix pg_dump to dump the new run_as_owner option of subscriptions (Philip Warner)

    Due to this oversight, subscriptions would always be restored with run_as_owner set to false, which is not equivalent to their behavior in pre-v16 releases.

  • Fix vacuumdb's handling of multiple -N switches (Nathan Bossart, Kuwamura Masaki)

    Multiple -N switches should exclude tables in multiple schemas, but in fact excluded nothing due to faulty construction of a generated query.

  • Fix vacuumdb to honor its --buffer-usage-limit option in analyze-only mode (Ryoga Yoshida, David Rowley)

• ⇑ Upgrade to 16.2 released on 2024-02-08 - docs

  • Fix misprocessing of window function run conditions (Richard Guo)

    This oversight could lead to “WindowFunc not found in subplan target lists” errors.

  • Fix detection of inner-side uniqueness for Memoize plans (Richard Guo)

    This mistake could lead to “cache entry already complete” errors.

  • Fix computation of nullingrels when constant-folding field selection (Richard Guo)

    Failure to do this led to errors like “wrong varnullingrels (b) (expected (b 3)) for Var 2/2”.

  • Skip inappropriate actions when MERGE causes a cross-partition update (Dean Rasheed)

    When executing a MERGE UPDATE action on a partitioned table, if the UPDATE is turned into a DELETE and INSERT due to changing a partition key column, skip firing AFTER UPDATE ROW triggers, as well as other post-update actions such as RLS checks. These actions would typically fail, which is why a regular UPDATE doesn't do them in such cases; MERGE shouldn't either.

  • Cope with BEFORE ROW DELETE triggers in cross-partition MERGE updates (Dean Rasheed)

    If such a trigger attempted to prevent the update by returning NULL, MERGE would suffer an error or assertion failure.

  • Fix corruption of local buffer state when an error occurs while trying to extend a temporary table (Tender Wang)

  • Fix use of wrong tuple slot while evaluating DISTINCT aggregates that have multiple arguments (David Rowley)

    This mistake could lead to errors such as “attribute 1 of type record has wrong type”.

  • Fix DROP ROLE with duplicate role names (Michael Paquier)

    Previously this led to a “tuple already updated by self” failure. Instead, ignore the duplicate.

  • Allow scram_SaltedPassword() to be interrupted (Bowen Shi)

    With large scram_iterations values, this function could take a long time to run. Allow it to be interrupted by query cancel requests.

  • Ensure cached statistics are discarded after a change to stats_fetch_consistency (Shinya Kato)

    In some code paths, it was possible for stale statistics to be returned.

  • Fix ownership tests for large objects (Tom Lane)

    Operations on large objects that require ownership privilege failed with “unrecognized class ID: 2613”, unless run by a superuser.

  • Fix reporting of I/O timing data in EXPLAIN (BUFFERS) (Michael Paquier)

    The numbers labeled as “shared/local” actually refer only to shared buffers, so change that label to “shared”.

  • Ensure durability of CREATE DATABASE (Noah Misch)

    If an operating system crash occurred during or shortly after CREATE DATABASE, recovery could fail, or subsequent connections to the new database could fail. If a base backup was taken in that window, similar problems could be observed when trying to use the backup. The symptom would be that the database directory, PG_VERSION file, or pg_filenode.map file was missing or empty.

  • Add more LOG messages when starting and ending recovery from a backup (Andres Freund)

    This change provides additional information in the postmaster log that may be useful for diagnosing recovery problems.

  • Fix signal handling in walreceiver processes (Heikki Linnakangas)

    Revert a change that made walreceivers non-responsive to SIGTERM while waiting for the replication connection to be established.

  • Fix integer overflow hazard in checking whether a record will fit into the WAL decoding buffer (Thomas Munro)

    This bug appears to be only latent except when running a 32-bit PostgreSQL build on a 64-bit platform.

  • Ensure that column default values are correctly transmitted by the pgoutput logical replication plugin (Nikhil Benesch)

    ALTER TABLE ADD COLUMN with a constant default value for the new column avoids rewriting existing tuples, instead expecting that reading code will insert the correct default into a tuple that lacks that column. If replication was subsequently initiated on the table, pgoutput would transmit NULL instead of the correct default for such a column, causing incorrect replication on the subscriber.

  • Fix failure of logical replication's initial sync for a table with no columns (Vignesh C)

    This case generated an improperly-formatted COPY command.

  • Re-validate a subscription's connection string before use (Vignesh C)

    This is meant to detect cases where a subscription was created without a password (which is allowed to superusers) but then the subscription owner is changed to a non-superuser.

  • Fix minor memory leak in connection string validation for CREATE SUBSCRIPTION (Jeff Davis)

  • Change initdb to always un-comment the postgresql.conf entries for the lc_xxx parameters (Kyotaro Horiguchi)

    initdb used to work this way before v16, and now it does again. The change caused initdb's --no-locale option to not have the intended effect on lc_messages.

  • Properly detect out-of-memory in one code path in pg_dump (Daniel Gustafsson)

  • Move is_valid_ascii() from mb/pg_wchar.h to utils/ascii.h (Jubilee Young)

    This change avoids the need to include <simd.h> in pg_wchar.h, which was causing problems for some third-party code.

• ⇑ Upgrade to 16.3 released on 2024-05-09 - docs

  • Require SELECT privilege on the target table for MERGE with a DO NOTHING clause (Álvaro Herrera)

    SELECT privilege would be required in all practical cases anyway, but require it even if the query reads no columns of the target table. This avoids an edge case in which MERGE would require no privileges whatever, which seems undesirable even when it's a do-nothing command.

  • Fix handling of self-modified tuples in MERGE (Dean Rasheed)

    Throw an error if a target row joins to more than one source row, as required by the SQL standard. (The previous coding could silently ignore this condition if a concurrent update was involved.) Also, throw a non-misleading error if a target row is already updated by a later command in the current transaction, thanks to a BEFORE trigger or a volatile function used in the query.

  • Make ALTER TABLE ... ADD COLUMN create identity/serial sequences with the same persistence as their owning tables (Peter Eisentraut)

    CREATE UNLOGGED TABLE will make any owned sequences be unlogged too. ALTER TABLE missed that consideration, so that an added identity column would have a logged sequence, which seems pointless.

  • In CREATE DATABASE, recognize strategy keywords case-insensitively for consistency with other options (Tomas Vondra)

  • Fix EXPLAIN's output for subplans in MERGE (Dean Rasheed)

    EXPLAIN would sometimes fail to properly display subplan Params referencing variables in other parts of the plan tree.

  • Fix updating of visibility map state in VACUUM with the DISABLE_PAGE_SKIPPING option (Heikki Linnakangas)

    Due to an oversight, this mode caused all heap pages to be dirtied, resulting in excess I/O. Also, visibility map bits that were incorrectly set would not get cleared.

  • Fix buffer usage reporting for parallel vacuuming (Anthonin Bonnefoy)

    Buffer accesses performed by parallel workers were not getting counted in the statistics reported in VERBOSE mode.

  • Fix “could not find pathkey item to sort” errors occurring while planning aggregate functions with ORDER BY or DISTINCT options (David Rowley)

    This is similar to a fix applied in 16.1, but it solves the problem for parallel plans.

  • Prevent potentially-incorrect optimization of some window functions (David Rowley)

    Disable “run condition” optimization of ntile() and count() with non-constant arguments. This avoids possible misbehavior with sub-selects, typically leading to errors like “WindowFunc not found in subplan target lists”.

  • Use a hash table instead of linear search for “catcache list” objects (Tom Lane)

    This change solves performance problems that were reported for certain operations in installations with many thousands of roles.

  • Fix race condition that could lead to reporting an incorrect conflict cause when invalidating a replication slot (Bertrand Drouvot)

  • Fix race condition in deciding whether a table sync operation is needed in logical replication (Vignesh C)

    An invalidation event arriving while a subscriber identifies which tables need to be synced would be forgotten about, so that any tables newly in need of syncing might not get processed in a timely fashion.

  • Fix thread-safety of error reporting for getaddrinfo() on Windows (Thomas Munro)

    A multi-threaded libpq client program could get an incorrect or corrupted error message after a network lookup failure.

  • In initdb's -c option, match parameter names case-insensitively (Tom Lane)

    The server treats parameter names case-insensitively, so this code should too. This avoids putting redundant entries into the generated postgresql.conf file.

  • In psql, avoid leaking a query result after the query is cancelled (Tom Lane)

    This happened only when cancelling a non-last query in a query string made with \; separators.

  • Skip files named .DS_Store in pg_basebackup, pg_checksums, and pg_rewind (Daniel Gustafsson)

    This avoids problems on macOS, where the Finder may create such files.

• ⇑ Upgrade to 16.4 released on 2024-08-08 - docs

  • Avoid incorrect results from Merge Right Anti Join plans (Richard Guo)

    If the inner relation is known to have unique join keys, the merge could misbehave when there are duplicated join keys in the outer relation.

  • Fix input of ISO-8601 “extended” time format for types time and timetz (Tom Lane)

    Re-allow cases such as T12:34:56.

  • Fix result for pg_size_pretty() when applied to the smallest possible bigint value (Joseph Koshakow)

  • Fix unportable usage of strnxfrm() (Jeff Davis)

    Some code paths for non-deterministic collations could fail with errors like “pg_strnxfrm() returned unexpected result”.

  • Lock owned sequences during ALTER TABLE SET LOGGED|UNLOGGED (Noah Misch)

    These commands change the persistence of a table's owned sequences along with the table, but they failed to acquire lock on the sequences while doing so. This could result in losing the effects of concurrent nextval() calls.

  • Re-forbid underscore in positional parameters (Erik Wienhold)

    As of v16 we allow integer literals to contain underscores. This change caused input such as $1_234 to be taken as a single token, but it did not work correctly. It seems better to revert to the original definition in which a parameter symbol is only $ followed by digits.

  • Avoid “can only drop stats once” error during replication slot creation and drop (Floris Van Nee)

  • Fix resource leakage in logical replication WAL sender (Hou Zhijie)

    The walsender process leaked memory when publishing changes to a partitioned table whose partitions have row types physically different from the partitioned table's.

  • Prevent leakage of reference counts for the shared memory block used for statistics (Anthonin Bonnefoy)

    A new backend process attaching to the statistics shared memory incremented its reference count, but failed to decrement the count when exiting. After 2³² sessions had been created, the reference count would overflow to zero, causing failures in all subsequent backend process starts.

  • Fix PL/pgSQL's handling of integer ranges containing underscores (Erik Wienhold)

    As of v16 we allow integer literals to contain underscores, but PL/pgSQL failed to handle examples such as FOR i IN 1_001..1_003.

  • Avoid memory leak within pg_dump during a binary upgrade (Daniel Gustafsson)

  • Allow contrib/pg_stat_statements to distinguish among utility statements appearing within SQL-language functions (Anthonin Bonnefoy)

    The SQL-language function executor failed to pass along the query ID that is computed for a utility (non SELECT/INSERT/UPDATE/DELETE/MERGE) statement.

  • Avoid “cursor can only scan forward” error in contrib/postgres_fdw (Etsuro Fujita)

    This error could occur if the remote server is v15 or later and a foreign table is mapped to a non-trivial remote view.

• ⇑ Upgrade to 17 released on 2024-09-26 - docs

  • Change functions to use a safe search_path during maintenance operations (Jeff Davis) §

    This prevents maintenance operations (ANALYZE, CLUSTER, REFRESH MATERIALIZED VIEW, REINDEX, or VACUUM) from performing unsafe access. Functions used by expression indexes and materialized views that need to reference non-default schemas must specify a search path during function creation.

  • Restrict ago to only appear at the end in interval values (Joseph Koshakow) § §

    Also, prevent empty interval units from appearing multiple times.

  • Remove server variable old_snapshot_threshold (Thomas Munro) §

    This variable allowed vacuum to remove rows that potentially could be still visible to running transactions, causing "snapshot too old" errors later if accessed. This feature might be re-added to PostgreSQL later if an improved implementation is found.

  • Change SET SESSION AUTHORIZATION handling of the initial session user's superuser status (Joseph Koshakow) §

    The new behavior is based on the session user's superuser status at the time the SET SESSION AUTHORIZATION command is issued, rather than their superuser status at connection time.

  • Remove feature which simulated per-database users (Nathan Bossart) §

    The feature, db_user_namespace, was rarely used.

  • Remove wal_sync_method value fsync_writethrough on Windows (Thomas Munro) §

    This value was the same as fsync on Windows.

  • Change file boundary handling of two WAL file name functions (Kyotaro Horiguchi, Andres Freund, Bruce Momjian) §

    The functions pg_walfile_name() and pg_walfile_name_offset() used to report the previous LSN segment number when the LSN was on a file segment boundary; it now returns the current LSN segment.

  • Remove server variable trace_recovery_messages since it is no longer needed (Bharath Rupireddy) §

  • Remove information schema column element_types.domain_default (Peter Eisentraut) §

  • Change pgrowlocks lock mode output labels (Bruce Momjian) §

  • Remove buffers_backend and buffers_backend_fsync from pg_stat_bgwriter (Bharath Rupireddy) §

    These fields are considered redundant to similar columns in pg_stat_io.

  • Rename I/O block read/write timing statistics columns of pg_stat_statements (Nazir Bilal Yavuz) §

    This renames blk_read_time to shared_blk_read_time, and blk_write_time to shared_blk_write_time.

  • Change pg_attribute.attstattarget and pg_statistic_ext.stxstattarget to represent the default statistics target as NULL (Peter Eisentraut) § §

  • Rename pg_collation.colliculocale to colllocale and pg_database.daticulocale to datlocale (Jeff Davis) §

  • Rename pg_stat_progress_vacuum column max_dead_tuples to max_dead_tuple_bytes, rename num_dead_tuples to num_dead_item_ids, and add dead_tuple_bytes (Masahiko Sawada) § §

  • Rename SLRU columns in system view pg_stat_slru (Alvaro Herrera) §

    The column names accepted by pg_stat_reset_slru() are also changed.

  • Allow the optimizer to improve CTE plans by considering the statistics and sort order of columns referenced in earlier row output clauses (Jian Guo, Richard Guo, Tom Lane) § §

  • Improve optimization of IS NOT NULL and IS NULL query restrictions (David Rowley, Richard Guo, Andy Fan) § §

    Remove IS NOT NULL restrictions from queries on NOT NULL columns and eliminate scans on NOT NULL columns if IS NULL is specified.

  • Allow partition pruning on boolean columns on IS [NOT] UNKNOWN conditionals (David Rowley) §

  • Improve optimization of range values when using containment operators <@ and @> (Kim Johan Andersson, Jian He) §

  • Allow correlated IN subqueries to be transformed into joins (Andy Fan, Tom Lane) §

  • Improve optimization of the LIMIT clause on partitioned tables, inheritance parents, and UNION ALL queries (Andy Fan, David Rowley) §

  • Allow query nodes to be run in parallel in more cases (Tom Lane) §

  • Allow GROUP BY columns to be internally ordered to match ORDER BY (Andrei Lepikhov, Teodor Sigaev) §

    This can be disabled using server variable enable_group_by_reordering.

  • Allow UNION (without ALL) to use MergeAppend (David Rowley) §

  • Fix MergeAppend plans to more accurately compute the number of rows that need to be sorted (Alexander Kuzmenkov) §

  • Allow GiST and SP-GiST indexes to be part of incremental sorts (Miroslav Bendik) §

    This is particularly useful for ORDER BY clauses where the first column has a GiST and SP-GiST index, and other columns do not.

  • Add columns to pg_stats to report range-type histogram information (Egor Rogov, Soumyadeep Chakraborty) §

  • Allow btree indexes to more efficiently find a set of values, such as those supplied by IN clauses using constants (Peter Geoghegan, Matthias van de Meent) §

  • Allow BRIN indexes to be created using parallel workers (Tomas Vondra, Matthias van de Meent) §

  • Allow vacuum to more efficiently remove and freeze tuples (Melanie Plageman, Heikki Linnakangas) §

    WAL traffic caused by vacuum is also more compact.

  • Allow vacuum to more efficiently store tuple references (Masahiko Sawada, John Naylor) § § § §

    Additionally, vacuum is no longer silently limited to one gigabyte of memory when maintenance_work_mem or autovacuum_work_mem are higher.

  • Optimize vacuuming of relations with no indexes (Melanie Plageman) §

  • Increase default vacuum_buffer_usage_limit to 2MB (Thomas Munro) §

  • Improve performance when checking roles with many memberships (Nathan Bossart) §

  • Improve performance of heavily-contended WAL writes (Bharath Rupireddy) §

  • Improve performance when transferring large blocks of data to a client (Melih Mutlu) §

  • Allow the grouping of file system reads with the new system variable io_combine_limit (Thomas Munro, Andres Freund, Melanie Plageman, Nazir Bilal Yavuz) § § §

  • Create system view pg_stat_checkpointer (Bharath Rupireddy, Anton A. Melnikov, Alexander Korotkov) § § §

    Relevant columns have been removed from pg_stat_bgwriter and added to this new system view.

  • Improve control over resetting statistics (Atsushi Torikoshi, Bharath Rupireddy) § § §

    Allow pg_stat_reset_shared() (with no arguments) and pg_stat_reset_shared(NULL) to reset all shared statistics. Allow pg_stat_reset_shared('slru') and pg_stat_reset_slru() (with no arguments) to reset SLRU statistics, which was already possible with pg_stat_reset_slru(NULL).

  • Add log messages related to WAL recovery from backups (Andres Freund) §

  • Add log_connections log line for trust connections (Jacob Champion) §

  • Add log message to report walsender acquisition and release of replication slots (Bharath Rupireddy) §

    This is enabled by the server variable log_replication_commands.

  • Add system view pg_wait_events that reports wait event types (Bertrand Drouvot) §

    This is useful for adding descriptions to wait events reported in pg_stat_activity.

  • Add wait events for checkpoint delays (Thomas Munro) §

  • Allow vacuum to report the progress of index processing (Sami Imseih) §

    This appears in system view pg_stat_progress_vacuum columns indexes_total and indexes_processed.

  • Allow granting the right to perform maintenance operations (Nathan Bossart) §

    The permission can be granted on a per-table basis using the MAINTAIN privilege and on a per-role basis via the pg_maintain predefined role. Permitted operations are VACUUM, ANALYZE, REINDEX, REFRESH MATERIALIZED VIEW, CLUSTER, and LOCK TABLE.

  • Allow roles with pg_monitor membership to execute pg_current_logfile() (Pavlo Golub, Nathan Bossart) §

  • Add system variable allow_alter_system to disallow ALTER SYSTEM (Jelte Fennema-Nio, Gabriele Bartolini) §

  • Allow ALTER SYSTEM to set unrecognized custom server variables (Tom Lane) §

    This is also possible with GRANT ON PARAMETER.

  • Add server variable transaction_timeout to restrict the duration of transactions (Andrey Borodin, Japin Li, Junwang Zhao, Alexander Korotkov) § § §

  • Add a builtin platform-independent collation provider (Jeff Davis) § § § §

    This supports C and C.UTF-8 collations.

  • Add server variable huge_pages_status to report the use of huge pages by Postgres (Justin Pryzby) §

    This is useful when huge_pages is set to try.

  • Add server variable to disable event triggers (Daniel Gustafsson) §

    The setting, event_triggers, allows for the temporary disabling of event triggers for debugging.

  • Allow the SLRU cache sizes to be configured (Andrey Borodin, Dilip Kumar, Alvaro Herrera) §

    The new server variables are commit_timestamp_buffers, multixact_member_buffers, multixact_offset_buffers, notify_buffers, serializable_buffers, subtransaction_buffers, and transaction_buffers. commit_timestamp_buffers, transaction_buffers, and subtransaction_buffers scale up automatically with shared_buffers.

  • Add support for incremental file system backup (Robert Haas, Jakub Wartak, Tomas Vondra) § §

    Incremental backups can be created using pg_basebackup's new --incremental option. The new application pg_combinebackup allows manipulation of base and incremental file system backups.

  • Allow the creation of WAL summarization files (Robert Haas, Nathan Bossart, Hubert Depesz Lubaczewski) § § § §

    These files record the block numbers that have changed within an LSN range and are useful for incremental file system backups. This is controlled by the server variables summarize_wal and wal_summary_keep_time, and introspected with pg_available_wal_summaries(), pg_wal_summary_contents(), and pg_get_wal_summarizer_state().

  • Add the system identifier to file system backup manifest files (Amul Sul) §

    This helps detect invalid WAL usage.

  • Allow connection string value dbname to be written when pg_basebackup writes connection information to postgresql.auto.conf (Vignesh C, Hayato Kuroda) §

  • Add column pg_replication_slots.invalidation_reason to report the reason for invalid slots (Shveta Malik, Bharath Rupireddy) § §

  • Add column pg_replication_slots.inactive_since to report slot inactivity duration (Bharath Rupireddy) § § §

  • Add function pg_sync_replication_slots() to synchronize logical replication slots (Hou Zhijie, Shveta Malik, Ajin Cherian, Peter Eisentraut) § §

  • Add the failover property to the replication protocol (Hou Zhijie, Shveta Malik) §

  • Add application pg_createsubscriber to create a logical replica from a physical standby server (Euler Taveira) §

  • Have pg_upgrade migrate valid logical slots and subscriptions (Hayato Kuroda, Hou Zhijie, Vignesh C, Julien Rouhaud, Shlok Kyal) § §

    This allows logical replication to continue quickly after the upgrade. This only works for old PostgreSQL clusters that are version 17 or later.

  • Enable the failover of logical slots (Hou Zhijie, Shveta Malik, Ajin Cherian) §

    This is controlled by an optional fifth argument to pg_create_logical_replication_slot().

  • Add server variable sync_replication_slots to enable failover logical slot synchronization (Shveta Malik, Hou Zhijie, Peter Smith) § §

  • Add logical replication failover control to CREATE/ALTER SUBSCRIPTION (Shveta Malik, Hou Zhijie, Ajin Cherian) § §

  • Allow the application of logical replication changes to use hash indexes on the subscriber (Hayato Kuroda) §

    Previously only btree indexes could be used for this purpose.

  • Improve logical decoding performance in cases where there are many subtransactions (Masahiko Sawada) §

  • Restart apply workers if subscription owner's superuser privileges are revoked (Vignesh C) §

    This forces reauthentication.

  • Add flush option to pg_logical_emit_message() (Michael Paquier) §

    This makes the message durable.

  • Allow specification of physical standbys that must be synchronized before they are visible to subscribers (Hou Zhijie, Shveta Malik) § §

    The new server variable is synchronized_standby_slots.

  • Add worker type column to pg_stat_subscription (Peter Smith) §

  • Add new COPY option ON_ERROR ignore to discard error rows (Damir Belyalov, Atsushi Torikoshi, Alex Shulgin, Jian He, Yugo Nagata) § § § §

    The default behavior is ON_ERROR stop.

  • Add new COPY option LOG_VERBOSITY which reports COPY FROM ignored error rows (Bharath Rupireddy) §

  • Allow COPY FROM to report the number of skipped rows during processing (Atsushi Torikoshi) §

    This appears in system view column pg_stat_progress_copy.tuples_skipped.

  • In COPY FROM, allow easy specification that all columns should be forced null or not null (Zhang Mingli) §

  • Allow partitioned tables to have identity columns (Ashutosh Bapat) §

  • Allow exclusion constraints on partitioned tables (Paul A. Jungwirth) §

    As long as exclusion constraints compare partition key columns for equality, other columns can use exclusion constraint-specific comparisons.

  • Add clearer ALTER TABLE method to set a column to the default statistics target (Peter Eisentraut) §

    The new syntax is ALTER TABLE ... SET STATISTICS DEFAULT; using SET STATISTICS -1 is still supported.

  • Allow ALTER TABLE to change a column's generation expression (Amul Sul) §

    The syntax is ALTER TABLE ... ALTER COLUMN ... SET EXPRESSION.

  • Allow specification of table access methods on partitioned tables (Justin Pryzby, Soumyadeep Chakraborty, Michael Paquier) § §

  • Add DEFAULT setting for ALTER TABLE .. SET ACCESS METHOD (Michael Paquier) §

  • Add support for event triggers that fire at connection time (Konstantin Knizhnik, Mikhail Gribkov) §

  • Add event trigger support for REINDEX (Garrett Thornburg, Jian He) §

  • Allow parenthesized syntax for CLUSTER options if a table name is not specified (Nathan Bossart) §

  • Allow EXPLAIN to report optimizer memory usage (Ashutosh Bapat) §

    The option is called MEMORY.

  • Add EXPLAIN option SERIALIZE to report the cost of converting data for network transmission (Stepan Rutz, Matthias van de Meent) §

  • Add local I/O block read/write timing statistics to EXPLAIN's BUFFERS output (Nazir Bilal Yavuz) §

  • Improve EXPLAIN's display of SubPlan nodes and output parameters (Tom Lane, Dean Rasheed) §

  • Add JIT deform_counter details to EXPLAIN (Dmitry Dolgov) §

  • Allow the interval data type to support +/-infinity values (Joseph Koshakow, Jian He, Ashutosh Bapat) §

  • Allow the use of an ENUM added via ALTER TYPE if the type was created in the same transaction (Tom Lane) §

    This was previously disallowed.

  • Allow MERGE to modify updatable views (Dean Rasheed) §

  • Add WHEN NOT MATCHED BY SOURCE to MERGE (Dean Rasheed) §

    WHEN NOT MATCHED on target rows was already supported.

  • Allow MERGE to use the RETURNING clause (Dean Rasheed) §

    The new RETURNING function merge_action() reports on the DML that generated the row.

  • Add function JSON_TABLE() to convert JSON data to a table representation (Nikita Glukhov, Teodor Sigaev, Oleg Bartunov, Alexander Korotkov, Andrew Dunstan, Amit Langote, Jian He) § §

    This function can be used in the FROM clause of SELECT queries as a tuple source.

  • Add SQL/JSON constructor functions JSON(), JSON_SCALAR(), and JSON_SERIALIZE() (Nikita Glukhov, Teodor Sigaev, Oleg Bartunov, Alexander Korotkov, Andrew Dunstan, Amit Langote) §

  • Add SQL/JSON query functions JSON_EXISTS(), JSON_QUERY(), and JSON_VALUE() (Nikita Glukhov, Teodor Sigaev, Oleg Bartunov, Alexander Korotkov, Andrew Dunstan, Amit Langote, Peter Eisentraut, Jian He) § § § § §

  • Add jsonpath methods to convert JSON values to other JSON data types (Jeevan Chalke) §

    The jsonpath methods are .bigint(), .boolean(), .date(), .decimal([precision [, scale]]), .integer(), .number(), .string(), .time(), .time_tz(), .timestamp(), and .timestamp_tz().

  • Add to_timestamp() time zone format specifiers (Tom Lane) §

    TZ accepts time zone abbreviations or numeric offsets, while OF accepts only numeric offsets.

  • Allow the session time zone to be specified by AS LOCAL (Vik Fearing) §

    This is useful when converting adding and removing time zones from time stamps values, rather than specifying the literal session time zone.

  • Add functions uuid_extract_timestamp() and uuid_extract_version() to return UUID information (Andrey Borodin) §

  • Add functions to generate random numbers in a specified range (Dean Rasheed) §

    The functions are random(min, max) and they take values of type integer, bigint, and numeric.

  • Add functions to convert integers to binary and octal strings (Eric Radman, Nathan Bossart) §

    The functions are to_bin() and to_oct().

  • Add Unicode informational functions (Jeff Davis) §

    Function unicode_version() returns the Unicode version, icu_unicode_version() returns the ICU version, and unicode_assigned() returns if the characters are assigned Unicode codepoints.

  • Add function xmltext() to convert text to a single XML text node (Jim Jones) §

  • Add function to_regtypemod() to return the type modifier of a type specification (David Wheeler, Erik Wienhold) §

  • Add pg_basetype() function to return a domain's base type (Steve Chavez) §

  • Add function pg_column_toast_chunk_id() to return a value's TOAST identifier (Yugo Nagata) §

    This returns NULL if the value is not stored in TOAST.

  • Allow plpgsql %TYPE and %ROWTYPE specifications to represent arrays of non-array types (Quan Zongliang, Pavel Stehule) §

  • Allow plpgsql %TYPE specification to reference composite column (Tom Lane) §

  • Add libpq function to change role passwords (Joe Conway) §

    The new function, PQchangePassword(), hashes the new password before sending it to the server.

  • Add libpq functions to close portals and prepared statements (Jelte Fennema-Nio) §

    The functions are PQclosePrepared(), PQclosePortal(), PQsendClosePrepared(), and PQsendClosePortal().

  • Add libpq API which allows for blocking and non-blocking cancel requests, with encryption if already in use (Jelte Fennema-Nio) §

    Previously only blocking, unencrypted cancel requests were supported.

  • Add libpq function PQsocketPoll() to allow polling of network sockets (Tristan Partin, Tom Lane) § §

  • Add libpq function PQsendPipelineSync() to send a pipeline synchronization point (Anton Kirilov) §

    This is similar to PQpipelineSync() but it does not flush to the server unless the size threshold of the output buffer is reached.

  • Add libpq function PQsetChunkedRowsMode() to allow retrieval of results in chunks (Daniel Vérité) §

  • Allow TLS connections without requiring a network round-trip negotiation (Greg Stark, Heikki Linnakangas, Peter Eisentraut, Michael Paquier, Daniel Gustafsson) § § § § § § § §

    This is enabled with the client-side option sslnegotiation=direct, requires ALPN, and only works on PostgreSQL 17 and later servers.

  • Improve psql display of default and empty privileges (Erik Wienhold, Laurenz Albe) §

    Command \dp now displays (none) for empty privileges; default still displays as empty.

  • Have backslash commands honor \pset null (Erik Wienhold, Laurenz Albe) §

    Previously \pset null was ignored.

  • Allow psql's \watch to stop after a minimum number of rows returned (Greg Sabino Mullane) §

    The parameter is min_rows.

  • Allow psql connection attempts to be canceled with control-C (Tristan Partin) §

  • Allow psql to honor FETCH_COUNT for non-SELECT queries (Daniel Vérité) §

  • Improve psql tab completion (Dagfinn Ilmari Mannsåker, Gilles Darold, Christoph Heiss, Steve Chavez, Vignesh C, Pavel Borisov, Jian He) § § § § § § § §

  • Add application pg_walsummary to dump WAL summary files (Robert Haas) §

  • Allow pg_dump's large objects to be restorable in batches (Tom Lane) §

    This allows the restoration of many large objects to avoid transaction limits and to be restored in parallel.

  • Add pg_dump option --exclude-extension (Ayush Vatsa) §

  • Allow pg_dump, pg_dumpall, and pg_restore to specify include/exclude objects in a file (Pavel Stehule, Daniel Gustafsson) §

    The option is called --filter.

  • Add the --sync-method parameter to several client applications (Justin Pryzby, Nathan Bossart) §

    The applications are initdb, pg_basebackup, pg_checksums, pg_dump, pg_rewind, and pg_upgrade.

  • Add pg_restore option --transaction-size to allow object restores in transaction batches (Tom Lane) §

    This allows the performance benefits of transaction batches without the problems of excessively large transaction blocks.

  • Change pgbench debug mode option from -d to --debug (Greg Sabino Mullane) §

    Option -d is now used for the database name, and the new --dbname option can be used as well.

  • Add pgbench option --exit-on-abort to exit after any client aborts (Yugo Nagata) §

  • Add pgbench command \syncpipeline to allow sending of sync messages (Anthonin Bonnefoy) §

  • Allow pg_archivecleanup to remove backup history files (Atsushi Torikoshi) §

    The option is --clean-backup-history.

  • Add some long options to pg_archivecleanup (Atsushi Torikoshi) §

    The long options are --debug, --dry-run, and --strip-extension.

  • Allow pg_basebackup and pg_receivewal to use dbname in their connection specification (Jelte Fennema-Nio) §

    This is useful for connection poolers that are sensitive to the database name.

  • Add pg_upgrade option --copy-file-range (Thomas Munro) §

    This is supported on Linux and FreeBSD.

  • Allow reindexdb --index to process indexes from different tables in parallel (Maxim Orlov, Svetlana Derevyanko, Alexander Korotkov) §

  • Allow reindexdb, vacuumdb, and clusterdb to process objects in all databases matching a pattern (Nathan Bossart) § § §

    The new option --all controls this behavior.

  • Remove support for OpenSSL 1.0.1 (Michael Paquier) §

  • Allow tests to pass in OpenSSL FIPS mode (Peter Eisentraut) § §

  • Use CPU AVX-512 instructions for bit counting (Paul Amonson, Nathan Bossart, Ants Aasma) § §

  • Require LLVM version 10 or later (Thomas Munro) §

  • Use native CRC instructions on 64-bit LoongArch CPUs (Xudong Yang) §

  • Remove AIX support (Heikki Linnakangas) §

  • Remove the Microsoft Visual Studio-specific PostgreSQL build option (Michael Paquier) §

    Meson is now the only available method for Visual Studio builds.

  • Remove configure option --disable-thread-safety (Thomas Munro, Heikki Linnakangas) § §

    We now assume all supported platforms have sufficient thread support.

  • Remove configure option --with-CC (Heikki Linnakangas) §

    Setting the CC environment variable is now the only supported method for specifying the compiler.

  • User-defined data type receive functions will no longer receive their data null-terminated (David Rowley) §

  • Add incremental JSON parser for use with huge JSON documents (Andrew Dunstan) §

  • Convert top-level README file to Markdown (Nathan Bossart) §

  • Remove no longer needed top-level INSTALL file (Tom Lane) §

  • Remove make's distprep option (Peter Eisentraut) §

  • Add make support for Android shared libraries (Peter Eisentraut) §

  • Add backend support for injection points (Michael Paquier) § § § §

    This is used for server debugging and they must be enabled at server compile time.

  • Add dynamic shared memory registry (Nathan Bossart) §

    This allows shared libraries which are not initialized at startup to coordinate dynamic shared memory access.

  • Fix emit_log_hook to use the same time value as other log records for the same query (Kambam Vinay, Michael Paquier) §

  • Improve documentation for using jsonpath for predicate checks (David Wheeler) §

  • Allow joins with non-join qualifications to be pushed down to foreign servers and custom scans (Richard Guo, Etsuro Fujita) §

    Foreign data wrappers and custom scans will need to be modified to handle these cases.

  • Allow pushdown of EXISTS and IN subqueries to postgres_fdw foreign servers (Alexander Pyhalov) §

  • Increase the default foreign data wrapper tuple cost (David Rowley, Umair Shahid) § §

    This value is used by the optimizer.

  • Allow dblink database operations to be interrupted (Noah Misch) §

  • Allow the creation of hash indexes on ltree columns (Tommy Pavlicek) §

    This also enables hash join and hash aggregation on ltree columns.

  • Allow unaccent character translation rules to contain whitespace and quotes (Michael Paquier) §

    The syntax for the unaccent.rules file has changed.

  • Allow amcheck to check for unique constraint violations using new option --checkunique (Anastasia Lubennikova, Pavel Borisov, Maxim Orlov) §

  • Allow citext tests to pass in OpenSSL FIPS mode (Peter Eisentraut) §

  • Allow pgcrypto tests to pass in OpenSSL FIPS mode (Peter Eisentraut) §

  • Remove some unused SPI macros (Bharath Rupireddy) §

  • Remove adminpack contrib extension (Daniel Gustafsson) §

    This was used by now end-of-life pgAdmin III.

  • Allow ALTER OPERATOR to set more optimization attributes (Tommy Pavlicek) §

    This is useful for extensions.

  • Allow extensions to define custom wait events (Masahiro Ikeda) § § § §

    Custom wait events have been added to postgres_fdw and dblink.

  • Add pg_buffercache function pg_buffercache_evict() to allow shared buffer eviction (Palak Chaturvedi, Thomas Munro) §

    This is useful for testing.

  • Replace CALL parameters in pg_stat_statements with placeholders (Sami Imseih) §

  • Replace savepoint names stored in pg_stat_statements with placeholders (Greg Sabino Mullane) §

    This greatly reduces the number of entries needed to record SAVEPOINT, RELEASE SAVEPOINT, and ROLLBACK TO SAVEPOINT commands.

  • Replace the two-phase commit GIDs stored in pg_stat_statements with placeholders (Michael Paquier) §

    This greatly reduces the number of entries needed to record PREPARE TRANSACTION, COMMIT PREPARED, and ROLLBACK PREPARED.

  • Track DEALLOCATE in pg_stat_statements (Dagfinn Ilmari Mannsåker, Michael Paquier) §

    DEALLOCATE names are stored in pg_stat_statements as placeholders.

  • Add local I/O block read/write timing statistics columns of pg_stat_statements (Nazir Bilal Yavuz) § §

    The new columns are local_blk_read_time and local_blk_write_time.

  • Add JIT deform_counter details to pg_stat_statements (Dmitry Dolgov) §

  • Add optional fourth argument (minmax_only) to pg_stat_statements_reset() to allow for the resetting of only min/max statistics (Andrei Zubkov) §

    This argument defaults to false.

  • Add pg_stat_statements columns stats_since and minmax_stats_since to track entry creation time and last min/max reset time (Andrei Zubkov) §

• ⇑ Upgrade to 17.1 released on 2024-11-14 - docs

  • Prevent trusted PL/Perl code from changing environment variables (Andrew Dunstan, Noah Misch) § § §

    The ability to manipulate process environment variables such as PATH gives an attacker opportunities to execute arbitrary code. Therefore, “trusted” PLs must not offer the ability to do that. To fix plperl, replace %ENV with a tied hash that rejects any modification attempt with a warning. Untrusted plperlu retains the ability to change the environment.

    The PostgreSQL Project thanks Coby Abrams for reporting this problem. (CVE-2024-10979)

  • Fix updates of catalog state for foreign-key constraints when attaching or detaching table partitions (Jehan-Guillaume de Rorthais, Tender Wang, Álvaro Herrera) § §

    If the referenced table is partitioned, then different catalog entries are needed for a referencing table that is stand-alone versus one that is a partition. ATTACH/DETACH PARTITION commands failed to perform this conversion correctly. In particular, after DETACH the now stand-alone table would be missing foreign-key enforcement triggers, which could result in the table later containing rows that fail the foreign-key constraint. A subsequent re-ATTACH could fail with surprising errors, too.

    The way to fix this is to do ALTER TABLE DROP CONSTRAINT on the now stand-alone table for each faulty constraint, and then re-add the constraint. If re-adding the constraint fails, then some erroneous data has crept in. You will need to manually re-establish consistency between the referencing and referenced tables, then re-add the constraint.

    This query can be used to identify broken constraints and construct the commands needed to recreate them:

    SELECT conrelid::pg_catalog.regclass AS "constrained table",
           conname AS constraint,
           confrelid::pg_catalog.regclass AS "references",
           pg_catalog.format('ALTER TABLE %s DROP CONSTRAINT %I;',
                             conrelid::pg_catalog.regclass, conname) AS "drop",
           pg_catalog.format('ALTER TABLE %s ADD CONSTRAINT %I %s;',
                             conrelid::pg_catalog.regclass, conname,
                             pg_catalog.pg_get_constraintdef(oid)) AS "add"
    FROM pg_catalog.pg_constraint c
    WHERE contype = 'f' AND conparentid = 0 AND
       (SELECT count(*) FROM pg_catalog.pg_constraint c2
        WHERE c2.conparentid = c.oid) <>
       (SELECT count(*) FROM pg_catalog.pg_inherits i
        WHERE (i.inhparent = c.conrelid OR i.inhparent = c.confrelid) AND
          EXISTS (SELECT 1 FROM pg_catalog.pg_partitioned_table
                  WHERE partrelid = i.inhparent));
    

    Since it is possible that one or more of the ADD CONSTRAINT steps will fail, you should save the query's output in a file and then attempt to perform each step.

  • Fix test for C locale when LC_COLLATE is different from LC_CTYPE (Jeff Davis) §

    When using libc as the default collation provider, the test to see if C locale is in use for collation accidentally checked LC_CTYPE not LC_COLLATE. This has no impact in the typical case where those settings are the same, nor if both are not C (nor its alias POSIX). However, if LC_CTYPE is C while LC_COLLATE is some other locale, wrong query answers could ensue, and corruption of indexes on strings was possible. Users of databases with such settings should reindex affected indexes after installing this update. The converse case with LC_COLLATE being C while LC_CTYPE is some other locale would cause performance degradation, but no actual errors.

  • Avoid planner failure after converting an IS NULL test on a NOT NULL column to constant FALSE (Richard Guo) §

    This bug typically led to errors such as “variable not found in subplan target lists”.

  • Avoid possible planner crash while inlining a SQL function whose arguments contain certain array-related constructs (Tom Lane, Nathan Bossart) §

  • Fix possible wrong answers or “wrong varnullingrels” planner errors for MERGE ... WHEN NOT MATCHED BY SOURCE actions (Dean Rasheed) § §

  • Fix edge case in B-tree ScalarArrayOp index scans (Peter Geoghegan) §

    When a scrollable cursor with a plan of this kind was backed up to its starting point and then run forward again, wrong answers were possible.

  • Fix validation of COPY's FORCE_NOT_NULL and FORCE_NULL options (Joel Jacobson) §

    Some incorrect usages are now rejected as they should be.

  • Fix server crash when a json_objectagg() call contains a volatile function (Amit Langote) §

  • Avoid crash when ALTER DATABASE SET is used to set a server parameter that requires search-path-based lookup, such as default_text_search_config (Jeff Davis) §

  • Avoid repeated lookups of opclasses and collations while creating a new index on a partitioned table (Tom Lane) §

    This was problematic mainly because some of the lookups would be done with a restricted search_path, leading to unexpected failures if the CREATE INDEX command referenced objects outside pg_catalog.

    This fix also prevents comments on the parent partitioned index from being copied to child indexes.

  • Add missing dependency from a partitioned table to a non-built-in access method specified in CREATE TABLE ... USING (Michael Paquier) §

    Dropping the access method should be blocked when a table exists that depends on it, but it was not, allowing subsequent odd behavior. Note that this fix only prevents problems for partitioned tables created after this update.

  • Disallow locale names containing non-ASCII characters (Thomas Munro) §

    This is only an issue on Windows, as such locale names are not used elsewhere. They are problematic because it's quite unclear what encoding such names are represented in (since the locale itself defines the encoding to use). In recent PostgreSQL releases, an abort in the Windows runtime library could occur because of confusion about that.

    Anyone who encounters the new error message should either create a new duplicated locale with an ASCII-only name using Windows Locale Builder, or consider using BCP 47-compliant locale names like tr-TR.

  • Fix psql's describe commands to again work with pre-9.4 servers (Tom Lane) §

    Commands involving display of an ACL (permissions) column failed with very old PostgreSQL servers, due to use of a function not present in those versions.

  • Avoid hanging if an interval less than 1ms is specified in psql's \watch command (Andrey Borodin, Michael Paquier) §

    Instead, treat this the same as an interval of zero (no wait between executions).

  • Fix failure to find replication password in ~/.pgpass (Tom Lane) §

    pg_basebackup and pg_receivewal failed to match an entry in ~/.pgpass that had replication in the database name field, if no -d or --dbname switch was supplied. This resulted in an unexpected prompt for password.

  • In pg_combinebackup, throw an error if an incremental backup file is present in a directory that is supposed to contain a full backup (Robert Haas) §

  • In pg_combinebackup, don't construct filenames containing double slashes (Robert Haas) §

    This caused no functional problems, but the duplicate slashes were visible in error messages, which could create confusion.

• ⇑ Upgrade to 17.2 released on 2024-11-21 - docs

  • Fix race conditions associated with dropping shared statistics entries (Kyotaro Horiguchi, Michael Paquier) §

    These bugs could lead to loss of statistics data, assertion failures, or “can only drop stats once” errors.

• ⇑ Upgrade to 17.3 released on 2025-02-13 - docs

  • Restore auto-truncation of database and user names appearing in connection requests (Nathan Bossart) §

    This reverts a v17 change that proved to cause trouble for some users. Over-length names should be truncated in an encoding-aware fashion, but for now just return to the former behavior of blind truncation at NAMEDATALEN-1 bytes.

  • Drop “Lock” suffix from LWLock wait event names (Bertrand Drouvot) §

    Refactoring unintentionally caused the pg_stat_activity view to show lock-related wait event names with a “Lock” suffix, which among other things broke joining it to pg_wait_events.

  • Fix possible failure to return all matching tuples for a btree index scan with a ScalarArrayOp (= ANY) condition (Peter Geoghegan) §

  • Fix possible re-use of stale results in window aggregates (David Rowley) §

    A window aggregate with a “run condition” optimization and a pass-by-reference result type might incorrectly return the result from the previous partition instead of performing a fresh calculation.

  • Prevent possible catalog corruption when a system catalog is vacuumed concurrently with an update (Noah Misch) § §

  • Fix detach of a partition that has its own foreign-key constraint referencing a partitioned table (Amul Sul) §

    In common cases, foreign keys are defined on a partitioned table's top level; but if instead one is defined on a partition and references a partitioned table, and the referencing partition is detached, the relevant pg_constraint entries were updated incorrectly. This led to errors like “could not find ON INSERT check triggers of foreign key constraint”.

  • Fix pg_get_constraintdef's support for NOT NULL constraints on domains (Álvaro Herrera) §

  • When deparsing a PASSING clause in a SQL/JSON query function, ensure that variable names are double-quoted when necessary (Dean Rasheed) §

  • Fix planning of pre-sorted UNION operations for cases where the input column datatypes don't all match (David Rowley) §

    This error could lead to sorting data with the wrong sort operator, with consequences ranging from no visible problem to core dumps.

  • Prevent “wrong varnullingrels” planner errors after pulling up a subquery that's underneath an outer join (Tom Lane) § §

  • Ignore nulling-relation marker bits when looking up statistics (Richard Guo) §

    This oversight could lead to failure to use relevant statistics about expressions, or to “corrupt MVNDistinct entry” errors.

  • Give the slotsync worker process its own process slot (Tom Lane, Hou Zhijie) §

    This was overlooked in the addition of the slotsync worker, with the result that its process slot effectively came out of the pool meant for regular backend processes. This could result in failure to launch the worker, or to subsequent failures of connection requests that should have succeeded according to the configured settings, if the number of regular backend processes approached max_connections.

  • Correct miscalculation of SLRU bank numbers (Yura Sokolov) §

    This error led to using a smaller number of banks than intended, causing more contention but no functional misbehavior.

  • Ensure that an already-set process latch doesn't prevent the postmaster from noticing socket events (Thomas Munro) §

    An extremely heavy workload of backends launching workers and workers exiting could prevent the postmaster from responding to incoming client connections in a timely fashion.

  • Fix multiple memory leaks in logical decoding output (Vignesh C, Masahiko Sawada, Boyu Yang) § § §

  • Fix small memory leak when updating the application_name or cluster_name settings (Tofig Aliev) §

  • Avoid crash when a background process tries to check a new value of synchronized_standby_slots (Álvaro Herrera) §

  • Avoid potential use-after-free in parallel vacuum (Vallimaharajan G, John Naylor) §

    This bug seems to have no consequences in standard builds, but it's theoretically a hazard.

  • Fix assertion failure at shutdown when writing out the statistics file (Michael Paquier) §

  • Avoid valgrind complaints about string hashing code (John Naylor) §

  • Avoid data loss when starting a bulk write on a relation fork that already contains data (Matthias van de Meent) §

    Any pre-existing data was overwritten with zeroes. This is not an issue for core PostgreSQL, which never does that. Some extensions would like to, however.

  • Avoid crash if a server process tried to iterate over a shared radix tree that it didn't create (Masahiko Sawada) §

    There is no code in core PostgreSQL that does this, but an extension might wish to.

  • Add psql tab completion for COPY (MERGE INTO) (Jian He) §

  • Fix memory leak in pg_restore with zstd-compressed data (Tom Lane) §

    The leak was per-decompression-operation, so would be most noticeable with a dump containing many tables or large objects.

  • Use SQL-standard function bodies in the declarations of contrib/earthdistance's SQL-language functions (Tom Lane, Ronan Dunklau) §

    This change allows their references to contrib/cube to be resolved during extension creation, reducing the risk of search-path-based failures and possible attacks.

    In particular, this restores their usability in contexts like generated columns, for which PostgreSQL v17 restricts the search path on security grounds. We have received reports of databases failing to be upgraded to v17 because of that. This patch has been included in v16 to provide a workaround: updating the earthdistance extension to this version beforehand should allow an upgrade to succeed.

  • Detect version mismatch between contrib/pageinspect's SQL declarations and the underlying shared library (Tomas Vondra) §

    Previously, such a mismatch could result in a crash while calling brin_page_items(). Instead throw an error recommending updating the extension.

  • When trying to cancel a remote query in contrib/postgres_fdw, re-issue the cancel request a few times if it didn't seem to do anything (Tom Lane) §

    This fixes a race condition where we might try to cancel a just-sent query before the remote server has started to process it, so that the initial cancel request is ignored.

  • Fix meson build system to support old OpenSSL libraries on Windows (Darek Slusarczyk) §

    Add support for the legacy library names ssleay32 and libeay32.

  • In Windows builds using meson, ensure all libcommon and libpgport functions are exported (Vladlen Popolitov, Heikki Linnakangas) § §

    This fixes “unresolved external symbol” build errors for extensions.

  • Fix meson configuration process to correctly detect OSSP's uuid.h header file under MSVC (Andrew Dunstan) §

  • When building with meson, install pgevent in pkglibdir not bindir (Peter Eisentraut) §

    This matches the behavior of the make-based build system and the old MSVC build system.

  • When building with meson, install sepgsql.sql under share/contrib/ not share/extension/ (Peter Eisentraut) §

    This matches what the make-based build system does.

• ⇑ Upgrade to 17.4 released on 2025-02-20 - docs

  • Fix small memory leak in pg_createsubscriber (Ranier Vilela) §

  • Fix meson build system to correctly detect availability of the bsd_auth.h system header (Nazir Bilal Yavuz) §

• source repo
• author info
• meta-info