2020-11-12 This release contains a variety of fixes from 13.0. For information about new features in major release 13, see . A dump/restore is not required for those running 13.X. Block DECLARE CURSOR ... WITH HOLD and firing of deferred triggers within index expressions and materialized view queries (Noah Misch) This is essentially a leak in the security restricted operation sandbox mechanism. An attacker having permission to create non-temporary SQL objects could parlay this leak to execute arbitrary SQL code as a superuser. The PostgreSQL Project thanks Etienne Stalmans for reporting this problem. (CVE-2020-25695) Fix usage of complex connection-string parameters in pg_dump, pg_restore, clusterdb, reindexdb, and vacuumdb (Tom Lane) The -d parameter of pg_dump and pg_restore, or the --maintenance-db parameter of the other programs mentioned, can be a connection string containing multiple connection parameters rather than just a database name. In cases where these programs need to initiate additional connections, such as parallel processing or processing of multiple databases, the connection string was forgotten and just the basic connection parameters (database name, host, port, and username) were used for the additional connections. This could lead to connection failures if the connection string included any other essential information, such as non-default SSL or GSS parameters. Worse, the connection might succeed but not be encrypted as intended, or be vulnerable to man-in-the-middle attacks that the intended connection parameters would have prevented. (CVE-2020-25694) When psql's \connect command re-uses connection parameters, ensure that all non-overridden parameters from a previous connection string are re-used (Tom Lane) This avoids cases where reconnection might fail due to omission of relevant parameters, such as non-default SSL or GSS options. Worse, the reconnection might succeed but not be encrypted as intended, or be vulnerable to man-in-the-middle attacks that the intended connection parameters would have prevented. This is largely the same problem as just cited for pg_dump et al, although psql's behavior is more complex since the user may intentionally override some connection parameters. (CVE-2020-25694) Prevent psql's \gset command from modifying specially-treated variables (Noah Misch) \gset without a prefix would overwrite whatever variables the server told it to. Thus, a compromised server could set specially-treated variables such as PROMPT1, giving the ability to execute arbitrary shell code in the user's session. The PostgreSQL Project thanks Nick Cleaton for reporting this problem. (CVE-2020-25696) Fix unintended breakage of the replication protocol (Álvaro Herrera) A walsender reports two command-completion events for START_REPLICATION. This was undocumented and apparently unintentional; so we failed to notice that a late 13.0 change removed the duplicate event. However it turns out that walreceivers require the extra event in some code paths. The most practical fix is to decree that the extra event is part of the protocol and resume generating it. Ensure that SLRU directories are properly fsync'd during checkpoints (Thomas Munro) This prevents possible data loss in a subsequent operating system crash. Fix ALTER ROLE for users with the BYPASSRLS attribute (Tom Lane, Stephen Frost) The BYPASSRLS attribute is only allowed to be changed by superusers, but other ALTER ROLE operations, such as password changes, should be allowed with only ordinary permission checks. The previous coding erroneously restricted all changes on such a role to superusers. Disallow ALTER TABLE ONLY ... DROP EXPRESSION when there are child tables (Peter Eisentraut) The current implementation cannot handle this case correctly, so just forbid it for now. Ensure that ALTER TABLE ONLY ... ENABLE/DISABLE TRIGGER does not recurse to child tables (Álvaro Herrera) Previously the ONLY flag was ignored. Allow LOCK TABLE to succeed on a self-referential view (Tom Lane) It previously threw an error complaining about infinite recursion, but there seems no need to disallow the case. Retain statistics about an index across REINDEX CONCURRENTLY (Michael Paquier, Fabrízio de Royes Mello) Non-concurrent reindexing has always preserved such statistics. Fix incorrect progress reporting from REINDEX CONCURRENTLY (Matthias van de Meent, Michael Paquier) Ensure that GENERATED columns are updated when the column(s) they depend on are updated via a rule or an updatable view (Tom Lane) This fix also takes care of possible failure to fire a column-specific trigger in such cases. Fix failures with collation-dependent partition bound expressions (Tom Lane) Support hashing of text arrays (Peter Eisentraut) Array hashing failed if the array element type is collatable. Notably, this prevented using hash partitioning with a text array column as partition key. Prevent internal overflows in cross-type datetime comparisons (Nikita Glukhov, Alexander Korotkov, Tom Lane) Previously, comparing a date to a timestamp would fail if the date is past the valid range for timestamps. There were also corner cases involving overflow of close-to-the-limit timestamp values during timezone rotation. Fix off-by-one conversion of negative years to BC dates in to_date() and to_timestamp() (Dar Alathar-Yemen, Tom Lane) Also, arrange for the combination of a negative year and an explicit BC marker to cancel out and produce AD. Allow the jsonpath .datetime() method to accept ISO 8601-format timestamps (Nikita Glukhov) This is not required by SQL, but it seems appropriate since our to_json() functions generate that timestamp format for Javascript compatibility. Ensure that standby servers will archive WAL timeline history files when archive_mode is set to always (Grigory Smolkin, Fujii Masao) This oversight could lead to failure of subsequent PITR recovery attempts. Fix edge cases in detecting premature death of the postmaster on platforms that use kqueue() (Thomas Munro) Avoid generating an incorrect incremental-sort plan when the sort key is a volatile expression (James Coleman) Fix possible crash when considering partition-wise joins during GEQO planning (Tom Lane) Fix possible infinite loop or corrupted output data in TOAST decompression (Tom Lane) Fix counting of the number of entries in B-tree indexes during cleanup-only VACUUMs (Peter Geoghegan) Ensure that data is detoasted before being inserted into a BRIN index (Tomas Vondra) Index entries are not supposed to contain out-of-line TOAST pointers, but BRIN didn't get that memo. This could lead to errors like missing chunk number 0 for toast value NNN. (If you are faced with such an error from an existing index, REINDEX should be enough to fix it.) Fix buffered GiST index builds to work when the index has included columns (Pavel Borisov) Fix unportable use of getnameinfo() in pg_hba_file_rules view (Tom Lane) On FreeBSD 11, and possibly other platforms, the view's address and netmask columns were always null due to this error. Avoid crash if debug_query_string is NULL when starting a parallel worker (Noah Misch) Avoid failures when a BEFORE ROW UPDATE trigger returns the old row of a table having dropped or missing columns (Amit Langote, Tom Lane) This method of suppressing an update could result in crashes, unexpected CHECK constraint failures, or incorrect RETURNING output, because missing columns would read as NULLs for those purposes. (A column is missing for this purpose if it was added by ALTER TABLE ADD COLUMN with a non-NULL, but constant, default value.) Dropped columns could cause trouble as well. Fix EXPLAIN's output for incremental sort plans to have correct tag nesting in XML output mode (Daniel Gustafsson) Avoid unnecessary failure when transferring very large payloads through shared memory queues (Markus Wanner) Fix omission of result data type coercion in some cases in SQL-language functions (Tom Lane) This could lead to wrong results or crashes, depending on the data types involved. Fix incorrect handling of template function attributes in JIT code generation (Andres Freund) This has been shown to cause crashes on s390x, and very possibly there are other cases on other platforms. Improve code generated for compare_exchange and fetch_add operations on PPC (Noah Misch) Fix relation cache memory leaks with RLS policies (Tom Lane) Fix edge-case memory leak in index_get_partition() (Justin Pryzby) Fix small memory leak when SIGHUP processing decides that a new GUC variable value cannot be applied without a restart (Tom Lane) Fix memory leaks in PL/pgsql's CALL processing (Pavel Stehule, Tom Lane) In libpq for Windows, call WSAStartup() once per process and WSACleanup() not at all (Tom Lane, Alexander Lakhin) Previously, libpq invoked WSAStartup() at connection start and WSACleanup() at connection cleanup. However, it appears that calling WSACleanup() can interfere with other program operations; notably, we have observed rare failures to emit expected output to stdout. There appear to be no ill effects from omitting the call, so do that. (This also eliminates a performance issue from repeated DLL loads and unloads when a program performs a series of database connections.) Fix ecpg library's per-thread initialization logic for Windows (Tom Lane, Alexander Lakhin) Multi-threaded ecpg applications could suffer rare misbehavior due to incorrect locking. Fix ecpg's mis-processing of B'...' and X'...' literals (Shenhao Wang) On Windows, make psql read the output of a backtick command in text mode, not binary mode (Tom Lane) This ensures proper handling of newlines. Ensure that pg_dump collects per-column information about extension configuration tables (Fabrízio de Royes Mello, Tom Lane) Failure to do this led to crashes when specifying --inserts, or underspecified (though usually correct) COPY commands when using COPY to reload the tables' data. Make pg_upgrade check for pre-existence of tablespace directories in the target cluster (Bruce Momjian) Fix potential memory leak in contrib/pgcrypto (Michael Paquier) Add check for an unlikely failure case in contrib/pgcrypto (Daniel Gustafsson) Fix recently-added timetz test case so it works when the USA is not observing daylight savings time (Tom Lane) Update time zone data files to tzdata release 2020d for DST law changes in Fiji, Morocco, Palestine, the Canadian Yukon, Macquarie Island, and Casey Station (Antarctica); plus historical corrections for France, Hungary, Monaco, and Palestine. Sync our copy of the timezone library with IANA tzcode release 2020d (Tom Lane) This absorbs upstream's change of zic's default output option from fat to slim. That's just cosmetic for our purposes, as we continue to select the fat mode in pre-v13 branches. This change also ensures that strftime() does not change errno unless it fails. 2020-09-24 PostgreSQL 13 contains many new features and enhancements, including: Space savings and performance gains from de-duplication of B-tree index entries Improved performance for queries that use aggregates or partitioned tables Better query planning when using extended statistics Parallelized vacuuming of indexes Incremental sorting The above items and other new features of PostgreSQL 13 are explained in more detail in the sections below. A dump/restore using or use of or logical replication is required for those wishing to migrate data from any previous release. See for general information on migrating to new major releases. Version 13 contains a number of changes that may affect compatibility with previous releases. Observe the following incompatibilities: Change SIMILAR TO ... ESCAPE NULL to return NULL (Tom Lane) This new behavior matches the SQL specification. Previously a null ESCAPE value was taken to mean using the default escape string (a backslash character). This also applies to substring(text FROM pattern ESCAPE text). The previous behavior has been retained in old views by keeping the original function unchanged. Make json[b]_to_tsvector() fully check the spelling of its string option (Dominik Czarnota) Change the way non-default values affect concurrency (Thomas Munro) Previously, this value was adjusted before setting the number of concurrent requests. The value is now used directly. Conversion of old values to new ones can be done using: SELECT round(sum(OLDVALUE / n::float)) AS newvalue FROM generate_series(1, OLDVALUE) s(n); Prevent display of auxiliary processes in and system views (Euler Taveira) Queries that join these views to and wish to see auxiliary processes will need to use left joins. Rename various wait events to improve consistency (Fujii Masao, Tom Lane) Fix ALTER FOREIGN TABLE ... RENAME COLUMN to return a more appropriate command tag (Fujii Masao) Previously it returned ALTER TABLE; now it returns ALTER FOREIGN TABLE. Fix ALTER MATERIALIZED VIEW ... RENAME COLUMN to return a more appropriate command tag (Fujii Masao) Previously it returned ALTER TABLE; now it returns ALTER MATERIALIZED VIEW. Rename configuration parameter wal_keep_segments to (Fujii Masao) This determines how much WAL to retain for standby servers. It is specified in megabytes, rather than number of files as with the old parameter. If you previously used wal_keep_segments, the following formula will give you an approximately equivalent setting: wal_keep_size = wal_keep_segments * wal_segment_size (typically 16MB) Remove support for defining operator classes using pre-PostgreSQL 8.0 syntax (Daniel Gustafsson) Remove support for defining foreign key constraints using pre-PostgreSQL 7.3 syntax (Daniel Gustafsson) Remove support for "opaque" pseudo-types used by pre-PostgreSQL 7.3 servers (Daniel Gustafsson) Remove support for upgrading unpackaged (pre-9.1) extensions (Tom Lane) The FROM option of CREATE EXTENSION is no longer supported. Any installations still using unpackaged extensions should upgrade them to a packaged version before updating to PostgreSQL 13. Remove support for posixrules files in the timezone database (Tom Lane) IANA's timezone group has deprecated this feature, meaning that it will gradually disappear from systems' timezone databases over the next few years. Rather than have a behavioral change appear unexpectedly with a timezone data update, we have removed PostgreSQL's support for this feature as of version 13. This affects only the behavior of POSIX-style time zone specifications that lack an explicit daylight savings transition rule; formerly the transition rule could be determined by installing a custom posixrules file, but now it is hard-wired. The recommended fix for any affected installations is to start using a geographical time zone name. In , when an lquery pattern contains adjacent asterisks with braces, e.g., *{2}.*{3}, properly interpret that as *{5} (Nikita Glukhov) Fix 's bt_metap() to return more appropriate data types that are less likely to overflow (Peter Geoghegan) Below you will find a detailed account of the changes between PostgreSQL 13 and the previous major release. Allow pruning of partitions to happen in more cases (Yuzuko Hosoya, Amit Langote, Álvaro Herrera) Allow partitionwise joins to happen in more cases (Ashutosh Bapat, Etsuro Fujita, Amit Langote, Tom Lane) For example, partitionwise joins can now happen between partitioned tables even when their partition bounds do not match exactly. Support row-level BEFORE triggers on partitioned tables (Álvaro Herrera) However, such a trigger is not allowed to change which partition is the destination. Allow partitioned tables to be logically replicated via publications (Amit Langote) Previously, partitions had to be replicated individually. Now a partitioned table can be published explicitly, causing all its partitions to be published automatically. Addition/removal of a partition causes it to be likewise added to or removed from the publication. The CREATE PUBLICATION option publish_via_partition_root controls whether changes to partitions are published as their own changes or their parent's. Allow logical replication into partitioned tables on subscribers (Amit Langote) Previously, subscribers could only receive rows into non-partitioned tables. Allow whole-row variables (that is, table.*) to be used in partitioning expressions (Amit Langote) More efficiently store duplicates in B-tree indexes (Anastasia Lubennikova, Peter Geoghegan) This allows efficient B-tree indexing of low-cardinality columns by storing duplicate keys only once. Users upgrading with pg_upgrade will need to use REINDEX to make an existing index use this feature. Allow GiST and SP-GiST indexes on box columns to support ORDER BY box <-> point queries (Nikita Glukhov) Allow GIN indexes to more efficiently handle ! (NOT) clauses in tsquery searches (Nikita Glukhov, Alexander Korotkov, Tom Lane, Julien Rouhaud) Allow index operator classes to take parameters (Nikita Glukhov) Allow CREATE INDEX to specify the GiST signature length and maximum number of integer ranges (Nikita Glukhov) Indexes created on four and eight-byte integer array, tsvector, , , and columns can now control these GiST index parameters, rather than using the defaults. Prevent indexes that use non-default collations from being added as a table's unique or primary key constraint (Tom Lane) The index's collation must match that of the underlying column, but ALTER TABLE previously failed to check this. Improve the optimizer's selectivity estimation for containment/match operators (Tom Lane) Allow setting the statistics target for extended statistics (Tomas Vondra) This is controlled with the new command option ALTER STATISTICS ... SET STATISTICS. Previously this was computed based on more general statistics target settings. Allow use of multiple extended statistics objects in a single query (Tomas Vondra) Allow use of extended statistics objects for OR clauses and IN/ANY constant lists (Pierre Ducroquet, Tomas Vondra) Allow functions in FROM clauses to be pulled up (inlined) if they evaluate to constants (Alexander Kuzmenkov, Aleksandr Parfenov) Implement incremental sorting (James Coleman, Alexander Korotkov, Tomas Vondra) If an intermediate query result is known to be sorted by one or more leading keys of a required sort ordering, the additional sorting can be done considering only the remaining keys, if the rows are sorted in batches that have equal leading keys. If necessary, this can be controlled using . Improve the performance of sorting inet values (Brandur Leach) Allow hash aggregation to use disk storage for large aggregation result sets (Jeff Davis) Previously, hash aggregation was avoided if it was expected to use more than memory. Now, a hash aggregation plan can be chosen despite that. The hash table will be spilled to disk if it exceeds work_mem times . This behavior is normally preferable to the old behavior, in which once hash aggregation had been chosen, the hash table would be kept in memory no matter how large it got — which could be very large if the planner had misestimated. If necessary, behavior similar to that can be obtained by increasing hash_mem_multiplier. Allow inserts, not only updates and deletes, to trigger vacuuming activity in autovacuum (Laurenz Albe, Darafei Praliaskouski) Previously, insert-only activity would trigger auto-analyze but not auto-vacuum, on the grounds that there could not be any dead tuples to remove. However, a vacuum scan has other useful side-effects such as setting page-all-visible bits, which improves the efficiency of index-only scans. Also, allowing an insert-only table to receive periodic vacuuming helps to spread out the work of freezing old tuples, so that there is not suddenly a large amount of freezing work to do when the entire table reaches the anti-wraparound threshold all at once. If necessary, this behavior can be adjusted with the new parameters and , or the equivalent table storage options. Add parameter to control I/O concurrency for maintenance operations (Thomas Munro) Allow WAL writes to be skipped during a transaction that creates or rewrites a relation, if is minimal (Kyotaro Horiguchi) Relations larger than will have their files fsync'ed rather than generating WAL. Previously this was done only for COPY operations, but the implementation had a bug that could cause data loss during crash recovery. Improve performance when replaying DROP DATABASE commands when many tablespaces are in use (Fujii Masao) Improve performance for truncation of very large relations (Kirk Jamison) Improve retrieval of the leading bytes of TOAST'ed values (Binguo Bao, Andrey Borodin) Previously, compressed out-of-line TOAST values were fully fetched even when it's known that only some leading bytes are needed. Now, only enough data to produce the result is fetched. Improve performance of LISTEN/NOTIFY (Martijn van Oosterhout, Tom Lane) Speed up conversions of integers to text (David Fetter) Reduce memory usage for query strings and extension scripts that contain many SQL statements (Amit Langote) Allow EXPLAIN, , autovacuum, and to track WAL usage statistics (Kirill Bychik, Julien Rouhaud) Allow a sample of SQL statements, rather than all statements, to be logged (Adrien Nayrat) A fraction of those statements taking more than duration will be logged. Add the backend type to csvlog and optionally log output (Peter Eisentraut) Improve control of prepared statement parameter logging (Alexey Bashtanov, Álvaro Herrera) The GUC setting controls the maximum length of parameter values output during logging of non-error statements, while does the same for logging of statements with errors. Previously, prepared statement parameters were never logged during errors. Allow function call backtraces to be logged after errors (Peter Eisentraut, Álvaro Herrera) The new parameter specifies which C functions should generate backtraces on error. Make vacuum buffer counters 64-bits wide to avoid overflow (Álvaro Herrera) Add leader_pid to to report a parallel worker's leader process (Julien Rouhaud) Add system view pg_stat_progress_basebackup to report the progress of streaming base backups (Fujii Masao) Add system view pg_stat_progress_analyze to report ANALYZE progress (Álvaro Herrera, Tatsuro Yamada, Vinayak Pokale) Add system view pg_shmem_allocations to display shared memory usage (Andres Freund, Robert Haas) Add system view pg_stat_slru to monitor internal SLRU caches (Tomas Vondra) Allow to be set as high as 1MB (Vyacheslav Makarov) The previous maximum was 100kB. Report a wait event while creating a DSM segment with posix_fallocate() (Thomas Munro) Add wait event VacuumDelay to report on cost-based vacuum delay (Justin Pryzby) Add wait events for WAL archive and recovery pause (Fujii Masao) The new events are BackupWaitWalArchive and RecoveryPause. Add wait events RecoveryConflictSnapshot and RecoveryConflictTablespace to monitor recovery conflicts (Masahiko Sawada) Improve performance of wait events on BSD-based systems (Thomas Munro) Allow only superusers to view the setting (Insung Moon) This was changed as a security precaution. Change the server's default minimum TLS version for encrypted connections from 1.0 to 1.2 (Peter Eisentraut) This choice can be controlled by . Tighten rules on which utility commands are allowed in read-only transaction mode (Robert Haas) This change also increases the number of utility commands that can run in parallel queries. Allow to be changed after server start (Peter Eisentraut) Disallow non-superusers from modifying system tables when is set (Peter Eisentraut) Previously, if was set at server start, non-superusers could issue INSERT/UPDATE/DELETE commands on system tables. Enable support for Unix-domain sockets on Windows (Peter Eisentraut) Allow streaming replication configuration settings to be changed by reload (Sergei Kornilov) Previously, a server restart was required to change and . Allow WAL receivers to use a temporary replication slot when a permanent one is not specified (Peter Eisentraut, Sergei Kornilov) This behavior can be enabled using . Allow WAL storage for replication slots to be limited by (Kyotaro Horiguchi) Replication slots that would require exceeding this value are marked invalid. Allow standby promotion to cancel any requested pause (Fujii Masao) Previously, promotion could not happen while the standby was in paused state. Generate an error if recovery does not reach the specified recovery target (Leif Gunnar Erlandsen, Peter Eisentraut) Previously, a standby would promote itself upon reaching the end of WAL, even if the target was not reached. Allow control over how much memory is used by logical decoding before it is spilled to disk (Tomas Vondra, Dilip Kumar, Amit Kapila) This is controlled by . Allow recovery to continue even if invalid pages are referenced by WAL (Fujii Masao) This is enabled using . Allow VACUUM to process a table's indexes in parallel (Masahiko Sawada, Amit Kapila) The new PARALLEL option controls this. Allow FETCH FIRST to use WITH TIES to return any additional rows that match the last result row (Surafel Temesgen) Report planning-time buffer usage in EXPLAIN's BUFFER output (Julien Rouhaud) Make CREATE TABLE LIKE propagate a CHECK constraint's NO INHERIT property to the created table (Ildar Musin, Chris Travers) When using LOCK TABLE on a partitioned table, do not check permissions on the child tables (Amit Langote) Allow OVERRIDING USER VALUE on inserts into identity columns (Dean Rasheed) Add ALTER TABLE ... DROP EXPRESSION to allow removing the GENERATED property from a column (Peter Eisentraut) Fix bugs in multi-step ALTER TABLE commands (Tom Lane) IF NOT EXISTS clauses now work as expected, in that derived actions (such as index creation) do not execute if the column already exists. Also, certain cases of combining related actions into one ALTER TABLE now work when they did not before. Add ALTER VIEW syntax to rename view columns (Fujii Masao) Renaming view columns was already possible, but one had to write ALTER TABLE RENAME COLUMN, which is confusing. Add ALTER TYPE options to modify a base type's TOAST properties and support functions (Tomas Vondra, Tom Lane) Add CREATE DATABASE LOCALE option (Peter Eisentraut) This combines the existing options LC_COLLATE and LC_CTYPE into a single option. Allow DROP DATABASE to disconnect sessions using the target database, allowing the drop to succeed (Pavel Stehule, Amit Kapila) This is enabled by the FORCE option. Add structure member tg_updatedcols to allow C-language update triggers to know which column(s) were updated (Peter Eisentraut) Add polymorphic data types for use by functions requiring compatible arguments (Pavel Stehule) The new data types are anycompatible, anycompatiblearray, anycompatiblenonarray, and anycompatiblerange. Add SQL data type xid8 to expose FullTransactionId (Thomas Munro) The existing xid data type is only four bytes so it does not provide the transaction epoch. Add data type regcollation and associated functions, to represent OIDs of collation objects (Julien Rouhaud) Use the glibc version in some cases as a collation version identifier (Thomas Munro) If the glibc version changes, a warning will be issued about possible corruption of collation-dependent indexes. Add support for collation versions on Windows (Thomas Munro) Allow ROW expressions to have their members extracted with suffix notation (Tom Lane) For example, (ROW(4, 5.0)).f1 now returns 4. Add alternate version of jsonb_set() with improved NULL handling (Andrew Dunstan) The new function, jsonb_set_lax(), handles a NULL new value by either setting the specified key to a JSON null, deleting the key, raising an exception, or returning the jsonb value unmodified, as requested. Add jsonpath .datetime() method (Nikita Glukhov, Teodor Sigaev, Oleg Bartunov, Alexander Korotkov) This function allows JSON values to be converted to timestamps, which can then be processed in jsonpath expressions. This change also adds jsonpath functions that support time-zone-aware output. Add SQL functions NORMALIZE() to normalize Unicode strings, and IS NORMALIZED to check for normalization (Peter Eisentraut) Add min() and max() aggregates for pg_lsn (Fabrízio de Royes Mello) These are particularly useful in monitoring queries. Allow Unicode escapes, e.g., E'\unnnn' or U&'\nnnn', to specify any character available in the database encoding, even when the database encoding is not UTF-8 (Tom Lane) Allow to_date() and to_timestamp() to recognize non-English month/day names (Juan José Santamaría Flecha, Tom Lane) The names recognized are the same as those output by to_char() with the same format patterns. Add datetime format patterns FF1 – FF6 to specify input or output of 1 to 6 fractional-second digits (Alexander Korotkov, Nikita Glukhov, Teodor Sigaev, Oleg Bartunov) These patterns can be used by to_char(), to_timestamp(), and jsonpath's .datetime(). Add SSSSS datetime format pattern as an SQL-standard alias for SSSS (Nikita Glukhov, Alexander Korotkov) Add function gen_random_uuid() to generate version-4 UUIDs (Peter Eisentraut) Previously UUID generation functions were only available in the external modules and . Add greatest-common-denominator (gcd) and least-common-multiple (lcm) functions (Vik Fearing) Improve the performance and accuracy of the numeric type's square root (sqrt) and natural log (ln) functions (Dean Rasheed) Add function min_scale() that returns the number of digits to the right of the decimal point that are required to represent a numeric value with full accuracy (Pavel Stehule) Add function trim_scale() to reduce the scale of a numeric value by removing trailing zeros (Pavel Stehule) Add commutators of distance operators (Nikita Glukhov) For example, previously only point <-> line was supported, now line <-> point works too. Create xid8 versions of all transaction ID functions (Thomas Munro) The old xid-based functions still exist, for backward compatibility. Allow get_bit() and set_bit() to set bits beyond the first 256MB of a bytea value (Movead Li) Allow advisory-lock functions to be used in some parallel operations (Tom Lane) Add the ability to remove an object's dependency on an extension (Álvaro Herrera) The object can be a function, materialized view, index, or trigger. The syntax is ALTER .. NO DEPENDS ON. Improve performance of simple PL/pgSQL expressions (Tom Lane, Amit Langote) Improve performance of PL/pgSQL functions that use immutable expressions (Konstantin Knizhnik) Allow libpq clients to require channel binding for encrypted connections (Jeff Davis) Using the libpq connection parameter channel_binding forces the other end of the TLS connection to prove it knows the user's password. This prevents man-in-the-middle attacks. Add libpq connection parameters to control the minimum and maximum TLS version allowed for an encrypted connection (Daniel Gustafsson) The settings are and . By default, the minimum TLS version is 1.2 (this represents a behavioral change from previous releases). Allow use of passwords to unlock client certificates (Craig Ringer, Andrew Dunstan) This is enabled by libpq's connection parameter. Allow libpq to use DER-encoded client certificates (Craig Ringer, Andrew Dunstan) Fix ecpg's EXEC SQL elif directive to work correctly (Tom Lane) Previously it behaved the same as endif followed by ifdef, so that a successful previous branch of the same if construct did not prevent expansion of the elif branch or following branches. Add transaction status (%x) to psql's default prompts (Vik Fearing) Allow the secondary psql prompt to be blank but the same width as the primary prompt (Thomas Munro) This is accomplished by setting PROMPT2 to %w. Allow psql's \g and \gx commands to change \pset output options for the duration of that single command (Tom Lane) This feature allows syntax like \g (expand=on), which is equivalent to \gx. Add psql commands to display operator classes and operator families (Sergey Cherkashin, Nikita Glukhov, Alexander Korotkov) The new commands are \dAc, \dAf, \dAo, and \dAp. Show table persistence in psql's \dt+ and related commands (David Fetter) In verbose mode, the table/index/view shows if the object is permanent, temporary, or unlogged. Improve output of psql's \d for TOAST tables (Justin Pryzby) Fix redisplay after psql's \e command (Tom Lane) When exiting the editor, if the query doesn't end with a semicolon or \g, the query buffer contents will now be displayed. Add \warn command to psql (David Fetter) This is like \echo except that the text is sent to stderr instead of stdout. Add the PostgreSQL home page to command-line --help output (Peter Eisentraut) Allow pgbench to partition its accounts table (Fabien Coelho) This allows performance testing of partitioning. Add pgbench command \aset, which behaves like \gset, but for multiple queries (Fabien Coelho) Allow pgbench to generate its initial data server-side, rather than client-side (Fabien Coelho) Allow pgbench to show script contents using option --show-script (Fabien Coelho) Generate backup manifests for base backups, and verify them (Robert Haas) A new tool pg_verifybackup can verify backups. Have pg_basebackup estimate the total backup size by default (Fujii Masao) This computation allows pg_stat_progress_basebackup to show progress. If that is not needed, it can be disabled by using the --no-estimate-size option. Previously, this computation happened only if the --progress option was used. Add an option to pg_rewind to configure standbys (Paul Guo, Jimmy Yih, Ashwin Agrawal) This matches pg_basebackup's --write-recovery-conf option. Allow pg_rewind to use the target cluster's to retrieve needed WAL (Alexey Kondratov) This is enabled using the -c/--restore-target-wal option. Have pg_rewind automatically run crash recovery before rewinding (Paul Guo, Jimmy Yih, Ashwin Agrawal) This can be disabled by using --no-ensure-shutdown. Increase the PREPARE TRANSACTION-related information reported by pg_waldump (Fujii Masao) Add pg_waldump option --quiet to suppress non-error output (Andres Freund, Robert Haas) Add pg_dump option --include-foreign-data to dump data from foreign servers (Luis Carril) Allow vacuum commands run by vacuumdb to operate in parallel mode (Masahiko Sawada) This is enabled with the new --parallel option. Allow reindexdb to operate in parallel (Julien Rouhaud) Parallel mode is enabled with the new --jobs option. Allow dropdb to disconnect sessions using the target database, allowing the drop to succeed (Pavel Stehule) This is enabled with the -f option. Remove --adduser and --no-adduser from createuser (Alexander Lakhin) The long-supported preferred options for this are called --superuser and --no-superuser. Use the directory of the pg_upgrade program as the default --new-bindir setting when running pg_upgrade (Daniel Gustafsson) Add a glossary to the documentation (Corey Huinker, Jürgen Purtz, Roger Harkavy, Álvaro Herrera) Reformat tables containing function and operator information for better clarity (Tom Lane) Upgrade to use DocBook 4.5 (Peter Eisentraut) Add support for building on Visual Studio 2019 (Haribabu Kommi) Add build support for MSYS2 (Peter Eisentraut) Add compare_exchange and fetch_add assembly language code for Power PC compilers (Noah Misch) Update Snowball stemmer dictionaries used by full text search (Panagiotis Mavrogiorgos) This adds Greek stemming and improves Danish and French stemming. Remove support for Windows 2000 (Michael Paquier) Remove support for non-ELF BSD systems (Peter Eisentraut) Remove support for Python versions 2.5.X and earlier (Peter Eisentraut) Remove support for OpenSSL 0.9.8 and 1.0.0 (Michael Paquier) Remove configure options --disable-float8-byval and --disable-float4-byval (Peter Eisentraut) These were needed for compatibility with some version-zero C functions, but those are no longer supported. Pass the query string to planner hook functions (Pascal Legrand, Julien Rouhaud) Add TRUNCATE command hook (Yuli Khodorkovskiy) Add TLS init hook (Andrew Dunstan) Allow building with no predefined Unix-domain socket directory (Peter Eisentraut) Reduce the probability of SysV resource key collision on Unix platforms (Tom Lane) Use operating system functions to reliably erase memory that contains sensitive information (Peter Eisentraut) For example, this is used for clearing passwords stored in memory. Add headerscheck script to test C header-file compatibility (Tom Lane) Implement internal lists as arrays, rather than a chain of cells (Tom Lane) This improves performance for queries that access many objects. Change the API for TS_execute() (Tom Lane, Pavel Borisov) TS_execute callbacks must now provide ternary (yes/no/maybe) logic. Calculating NOT queries accurately is now the default. Allow extensions to be specified as trusted (Tom Lane) Such extensions can be installed in a database by users with database-level CREATE privileges, even if they are not superusers. This change also removes the pg_pltemplate system catalog. Allow non-superusers to connect to foreign servers without using a password (Craig Ringer) Specifically, allow a superuser to set password_required to false for a user mapping. Care must still be taken to prevent non-superusers from using superuser credentials to connect to the foreign server. Allow postgres_fdw to use certificate authentication (Craig Ringer) Different users can use different certificates. Allow to control access to the TRUNCATE command (Yuli Khodorkovskiy) Add extension bool_plperl which transforms SQL booleans to/from PL/Perl booleans (Ivan Panchenko) Have treat SELECT ... FOR UPDATE commands as distinct from those without FOR UPDATE (Andrew Gierth, Vik Fearing) Allow pg_stat_statements to optionally track the planning time of statements (Julien Rouhaud, Pascal Legrand, Thomas Munro, Fujii Masao) Previously only execution time was tracked. Overhaul 's lquery syntax to treat NOT (!) more logically (Filip Rembialkowski, Tom Lane, Nikita Glukhov) Also allow non-* queries to use a numeric range ({}) of matches. Add support for binary I/O of , lquery, and ltxtquery types (Nino Floris) Add an option to to ignore the sign of integers (Jeff Janes) Add function pg_file_sync() to allow fsync'ing a file (Fujii Masao) Add functions to output t_infomask/t_infomask2 values in human-readable format (Craig Ringer, Sawada Masahiko, Michael Paquier) Add B-tree index de-duplication processing columns to pageinspect output (Peter Geoghegan) The following individuals (in alphabetical order) have contributed to this release as patch authors, committers, reviewers, testers, or reporters of issues. Abhijit Menon-Sen Adam Lee Adam Scott Adé Heyward Adrien Nayrat Ahsan Hadi Alastair McKinley Aleksandr Parfenov Alex Aktsipetrov Alex Macy Alex Shulgin Alexander Korotkov Alexander Kukushkin Alexander Kuzmenkov Alexander Lakhin Alexey Bashtanov Alexey Kondratov Álvaro Herrera Amit Kapila Amit Khandekar Amit Langote Amul Sul Anastasia Lubennikova Andreas Joseph Krogh Andreas Karlsson Andreas Kunert Andreas Seltenreich Andrei Zubkov Andres Freund Andrew Bille Andrew Dunstan Andrew Gierth Andrey Borodin Andrey Klychkov Andrey Lepikhov Anna Akenteva Anna Endo Anthony Nowocien Anton Vlasov Antonin Houska Ants Aasma Arne Roland Arnold Müller Arseny Sher Arthur Nascimento Arthur Zakirov Ashutosh Bapat Ashutosh Sharma Ashwin Agrawal Asif Rehman Asim Praveen Atsushi Torikoshi Augustinas Jokubauskas Austin Drenski Basil Bourque Beena Emerson Ben Cornett Benjie Gillam Benoît Lobréau Bernd Helmle Bharath Rupireddy Bhargav Kamineni Binguo Bao Brad DeJong Brandur Leach Brent Bates Brian Williams Bruce Momjian Cameron Ezell Cary Huang Chapman Flack Charles Offenbacher Chen Huajun Chenyang Lu Chris Bandy Chris Travers Christoph Berg Christophe Courtois Corey Huinker Craig Ringer Cuiping Lin Dagfinn Ilmari Mannsåker Daniel Fiori Daniel Gustafsson Daniel Vérité Daniel Westermann Darafei Praliaskouski Daryl Waycott Dave Cramer David Christensen David Fetter David G. Johnston David Gilman David Harper David Rowley David Steele David Zhang Davinder Singh Dean Rasheed Denis Stuchalin Dent John Didier Gautheron Dilip Kumar Dmitry Belyavsky Dmitry Dolgov Dmitry Ivanov Dmitry Telpt Dmitry Uspenskiy Dominik Czarnota Dongming Liu Ed Morley Edmund Horner Emre Hasegeli Eric Gillum Erik Rijkers Erwin Brandstetter Ethan Waldo Etsuro Fujita Eugen Konkov Euler Taveira Fabien Coelho Fabrízio de Royes Mello Felix Lechner Filip Janus Filip Rembialkowski Frank Gagnepain Georgios Kokolatos Gilles Darold Greg Nancarrow Grigory Smolkin Guancheng Luo Guillaume Lelarge Hadi Moshayedi Haiying Tang Hamid Akhtar Hans Buschmann Hao Wu Haribabu Kommi Haruka Takatsuka Heath Lord Heikki Linnakangas Himanshu Upadhyaya Hironobu Suzuki Hugh McMaster Hugh Ranalli Hugh Wang Ian Barwick Ibrar Ahmed Ildar Musin Insung Moon Ireneusz Pluta Isaac Morland Ivan Kartyshov Ivan Panchenko Ivan Sergio Borgonovo Jaime Casanova James Coleman James Gray James Hunter James Inform James Lucas Jan Mussler Jaroslav Sivy Jeevan Chalke Jeevan Ladhe Jeff Davis Jeff Janes Jehan-Guillaume de Rorthais Jeremy Evans Jeremy Schneider Jeremy Smith Jerry Sievers Jesper Pedersen Jesse Kinkead Jesse Zhang Jian Zhang Jie Zhang Jim Nasby Jimmy Yih Jobin Augustine Joe Conway John Hsu John Naylor Jon Jensen Jonathan Katz Jorge Gustavo Rocha Josef Šimánek Joseph Nahmias Juan José Santamaría Flecha Julian Backes Julien Rouhaud Jürgen Purtz Justin King Justin Pryzby Karl O. Pinc Keisuke Kuroda Keith Fiske Kelly Min Ken Tanzer Kirill Bychik Kirk Jamison Konstantin Knizhnik Kuntal Ghosh Kyle Kingsbury Kyotaro Horiguchi Lars Kanis Laurenz Albe Leif Gunnar Erlandsen Li Japin Liudmila Mantrova Lucas Viecelli Luis M. Carril Lukáš Sobotka Maciek Sakrejda Magnus Hagander Mahadevan Ramachandran Mahendra Singh Thalor Manuel Rigger Marc Munro Marcos David Marina Polyakova Mark Dilger Mark Wong Marko Tiikkaja Markus Winand Marti Raudsepp Martijn van Oosterhout Masahiko Sawada Masahiro Ikeda Masao Fujii Mateusz Guzik Matt Jibson Matteo Beccati Maxence Ahlouche Melanie Plageman Michael Banck Michael Luo Michael Meskes Michael Paquier Michail Nikolaev Mike Palmiotto Mithun Cy Movead Li Nathan Bossart Nazli Ugur Koyluoglu Neha Sharma Nicola Contu Nicolás Alvarez Nikhil Sontakke Nikita Glukhov Nikolay Shaplov Nino Floris Noah Misch Noriyoshi Shinoda Oleg Bartunov Oleg Samoilov Oleksii Kliukin Ondrej Jirman Panagiotis Mavrogiorgos Pascal Legrand Patrick McHardy Paul Guo Paul Jungwirth Paul Ramsey Paul Sivash Paul Spencer Pavan Deolasee Pavel Borisov Pavel Luzanov Pavel Stehule Pavel Suderevsky Peifeng Qiu Pengzhou Tang Peter Billen Peter Eisentraut Peter Geoghegan Peter Smith Petr Fedorov Petr Jelínek Phil Bayer Philip Semanchuk Philippe Beaudoin Pierre Ducroquet Pierre Giraud Piotr Gabriel Kosinski Piotr Wlodarczyk Prabhat Sahu Quan Zongliang Quentin Rameau Rafael Castro Rafia Sabih Raj Mohite Rajkumar Raghuwanshi Ramanarayana M Ranier Vilela Rares Salcudean Raúl Marín Rodríguez Raymond Martin Reijo Suhonen Richard Guo Robert Ford Robert Haas Robert Kahlert Robert Treat Robin Abbi Robins Tharakan Roger Harkavy Roman Peshkurov Rui DeSousa Rui Hai Jiang Rushabh Lathia Ryan Lambert Ryohei Takahashi Scott Ribe Sean Farrell Sehrope Sarkuni Sergei Agalakov Sergei Kornilov Sergey Cherkashin Shawn Debnath Shawn Wang Shay Rojansky Shenhao Wang Simon Riggs Slawomir Chodnicki Soumyadeep Chakraborty Stéphane Lorek Stephen Frost Steve Rogerson Steven Winfield Surafel Temesgen Suraj Kharage Takanori Asaba Takao Fujii Takayuki Tsunakawa Takuma Hoshiai Tatsuhito Kasahara Tatsuo Ishii Tatsuro Yamada Taylor Vesely Teodor Sigaev Tham Nguyen Thibaut Madelaine Thom Brown Thomas Kellerer Thomas Munro Tiago Anastacio Tim Clarke Tim Möhlmann Tom Ellis Tom Gottfried Tom Lane Tomas Vondra Tuomas Leikola Tushar Ahuja Victor Wagner Victor Yegorov Vignesh C Vik Fearing Vinay Banakar Vladimir Leskov Vladimir Sitnikov Vyacheslav Makarov Vyacheslav Shablistyy Will Leinweber William Crowell Wyatt Alt Yang Xiao Yaroslav Schekin Yi Huang Yigong Hu Yoann La Cancellera Yoshikazu Imai Yu Kimura Yugo Nagata Yuli Khodorkovskiy Yusuke Egashira Yuya Watari Yuzuko Hosoya ZhenHua Cai