2022-05-12 This release contains a variety of fixes from 13.6. For information about new features in major release 13, see . A dump/restore is not required for those running 13.X. However, if you have any GiST indexes on columns of type ltree (supplied by the contrib/ltree extension), you should re-index them after updating. See the second changelog entry below. Also, if you are upgrading from a version earlier than 13.6, see . Confine additional operations within security restricted operation sandboxes (Sergey Shinderuk, Noah Misch) Autovacuum, CLUSTER, CREATE INDEX, REINDEX, REFRESH MATERIALIZED VIEW, and pg_amcheck activated the security restricted operation protection mechanism too late, or even not at all in some code paths. A user having permission to create non-temporary objects within a database could define an object that would execute arbitrary SQL code with superuser permissions the next time that autovacuum processed the object, or that some superuser ran one of the affected commands against it. The PostgreSQL Project thanks Alexander Lakhin for reporting this problem. (CVE-2022-1552) Fix default signature length for gist_ltree_ops indexes (Tomas Vondra, Alexander Korotkov) The default signature length (hash size) for GiST indexes on ltree columns was accidentally changed while upgrading that operator class to support operator class parameters. If any operations had been done on such an index without first upgrading the ltree extension to version 1.2, they were done assuming that the signature length was 28 bytes rather than the intended 8. This means it is very likely that such indexes are now corrupt. For safety we recommend re-indexing all GiST indexes on ltree columns after installing this update. (Note that GiST indexes on ltree[] columns, that is arrays of ltree, are not affected.) Stop using query-provided column aliases for the columns of whole-row variables that refer to plain tables (Tom Lane) The column names in tuples produced by a whole-row variable (such as tbl.* in contexts other than the top level of a SELECT list) are now always those of the associated named composite type, if there is one. We'd previously attempted to make them track any column aliases that had been applied to the FROM entry the variable refers to. But that's semantically dubious, because really then the output of the variable is not at all of the composite type it claims to be. Previous attempts to deal with that inconsistency had bad results up to and including storing unreadable data on disk, so just give up on the whole idea. In cases where it's important to be able to relabel such columns, a workaround is to introduce an extra level of sub-SELECT, so that the whole-row variable is referring to the sub-SELECT's output and not to a plain table. Then the variable is of type record to begin with and there's no issue. Fix incorrect output for types timestamptz and timetz in table_to_xmlschema() and allied functions (Renan Soares Lopes) The xmlschema output for these types included a malformed regular expression. Avoid core dump in parser for a VALUES clause with zero columns (Tom Lane) Fix planner errors for GROUPING() constructs that reference outer query levels (Richard Guo, Tom Lane) Fix plan generation for index-only scans on indexes with both returnable and non-returnable columns (Tom Lane) The previous coding could try to read non-returnable columns in addition to the returnable ones. This was fairly harmless because it didn't actually do anything with the bogus values, but it fell foul of a recently-added error check that rejected such a plan. Avoid accessing a no-longer-pinned shared buffer while attempting to lock an outdated tuple during EvalPlanQual (Tom Lane) The code would touch the buffer a couple more times after releasing its pin. In theory another process could recycle the buffer (or more likely, try to defragment its free space) as soon as the pin is gone, probably leading to failure to find the newer version of the tuple. Fix query-lifespan memory leak in an IndexScan node that is performing reordering (Aliaksandr Kalenik) Fix ALTER FUNCTION to support changing a function's parallelism property and its SET-variable list in the same command (Tom Lane) The parallelism property change was lost if the same command also updated the function's SET clause. Fix bogus errors from attempts to alter system columns of tables (Tom Lane) The system should just tell you that you can't do it, but sometimes it would report no owned sequence found instead. Fix mis-sorting of table rows when CLUSTERing using an index whose leading key is an expression (Peter Geoghegan, Thomas Munro) The table would be rebuilt with the correct data, but in an order having little to do with the index order. Fix risk of deadlock failures while dropping a partitioned index (Jimmy Yih, Gaurab Dey, Tom Lane) Ensure that the required table and index locks are taken in the standard order (parents before children, tables before indexes). The previous coding for DROP INDEX did it differently, and so could deadlock against concurrent queries taking these locks in the standard order. Fix race condition between DROP TABLESPACE and checkpointing (Nathan Bossart) The checkpoint forced by DROP TABLESPACE could sometimes fail to remove all dead files from the tablespace's directory, leading to a bogus tablespace is not empty error. Fix possible trouble in crash recovery after a TRUNCATE command that overlaps a checkpoint (Kyotaro Horiguchi, Heikki Linnakangas, Robert Haas) TRUNCATE must ensure that the table's disk file is truncated before the checkpoint is allowed to complete. Otherwise, replay starting from that checkpoint might find unexpected data in the supposedly-removed pages, possibly causing replay failure. Fix unsafe toast-data accesses during temporary object cleanup (Andres Freund) Temporary-object deletion during server process exit could fail with FATAL: cannot fetch toast data without an active snapshot. This was usually harmless since the next use of that temporary schema would clean up successfully. Improve wait logic in RegisterSyncRequest (Thomas Munro) If we run out of space in the checkpointer sync request queue (which is hopefully rare on real systems, but is common when testing with a very small buffer pool), we wait for it to drain. While waiting, we should report that as a wait event so that users know what is going on, and also watch for postmaster death, since otherwise the loop might never terminate if the checkpointer has already exited. Fix PANIC: xlog flush request is not satisfied failure during standby promotion when there is a missing WAL continuation record (Sami Imseih) Fix possibility of self-deadlock in hot standby conflict handling (Andres Freund) With unlucky timing, the WAL-applying process could get stuck while waiting for some other process to release a buffer lock. Fix possible mis-identification of the correct ancestor relation to publish logical replication changes through (Tomas Vondra, Hou zj, Amit Kapila) If publish_via_partition_root is enabled, and there are multiple publications naming different ancestors of the currently-modified relation, the wrong ancestor might be chosen for reporting the change. Ensure that logical replication apply workers can be restarted even when we're up against the max_sync_workers_per_subscription limit (Amit Kapila) Faulty coding of the limit check caused a restarted worker to exit immediately, leaving fewer workers than there should be. Include unchanged replica identity key columns in the WAL log for an update, if they are stored out-of-line (Dilip Kumar, Amit Kapila) Otherwise subscribers cannot see the values and will fail to replicate the update. Cope correctly with platforms that have no support for altering the server process's display in ps(1) (Andrew Dunstan) Few platforms are like this (the only supported one is Cygwin), so we'd managed not to notice that refactoring introduced a potential memory clobber. Disallow execution of SPI functions during PL/Perl function compilation (Tom Lane) Perl can be convinced to execute user-defined code during compilation of a PL/Perl function. However, it's not okay for such code to try to invoke SQL operations via SPI. That results in a crash, and if it didn't crash it would be a security hazard, because we really don't want code execution during function validation. Put in a check to give a friendlier error message instead. Make libpq accept root-owned SSL private key files (David Steele) This change synchronizes libpq's rules for safe ownership and permissions of SSL key files with the rules the server has used since release 9.6. Namely, in addition to the current rules, allow the case where the key file is owned by root and has permissions rw-r----- or less. This is helpful for system-wide management of key files. Fix behavior of libpq's PQisBusy() function after a connection failure (Tom Lane) If we'd detected a write failure, PQisBusy() would always return true, which is the wrong thing: we want input processing to carry on normally until we've read whatever is available from the server. The practical effect of this error is that applications using libpq's async-query API would typically detect connection loss only when PQconsumeInput() returns a hard failure. With this fix, a connection loss will normally be reported via an error PGresult object, which is a much cleaner behavior for most applications. Make pg_ctl recheck postmaster aliveness while waiting for stop/restart/promote actions (Tom Lane) pg_ctl would verify that the postmaster is alive as a side-effect of sending the stop or promote signal, but then it just naively waited to see the on-disk state change. If the postmaster died uncleanly without having removed its PID file or updated the control file, pg_ctl would wait until timeout. Instead make it recheck every so often that the postmaster process is still there. Fix error handling in pg_waldump (Kyotaro Horiguchi, Andres Freund) While trying to read a WAL file to determine the WAL segment size, pg_waldump would report an incorrect error for the case of a too-short file. In addition, the file name reported in this and related error messages could be garbage. Ensure that contrib/pageinspect functions cope with all-zero pages (Michael Paquier) This is a legitimate edge case, but the module was mostly unprepared for it. Arrange to return nulls, or no rows, as appropriate; that seems more useful than raising an error. In contrib/pageinspect, add defenses against incorrect page special space contents, tighten checks for correct page size, and add some missing checks that an index is of the expected type (Michael Paquier, Justin Pryzby, Julien Rouhaud) These changes make it less likely that the module will crash on bad data. In contrib/postgres_fdw, verify that ORDER BY clauses are safe to ship before requesting a remotely-ordered query, and include a USING clause if necessary (Ronan Dunklau) This fix prevents situations where the remote server might sort in a different order than we intend. While sometimes that would be only cosmetic, it could produce thoroughly wrong results if the remote data is used as input for a locally-performed merge join. Update JIT code to work with LLVM 14 (Thomas Munro) Clean up assorted failures under clang's -fsanitize=undefined checks (Tom Lane, Andres Freund, Zhihong Yu) Most of these changes are just for pro-forma compliance with the letter of the C and POSIX standards, and are unlikely to have any effect on production builds. Fix PL/Perl so it builds on C compilers that don't support statements nested within expressions (Tom Lane) Fix possible build failure of pg_dumpall on Windows, when not using MSVC to build (Andres Freund) In Windows builds, use gendef instead of pexports to build DEF files (Andrew Dunstan) This adapts the build process to work on recent MSys tool chains. Prevent extra expansion of shell wildcard patterns in programs built under MinGW (Andrew Dunstan) For some reason the C library provided by MinGW will expand shell wildcard characters in a program's command-line arguments by default. This is confusing, not least because it doesn't happen under MSVC, so turn it off. Update time zone data files to tzdata release 2022a for DST law changes in Palestine, plus historical corrections for Chile and Ukraine. 2022-02-10 This release contains a variety of fixes from 13.5. For information about new features in major release 13, see . A dump/restore is not required for those running 13.X. However, if you have applied REINDEX CONCURRENTLY to a TOAST table's index, or observe failures to access TOAST datums, see the first changelog entry below. Also, if you are upgrading from a version earlier than 13.5, see . Enforce standard locking protocol for TOAST table updates, to prevent problems with REINDEX CONCURRENTLY (Michael Paquier) If applied to a TOAST table or TOAST table's index, REINDEX CONCURRENTLY tended to produce a corrupted index. This happened because sessions updating TOAST entries released their ROW EXCLUSIVE locks immediately, rather than holding them until transaction commit as all other updates do. The fix is to make TOAST updates hold the table lock according to the normal rule. Any existing corrupted indexes can be repaired by reindexing again. Avoid null-pointer crash in ALTER STATISTICS when the statistics object is dropped concurrently (Tomas Vondra) Fix incorrect plan creation for parallel single-child Append nodes (David Rowley) In some cases the Append would be simplified away when it should not be, leading to wrong query results (duplicated rows). Fix index-only scan plans for cases where not all index columns can be returned (Tom Lane) If an index has both returnable and non-returnable columns, and one of the non-returnable columns is an expression using a table column that appears in a returnable index column, then a query using that expression could result in an index-only scan plan that attempts to read the non-returnable column, instead of recomputing the expression from the returnable column as intended. The non-returnable column would read as NULL, resulting in wrong query results. Ensure that casting to an unspecified typmod generates a RelabelType node rather than a length-coercion function call (Tom Lane) While the coercion function should do the right thing (nothing), this translation is undesirably inefficient. Fix checking of anycompatible-family data type matches (Tom Lane) In some cases the parser would think that a function or operator with anycompatible-family polymorphic parameters matches a set of arguments that it really shouldn't match. In reported cases, that led to matching more than one operator to a call, leading to ambiguous-operator errors; but a failure later on is also possible. Fix WAL replay failure when database consistency is reached exactly at a WAL page boundary (Álvaro Herrera) Fix startup of a physical replica to tolerate transaction ID wraparound (Abhijit Menon-Sen, Tomas Vondra) If a replica server is started while the set of active transactions on the primary crosses a wraparound boundary (so that there are some newer transactions with smaller XIDs than older ones), the replica would fail with out-of-order XID insertion in KnownAssignedXids. The replica would retry, but could never get past that error. In logical replication, avoid double transmission of a child table's data (Hou Zhijie) If a publication includes both child and parent tables, and has the publish_via_partition_root option set, subscribers uselessly initiated synchronization on both child and parent tables. Ensure that only the parent table is synchronized in such cases. Remove lexical limitations for SQL commands issued on a logical replication connection (Tom Lane) The walsender process would fail for a SQL command containing an unquoted semicolon, or with dollar-quoted literals containing odd numbers of single or double quote marks, or when the SQL command starts with a comment. Moreover, faulty error recovery could lead to unexpected errors in later commands too. Fix possible loss of the commit timestamp for the last subtransaction of a transaction (Alex Kingsborough, Kyotaro Horiguchi) Be sure to fsync the pg_logical/mappings subdirectory during checkpoints (Nathan Bossart) On some filesystems this oversight could lead to losing logical rewrite status files after a system crash. Build extended statistics for partitioned tables (Justin Pryzby) A previous bug fix disabled building of extended statistics for old-style inheritance trees, but it also prevented building them for partitioned tables, which was an unnecessary restriction. This change allows ANALYZE to compute values for statistics objects for partitioned tables. (But note that autovacuum does not process partitioned tables as such, so you must periodically issue manual ANALYZE on the partitioned table if you want to maintain such statistics.) Ignore extended statistics for inheritance trees (Justin Pryzby) Currently, extended statistics values are only computed locally for each table, not for entire inheritance trees. However the values were mistakenly consulted when planning queries across inheritance trees, possibly resulting in worse-than-default estimates. Disallow altering data type of a partitioned table's columns when the partitioned table's row type is used as a composite type elsewhere (Tom Lane) This restriction has long existed for regular tables, but through an oversight it was not checked for partitioned tables. Disallow ALTER TABLE ... DROP NOT NULL for a column that is part of a replica identity index (Haiying Tang, Hou Zhijie) The same prohibition already existed for primary key indexes. Correctly update cached table state during ALTER TABLE ADD PRIMARY KEY USING INDEX (Hou Zhijie) Concurrent sessions failed to update their opinion of whether the table has a primary key, possibly causing incorrect logical replication behavior. Correctly update cached table state when switching REPLICA IDENTITY index (Tang Haiying, Hou Zhijie) Concurrent sessions failed to update their opinion of which index is the replica identity one, possibly causing incorrect logical replication behavior. Allow parallel vacuuming and concurrent index building to be ignored while computing oldest xmin (Masahiko Sawada) Non-parallelized instances of these operations were already ignored, but the logic did not work for parallelized cases. Holding back the xmin horizon has undesirable effects such as delaying vacuum cleanup. Avoid leaking memory during REASSIGN OWNED BY operations that reassign ownership of many objects (Justin Pryzby) Improve performance of walsenders sending logical changes by avoiding unnecessary cache accesses (Hou Zhijie) Fix display of cert authentication method's options in pg_hba_file_rules view (Magnus Hagander) The cert authentication method implies clientcert=verify-full, but the pg_hba_file_rules view incorrectly reported clientcert=verify-ca. Fix display of whole-row variables appearing in INSERT ... VALUES rules (Tom Lane) A whole-row variable would be printed as var.*, but that allows it to be expanded to individual columns when the rule is reloaded, resulting in different semantics. Attach an explicit cast to prevent that, as we do elsewhere. Fix one-byte buffer overrun when applying Unicode string normalization to an empty string (Michael Paquier) The practical impact of this is limited thanks to alignment considerations; but in debug builds, a warning was raised. Fix or remove some incorrect assertions (Simon Riggs, Michael Paquier, Alexander Lakhin) These errors should affect only debug builds, not production. Fix race condition that could lead to failure to localize error messages that are reported early in multi-threaded use of libpq or ecpglib (Tom Lane) Avoid calling strerror from libpq's PQcancel function (Tom Lane) PQcancel is supposed to be safe to call from a signal handler, but strerror is not safe. The faulty usage only occurred in the unlikely event of failure to send the cancel message to the server, perhaps explaining the lack of reports. Make psql's \password command default to setting the password for CURRENT_USER, not the connection's original user name (Tom Lane) This agrees with the documented behavior, and avoids probable permissions failure if SET ROLE or SET SESSION AUTHORIZATION has been done since the session began. To prevent confusion, the role name to be acted on is now included in the password prompt. Fix psql \d command's query for identifying parent triggers (Justin Pryzby) The previous coding failed with more than one row returned by a subquery used as an expression if a partition had triggers and there were unrelated statement-level triggers of the same name on some parent partitioned table. Fix psql's tab-completion of label values for enum types (Tom Lane) In psql and some other client programs, avoid trying to invoke gettext() from a control-C signal handler (Tom Lane) While no reported failures have been traced to this mistake, it seems highly unlikely to be a safe thing to do. Allow canceling the initial password prompt in pg_receivewal and pg_recvlogical (Tom Lane, Nathan Bossart) Previously it was impossible to terminate these programs via control-C while they were prompting for a password. Fix pg_dump's dump ordering for user-defined casts (Tom Lane) In rare cases, the output script might refer to a user-defined cast before it had been created. Fix pg_dump's --inserts and --column-inserts modes to handle tables containing both generated columns and dropped columns (Tom Lane) Fix possible mis-reporting of errors in pg_dump and pg_basebackup (Tom Lane) The previous code failed to check for errors from some kernel calls, and could report the wrong errno values in other cases. Fix results of index-only scans on contrib/btree_gist indexes on char(N) columns (Tom Lane) Index-only scans returned column values with trailing spaces removed, which is not the expected behavior. That happened because that's how the data was stored in the index. This fix changes the code to store char(N) values with the expected amount of space padding. The behavior of such an index will not change immediately unless you REINDEX it; otherwise space-stripped values will be gradually replaced over time during updates. Queries that do not use index-only scan plans will be unaffected in any case. Change configure to use Python's sysconfig module, rather than the deprecated distutils module, to determine how to build PL/Python (Peter Eisentraut, Tom Lane, Andres Freund) With Python 3.10, this avoids configure-time warnings about distutils being deprecated and scheduled for removal in Python 3.12. Presumably, once 3.12 is out, configure --with-python would fail altogether. This future-proofing does come at a cost: sysconfig did not exist before Python 2.7, nor before 3.2 in the Python 3 branch, so it is no longer possible to build PL/Python against long-dead Python versions. Fix PL/Perl compile failure on Windows with Perl 5.28 and later (Victor Wagner) Fix PL/Python compile failure with Python 3.11 and later (Peter Eisentraut) Add support for building with Visual Studio 2022 (Hans Buschmann) Allow the .bat wrapper scripts in our MSVC build system to be called without first changing into their directory (Anton Voloshin, Andrew Dunstan) 2021-11-11 This release contains a variety of fixes from 13.4. For information about new features in major release 13, see . A dump/restore is not required for those running 13.X. However, note that installations using physical replication should update standby servers before the primary server, as explained in the third changelog entry below. Also, several bugs have been found that may have resulted in corrupted indexes, as explained in the next several changelog entries. If any of those cases apply to you, it's recommended to reindex possibly-affected indexes after updating. Also, if you are upgrading from a version earlier than 13.2, see . Make the server reject extraneous data after an SSL or GSS encryption handshake (Tom Lane) A man-in-the-middle with the ability to inject data into the TCP connection could stuff some cleartext data into the start of a supposedly encryption-protected database session. This could be abused to send faked SQL commands to the server, although that would only work if the server did not demand any authentication data. (However, a server relying on SSL certificate authentication might well not do so.) The PostgreSQL Project thanks Jacob Champion for reporting this problem. (CVE-2021-23214) Make libpq reject extraneous data after an SSL or GSS encryption handshake (Tom Lane) A man-in-the-middle with the ability to inject data into the TCP connection could stuff some cleartext data into the start of a supposedly encryption-protected database session. This could probably be abused to inject faked responses to the client's first few queries, although other details of libpq's behavior make that harder than it sounds. A different line of attack is to exfiltrate the client's password, or other sensitive data that might be sent early in the session. That has been shown to be possible with a server vulnerable to CVE-2021-23214. The PostgreSQL Project thanks Jacob Champion for reporting this problem. (CVE-2021-23222) Fix physical replication for cases where the primary crashes after shipping a WAL segment that ends with a partial WAL record (Álvaro Herrera) If the primary did not survive long enough to finish writing the rest of the incomplete WAL record, then the previous crash-recovery logic had it back up and overwrite WAL starting from the beginning of the incomplete WAL record. This is problematic since standby servers may already have copies of that WAL segment. They will then see an inconsistent next segment, and will not be able to recover without manual intervention. To fix, do not back up over a WAL segment boundary when restarting after a crash. Instead write a new type of WAL record at the start of the next WAL segment, informing readers that the incomplete WAL record will never be finished and must be disregarded. When applying this update, it's best to update standby servers before the primary, so that they will be ready to handle this new WAL record type if the primary happens to crash. Fix CREATE INDEX CONCURRENTLY to wait for the latest prepared transactions (Andrey Borodin) Rows inserted by just-prepared transactions might be omitted from the new index, causing queries relying on the index to miss such rows. The previous fix for this type of problem failed to account for PREPARE TRANSACTION commands that were still in progress when CREATE INDEX CONCURRENTLY checked for them. As before, in installations that have enabled prepared transactions (max_prepared_transactions > 0), it's recommended to reindex any concurrently-built indexes in case this problem occurred when they were built. Avoid race condition that can cause backends to fail to add entries for new rows to an index being built concurrently (Noah Misch, Andrey Borodin) While it's apparently rare in the field, this case could potentially affect any index built or reindexed with the CONCURRENTLY option. It is recommended to reindex any such indexes to make sure they are correct. Fix float4 and float8 hash functions to produce uniform results for NaNs (Tom Lane) Since PostgreSQL's floating-point types deem all NaNs to be equal, it's important for the hash functions to produce the same hash code for all bit-patterns that are NaNs according to the IEEE 754 standard. This failed to happen before, meaning that hash indexes and hash-based query plans might produce incorrect results for non-canonical NaN values. ('-NaN'::float8 is one way to produce such a value on most machines.) It is advisable to reindex hash indexes on floating-point columns, if there is any possibility that they might contain such values. Fix REINDEX CONCURRENTLY to preserve operator class parameters that were attached to the target index (Michael Paquier) Prevent data loss during crash recovery of CREATE TABLESPACE, when wal_level = minimal (Noah Misch) If the server crashed between CREATE TABLESPACE and the next checkpoint, replay would fully remove the contents of the new tablespace's directory, relying on subsequent WAL replay to restore everything within that directory. This interacts badly with optimizations that skip writing WAL (one example is COPY into a just-created table). Such optimizations are applied only when wal_level is minimal, which is not the default in v10 and later. Ensure that the relation cache is invalidated for a table being attached to or detached from a partitioned table (Amit Langote, Álvaro Herrera) This oversight could allow misbehavior of subsequent inserts/updates addressed directly to the partition, but only in currently-existing sessions. Ensure that the relation cache is invalidated for all partitions of a partitioned table that is being added to or removed from a publication (Hou Zhijie, Vignesh C) This oversight could lead to improper replication behavior until all currently-existing sessions have exited. Ensure that the relation cache is invalidated when creating or dropping a FOR ALL TABLES publication (Hou Zhijie, Vignesh C) This oversight could lead to improper replication behavior until all currently-existing sessions have exited. Don't discard a cast to the same type with unspecified type modifier (Tom Lane) For example, if column f1 is of type numeric(18,3), the parser used to simply discard a cast like f1::numeric, on the grounds that it would have no run-time effect. That's true, but the exposed type of the expression should still be considered to be plain numeric, not numeric(18,3). This is important for correctly resolving the type of larger constructs, such as recursive UNIONs. Fix updates of element fields in arrays of domain over composite (Tom Lane) A command such as UPDATE tab SET fld[1].subfld = val failed if the array's elements were domains rather than plain composites. Disallow the combination of FETCH FIRST WITH TIES and FOR UPDATE SKIP LOCKED (David Christensen) FETCH FIRST WITH TIES necessarily fetches one more row than requested, since it cannot stop until it finds a row that is not a tie. In our current implementation, if FOR UPDATE is used then that row will also get locked even though it is not returned. That results in undesirable behavior if the SKIP LOCKED option is specified. It's difficult to change this without introducing a different set of undesirable behaviors, so for now, forbid the combination. Disallow creating an ICU collation if the current database's encoding won't support it (Tom Lane) Previously this was allowed, but then the collation could not be referenced because of the way collation lookup works; you could not use the collation, nor even drop it. Disallow ALTER INDEX index ALTER COLUMN col SET (options) (Nathan Bossart, Michael Paquier) While the parser accepted this, it's undocumented and doesn't actually work. Fix corner-case loss of precision in numeric power() (Dean Rasheed) The result could be inaccurate when the first argument is very close to 1. Avoid regular expression errors with capturing parentheses inside {0} (Tom Lane) Regular expressions like (.){0}...\1 drew invalid backreference number. Other regexp engines such as Perl don't complain, though, and for that matter ours doesn't either in some closely related cases. Worse, it could throw an assertion failure instead. Fix it so that no error is thrown and instead the back-reference is silently deemed to never match. Prevent regular expression back-references from sometimes matching when they shouldn't (Tom Lane) The regexp engine was careless about clearing match data for capturing parentheses after rejecting a partial match. This could allow a later back-reference to match in places where it should fail for lack of a defined referent. Fix regular expression performance bug with back-references inside iteration nodes (Tom Lane) Incorrect back-tracking logic could result in exponential time spent looking for a match. Fortunately the problem is masked in most cases by other optimizations. Fix incorrect results from AT TIME ZONE applied to a time with time zone value (Tom Lane) The results were incorrect if the target time zone was specified by a dynamic timezone abbreviation (that is, one that is defined as equivalent to a full time zone name, rather than a fixed UTC offset). Fix planner error with pulling up subquery expressions into function rangetable entries (Tom Lane) If a function in FROM laterally references the output of some sub-SELECT earlier in the FROM clause, and we are able to flatten that sub-SELECT into the outer query, the expression(s) copied into the function expression were not fully processed. This could lead to crashes at execution. Fix mistranslation of PlaceHolderVars to inheritance child relations (Tom Lane) This error could result in assertion failures, or in mis-planning of queries having partitioned or inherited tables on the nullable side of an outer join. Avoid using MCV-only statistics to estimate the range of a column (Tom Lane) There are corner cases in which ANALYZE will build a most-common-values (MCV) list but not a histogram, even though the MCV list does not account for all the observed values. In such cases, keep the planner from using the MCV list alone to estimate the range of column values. Fix restoration of a Portal's snapshot inside a subtransaction (Bertrand Drouvot) If a procedure commits or rolls back a transaction, and then its next significant action is inside a new subtransaction, snapshot management went wrong, leading to a dangling pointer and probable crash. A typical example in PL/pgSQL is a COMMIT immediately followed by a BEGIN ... EXCEPTION block that performs a query. Clean up correctly if a transaction fails after exporting its snapshot (Dilip Kumar) This oversight would only cause a problem if the same session attempted to export a snapshot again. The most likely scenario for that is creation of a replication slot (followed by rollback) and then creation of another replication slot. Prevent wraparound of overflowed-subtransaction tracking on standby servers (Kyotaro Horiguchi, Alexander Korotkov) This oversight could cause significant performance degradation (manifesting as excessive SubtransSLRU traffic) on standby servers. Ensure that prepared transactions are properly accounted for during promotion of a standby server (Michael Paquier, Andres Freund) There was a narrow window where a prepared transaction could be omitted from a snapshot taken by a concurrently-running session. If that session then used the snapshot to perform data updates, erroneous results or data corruption could occur. Disallow LISTEN in background workers (Tom Lane) There's no infrastructure to support this, so if someone did it, it would only result in preventing cleanup of the NOTIFY queue. Send NOTIFY signals to other backends during transaction commit, not in the server's idle loop (Artur Zakirov, Tom Lane) This change allows notifications to be delivered immediately after an intra-procedure COMMIT. It also allows logical replication workers to send notifications. Refuse to rewind a cursor marked NO SCROLL if it has been held over from a previous transaction due to the WITH HOLD option (Tom Lane) We have long forbidden fetching backwards from a NO SCROLL cursor, but for historical reasons the prohibition didn't extend to cases in which we rewind the query altogether and then re-fetch forwards. That exception leads to inconsistencies, particularly for held-over cursors which may not have stored all the data necessary to rewind. Disallow rewinding for non-scrollable held-over cursors to block the worst inconsistencies. (v15 will remove the exception altogether.) Fix possible failure while saving a WITH HOLD cursor at transaction end, if it had already been read to completion (Tom Lane) Fix detection of a relation that has grown to the maximum allowed length (Tom Lane) An attempt to extend a table or index past the limit of 2^32-1 blocks was rejected, but not soon enough to prevent inconsistent internal state from being created. Correctly track the presence of data-modifying CTEs when expanding a DO INSTEAD rule (Greg Nancarrow, Tom Lane) The previous failure to do this could lead to problems such as unsafely choosing a parallel plan. Fix incorrect reporting of permissions failures on extended statistics objects (Tomas Vondra) The code typically produced cache lookup error rather than the intended message. Fix incorrect snapshot handling in parallel workers (Greg Nancarrow) This oversight could lead to misbehavior in parallel queries if the transaction isolation level is less than REPEATABLE READ. Fix logical decoding to correctly ignore toast-table changes for transient tables (Bertrand Drouvot) Logical decoding normally ignores changes in transient tables such as those created during an ALTER TABLE heap rewrite. But that filtering wasn't applied to the associated toast table if any, leading to possible errors when rewriting a table that's being published. Fix logical decoding's memory usage accounting to handle TOAST data correctly (Bertrand Drouvot) Ensure that walreceiver processes create all required archive notification files before exiting (Fujii Masao) If a walreceiver exited exactly at a WAL segment boundary, it failed to make a notification file for the last-received segment, thus delaying archiving of that segment on the standby. Fix computation of the WAL range to include in a backup manifest when a timeline change is involved (Kyotaro Horiguchi) Avoid trying to lock the OLD and NEW pseudo-relations in a rule that uses SELECT FOR UPDATE (Masahiko Sawada, Tom Lane) Fix parser's processing of aggregate FILTER clauses (Tom Lane) If the FILTER expression is a plain boolean column, the semantic level of the aggregate could be mis-determined, leading to not-per-spec behavior. If the FILTER expression is itself a boolean-returning aggregate, an error should be thrown but was not, likely resulting in a crash at execution. Ensure that the correct lock level is used when renaming a table (Nathan Bossart, Álvaro Herrera) For historical reasons, ALTER INDEX ... RENAME can be applied to any sort of relation. The lock level required to rename an index is lower than that required to rename a table or other kind of relation, but the code got this wrong and would use the weaker lock level whenever the command is spelled ALTER INDEX. Prevent ALTER TYPE/DOMAIN/OPERATOR ... SET from changing extension membership (Tom Lane) ALTER ... SET executed by an extension script would cause the target object to become a member of the extension if it was not already. In itself this isn't too troubling, since there's little reason for an extension script to touch an object not belonging to the extension. But ALTER TYPE SET will recurse to dependent domains, thus causing them to also become extension members. This causes unwanted side-effects from extension upgrade scripts that use that command to adjust the properties of a base type belonging to the extension. Fix by redefining these ALTER cases to never change extension membership. Avoid trying to clean up LLVM state after an error within LLVM (Andres Freund, Justin Pryzby) This prevents a likely crash during backend exit after a fatal LLVM error. Avoid null-pointer-dereference crash when dropping a role that owns objects being dropped concurrently (Álvaro Herrera) Prevent snapshot reference leak warning when lo_export() or a related function fails (Heikki Linnakangas) Ensure that scans of SP-GiST indexes are counted in the statistics views (Tom Lane) Incrementing the number-of-index-scans counter was overlooked in the SP-GiST code, although per-tuple counters were advanced correctly. Fix inefficient code generation for CoerceToDomain expression nodes (Ranier Vilela) Recalculate relevant wait intervals if recovery_min_apply_delay is changed during recovery (Soumyadeep Chakraborty, Ashwin Agrawal) Fix infinite loop if a simplehash.h hash table reaches 2^32 elements (Yura Sokolov) It seems unlikely that this bug has been hit in practice, as it would require work_mem settings of hundreds of gigabytes for existing uses of simplehash.h. Avoid O(N^2) behavior in some list-manipulation operations (Nathan Bossart, Tom Lane) These changes fix slow processing in several scenarios, including: when a standby replays a transaction that held many exclusive locks on the primary; when many files are due to be unlinked after a checkpoint; when hash aggregation involves many batches; and when pg_trgm extracts indexable conditions from a complex regular expression. Only the first of these scenarios has actually been reported from the field, but they all seem like plausible consequences of inefficient list deletions. Reduce memory consumption during calculation of extended statistics (Justin Pryzby, Tomas Vondra) Add more defensive checks around B-tree posting list splits (Peter Geoghegan) This change should help detect index corruption involving duplicate table TIDs. Disallow setting huge_pages to on when shared_memory_type is sysv (Thomas Munro) Previously, this setting was accepted, but it did nothing for lack of any implementation. Fix missing libpq functions on AIX (Tony Reix) Code reorganization led to the following documented functions not being exported from libpq on AIX: pg_encoding_to_char(), pg_utf_mblen(), pg_char_to_encoding(), pg_valid_server_encoding(), and pg_valid_server_encoding_id(). Restore them to visibility. Fix ecpg to recover correctly after malloc() failure while establishing a connection (Michael Paquier) Fix misevaluation of stable functions called in the arguments of a PL/pgSQL CALL statement (Tom Lane) They were being called with an out-of-date snapshot, so that they would not see any database changes made since the start of the session's top-level command. Allow EXIT out of the outermost block in a PL/pgSQL routine (Tom Lane) If the routine does not require an explicit RETURN, this usage should be valid, but it was rejected. Remove pg_ctl's hard-coded limits on the total length of generated commands (Phil Krylov) For example, this removes a restriction on how many command-line options can be passed through to the postmaster. Individual path names that pg_ctl deals with, such as the postmaster executable's name or the data directory name, are still limited to MAXPGPATH bytes in most cases. Fix pg_dump to dump non-global default privileges correctly (Neil Chen, Masahiko Sawada) If a global (unrestricted) ALTER DEFAULT PRIVILEGES command revoked some present-by-default privilege, for example EXECUTE for functions, and then a restricted ALTER DEFAULT PRIVILEGES command granted that privilege again for a selected role or schema, pg_dump failed to dump the restricted privilege grant correctly. Make pg_dump acquire shared lock on partitioned tables that are to be dumped (Tom Lane) This oversight was usually pretty harmless, since once pg_dump has locked any of the leaf partitions, that would suffice to prevent significant DDL on the partitioned table itself. However problems could ensue when dumping a childless partitioned table, since no relevant lock would be held. Improve pg_dump's performance by avoiding making per-table queries for RLS policies, and by avoiding repetitive calls to format_type() (Tom Lane) These changes provide only marginal improvement when dumping from a local server, but a dump from a remote server can benefit substantially due to fewer network round-trips. Fix crash in pg_dump when attempting to dump trigger definitions from a pre-8.3 server (Tom Lane) Fix incorrect filename in pg_restore's error message about an invalid large object TOC file (Daniel Gustafsson) Ensure that pgbench exits with non-zero status after a socket-level failure (Yugo Nagata, Fabien Coelho) The desired behavior is to finish out the run but then exit with status 2. Also, fix the reporting of such errors. Fix failure of contrib/btree_gin indexes on "char" (not char(n)) columns, when an indexscan using the < or <= operator is performed (Tom Lane) Such an indexscan failed to return all the entries it should. Change contrib/pg_stat_statements to read its query texts file in units of at most 1GB (Tom Lane) Such large query text files are very unusual, but if they do occur, the previous coding would fail on Windows 64 (which rejects individual read requests of more than 2GB). Fix null-pointer crash when contrib/postgres_fdw tries to report a data conversion error (Tom Lane) Add spinlock support for the RISC-V architecture (Marek Szuba) This is essential for reasonable performance on that platform. Support OpenSSL 3.0.0 (Peter Eisentraut, Daniel Gustafsson, Michael Paquier) Set correct type identifier on OpenSSL BIO (I/O abstraction) objects created by PostgreSQL (Itamar Gafni) This oversight probably only matters for code that is doing tasks like auditing the OpenSSL installation. But it's nominally a violation of the OpenSSL API, so fix it. Fix our pkg-config files to again support static linking of libpq (Peter Eisentraut) Make pg_regexec() robust against an out-of-range search_start parameter (Tom Lane) Return REG_NOMATCH, instead of possibly crashing, when search_start is past the end of the string. This case is probably unreachable within core PostgreSQL, but extensions might be more careless about the parameter value. Ensure that GetSharedSecurityLabel() can be used in a newly-started session that has not yet built its critical relation cache entries (Jeff Davis) Use the CLDR project's data to map Windows time zone names to IANA time zones (Tom Lane) When running on Windows, initdb attempts to set the new cluster's timezone parameter to the IANA time zone matching the system's prevailing time zone. We were using a mapping table that we'd generated years ago and updated only fitfully; unsurprisingly, it contained a number of errors as well as omissions of recently-added zones. It turns out that CLDR has been tracking the most appropriate mappings, so start using their data. This change will not affect any existing installation, only newly-initialized clusters. Update time zone data files to tzdata release 2021e for DST law changes in Fiji, Jordan, Palestine, and Samoa, plus historical corrections for Barbados, Cook Islands, Guyana, Niue, Portugal, and Tonga. Also, the Pacific/Enderbury zone has been renamed to Pacific/Kanton. Also, the following zones have been merged into nearby, more-populous zones whose clocks have agreed with them since 1970: Africa/Accra, America/Atikokan, America/Blanc-Sablon, America/Creston, America/Curacao, America/Nassau, America/Port_of_Spain, Antarctica/DumontDUrville, and Antarctica/Syowa. In all these cases, the previous zone name remains as an alias. 2021-08-12 This release contains a variety of fixes from 13.3. For information about new features in major release 13, see . A dump/restore is not required for those running 13.X. However, if you are upgrading from a version earlier than 13.2, see . Fix mis-planning of repeated application of a projection step (Tom Lane) The planner could create an incorrect plan in cases where two ProjectionPaths were stacked on top of each other. The only known way to trigger that situation involves parallel sort operations, but there may be other instances. The result would be crashes or incorrect query results. Disclosure of server memory contents is also possible. (CVE-2021-3677) Disallow SSL renegotiation more completely (Michael Paquier) SSL renegotiation has been disabled for some time, but the server would still cooperate with a client-initiated renegotiation request. A maliciously crafted renegotiation request could result in a server crash (see OpenSSL issue CVE-2021-3449). Disable the feature altogether on OpenSSL versions that permit doing so, which are 1.1.0h and newer. Restore the Portal-level snapshot after COMMIT or ROLLBACK within a procedure (Tom Lane) This change fixes cases where an attempt to fetch a toasted value immediately after COMMIT/ROLLBACK would fail with errors like no known snapshots or missing chunk number 0 for toast value. Some extensions may attempt to execute SQL code outside of any Portal. They are responsible for ensuring that an outer snapshot exists before doing so. Previously, not providing a snapshot might work or it might not; now it will consistently fail with cannot execute SQL without an outer snapshot or portal. Avoid misbehavior when persisting the output of a cursor that's reading a non-stable query (Tom Lane) Previously, we'd always rewind and re-read the whole query result, possibly getting results different from the earlier execution, causing great confusion later. For a NO SCROLL cursor, we can fix this by only storing the not-yet-read portion of the query output, which is sufficient since a NO SCROLL cursor can't be backed up. Cursors with the SCROLL option remain at hazard, but that was already documented to be an unsafe option to use with a non-stable query. Make those documentation warnings stronger. Also force NO SCROLL mode for the implicit cursor used by a PL/pgSQL FOR-over-query loop, to avoid this type of problem when persisting such a cursor during an intra-procedure commit. Reject SELECT ... GROUP BY GROUPING SETS (()) FOR UPDATE (Tom Lane) This should be disallowed, just as FOR UPDATE with a plain GROUP BY is disallowed, but the test for that failed to handle empty grouping sets correctly. The end result would be a null-pointer dereference in the executor. Reject cases where a query in WITH rewrites to just NOTIFY (Tom Lane) Such cases previously crashed. In numeric multiplication, round the result rather than failing if it would have more than 16383 digits after the decimal point (Dean Rasheed) Fix corner-case errors and loss of precision when raising numeric values to very large powers (Dean Rasheed) Fix division-by-zero failure in to_char() with EEEE format and a numeric input value less than 10^(-1001) (Dean Rasheed) Fix pg_size_pretty(bigint) to round negative values consistently with the way it rounds positive ones (and consistently with the numeric version) (Dean Rasheed, David Rowley) Make pg_filenode_relation(0, 0) return NULL rather than failing (Justin Pryzby) Make ALTER EXTENSION lock the extension when adding or removing a member object (Tom Lane) The previous coding allowed ALTER EXTENSION ADD/DROP to occur concurrently with DROP EXTENSION, leading to a crash or corrupt catalog entries. Fix ALTER SUBSCRIPTION to reject an empty slot name (Japin Li) When cloning a partitioned table's triggers to a new partition, ensure that their enabled status is copied (Álvaro Herrera) Avoid alias conflicts in queries generated for REFRESH MATERIALIZED VIEW CONCURRENTLY (Tom Lane, Bharath Rupireddy) This command failed on materialized views containing columns with certain names, notably mv and newdata. Fix PREPARE TRANSACTION to check correctly for conflicting session-lifespan and transaction-lifespan locks (Tom Lane) A transaction cannot be prepared if it has both session-lifespan and transaction-lifespan locks on the same advisory-lock ID value. This restriction was not fully checked, which could lead to a PANIC during PREPARE TRANSACTION. Fix misbehavior of DROP OWNED BY when the target role is listed more than once in an RLS policy (Tom Lane) Skip unnecessary error tests when removing a role from an RLS policy during DROP OWNED BY (Tom Lane) Notably, this fixes some cases where it was necessary to be a superuser to use DROP OWNED BY. Re-allow old-style Windows locale names in CREATE COLLATION commands (Thomas Munro) Previously we were failing because the operating system can't provide version information for such locales. At some point we may decide to require version information, but no such policy exists yet, so re-allow the case for now. Disallow whole-row variables in GENERATED expressions (Tom Lane) Use of a whole-row variable clearly violates the rule that a generated column cannot depend on itself, so such cases have no well-defined behavior. The actual behavior frequently included a crash. Fix usage of tableoid in GENERATED expressions (Tom Lane) Some code paths failed to provide a valid value for this system column while evaluating a GENERATED expression. Don't store a fast default when adding a column to a foreign table (Andrew Dunstan) The fast default is useless since no local heap storage exists for such a table, but it confused subsequent operations. In addition to suppressing creation of such catalog entries in ALTER TABLE commands, adjust the downstream code to cope when one is incorrectly present. Allow index state flags to be updated transactionally (Michael Paquier, Andrey Lepikhov) This avoids failures when dealing with index predicates that aren't really immutable. While that's not considered a supported case, the original reason for using a non-transactional update here is long gone, so we may as well change it. Avoid corrupting the plan cache entry when CREATE DOMAIN or ALTER DOMAIN appears in a cached plan (Tom Lane) Make walsenders show their latest replication commands in pg_stat_activity (Tom Lane) Previously, a walsender would show its latest SQL command, which was confusing if it's now doing some replication operation instead. Now we show replication-protocol commands on the same footing as SQL commands. Make pg_settings.pending_restart show as true when the pertinent entry in postgresql.conf has been removed (Álvaro Herrera) pending_restart correctly showed the case where an entry that cannot be changed without a postmaster restart has been modified, but not where the entry had been removed altogether. On 64-bit Windows, allow the effective value of work_mem times hash_mem_multiplier to exceed 2GB (Tom Lane) This allows hash_mem_multiplier to be used for its intended purpose of preventing large hash aggregations from spilling to disk, even when large means multiple gigabytes. Fix mis-planning of queries involving regular tables that are inheritance children of foreign tables (Amit Langote) SELECT FOR UPDATE and related commands would fail with assertion failures or could not find junk column errors in such cases. Fix pullup of constant function-in-FROM results when the FROM item is marked LATERAL (Tom Lane) Fix corner-case failure of a new standby to follow a new primary (Dilip Kumar, Robert Haas) Under a narrow combination of conditions, the standby could wind up trying to follow the wrong WAL timeline. Update minimum recovery point when WAL replay of a transaction abort record causes file truncation (Fujii Masao) File truncation is irreversible, so it's no longer safe to stop recovery at a point earlier than that record. The corresponding case for transaction commit was fixed years ago, but this one was overlooked. Advance oldest-required-WAL-segment horizon properly after a replication slot is invalidated (Kyotaro Horiguchi) If all slots were invalidated, the horizon would not move again, eventually allowing the server's WAL storage to run out of space. In walreceivers, avoid attempting catalog lookups after an error (Masahiko Sawada, Bharath Rupireddy) Ensure that a standby server's startup process will respond to a shutdown signal promptly while waiting for WAL to arrive (Fujii Masao, Soumyadeep Chakraborty) Correctly clear shared state after failing to become a member of a transaction commit group (Amit Kapila) Given the right timing, this could cause an assertion failure when some later session re-uses the same PGPROC object. Add locking to avoid reading incorrect relmapper data in the face of a concurrent write from another process (Heikki Linnakangas) Improve progress reporting for the sort phase of a parallel btree index build (Matthias van de Meent) Improve checks for violations of replication protocol (Tom Lane) Logical replication workers frequently used Asserts to check for cases that could be triggered by invalid or out-of-order replication commands. This seems unwise, so promote these tests to regular error checks. Fix assorted crash cases in logical replication of partitioned-table updates (Amit Langote, Tom Lane) Fix potential crash when firing AFTER triggers of partitioned tables in logical replication workers (Tom Lane) Fix deadlock when multiple logical replication workers try to truncate the same table (Peter Smith, Haiying Tang) Fix error cases and memory leaks in logical decoding of speculative insertions (Dilip Kumar) Fix memory leak in logical replication output (Amit Langote) Avoid leaving an invalid record-type hash table entry behind after an error (Sait Talha Nisanci) This could lead to later crashes or memory leakage. Fix plan cache reference leaks in some error cases in CREATE TABLE ... AS EXECUTE (Tom Lane) Fix race condition in code for sharing tuple descriptors across parallel workers (Thomas Munro) Given the right timing, a crash could result. Fix race condition when invalidating an obsolete replication slot concurrently with an attempt to drop or update it (Andres Freund, Álvaro Herrera) Fix possible race condition when releasing BackgroundWorkerSlots (Tom Lane) It's likely that this doesn't fix any observable bug on Intel hardware, but machines with weaker memory ordering rules could have problems. Fix latent crash in sorting code (Ronan Dunklau) One code path could attempt to free a null pointer. The case appears unreachable in the core server's use of sorting, but perhaps it could be triggered by extensions. Harden B-tree posting list split code against corrupt data (Peter Geoghegan) Throw an error, rather than crashing, for an attempt to insert an item with a TID identical to an existing entry. While that shouldn't ever happen, it has been reported to happen when the index is inconsistent with its table. Prevent infinite loops in SP-GiST index insertion (Tom Lane) In the event that INCLUDE columns take up enough space to prevent a leaf index tuple from ever fitting on a page, the text_ops operator class would get into an infinite loop vainly trying to make the tuple fit. While pre-v11 versions don't have INCLUDE columns, back-patch this anti-looping fix to them anyway, as it seems like a good defense against bugs in operator classes. Ensure that SP-GiST index insertion can be terminated by a query cancel request (Tom Lane, Álvaro Herrera) Fix uninitialized-variable bug that could cause PL/pgSQL to act as though an INTO clause specified STRICT, even though it didn't (Tom Lane) Don't abort the process for an out-of-memory failure in libpq's printing functions (Tom Lane) In ecpg, allow the numeric value INT_MIN (usually -2147483648) to be converted to integer (John Naylor) In psql and other client programs, avoid overrunning the ends of strings when dealing with invalidly-encoded data (Tom Lane) An incorrectly-encoded multibyte character near the end of a string could cause various processing loops to run past the string's terminating NUL, with results ranging from no detectable issue to a program crash, depending on what happens to be in the following memory. This is reminiscent of CVE-2006-2313, although these particular cases do not appear to have interesting security consequences. Fix pg_dump to correctly handle triggers on partitioned tables whose enabled status is different from their parent triggers' status (Justin Pryzby, Álvaro Herrera) Avoid invalid creation date in header warnings observed when running pg_restore on an archive file created in a different time zone (Tom Lane) Make pg_upgrade carry forward the old installation's oldestXID value (Bertrand Drouvot) Previously, the new installation's oldestXID was set to a value old enough to (usually) force immediate anti-wraparound autovacuuming. That's not desirable from a performance standpoint; what's worse, installations using large values of autovacuum_freeze_max_age could suffer unwanted forced shutdowns soon after an upgrade. Extend pg_upgrade to detect and warn about extensions that should be upgraded (Bruce Momjian) A script file is now produced containing the ALTER EXTENSION UPDATE commands needed to bring extensions up to the versions that are considered default in the new installation. Avoid problems when switching pg_receivewal between compressed and non-compressed WAL storage (Michael Paquier) Fix contrib/postgres_fdw to work usefully with generated columns (Etsuro Fujita) postgres_fdw will now behave reasonably with generated columns, so long as a generated column in a foreign table represents a generated column in the remote table. IMPORT FOREIGN SCHEMA will now import generated columns that way by default. In contrib/postgres_fdw, avoid attempting catalog lookups after an error (Tom Lane) While this usually worked, it's not very safe since the error might have been one that made catalog access nonfunctional. A side effect of the fix is that messages about data conversion errors will now mention the query's table and column aliases (if used) rather than the true underlying name of a foreign table or column. Improve the isolation-test infrastructure (Tom Lane, Michael Paquier) Allow isolation test steps to be annotated to show the expected completion order. This allows getting stable results from otherwise-racy test cases, without the long delays that we previously used (not entirely successfully) to fend off race conditions. Allow non-quoted identifiers as isolation test session/step names (formerly, all such names had to be double-quoted). Detect and warn about unused steps in isolation tests. Improve display of query results in isolation tests. Remove isolationtester's dry-run mode. Remove memory leaks in isolationtester itself. Reduce overhead of cache-clobber testing (Tom Lane) Fix PL/Python's regression tests to pass with Python 3.10 (Honza Horak) Make printf("%s", NULL) print (null) instead of crashing (Tom Lane) This should improve server robustness in corner cases, and it syncs our printf implementation with common libraries. Fix incorrect log message when point-in-time recovery stops at a ROLLBACK PREPARED record (Simon Riggs) Improve ALTER TABLE's messages for wrong-relation-kind errors (Kyotaro Horiguchi) Clarify error messages referring to non-negative values (Bharath Rupireddy) Fix configure to work with OpenLDAP 2.5, which no longer has a separate libldap_r library (Adrian Ho, Tom Lane) If there is no libldap_r library, we now silently assume that libldap is thread-safe. Add new make targets world-bin and install-world-bin (Andrew Dunstan) These are the same as world and install-world respectively, except that they do not build or install the documentation. Fix make rule for TAP tests (prove_installcheck) to work in PGXS usage (Andrew Dunstan) Adjust JIT code to prepare for forthcoming LLVM API change (Thomas Munro, Andres Freund) LLVM 13 has made an incompatible API change that will cause crashing of our previous JIT compiler. Avoid assuming that strings returned by GSSAPI libraries are null-terminated (Tom Lane) The GSSAPI spec provides for a string pointer and length. It seems that in practice the next byte after the string is usually zero, so that our previous coding didn't actually fail; but we do have a report of AddressSanitizer complaints. Enable building with GSSAPI on MSVC (Michael Paquier) Fix various incompatibilities with modern Kerberos builds. In MSVC builds, include --with-pgport in the set of configure options reported by pg_config, if it had been specified (Andrew Dunstan) 2021-05-13 This release contains a variety of fixes from 13.2. For information about new features in major release 13, see . A dump/restore is not required for those running 13.X. However, if you are upgrading from a version earlier than 13.2, see . Prevent integer overflows in array subscripting calculations (Tom Lane) The array code previously did not complain about cases where an array's lower bound plus length overflows an integer. This resulted in later entries in the array becoming inaccessible (since their subscripts could not be written as integers), but more importantly it confused subsequent assignment operations. This could lead to memory overwrites, with ensuing crashes or unwanted data modifications. (CVE-2021-32027) Fix mishandling of junk columns in INSERT ... ON CONFLICT ... UPDATE target lists (Tom Lane) If the UPDATE list contains any multi-column sub-selects (which give rise to junk columns in addition to the results proper), the UPDATE path would end up storing tuples that include the values of the extra junk columns. That's fairly harmless in the short run, but if new columns are added to the table then the values would become accessible, possibly leading to malfunctions if they don't match the datatypes of the added columns. In addition, in versions supporting cross-partition updates, a cross-partition update triggered by such a case had the reverse problem: the junk columns were removed from the target list, typically causing an immediate crash due to malfunction of the multi-column sub-select mechanism. (CVE-2021-32028) Fix possibly-incorrect computation of UPDATE ... RETURNING outputs for joined cross-partition updates (Amit Langote, Etsuro Fujita) If an UPDATE for a partitioned table caused a row to be moved to another partition with a physically different row type (for example, one with a different set of dropped columns), computation of RETURNING results for that row could produce errors or wrong answers. No error is observed unless the UPDATE involves other tables being joined to the target table. (CVE-2021-32029) Fix adjustment of constraint deferrability properties in partitioned tables (Álvaro Herrera) When applied to a foreign-key constraint of a partitioned table, ALTER TABLE ... ALTER CONSTRAINT failed to adjust the DEFERRABLE and/or INITIALLY DEFERRED markings of the constraints and triggers of leaf partitions. This led to unexpected behavior of such constraints. After updating to this version, any misbehaving partitioned tables can be fixed by executing a new ALTER command to set the desired properties. This change also disallows applying such an ALTER directly to the constraints of leaf partitions. The only supported case is for the whole partitioning hierarchy to have identical constraint properties, so such ALTERs must be applied at the partition root. When attaching a child table with ALTER TABLE ... INHERIT, insist that any generated columns in the parent be generated the same way in the child (Peter Eisentraut) Forbid marking an identity column as nullable (Vik Fearing) GENERATED ... AS IDENTITY implies NOT NULL, so don't allow it to be combined with an explicit NULL specification. Allow ALTER ROLE/DATABASE ... SET to set the role, session_authorization, and temp_buffers parameters (Tom Lane) Previously, over-eager validity checks might reject these commands, even if the values would have worked when used later. This created a command ordering hazard for dump/reload and upgrade scenarios. Ensure that REINDEX CONCURRENTLY preserves any statistics target that's been set for the index (Michael Paquier) Fix COMMIT AND CHAIN to work correctly when the current transaction has live savepoints (Fujii Masao) Fix list-manipulation bug in WITH RECURSIVE processing (Michael Paquier, Tom Lane) Sufficiently deep nesting of WITH constructs (at least seven levels) triggered core dumps or incorrect complaints of faulty WITH nesting. Fix bug with coercing the result of a COLLATE expression to a non-collatable type (Tom Lane) This led to a parse tree in which the COLLATE appears to be applied to a non-collatable value. While that normally has no real impact (since COLLATE has no effect at runtime), it was possible to construct views that would be rejected during dump/reload. Fix use-after-free bug in saving tuples for AFTER triggers (Amit Langote) This could cause crashes in some situations. Disallow calling window functions and procedures via the fast path wire protocol message (Tom Lane) Only plain functions are supported here. While trying to call an aggregate function failed already, calling a window function would crash, and calling a procedure would work only if the procedure did no transaction control. Extend pg_identify_object_as_address() to support event triggers (Joel Jacobson) Fix to_char()'s handling of Roman-numeral month format codes with negative intervals (Julien Rouhaud) Previously, such cases would usually cause a crash. Check that the argument of pg_import_system_collations() is a valid schema OID (Tom Lane) Fix use of uninitialized value while parsing an \{m,n\} quantifier in a BRE-mode regular expression (Tom Lane) This error could cause the quantifier to act non-greedy, that is behave like an {m,n}? quantifier would do in full regular expressions. Fix could not find pathkey item to sort planner errors in some situations where the sort key involves an aggregate or window function (James Coleman, Tom Lane) Don't ignore system columns when estimating the number of groups using extended statistics (Tomas Vondra) This led to strange estimates for queries such as SELECT ... GROUP BY a, b, ctid. Avoid divide-by-zero when estimating selectivity of a regular expression with a very long fixed prefix (Tom Lane) This typically led to a NaN selectivity value, causing assertion failures or strange planner behavior. Fix access-off-the-end-of-the-table error in BRIN index bitmap scans (Tomas Vondra) If the page range size used by a BRIN index isn't a power of two, there were corner cases in which a bitmap scan could try to fetch pages past the actual end of the table, leading to could not open file errors. Fix potentially wrong answers from GIN tsvector index searches, when there are many matching tuples (Tom Lane) If the number of index matches became large enough to make the bitmap holding them become lossy (a threshold that depends on work_mem), the code could get confused about whether rechecks are required, allowing rows to be returned that don't actually match the query. Fix concurrency issues with WAL segment recycling on Windows (Michael Paquier) This reverts a change that caused intermittent could not rename file ...: Permission denied log messages. While there were not serious consequences, the log spam was annoying. Avoid incorrect timeline change while recovering uncommitted two-phase transactions from WAL (Soumyadeep Chakraborty, Jimmy Yih, Kevin Yeap) This error could lead to subsequent WAL records being written under the wrong timeline ID, leading to consistency problems, or even complete failure to be able to restart the server, later on. Ensure that locks are released while shutting down a standby server's startup process (Fujii Masao) When a standby server is shut down while still in recovery, some locks might be left held. This causes assertion failures in debug builds; it's unclear whether any serious consequence could occur in production builds. Fix crash when a logical replication worker does ALTER SUBSCRIPTION REFRESH (Peter Smith) The core code won't do this, but a replica trigger could. Ensure we default to wal_sync_method = fdatasync on recent FreeBSD (Thomas Munro) FreeBSD 13 supports open_datasync, which would normally become the default choice. However, it's unclear whether that is actually an improvement for Postgres, so preserve the existing default for now. Disable the vacuum_cleanup_index_scale_factor parameter and storage option (Peter Geoghegan) The notion of tracking stale index statistics proved to interact badly with the autovacuum_vacuum_insert_threshold parameter, resulting in unnecessary full-index scans and consequent degradation of autovacuum performance. The latter mechanism seems superior, so remove the stale-statistics logic. The control parameter for that, vacuum_cleanup_index_scale_factor, will be removed entirely in v14. In v13, it remains present to avoid breaking existing configuration files, but it no longer does anything. Pass the correct trigger OID to object post-alter hooks during ALTER CONSTRAINT (Álvaro Herrera) When updating trigger properties during ALTER CONSTRAINT, the post-alter hook was told that we are updating a trigger, but the constraint's OID was passed instead of the trigger's. Ensure we finish cleaning up when interrupted while detaching a DSM segment (Thomas Munro) This error could result in temporary files not being cleaned up promptly after a parallel query. Fix assorted minor memory leaks in the server (Tom Lane, Andres Freund) Fix uninitialized variable in walreceiver's statistics in shared memory (Fujii Masao) This error was harmless on most platforms, but could cause issues on platforms lacking atomic variables and/or spinlock support. Reduce the overhead of dtrace probes for LWLock operations, when dtrace support is compiled in but not active (Peter Eisentraut) Fix failure when a PL/pgSQL DO block makes use of both composite-type variables and transaction control (Tom Lane) Previously, such cases led to errors about leaked tuple descriptors. Prevent infinite loop in libpq if a ParameterDescription message with a corrupt length is received (Tom Lane) When initdb prints instructions about how to start the server, make the path shown for pg_ctl use backslash separators on Windows (Nitin Jadhav) Fix psql to restore the previous behavior of \connect service=something (Tom Lane) A previous bug fix caused environment variables (such as PGPORT) to override entries in the service file in this context. Restore the previous behavior, in which the priority is the other way around. Fix psql's ON_ERROR_ROLLBACK feature to handle COMMIT AND CHAIN commands correctly (Arthur Nascimento) Previously, this case failed with savepoint "pg_psql_temporary_savepoint" does not exist. In psql, avoid repeated could not print result table failures after the first such error (Álvaro Herrera) Fix race condition in detection of file modification by psql's \e and related commands (Laurenz Albe) A very fast typist could fool the code's file-timestamp-based detection of whether the temporary edit file was changed. Fix pg_dump's dumping of generated columns in partitioned tables (Peter Eisentraut) A fix introduced in the previous minor release should not be applied to partitioned tables, only traditionally-inherited tables. Fix missed file version check in pg_restore (Tom Lane) When reading a custom-format archive from a non-seekable source, pg_restore neglected to check the archive version. If it was fed a newer archive version than it can support, it would fail messily later on. Add some more checks to pg_upgrade for user tables containing non-upgradable data types (Tom Lane) Fix detection of some cases where a non-upgradable data type is embedded within a container type (such as an array or range). Also disallow upgrading when user tables contain columns of system-defined composite types, since those types' OIDs are not stable across versions. Fix incorrect progress-reporting calculation in pg_checksums (Shinya Kato) Fix pg_waldump to count XACT records correctly when generating per-record statistics (Kyotaro Horiguchi) Fix contrib/amcheck to not complain about the tuple flags HEAP_XMAX_LOCK_ONLY and HEAP_KEYS_UPDATED both being set (Julien Rouhaud) This is a valid state after SELECT FOR UPDATE. Adjust VPATH build rules to support recent Oracle Developer Studio compiler versions (Noah Misch) Fix testing of PL/Python for Python 3 on Solaris (Noah Misch) 2021-02-11 This release contains a variety of fixes from 13.1. For information about new features in major release 13, see . A dump/restore is not required for those running 13.X. However, see the first changelog item below concerning a possible need to update stored views. Also see the third and fourth changelog items, which describe cases in which reindexing indexes after the upgrade may be advisable. Fix failure to check per-column SELECT privileges in some join queries (Tom Lane) In some cases involving joins, the parser failed to record all the columns read by a query in the column-usage bitmaps that are used for permissions checking. Although the executor would still insist on some sort of SELECT privilege to run the query, this meant that a user having SELECT privilege on only one column of a table could nonetheless read all its columns through a suitably crafted query. A stored view that is subject to this problem will have incomplete column-usage bitmaps, and thus permissions will still not be enforced properly on the view after updating. In installations that depend on column-level permissions for security, it is recommended to CREATE OR REPLACE all user-defined views to cause them to be re-parsed. The PostgreSQL Project thanks Sven Klemm for reporting this problem. (CVE-2021-20229) Fix information leakage in constraint-violation error messages (Heikki Linnakangas) If an UPDATE command attempts to move a row to a different partition but finds that it violates some constraint on the new partition, and the columns in that partition are in different physical positions than in the parent table, the error message could reveal the contents of columns that the user does not have SELECT privilege on. (CVE-2021-3393) Fix incorrect detection of concurrent page splits while inserting into a GiST index (Heikki Linnakangas) Concurrent insertions could lead to a corrupt index with entries placed in the wrong pages. It's recommended to reindex any GiST index that's been subject to concurrent insertions. Fix CREATE INDEX CONCURRENTLY to wait for concurrent prepared transactions (Andrey Borodin) At the point where CREATE INDEX CONCURRENTLY waits for all concurrent transactions to complete so that it can see rows they inserted, it must also wait for all prepared transactions to complete, for the same reason. Its failure to do so meant that rows inserted by prepared transactions might be omitted from the new index, causing queries relying on the index to miss such rows. In installations that have enabled prepared transactions (max_prepared_transactions > 0), it's recommended to reindex any concurrently-built indexes in case this problem occurred when they were built. Avoid crash when trying to rescan an aggregation plan node that has both hashed and sorted grouping sets (Jeff Davis) Fix possible incorrect query results when a hash aggregation node spills some tuples to disk (Tom Lane) It was possible for aggregation grouping values to be replaced by nulls when the tuples are read back in, leading to wrong answers. Fix edge case in incremental sort (Neil Chen) If the last tuple of a sort batch chanced to be the first tuple of the next group of already-sorted tuples, the code did the wrong thing. This could lead to retrieved too many tuples in a bounded sort error messages, or to silently-wrong sorting results. Avoid crash when a CALL or DO statement that performs a transaction rollback is executed via extended query protocol (Thomas Munro, Tom Lane) In PostgreSQL 13, this case reliably caused a null-pointer dereference. In earlier versions the bug seems to have no visible symptoms, but it's not quite clear that it could never cause a problem. Avoid unnecessary errors with BEFORE UPDATE triggers on partitioned tables (Álvaro Herrera) A BEFORE UPDATE FOR EACH ROW trigger that modified the row in any way prevented UPDATE from moving the row to another partition when needed; but there is no longer any reason for this restriction. Fix partition pruning logic to handle asymmetric hash partition sets (Tom Lane) If a hash-partitioned table has unequally-sized partitions (that is, varying modulus values), or it lacks partitions for some remainder values, then the planner's pruning logic could mistakenly conclude that some partitions don't need to be scanned, leading to failure to find rows that the query should find. Avoid incorrect results when WHERE CURRENT OF is applied to a cursor whose plan contains a MergeAppend node (Tom Lane) This case is unsupported (in general, a cursor using ORDER BY is not guaranteed to be simply updatable); but the code previously did not reject it, and could silently give false matches. Fix crash when WHERE CURRENT OF is applied to a cursor whose plan contains a custom scan node (David Geier) Fix planner's mishandling of placeholders whose evaluation should be delayed by an outer join (Tom Lane) This occurs in particular with trivial subqueries containing lateral references to outer-join outputs. The mistake could result in a malformed plan. The known cases trigger a failed to assign all NestLoopParams to plan nodes error, but other symptoms may be possible. Fix planner's handling of placeholders during removal of useless RESULT RTEs (Tom Lane) This oversight could lead to no relation entry for relid N planner errors. Fix planner's handling of a placeholder that is computed at some join level and used only at that same level (Tom Lane) This oversight could lead to failed to build any N-way joins planner errors. Consider unsorted subpaths when planning a Gather Merge operation (James Coleman) It's possible to use such a path by adding an explicit Sort node, and in some cases that gives rise to a superior plan. Do not consider ORDER BY expressions involving parallel-restricted functions or set-returning functions when trying to parallelize sorts (James Coleman) Such cases cannot safely be pushed into worker processes, but the incremental sort feature accidentally made us consider them. Be more careful about whether index AMs support mark/restore (Andrew Gierth) This prevents errors about missing support functions in rare edge cases. Fix overestimate of the amount of shared memory needed for parallel queries (Takayuki Tsunakawa) Fix ALTER DEFAULT PRIVILEGES to handle duplicated arguments safely (Michael Paquier) Duplicate role or schema names within the same command could lead to tuple already updated by self errors or unique-constraint violations. Flush ACL-related caches when pg_authid changes (Noah Misch) This change ensures that permissions-related decisions will promptly reflect the results of ALTER ROLE ... [NO] INHERIT. Fix failure to detect snapshot too old conditions in tables rewritten in the current transaction (Kyotaro Horiguchi, Noah Misch) This is only a hazard when wal_level is set to minimal and the rewrite is performed by ALTER TABLE SET TABLESPACE. Fix spurious failure of CREATE PUBLICATION when applied to a table created or rewritten in the current transaction (Kyotaro Horiguchi) This is only a hazard when wal_level is set to minimal. Prevent misprocessing of ambiguous CREATE TABLE LIKE clauses (Tom Lane) A LIKE clause is re-examined after initial creation of the new table, to handle importation of indexes and such. It was possible for this re-examination to find a different table of the same name, causing unexpected behavior; one example is where the new table is a temporary table of the same name as the LIKE target. Rearrange order of operations in CREATE TABLE LIKE so that indexes are cloned before building foreign key constraints (Tom Lane) This fixes the case where a self-referential foreign key constraint declared in the outer CREATE TABLE depends on an index that's coming from the LIKE clause. Disallow CREATE STATISTICS on system catalogs (Tomas Vondra) Disallow converting an inheritance child table to a view (Tom Lane) Ensure that disk space allocated for a dropped relation is released promptly at commit (Thomas Munro) Previously, if the dropped relation spanned multiple 1GB segments, only the first segment was truncated immediately. Other segments were simply unlinked, which doesn't authorize the kernel to release the storage so long as any other backends still have the files open. Prevent dropping a tablespace that is referenced by a partitioned relation, but is not used for any actual storage (Álvaro Herrera) Previously this was allowed, but subsequent operations on the partitioned relation would fail. Fix progress reporting for CLUSTER (Matthias van de Meent) Fix handling of backslash-escaped multibyte characters in COPY FROM (Heikki Linnakangas) A backslash followed by a multibyte character was not handled correctly. In some client character encodings, this could lead to misinterpreting part of a multibyte character as a field separator or end-of-copy-data marker. Avoid preallocating executor hash tables in EXPLAIN without ANALYZE (Alexey Bashtanov) Fix recently-introduced race condition in LISTEN/NOTIFY queue handling (Tom Lane) A newly-listening backend could attempt to read SLRU pages that were in process of being truncated, possibly causing an error. Allow the jsonb concatenation operator to handle all combinations of JSON data types (Tom Lane) We can concatenate two JSON objects or two JSON arrays. Handle other cases by wrapping non-array inputs in one-element arrays, then performing an array concatenation. Previously, some combinations of inputs followed this rule but others arbitrarily threw an error. Fix use of uninitialized value while parsing a * quantifier in a BRE-mode regular expression (Tom Lane) This error could cause the quantifier to act non-greedy, that is behave like a *? quantifier would do in full regular expressions. Fix numeric power() for the case where the exponent is exactly INT_MIN (-2147483648) (Dean Rasheed) Previously, a result with no significant digits was produced. Fix integer-overflow cases in substring() functions (Tom Lane, Pavel Stehule) If the specified starting index and length overflow an integer when added together, substring() misbehaved, either throwing a bogus negative substring length error for a case that should succeed, or failing to complain that a negative length is negative (and instead returning the whole string, in most cases). Prevent possible data loss from incorrect detection of the wraparound point of an SLRU log (Noah Misch) The wraparound point typically falls in the middle of a page, which must be rounded off to a page boundary, and that was not done correctly. No issue could arise unless an installation had gotten to within one page of SLRU overflow, which is unlikely in a properly-functioning system. If this did happen, it would manifest in later apparent wraparound or could not access status of transaction errors. Fix WAL-reading logic to handle timeline switches correctly (Kyotaro Horiguchi, Fujii Masao) Previously, if WAL archiving is enabled, a standby could fail to follow a primary running on a newer timeline, with errors like requested WAL segment has already been removed. Fix memory leak in walsender processes while sending new snapshots for logical decoding (Amit Kapila) Fix relation cache leak in walsender processes while sending row changes via the root of a partitioned relation during logical replication (Amit Langote, Mark Zhao) Fix walsender to accept additional commands after terminating replication (Jeff Davis) Ensure detection of deadlocks between hot standby backends and the startup (WAL-application) process (Fujii Masao) The startup process did not run the deadlock detection code, so that in situations where the startup process is last to join a circular wait situation, the deadlock might never be recognized. Fix possible failure to detect recovery conflicts while deleting an index entry that references a HOT chain (Peter Geoghegan) The code failed to traverse the HOT chain and might thus compute a too-old XID horizon, which could lead to incorrect conflict processing in hot standby. The practical impact of this bug is limited; in most cases the correct XID horizon would be found anyway from nearby operations. Ensure that a nonempty value of krb_server_keyfile always overrides any setting of KRB5_KTNAME in the server's environment (Tom Lane) Previously, which setting took precedence depended on whether the client requests GSS encryption. In server log messages about failing to match connections to pg_hba.conf entries, include details about whether GSS encryption has been activated (Kyotaro Horiguchi, Tom Lane) This is relevant data if hostgssenc or hostnogssenc entries exist. Fix assorted issues in server's support for GSS encryption (Tom Lane) Remove pointless restriction that only GSS authentication can be used on a GSS-encrypted connection. Add GSS encryption information to connection-authorized log messages. Include GSS-related space when computing the required size of shared memory (this omission could have caused problems with very high max_connections settings). Avoid possible infinite recursion when reporting an unrecoverable GSS encryption error. Ensure that unserviced requests for background workers are cleaned up when the postmaster begins a smart or fast shutdown sequence (Tom Lane) Previously, there was a race condition whereby a child process that had requested a background worker just before shutdown could wait indefinitely, preventing shutdown from completing. Fix portability problem in parsing of recovery_target_xid values (Michael Paquier) The target XID is potentially 64 bits wide, but it was parsed with strtoul(), causing misbehavior on platforms where long is 32 bits (such as Windows). Avoid trying to use parallel index build in a standalone backend (Yulin Pei) Allow index AMs to support included columns without necessarily supporting multiple key columns (Tom Lane) While taking a base backup, avoid executing any SHA256 code if a backup manifest is not needed (Michael Paquier) When using OpenSSL operating in FIPS mode, SHA256 hashing is rejected, leading to an error. This change makes it possible to take a base backup on such a platform, so long as --no-manifest is specified. Avoid assertion failure during parallel aggregation of an aggregate with a non-strict deserialization function (Andrew Gierth) No such aggregate functions exist in core PostgreSQL, but some extensions such as PostGIS provide some. The mistake is harmless anyway in a non-assert build. Avoid assertion failure in pg_get_functiondef() when examining a function with a TRANSFORM option (Tom Lane) Fix data structure misallocation in PL/pgSQL's CALL statement (Tom Lane) A CALL in a PL/pgSQL procedure, to another procedure that has OUT parameters, would fail if the called procedure did a COMMIT or ROLLBACK. In libpq, do not skip trying SSL after GSS encryption (Tom Lane) If we successfully made a GSS-encrypted connection, but then failed during authentication, we would fall back to an unencrypted connection rather than next trying an SSL-encrypted connection. This could lead to unexpected connection failure, or to silently getting an unencrypted connection where an encrypted one is expected. Fortunately, GSS encryption could only succeed if both client and server hold valid tickets in the same Kerberos infrastructure. It seems unlikely for that to be true in an environment that requires SSL encryption instead. Make libpq's PQconndefaults() function report the correct default value for channel_binding (Daniele Varrazzo) In psql, re-allow including a password in a connection_string argument of a \connect command (Tom Lane) This used to work, but a recent bug fix caused the password to be ignored (resulting in prompting for a password). In psql's \d commands, don't truncate the display of column default values (Tom Lane) Formerly, they were arbitrarily truncated at 128 characters. Fix assorted bugs in psql's \help command (Kyotaro Horiguchi, Tom Lane) \help with two argument words failed to find a command description using only the first word, for example \help reset all should show the help for RESET but did not. Also, \help often failed to invoke the pager when it should. It also leaked memory. Fix pg_dump's dumping of inherited generated columns (Peter Eisentraut) The previous behavior resulted in (harmless) errors during restore. In pg_dump, ensure that the restore script runs ALTER PUBLICATION ADD TABLE commands as the owner of the publication, and similarly runs ALTER INDEX ATTACH PARTITION commands as the owner of the partitioned index (Tom Lane) Previously, these commands would be run by the role that started the restore script; which will usually work, but in corner cases that role might not have adequate permissions. Fix pg_dump to handle WITH GRANT OPTION in an extension's initial privileges (Noah Misch) If an extension's script creates an object and grants privileges on it with grant option, then later the user revokes such privileges, pg_dump would generate incorrect SQL for reproducing the situation. (Few if any extensions do this today.) In pg_rewind, ensure that all WAL is accounted for when rewinding a standby server (Ian Barwick, Heikki Linnakangas) In pgbench, disallow a digit as the first character of a variable name (Fabien Coelho) This prevents trying to substitute variables into timestamp literal values, which may contain strings like 12:34. Report the correct database name in connection failure error messages from some client programs (Álvaro Herrera) If the database name was defaulted rather than given on the command line, pg_dumpall, pgbench, oid2name, and vacuumlo would produce misleading error messages after a connection failure. Fix memory leak in contrib/auto_explain (Japin Li) Memory consumed while producing the EXPLAIN output was not freed until the end of the current transaction (for a top-level statement) or the end of the surrounding statement (for a nested statement). This was particularly a problem with log_nested_statements enabled. In contrib/postgres_fdw, avoid leaking open connections to remote servers when a user mapping or foreign server object is dropped (Bharath Rupireddy) Open connections that depend on a dropped user mapping or foreign server can no longer be referenced, but formerly they were kept around anyway for the duration of the local session. Fix faulty assertion in contrib/postgres_fdw (Etsuro Fujita) In contrib/pgcrypto, check for error returns from OpenSSL's EVP functions (Michael Paquier) We do not really expect errors here, but this change silences warnings from static analysis tools. Make contrib/pg_prewarm more robust when the cluster is shut down before prewarming is complete (Tom Lane) Previously, autoprewarm would rewrite its status file with only the block numbers that it had managed to load so far, thus perhaps largely disabling the prewarm functionality in the next startup. Instead, suppress status file updates until the initial loading pass is complete. In contrib/pg_trgm's GiST index support, avoid crash in the rare case that picksplit is called on exactly two index items (Andrew Gierth, Alexander Korotkov) Fix miscalculation of timeouts in contrib/pg_prewarm and contrib/postgres_fdw (Alexey Kondratov, Tom Lane) The main loop in contrib/pg_prewarm's autoprewarm parent process underestimated its desired sleep time by a factor of 1000, causing it to consume much more CPU than intended. When waiting for a result from a remote server, contrib/postgres_fdw overestimated the desired timeout by a factor of 1000 (though this error had been mitigated by imposing a clamp to 60 seconds). Both of these errors stemmed from incorrectly converting seconds-and-microseconds to milliseconds. Introduce a new API TimestampDifferenceMilliseconds() to make it easier to get this right in the future. Improve configure's heuristics for selecting PG_SYSROOT on macOS (Tom Lane) The new method is more likely to produce desirable results when Xcode is newer than the underlying operating system. Choosing a sysroot that does not match the OS version may result in nonfunctional executables. While building on macOS, specify -isysroot in link steps as well as compile steps (James Hilliard) This likewise improves the results when Xcode is out of sync with the operating system. Fix JIT compilation to be compatible with LLVM 11 and LLVM 12 (Andres Freund) Fix potential mishandling of references to boolean variables in JIT expression compilation (Andres Freund) No field reports attributable to this have been seen, but it seems likely that it could cause problems on some architectures. Fix compile failure with ICU 68 and later (Tom Lane) Avoid memcpy() with a NULL source pointer and zero count during partitioned index creation (Álvaro Herrera) While such a call is not known to cause problems in itself, some compilers assume that the arguments of memcpy() are never NULL, which could result in incorrect optimization of nearby code. Update time zone data files to tzdata release 2021a for DST law changes in Russia (Volgograd zone) and South Sudan, plus historical corrections for Australia, Bahamas, Belize, Bermuda, Ghana, Israel, Kenya, Nigeria, Palestine, Seychelles, and Vanuatu. Notably, the Australia/Currie zone has been corrected to the point where it is identical to Australia/Hobart. 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