2018-05-10 This release contains a variety of fixes from 10.3. For information about new features in major release 10, see . A dump/restore is not required for those running 10.X. However, if you use the adminpack extension, you should update it as per the first changelog entry below. Also, if the function marking mistakes mentioned in the second and third changelog entries below affect you, you will want to take steps to correct your database catalogs. Also, if you are upgrading from a version earlier than 10.3, see . Remove public execute privilege from contrib/adminpack's pg_logfile_rotate() function (Stephen Frost) pg_logfile_rotate() is a deprecated wrapper for the core function pg_rotate_logfile(). When that function was changed to rely on SQL privileges for access control rather than a hard-coded superuser check, pg_logfile_rotate() should have been updated as well, but the need for this was missed. Hence, if adminpack is installed, any user could request a logfile rotation, creating a minor security issue. After installing this update, administrators should update adminpack by performing ALTER EXTENSION adminpack UPDATE in each database in which adminpack is installed. (CVE-2018-1115) Fix incorrect volatility markings on a few built-in functions (Thomas Munro, Tom Lane) The functions query_to_xml, cursor_to_xml, cursor_to_xmlschema, query_to_xmlschema, and query_to_xml_and_xmlschema should be marked volatile because they execute user-supplied queries that might contain volatile operations. They were not, leading to a risk of incorrect query optimization. This has been repaired for new installations by correcting the initial catalog data, but existing installations will continue to contain the incorrect markings. Practical use of these functions seems to pose little hazard, but in case of trouble, it can be fixed by manually updating these functions' pg_proc entries, for example ALTER FUNCTION pg_catalog.query_to_xml(text, boolean, boolean, text) VOLATILE. (Note that that will need to be done in each database of the installation.) Another option is to pg_upgrade the database to a version containing the corrected initial data. Fix incorrect parallel-safety markings on a few built-in functions (Thomas Munro, Tom Lane) The functions brin_summarize_new_values, brin_summarize_range, brin_desummarize_range, gin_clean_pending_list, cursor_to_xml, cursor_to_xmlschema, ts_rewrite, ts_stat, binary_upgrade_create_empty_extension, and pg_import_system_collations should be marked parallel-unsafe; some because they perform database modifications directly, and others because they execute user-supplied queries that might do so. They were marked parallel-restricted instead, leading to a risk of unexpected query errors. This has been repaired for new installations by correcting the initial catalog data, but existing installations will continue to contain the incorrect markings. Practical use of these functions seems to pose little hazard unless force_parallel_mode is turned on. In case of trouble, it can be fixed by manually updating these functions' pg_proc entries, for example ALTER FUNCTION pg_catalog.brin_summarize_new_values(regclass) PARALLEL UNSAFE. (Note that that will need to be done in each database of the installation.) Another option is to pg_upgrade the database to a version containing the corrected initial data. Avoid re-using TOAST value OIDs that match dead-but-not-yet-vacuumed TOAST entries (Pavan Deolasee) Once the OID counter has wrapped around, it's possible to assign a TOAST value whose OID matches a previously deleted entry in the same TOAST table. If that entry were not yet vacuumed away, this resulted in unexpected chunk number 0 (expected 1) for toast value nnnnn errors, which would persist until the dead entry was removed by VACUUM. Fix by not selecting such OIDs when creating a new TOAST entry. Correctly enforce any CHECK constraints on individual partitions during COPY to a partitioned table (Etsuro Fujita) Previously, only constraints declared for the partitioned table as a whole were checked. Accept TRUE and FALSE as partition bound values (Amit Langote) Previously, only string-literal values were accepted for a boolean partitioning column. But then pg_dump would print such values as TRUE or FALSE, leading to dump/reload failures. Fix memory management for partition key comparison functions (Álvaro Herrera, Amit Langote) This error could lead to crashes when using user-defined operator classes for partition keys. Fix possible crash when a query inserts tuples in several partitions of a partitioned table, and those partitions don't have identical row types (Etsuro Fujita, Amit Langote) Change ANALYZE's algorithm for updating pg_class.reltuples (David Gould) Previously, pages not actually scanned by ANALYZE were assumed to retain their old tuple density. In a large table where ANALYZE samples only a small fraction of the pages, this meant that the overall tuple density estimate could not change very much, so that reltuples would change nearly proportionally to changes in the table's physical size (relpages) regardless of what was actually happening in the table. This has been observed to result in reltuples becoming so much larger than reality as to effectively shut off autovacuuming. To fix, assume that ANALYZE's sample is a statistically unbiased sample of the table (as it should be), and just extrapolate the density observed within those pages to the whole table. Include extended-statistics objects in the set of table properties duplicated by CREATE TABLE ... LIKE ... INCLUDING ALL (David Rowley) Also add an INCLUDING STATISTICS option, to allow finer-grained control over whether this happens. Fix CREATE TABLE ... LIKE with bigint identity columns (Peter Eisentraut) On platforms where long is 32 bits (which includes 64-bit Windows as well as most 32-bit machines), copied sequence parameters would be truncated to 32 bits. Avoid deadlocks in concurrent CREATE INDEX CONCURRENTLY commands that are run under SERIALIZABLE or REPEATABLE READ transaction isolation (Tom Lane) Fix possible slow execution of REFRESH MATERIALIZED VIEW CONCURRENTLY (Thomas Munro) Fix UPDATE/DELETE ... WHERE CURRENT OF to not fail when the referenced cursor uses an index-only-scan plan (Yugo Nagata, Tom Lane) Fix incorrect planning of join clauses pushed into parameterized paths (Andrew Gierth, Tom Lane) This error could result in misclassifying a condition as a join filter for an outer join when it should be a plain filter condition, leading to incorrect join output. Fix possibly incorrect generation of an index-only-scan plan when the same table column appears in multiple index columns, and only some of those index columns use operator classes that can return the column value (Kyotaro Horiguchi) Fix misoptimization of CHECK constraints having provably-NULL subclauses of top-level AND/OR conditions (Tom Lane, Dean Rasheed) This could, for example, allow constraint exclusion to exclude a child table that should not be excluded from a query. Prevent planner crash when a query has multiple GROUPING SETS, none of which can be implemented by sorting (Andrew Gierth) Fix executor crash due to double free in some GROUPING SETS usages (Peter Geoghegan) Fix misexecution of self-joins on transition tables (Thomas Munro) Avoid crash if a table rewrite event trigger is added concurrently with a command that could call such a trigger (Álvaro Herrera, Andrew Gierth, Tom Lane) Avoid failure if a query-cancel or session-termination interrupt occurs while committing a prepared transaction (Stas Kelvich) Fix query-lifespan memory leakage in repeatedly executed hash joins (Tom Lane) Fix possible leak or double free of visibility map buffer pins (Amit Kapila) Avoid spuriously marking pages as all-visible (Dan Wood, Pavan Deolasee, Álvaro Herrera) This could happen if some tuples were locked (but not deleted). While queries would still function correctly, vacuum would normally ignore such pages, with the long-term effect that the tuples were never frozen. In recent releases this would eventually result in errors such as found multixact nnnnn from before relminmxid nnnnn. Fix overly strict sanity check in heap_prepare_freeze_tuple (Álvaro Herrera) This could result in incorrect cannot freeze committed xmax failures in databases that have been pg_upgrade'd from 9.2 or earlier. Prevent dangling-pointer dereference when a C-coded before-update row trigger returns the old tuple (Rushabh Lathia) Reduce locking during autovacuum worker scheduling (Jeff Janes) The previous behavior caused drastic loss of potential worker concurrency in databases with many tables. Ensure client hostname is copied while copying pg_stat_activity data to local memory (Edmund Horner) Previously the supposedly-local snapshot contained a pointer into shared memory, allowing the client hostname column to change unexpectedly if any existing session disconnected. Handle pg_stat_activity information for auxiliary processes correctly (Edmund Horner) The application_name, client_hostname, and query fields might show incorrect data for such processes. Fix incorrect processing of multiple compound affixes in ispell dictionaries (Arthur Zakirov) Fix collation-aware searches (that is, indexscans using inequality operators) in SP-GiST indexes on text columns (Tom Lane) Such searches would return the wrong set of rows in most non-C locales. Prevent query-lifespan memory leakage with SP-GiST operator classes that use traversal values (Anton Dignös) Count the number of index tuples correctly during initial build of an SP-GiST index (Tomas Vondra) Previously, the tuple count was reported to be the same as that of the underlying table, which is wrong if the index is partial. Count the number of index tuples correctly during vacuuming of a GiST index (Andrey Borodin) Previously it reported the estimated number of heap tuples, which might be inaccurate, and is certainly wrong if the index is partial. Fix a corner case where a streaming standby gets stuck at a WAL continuation record (Kyotaro Horiguchi) In logical decoding, avoid possible double processing of WAL data when a walsender restarts (Craig Ringer) Fix logical replication to not assume that type OIDs match between the local and remote servers (Masahiko Sawada) Allow scalarltsel and scalargtsel to be used on non-core datatypes (Tomas Vondra) Reduce libpq's memory consumption when a server error is reported after a large amount of query output has been collected (Tom Lane) Discard the previous output before, not after, processing the error message. On some platforms, notably Linux, this can make a difference in the application's subsequent memory footprint. Fix double-free crashes in ecpg (Patrick Krecker, Jeevan Ladhe) Fix ecpg to handle long long int variables correctly in MSVC builds (Michael Meskes, Andrew Gierth) Fix mis-quoting of values for list-valued GUC variables in dumps (Michael Paquier, Tom Lane) The local_preload_libraries, session_preload_libraries, shared_preload_libraries, and temp_tablespaces variables were not correctly quoted in pg_dump output. This would cause problems if settings for these variables appeared in CREATE FUNCTION ... SET or ALTER DATABASE/ROLE ... SET clauses. Fix pg_recvlogical to not fail against pre-v10 PostgreSQL servers (Michael Paquier) A previous fix caused pg_recvlogical to issue a command regardless of server version, but it should only be issued to v10 and later servers. Ensure that pg_rewind deletes files on the target server if they are deleted from the source server during the run (Takayuki Tsunakawa) Failure to do this could result in data inconsistency on the target, particularly if the file in question is a WAL segment. Fix pg_rewind to handle tables in non-default tablespaces correctly (Takayuki Tsunakawa) Fix overflow handling in PL/pgSQL integer FOR loops (Tom Lane) The previous coding failed to detect overflow of the loop variable on some non-gcc compilers, leading to an infinite loop. Adjust PL/Python regression tests to pass under Python 3.7 (Peter Eisentraut) Support testing PL/Python and related modules when building with Python 3 and MSVC (Andrew Dunstan) Fix errors in initial build of contrib/bloom indexes (Tomas Vondra, Tom Lane) Fix possible omission of the table's last tuple from the index. Count the number of index tuples correctly, in case it is a partial index. Rename internal b64_encode and b64_decode functions to avoid conflict with Solaris 11.4 built-in functions (Rainer Orth) Sync our copy of the timezone library with IANA tzcode release 2018e (Tom Lane) This fixes the zic timezone data compiler to cope with negative daylight-savings offsets. While the PostgreSQL project will not immediately ship such timezone data, zic might be used with timezone data obtained directly from IANA, so it seems prudent to update zic now. Update time zone data files to tzdata release 2018d for DST law changes in Palestine and Antarctica (Casey Station), plus historical corrections for Portugal and its colonies, as well as Enderbury, Jamaica, Turks & Caicos Islands, and Uruguay. 2018-03-01 This release contains a variety of fixes from 10.2. For information about new features in major release 10, see . A dump/restore is not required for those running 10.X. However, if you run an installation in which not all users are mutually trusting, or if you maintain an application or extension that is intended for use in arbitrary situations, it is strongly recommended that you read the documentation changes described in the first changelog entry below, and take suitable steps to ensure that your installation or code is secure. Also, the changes described in the second changelog entry below may cause functions used in index expressions or materialized views to fail during auto-analyze, or when reloading from a dump. After upgrading, monitor the server logs for such problems, and fix affected functions. Also, if you are upgrading from a version earlier than 10.2, see . Document how to configure installations and applications to guard against search-path-dependent trojan-horse attacks from other users (Noah Misch) Using a search_path setting that includes any schemas writable by a hostile user enables that user to capture control of queries and then run arbitrary SQL code with the permissions of the attacked user. While it is possible to write queries that are proof against such hijacking, it is notationally tedious, and it's very easy to overlook holes. Therefore, we now recommend configurations in which no untrusted schemas appear in one's search path. Relevant documentation appears in (for database administrators and users), (for application authors), (for extension authors), and (for authors of SECURITY DEFINER functions). (CVE-2018-1058) Avoid use of insecure search_path settings in pg_dump and other client programs (Noah Misch, Tom Lane) pg_dump, pg_upgrade, vacuumdb and other PostgreSQL-provided applications were themselves vulnerable to the type of hijacking described in the previous changelog entry; since these applications are commonly run by superusers, they present particularly attractive targets. To make them secure whether or not the installation as a whole has been secured, modify them to include only the pg_catalog schema in their search_path settings. Autovacuum worker processes now do the same, as well. In cases where user-provided functions are indirectly executed by these programs — for example, user-provided functions in index expressions — the tighter search_path may result in errors, which will need to be corrected by adjusting those user-provided functions to not assume anything about what search path they are invoked under. That has always been good practice, but now it will be necessary for correct behavior. (CVE-2018-1058) Prevent logical replication from trying to ship changes for unpublishable relations (Peter Eisentraut) A publication marked FOR ALL TABLES would incorrectly ship changes in materialized views and information_schema tables, which are supposed to be omitted from the change stream. Fix misbehavior of concurrent-update rechecks with CTE references appearing in subplans (Tom Lane) If a CTE (WITH clause reference) is used in an InitPlan or SubPlan, and the query requires a recheck due to trying to update or lock a concurrently-updated row, incorrect results could be obtained. Fix planner failures with overlapping mergejoin clauses in an outer join (Tom Lane) These mistakes led to left and right pathkeys do not match in mergejoin or outer pathkeys do not match mergeclauses planner errors in corner cases. Repair pg_upgrade's failure to preserve relfrozenxid for materialized views (Tom Lane, Andres Freund) This oversight could lead to data corruption in materialized views after an upgrade, manifesting as could not access status of transaction or found xmin from before relfrozenxid errors. The problem would be more likely to occur in seldom-refreshed materialized views, or ones that were maintained only with REFRESH MATERIALIZED VIEW CONCURRENTLY. If such corruption is observed, it can be repaired by refreshing the materialized view (without CONCURRENTLY). Fix incorrect pg_dump output for some non-default sequence limit values (Alexey Bashtanov) Fix pg_dump's mishandling of STATISTICS objects (Tom Lane) An extended statistics object's schema was mislabeled in the dump's table of contents, possibly leading to the wrong results in a schema-selective restore. Its ownership was not correctly restored, either. Also, change the logic so that statistics objects are dumped/restored, or not, as independent objects rather than tying them to the dump/restore decision for the table they are on. The original definition could not scale to the planned future extension to cross-table statistics. Fix incorrect reporting of PL/Python function names in error CONTEXT stacks (Tom Lane) An error occurring within a nested PL/Python function call (that is, one reached via a SPI query from another PL/Python function) would result in a stack trace showing the inner function's name twice, rather than the expected results. Also, an error in a nested PL/Python DO block could result in a null pointer dereference crash on some platforms. Allow contrib/auto_explain's log_min_duration setting to range up to INT_MAX, or about 24 days instead of 35 minutes (Tom Lane) Mark assorted GUC variables as PGDLLIMPORT, to ease porting extension modules to Windows (Metin Doslu) 2018-02-08 This release contains a variety of fixes from 10.1. For information about new features in major release 10, see . A dump/restore is not required for those running 10.X. However, if you use contrib/cube's ~> operator, see the entry below about that. Also, if you are upgrading from a version earlier than 10.1, see . Fix processing of partition keys containing multiple expressions (Álvaro Herrera, David Rowley) This error led to crashes or, with carefully crafted input, disclosure of arbitrary backend memory. (CVE-2018-1052) Ensure that all temporary files made by pg_upgrade are non-world-readable (Tom Lane, Noah Misch) pg_upgrade normally restricts its temporary files to be readable and writable only by the calling user. But the temporary file containing pg_dumpall -g output would be group- or world-readable, or even writable, if the user's umask setting allows. In typical usage on multi-user machines, the umask and/or the working directory's permissions would be tight enough to prevent problems; but there may be people using pg_upgrade in scenarios where this oversight would permit disclosure of database passwords to unfriendly eyes. (CVE-2018-1053) Fix vacuuming of tuples that were updated while key-share locked (Andres Freund, Álvaro Herrera) In some cases VACUUM would fail to remove such tuples even though they are now dead, leading to assorted data corruption scenarios. Fix failure to mark a hash index's metapage dirty after adding a new overflow page, potentially leading to index corruption (Lixian Zou, Amit Kapila) Ensure that vacuum will always clean up the pending-insertions list of a GIN index (Masahiko Sawada) This is necessary to ensure that dead index entries get removed. The old code got it backwards, allowing vacuum to skip the cleanup if some other process were running cleanup concurrently, thus risking invalid entries being left behind in the index. Fix inadequate buffer locking in some LSN fetches (Jacob Champion, Asim Praveen, Ashwin Agrawal) These errors could result in misbehavior under concurrent load. The potential consequences have not been characterized fully. Fix incorrect query results from cases involving flattening of subqueries whose outputs are used in GROUPING SETS (Heikki Linnakangas) Fix handling of list partitioning constraints for partition keys of boolean or array types (Amit Langote) Avoid unnecessary failure in a query on an inheritance tree that occurs concurrently with some child table being removed from the tree by ALTER TABLE NO INHERIT (Tom Lane) Fix spurious deadlock failures when multiple sessions are running CREATE INDEX CONCURRENTLY (Jeff Janes) During VACUUM FULL, update the table's size fields in pg_class sooner (Amit Kapila) This prevents poor behavior when rebuilding hash indexes on the table, since those use the pg_class statistics to govern the initial hash size. Fix UNION/INTERSECT/EXCEPT over zero columns (Tom Lane) Disallow identity columns on typed tables and partitions (Michael Paquier) These cases will be treated as unsupported features for now. Fix assorted failures to apply the correct default value when inserting into an identity column (Michael Paquier, Peter Eisentraut) In several contexts, notably COPY and ALTER TABLE ADD COLUMN, the expected default value was not applied and instead a null value was inserted. Fix failures when an inheritance tree contains foreign child tables (Etsuro Fujita) A mix of regular and foreign tables in an inheritance tree resulted in creation of incorrect plans for UPDATE and DELETE queries. This led to visible failures in some cases, notably when there are row-level triggers on a foreign child table. Repair failure with correlated sub-SELECT inside VALUES inside a LATERAL subquery (Tom Lane) Fix could not devise a query plan for the given query planner failure for some cases involving nested UNION ALL inside a lateral subquery (Tom Lane) Allow functional dependency statistics to be used for boolean columns (Tom Lane) Previously, although extended statistics could be declared and collected on boolean columns, the planner failed to apply them. Avoid underestimating the number of groups emitted by subqueries containing set-returning functions in their grouping columns (Tom Lane) Cases similar to SELECT DISTINCT unnest(foo) got a lower output rowcount estimate in 10.0 than they did in earlier releases, possibly resulting in unfavorable plan choices. Restore the prior estimation behavior. Fix use of triggers in logical replication workers (Petr Jelinek) Fix logical decoding to correctly clean up disk files for crashed transactions (Atsushi Torikoshi) Logical decoding may spill WAL records to disk for transactions generating many WAL records. Normally these files are cleaned up after the transaction's commit or abort record arrives; but if no such record is ever seen, the removal code misbehaved. Fix walsender timeout failure and failure to respond to interrupts when processing a large transaction (Petr Jelinek) Fix race condition during replication origin drop that could allow the dropping process to wait indefinitely (Tom Lane) Allow members of the pg_read_all_stats role to see walsender statistics in the pg_stat_replication view (Feike Steenbergen) Show walsenders that are sending base backups as active in the pg_stat_activity view (Magnus Hagander) Fix reporting of scram-sha-256 authentication method in the pg_hba_file_rules view (Michael Paquier) Previously this was printed as scram-sha256, possibly confusing users as to the correct spelling. Fix has_sequence_privilege() to support WITH GRANT OPTION tests, as other privilege-testing functions do (Joe Conway) In databases using UTF8 encoding, ignore any XML declaration that asserts a different encoding (Pavel Stehule, Noah Misch) We always store XML strings in the database encoding, so allowing libxml to act on a declaration of another encoding gave wrong results. In encodings other than UTF8, we don't promise to support non-ASCII XML data anyway, so retain the previous behavior for bug compatibility. This change affects only xpath() and related functions; other XML code paths already acted this way. Provide for forward compatibility with future minor protocol versions (Robert Haas, Badrul Chowdhury) Up to now, PostgreSQL servers simply rejected requests to use protocol versions newer than 3.0, so that there was no functional difference between the major and minor parts of the protocol version number. Allow clients to request versions 3.x without failing, sending back a message showing that the server only understands 3.0. This makes no difference at the moment, but back-patching this change should allow speedier introduction of future minor protocol upgrades. Allow a client that supports SCRAM channel binding (such as v11 or later libpq) to connect to a v10 server (Michael Paquier) v10 does not have this feature, and the connection-time negotiation about whether to use it was done incorrectly. Avoid live-lock in ConditionVariableBroadcast() (Tom Lane, Thomas Munro) Given repeatedly-unlucky timing, a process attempting to awaken all waiters for a condition variable could loop indefinitely. Due to the limited usage of condition variables in v10, this affects only parallel index scans and some operations on replication slots. Clean up waits for condition variables correctly during subtransaction abort (Robert Haas) Ensure that child processes that are waiting for a condition variable will exit promptly if the postmaster process dies (Tom Lane) Fix crashes in parallel queries using more than one Gather node (Thomas Munro) Fix hang in parallel index scan when processing a deleted or half-dead index page (Amit Kapila) Avoid crash if parallel bitmap heap scan is unable to allocate a shared memory segment (Robert Haas) Cope with failure to start a parallel worker process (Amit Kapila, Robert Haas) Parallel query previously tended to hang indefinitely if a worker could not be started, as the result of fork() failure or other low-probability problems. Avoid unnecessary failure when no parallel workers can be obtained during parallel query startup (Robert Haas) Fix collection of EXPLAIN statistics from parallel workers (Amit Kapila, Thomas Munro) Ensure that query strings passed to parallel workers are correctly null-terminated (Thomas Munro) This prevents emitting garbage in postmaster log output from such workers. Avoid unsafe alignment assumptions when working with __int128 (Tom Lane) Typically, compilers assume that __int128 variables are aligned on 16-byte boundaries, but our memory allocation infrastructure isn't prepared to guarantee that, and increasing the setting of MAXALIGN seems infeasible for multiple reasons. Adjust the code to allow use of __int128 only when we can tell the compiler to assume lesser alignment. The only known symptom of this problem so far is crashes in some parallel aggregation queries. Prevent stack-overflow crashes when planning extremely deeply nested set operations (UNION/INTERSECT/EXCEPT) (Tom Lane) Avoid crash during an EvalPlanQual recheck of an indexscan that is the inner child of a merge join (Tom Lane) This could only happen during an update or SELECT FOR UPDATE of a join, when there is a concurrent update of some selected row. Fix crash in autovacuum when extended statistics are defined for a table but can't be computed (Álvaro Herrera) Fix null-pointer crashes for some types of LDAP URLs appearing in pg_hba.conf (Thomas Munro) Prevent out-of-memory failures due to excessive growth of simple hash tables (Tomas Vondra, Andres Freund) Fix sample INSTR() functions in the PL/pgSQL documentation (Yugo Nagata, Tom Lane) These functions are stated to be Oracle compatible, but they weren't exactly. In particular, there was a discrepancy in the interpretation of a negative third parameter: Oracle thinks that a negative value indicates the last place where the target substring can begin, whereas our functions took it as the last place where the target can end. Also, Oracle throws an error for a zero or negative fourth parameter, whereas our functions returned zero. The sample code has been adjusted to match Oracle's behavior more precisely. Users who have copied this code into their applications may wish to update their copies. Fix pg_dump to make ACL (permissions), comment, and security label entries reliably identifiable in archive output formats (Tom Lane) The tag portion of an ACL archive entry was usually just the name of the associated object. Make it start with the object type instead, bringing ACLs into line with the convention already used for comment and security label archive entries. Also, fix the comment and security label entries for the whole database, if present, to make their tags start with DATABASE so that they also follow this convention. This prevents false matches in code that tries to identify large-object-related entries by seeing if the tag starts with LARGE OBJECT. That could have resulted in misclassifying entries as data rather than schema, with undesirable results in a schema-only or data-only dump. Note that this change has user-visible results in the output of pg_restore --list. Rename pg_rewind's copy_file_range function to avoid conflict with new Linux system call of that name (Andres Freund) This change prevents build failures with newer glibc versions. In ecpg, detect indicator arrays that do not have the correct length and report an error (David Rader) Change the behavior of contrib/cube's cube ~> int operator to make it compatible with KNN search (Alexander Korotkov) The meaning of the second argument (the dimension selector) has been changed to make it predictable which value is selected even when dealing with cubes of varying dimensionalities. This is an incompatible change, but since the point of the operator was to be used in KNN searches, it seems rather useless as-is. After installing this update, any expression indexes or materialized views using this operator will need to be reindexed/refreshed. Avoid triggering a libc assertion in contrib/hstore, due to use of memcpy() with equal source and destination pointers (Tomas Vondra) Fix incorrect display of tuples' null bitmaps in contrib/pageinspect (Maksim Milyutin) Fix incorrect output from contrib/pageinspect's hash_page_items() function (Masahiko Sawada) In contrib/postgres_fdw, avoid outer pathkeys do not match mergeclauses planner error when constructing a plan involving a remote join (Robert Haas) In contrib/postgres_fdw, avoid planner failure when there are duplicate GROUP BY entries (Jeevan Chalke) Provide modern examples of how to auto-start Postgres on macOS (Tom Lane) The scripts in contrib/start-scripts/osx use infrastructure that's been deprecated for over a decade, and which no longer works at all in macOS releases of the last couple of years. Add a new subdirectory contrib/start-scripts/macos containing scripts that use the newer launchd infrastructure. Fix incorrect selection of configuration-specific libraries for OpenSSL on Windows (Andrew Dunstan) Support linking to MinGW-built versions of libperl (Noah Misch) This allows building PL/Perl with some common Perl distributions for Windows. Fix MSVC build to test whether 32-bit libperl needs -D_USE_32BIT_TIME_T (Noah Misch) Available Perl distributions are inconsistent about what they expect, and lack any reliable means of reporting it, so resort to a build-time test on what the library being used actually does. On Windows, install the crash dump handler earlier in postmaster startup (Takayuki Tsunakawa) This may allow collection of a core dump for some early-startup failures that did not produce a dump before. On Windows, avoid encoding-conversion-related crashes when emitting messages very early in postmaster startup (Takayuki Tsunakawa) Use our existing Motorola 68K spinlock code on OpenBSD as well as NetBSD (David Carlier) Add support for spinlocks on Motorola 88K (David Carlier) Update time zone data files to tzdata release 2018c for DST law changes in Brazil, Sao Tome and Principe, plus historical corrections for Bolivia, Japan, and South Sudan. The US/Pacific-New zone has been removed (it was only an alias for America/Los_Angeles anyway). 2017-11-09 This release contains a variety of fixes from 10.0. For information about new features in major release 10, see . A dump/restore is not required for those running 10.X. However, if you use BRIN indexes, see the fourth changelog entry below. Ensure that INSERT ... ON CONFLICT DO UPDATE checks table permissions and RLS policies in all cases (Dean Rasheed) The update path of INSERT ... ON CONFLICT DO UPDATE requires SELECT permission on the columns of the arbiter index, but it failed to check for that in the case of an arbiter specified by constraint name. In addition, for a table with row level security enabled, it failed to check updated rows against the table's SELECT policies (regardless of how the arbiter index was specified). (CVE-2017-15099) Fix crash due to rowtype mismatch in json{b}_populate_recordset() (Michael Paquier, Tom Lane) These functions used the result rowtype specified in the FROM ... AS clause without checking that it matched the actual rowtype of the supplied tuple value. If it didn't, that would usually result in a crash, though disclosure of server memory contents seems possible as well. (CVE-2017-15098) Fix sample server-start scripts to become $PGUSER before opening $PGLOG (Noah Misch) Previously, the postmaster log file was opened while still running as root. The database owner could therefore mount an attack against another system user by making $PGLOG be a symbolic link to some other file, which would then become corrupted by appending log messages. By default, these scripts are not installed anywhere. Users who have made use of them will need to manually recopy them, or apply the same changes to their modified versions. If the existing $PGLOG file is root-owned, it will need to be removed or renamed out of the way before restarting the server with the corrected script. (CVE-2017-12172) Fix BRIN index summarization to handle concurrent table extension correctly (Álvaro Herrera) Previously, a race condition allowed some table rows to be omitted from the index. It may be necessary to reindex existing BRIN indexes to recover from past occurrences of this problem. Fix possible failures during concurrent updates of a BRIN index (Tom Lane) These race conditions could result in errors like invalid index offnum or inconsistent range map. Prevent logical replication from setting non-replicated columns to nulls when replicating an UPDATE (Petr Jelinek) Fix logical replication to fire BEFORE ROW DELETE triggers when expected (Masahiko Sawada) Previously, that failed to happen unless the table also had a BEFORE ROW UPDATE trigger. Fix crash when logical decoding is invoked from a SPI-using function, in particular any function written in a PL language (Tom Lane) Ignore CTEs when looking up the target table for INSERT/UPDATE/DELETE, and prevent matching schema-qualified target table names to trigger transition table names (Thomas Munro) This restores the pre-v10 behavior for CTEs attached to DML commands. Avoid evaluating an aggregate function's argument expression(s) at rows where its FILTER test fails (Tom Lane) This restores the pre-v10 (and SQL-standard) behavior. Fix incorrect query results when multiple GROUPING SETS columns contain the same simple variable (Tom Lane) Fix query-lifespan memory leakage while evaluating a set-returning function in a SELECT's target list (Tom Lane) Allow parallel execution of prepared statements with generic plans (Amit Kapila, Kuntal Ghosh) Fix incorrect parallelization decisions for nested queries (Amit Kapila, Kuntal Ghosh) Fix parallel query handling to not fail when a recently-used role is dropped (Amit Kapila) Fix crash in parallel execution of a bitmap scan having a BitmapAnd plan node below a BitmapOr node (Dilip Kumar) Fix json_build_array(), json_build_object(), and their jsonb equivalents to handle explicit VARIADIC arguments correctly (Michael Paquier) Fix autovacuum's work item logic to prevent possible crashes and silent loss of work items (Álvaro Herrera) Fix corner-case crashes when columns have been added to the end of a view (Tom Lane) Record proper dependencies when a view or rule contains FieldSelect or FieldStore expression nodes (Tom Lane) Lack of these dependencies could allow a column or data type DROP to go through when it ought to fail, thereby causing later uses of the view or rule to get errors. This patch does not do anything to protect existing views/rules, only ones created in the future. Correctly detect hashability of range data types (Tom Lane) The planner mistakenly assumed that any range type could be hashed for use in hash joins or hash aggregation, but actually it must check whether the range's subtype has hash support. This does not affect any of the built-in range types, since they're all hashable anyway. Correctly ignore RelabelType expression nodes when examining functional-dependency statistics (David Rowley) This allows, e.g., extended statistics on varchar columns to be used properly. Prevent sharing transition states between ordered-set aggregates (David Rowley) This causes a crash with the built-in ordered-set aggregates, and probably with user-written ones as well. v11 and later will include provisions for dealing with such cases safely, but in released branches, just disable the optimization. Prevent idle_in_transaction_session_timeout from being ignored when a statement_timeout occurred earlier (Lukas Fittl) Fix low-probability loss of NOTIFY messages due to XID wraparound (Marko Tiikkaja, Tom Lane) If a session executed no queries, but merely listened for notifications, for more than 2 billion transactions, it started to miss some notifications from concurrently-committing transactions. Reduce the frequency of data flush requests during bulk file copies to avoid performance problems on macOS, particularly with its new APFS file system (Tom Lane) Allow COPY's FREEZE option to work when the transaction isolation level is REPEATABLE READ or higher (Noah Misch) This case was unintentionally broken by a previous bug fix. Fix AggGetAggref() to return the correct Aggref nodes to aggregate final functions whose transition calculations have been merged (Tom Lane) Fix insufficient schema-qualification in some new queries in pg_dump and psql (Vitaly Burovoy, Tom Lane, Noah Misch) Avoid use of @> operator in psql's queries for \d (Tom Lane) This prevents problems when the parray_gin extension is installed, since that defines a conflicting operator. Fix pg_basebackup's matching of tablespace paths to canonicalize both paths before comparing (Michael Paquier) This is particularly helpful on Windows. Fix libpq to not require user's home directory to exist (Tom Lane) In v10, failure to find the home directory while trying to read ~/.pgpass was treated as a hard error, but it should just cause that file to not be found. Both v10 and previous release branches made the same mistake when reading ~/.pg_service.conf, though this was less obvious since that file is not sought unless a service name is specified. In ecpglib, correctly handle backslashes in string literals depending on whether standard_conforming_strings is set (Tsunakawa Takayuki) Make ecpglib's Informix-compatibility mode ignore fractional digits in integer input strings, as expected (Gao Zengqi, Michael Meskes) Fix missing temp-install prerequisites for check-like Make targets (Noah Misch) Some non-default test procedures that are meant to work like make check failed to ensure that the temporary installation was up to date. Update time zone data files to tzdata release 2017c for DST law changes in Fiji, Namibia, Northern Cyprus, Sudan, Tonga, and Turks & Caicos Islands, plus historical corrections for Alaska, Apia, Burma, Calcutta, Detroit, Ireland, Namibia, and Pago Pago. In the documentation, restore HTML anchors to being upper-case strings (Peter Eisentraut) Due to a toolchain change, the 10.0 user manual had lower-case strings for intrapage anchors, thus breaking some external links into our website documentation. Return to our previous convention of using upper-case strings. 2017-10-05 Major enhancements in PostgreSQL 10 include: Logical replication using publish/subscribe Declarative table partitioning Improved query parallelism Significant general performance improvements Stronger password authentication based on SCRAM-SHA-256 Improved monitoring and control The above items are explained in more detail in the sections below. A dump/restore using , or use of , is required for those wishing to migrate data from any previous release. Version 10 contains a number of changes that may affect compatibility with previous releases. Observe the following incompatibilities: Hash indexes must be rebuilt after pg_upgrade-ing from any previous major PostgreSQL version (Mithun Cy, Robert Haas, Amit Kapila) Major hash index improvements necessitated this requirement. pg_upgrade will create a script to assist with this. Rename write-ahead log directory pg_xlog to pg_wal, and rename transaction status directory pg_clog to pg_xact (Michael Paquier) Users have occasionally thought that these directories contained only inessential log files, and proceeded to remove write-ahead log files or transaction status files manually, causing irrecoverable data loss. These name changes are intended to discourage such errors in future. Rename SQL functions, tools, and options that reference xlog to wal (Robert Haas) For example, pg_switch_xlog() becomes pg_switch_wal(), pg_receivexlog becomes pg_receivewal, and --xlogdir becomes --waldir. This is for consistency with the change of the pg_xlog directory name; in general, the xlog terminology is no longer used in any user-facing places. Rename WAL-related functions and views to use lsn instead of location (David Rowley) There was previously an inconsistent mixture of the two terminologies. Change the implementation of set-returning functions appearing in a query's SELECT list (Andres Freund) Set-returning functions are now evaluated before evaluation of scalar expressions in the SELECT list, much as though they had been placed in a LATERAL FROM-clause item. This allows saner semantics for cases where multiple set-returning functions are present. If they return different numbers of rows, the shorter results are extended to match the longest result by adding nulls. Previously the results were cycled until they all terminated at the same time, producing a number of rows equal to the least common multiple of the functions' periods. In addition, set-returning functions are now disallowed within CASE and COALESCE constructs. For more information see . Use standard row constructor syntax in UPDATE ... SET (column_list) = row_constructor (Tom Lane) The row_constructor can now begin with the keyword ROW; previously that had to be omitted. If just one column name appears in the column_list, then the row_constructor now must use the ROW keyword, since otherwise it is not a valid row constructor but just a parenthesized expression. Also, an occurrence of table_name.* within the row_constructor is now expanded into multiple columns, as occurs in other uses of row_constructors. When ALTER TABLE ... ADD PRIMARY KEY marks columns NOT NULL, that change now propagates to inheritance child tables as well (Michael Paquier) Prevent statement-level triggers from firing more than once per statement (Tom Lane) Cases involving writable CTEs updating the same table updated by the containing statement, or by another writable CTE, fired BEFORE STATEMENT or AFTER STATEMENT triggers more than once. Also, if there were statement-level triggers on a table affected by a foreign key enforcement action (such as ON DELETE CASCADE), they could fire more than once per outer SQL statement. This is contrary to the SQL standard, so change it. Move sequences' metadata fields into a new pg_sequence system catalog (Peter Eisentraut) A sequence relation now stores only the fields that can be modified by nextval(), that is last_value, log_cnt, and is_called. Other sequence properties, such as the starting value and increment, are kept in a corresponding row of the pg_sequence catalog. ALTER SEQUENCE updates are now fully transactional, implying that the sequence is locked until commit. The nextval() and setval() functions remain nontransactional. The main incompatibility introduced by this change is that selecting from a sequence relation now returns only the three fields named above. To obtain the sequence's other properties, applications must look into pg_sequence. The new system view pg_sequences can also be used for this purpose; it provides column names that are more compatible with existing code. The output of psql's \d command for a sequence has been redesigned, too. Make stream the WAL needed to restore the backup by default (Magnus Hagander) This changes pg_basebackup's -X/--wal-method default to stream. An option value none has been added to reproduce the old behavior. The pg_basebackup option -x has been removed (instead, use -X fetch). Change how logical replication uses pg_hba.conf (Peter Eisentraut) In previous releases, a logical replication connection required the replication keyword in the database column. As of this release, logical replication matches a normal entry with a database name or keywords such as all. Physical replication continues to use the replication keyword. Since built-in logical replication is new in this release, this change only affects users of third-party logical replication plugins. Make all actions wait for completion by default (Peter Eisentraut) Previously some pg_ctl actions didn't wait for completion, and required the use of -w to do so. Change the default value of the server parameter from pg_log to log (Andreas Karlsson) Add configuration option to specify file name for custom OpenSSL DH parameters (Heikki Linnakangas) This replaces the hardcoded, undocumented file name dh1024.pem. Note that dh1024.pem is no longer examined by default; you must set this option if you want to use custom DH parameters. Increase the size of the default DH parameters used for OpenSSL ephemeral DH ciphers to 2048 bits (Heikki Linnakangas) The size of the compiled-in DH parameters has been increased from 1024 to 2048 bits, making DH key exchange more resistant to brute-force attacks. However, some old SSL implementations, notably some revisions of Java Runtime Environment version 6, will not accept DH parameters longer than 1024 bits, and hence will not be able to connect over SSL. If it's necessary to support such old clients, you can use custom 1024-bit DH parameters instead of the compiled-in defaults. See . Remove the ability to store unencrypted passwords on the server (Heikki Linnakangas) The server parameter no longer supports off or plain. The UNENCRYPTED option is no longer supported in CREATE/ALTER USER ... PASSWORD. Similarly, the --unencrypted option has been removed from createuser. Unencrypted passwords migrated from older versions will be stored encrypted in this release. The default setting for password_encryption is still md5. Add and server parameters to control parallel queries (Amit Kapila, Robert Haas) These replace min_parallel_relation_size, which was found to be too generic. Don't downcase unquoted text within and related server parameters (QL Zhuo) These settings are really lists of file names, but they were previously treated as lists of SQL identifiers, which have different parsing rules. Remove sql_inheritance server parameter (Robert Haas) Changing this setting from the default value caused queries referencing parent tables to not include child tables. The SQL standard requires them to be included, however, and this has been the default since PostgreSQL 7.1. Allow multi-dimensional arrays to be passed into PL/Python functions, and returned as nested Python lists (Alexey Grishchenko, Dave Cramer, Heikki Linnakangas) This feature requires a backwards-incompatible change to the handling of arrays of composite types in PL/Python. Previously, you could return an array of composite values by writing, e.g., [[col1, col2], [col1, col2]]; but now that is interpreted as a two-dimensional array. Composite types in arrays must now be written as Python tuples, not lists, to resolve the ambiguity; that is, write [(col1, col2), (col1, col2)] instead. Remove PL/Tcl's module auto-loading facility (Tom Lane) This functionality has been replaced by new server parameters and , which are easier to use and more similar to features available in other PLs. Remove pg_dump/pg_dumpall support for dumping from pre-8.0 servers (Tom Lane) Users needing to dump from pre-8.0 servers will need to use dump programs from PostgreSQL 9.6 or earlier. The resulting output should still load successfully into newer servers. Remove support for floating-point timestamps and intervals (Tom Lane) This removes configure's --disable-integer-datetimes option. Floating-point timestamps have few advantages and have not been the default since PostgreSQL 8.3. Remove server support for client/server protocol version 1.0 (Tom Lane) This protocol hasn't had client support since PostgreSQL 6.3. Remove contrib/tsearch2 module (Robert Haas) This module provided compatibility with the version of full text search that shipped in pre-8.3 PostgreSQL releases. Remove createlang and droplang command-line applications (Peter Eisentraut) These had been deprecated since PostgreSQL 9.1. Instead, use CREATE EXTENSION and DROP EXTENSION directly. Remove support for version-0 function calling conventions (Andres Freund) Extensions providing C-coded functions must now conform to version 1 calling conventions. Version 0 has been deprecated since 2001. Below you will find a detailed account of the changes between PostgreSQL 10 and the previous major release. Support parallel B-tree index scans (Rahila Syed, Amit Kapila, Robert Haas, Rafia Sabih) This change allows B-tree index pages to be searched by separate parallel workers. Support parallel bitmap heap scans (Dilip Kumar) This allows a single index scan to dispatch parallel workers to process different areas of the heap. Allow merge joins to be performed in parallel (Dilip Kumar) Allow non-correlated subqueries to be run in parallel (Amit Kapila) Improve ability of parallel workers to return pre-sorted data (Rushabh Lathia) Increase parallel query usage in procedural language functions (Robert Haas, Rafia Sabih) Add server parameter to limit the number of worker processes that can be used for query parallelism (Julien Rouhaud) This parameter can be set lower than to reserve worker processes for purposes other than parallel queries. Add write-ahead logging support to hash indexes (Amit Kapila) This makes hash indexes crash-safe and replicatable. The former warning message about their use is removed. Improve hash index performance (Amit Kapila, Mithun Cy, Ashutosh Sharma) Add SP-GiST index support for INET and CIDR data types (Emre Hasegeli) Add option to allow BRIN index summarization to happen more aggressively (Álvaro Herrera) A new CREATE INDEX option enables auto-summarization of the previous BRIN page range when a new page range is created. Add functions to remove and re-add BRIN summarization for BRIN index ranges (Álvaro Herrera) The new SQL function brin_summarize_range() updates BRIN index summarization for a specified range and brin_desummarize_range() removes it. This is helpful to update summarization of a range that is now smaller due to UPDATEs and DELETEs. Improve accuracy in determining if a BRIN index scan is beneficial (David Rowley, Emre Hasegeli) Allow faster GiST inserts and updates by reusing index space more efficiently (Andrey Borodin) Reduce page locking during vacuuming of GIN indexes (Andrey Borodin) Reduce locking required to change table parameters (Simon Riggs, Fabrízio Mello) For example, changing a table's setting can now be done with a more lightweight lock. Allow tuning of predicate lock promotion thresholds (Dagfinn Ilmari Mannsåker) Lock promotion can now be controlled through two new server parameters, and . Add multi-column optimizer statistics to compute the correlation ratio and number of distinct values (Tomas Vondra, David Rowley, Álvaro Herrera) New commands are CREATE STATISTICS, ALTER STATISTICS, and DROP STATISTICS. This feature is helpful in estimating query memory usage and when combining the statistics from individual columns. Improve performance of queries affected by row-level security restrictions (Tom Lane) The optimizer now has more knowledge about where it can place RLS filter conditions, allowing better plans to be generated while still enforcing the RLS conditions safely. Speed up aggregate functions that calculate a running sum using numeric-type arithmetic, including some variants of SUM(), AVG(), and STDDEV() (Heikki Linnakangas) Improve performance of character encoding conversions by using radix trees (Kyotaro Horiguchi, Heikki Linnakangas) Reduce expression evaluation overhead during query execution, as well as plan node calling overhead (Andres Freund) This is particularly helpful for queries that process many rows. Allow hashed aggregation to be used with grouping sets (Andrew Gierth) Use uniqueness guarantees to optimize certain join types (David Rowley) Improve sort performance of the macaddr data type (Brandur Leach) Reduce statistics tracking overhead in sessions that reference many thousands of relations (Aleksander Alekseev) Allow explicit control over EXPLAIN's display of planning and execution time (Ashutosh Bapat) By default planning and execution time are displayed by EXPLAIN ANALYZE and are not displayed in other cases. The new EXPLAIN option SUMMARY allows explicit control of this. Add default monitoring roles (Dave Page) New roles pg_monitor, pg_read_all_settings, pg_read_all_stats, and pg_stat_scan_tables allow simplified permission configuration. Properly update the statistics collector during REFRESH MATERIALIZED VIEW (Jim Mlodgenski) Change the default value of to include current timestamp (with milliseconds) and the process ID in each line of postmaster log output (Christoph Berg) The previous default was an empty prefix. Add functions to return the log and WAL directory contents (Dave Page) The new functions are pg_ls_logdir() and pg_ls_waldir() and can be executed by non-superusers with the proper permissions. Add function pg_current_logfile() to read logging collector's current stderr and csvlog output file names (Gilles Darold) Report the address and port number of each listening socket in the server log during postmaster startup (Tom Lane) Also, when logging failure to bind a listening socket, include the specific address we attempted to bind to. Reduce log chatter about the starting and stopping of launcher subprocesses (Tom Lane) These are now DEBUG1-level messages. Reduce message verbosity of lower-numbered debug levels controlled by (Robert Haas) This also changes the verbosity of debug levels. Add pg_stat_activity reporting of low-level wait states (Michael Paquier, Robert Haas, Rushabh Lathia) This change enables reporting of numerous low-level wait conditions, including latch waits, file reads/writes/fsyncs, client reads/writes, and synchronous replication. Show auxiliary processes, background workers, and walsender processes in pg_stat_activity (Kuntal Ghosh, Michael Paquier) This simplifies monitoring. A new column backend_type identifies the process type. Allow pg_stat_activity to show the SQL query being executed by parallel workers (Rafia Sabih) Rename pg_stat_activity.wait_event_type values LWLockTranche and LWLockNamed to LWLock (Robert Haas) This makes the output more consistent. Add SCRAM-SHA-256 support for password negotiation and storage (Michael Paquier, Heikki Linnakangas) This provides better security than the existing md5 negotiation and storage method. Change the server parameter from boolean to enum (Michael Paquier) This was necessary to support additional password hashing options. Add view pg_hba_file_rules to display the contents of pg_hba.conf (Haribabu Kommi) This shows the file contents, not the currently active settings. Support multiple RADIUS servers (Magnus Hagander) All the RADIUS related parameters are now plural and support a comma-separated list of servers. Allow SSL configuration to be updated during configuration reload (Andreas Karlsson, Tom Lane) This allows SSL to be reconfigured without a server restart, by using pg_ctl reload, SELECT pg_reload_conf(), or sending a SIGHUP signal. However, reloading the SSL configuration does not work if the server's SSL key requires a passphrase, as there is no way to re-prompt for the passphrase. The original configuration will apply for the life of the postmaster in that case. Make the maximum value of effectively unlimited (Jim Nasby) After creating or unlinking files, perform an fsync on their parent directory (Michael Paquier) This reduces the risk of data loss after a power failure. Prevent unnecessary checkpoints and WAL archiving on otherwise-idle systems (Michael Paquier) Add server parameter to add details to WAL that can be sanity-checked on the standby (Kuntal Ghosh, Robert Haas) Any sanity-check failure generates a fatal error on the standby. Increase the maximum configurable WAL segment size to one gigabyte (Beena Emerson) A larger WAL segment size allows for fewer invocations and fewer WAL files to manage. Add the ability to logically replicate tables to standby servers (Petr Jelinek) Logical replication allows more flexibility than physical replication does, including replication between different major versions of PostgreSQL and selective replication. Allow waiting for commit acknowledgement from standby servers irrespective of the order they appear in (Masahiko Sawada) Previously the server always waited for the active standbys that appeared first in synchronous_standby_names. The new synchronous_standby_names keyword ANY allows waiting for any number of standbys irrespective of their ordering. This is known as quorum commit. Reduce configuration changes necessary to perform streaming backup and replication (Magnus Hagander, Dang Minh Huong) Specifically, the defaults were changed for , , , and to make them suitable for these usages out-of-the-box. Enable replication from localhost connections by default in pg_hba.conf (Michael Paquier) Previously pg_hba.conf's replication connection lines were commented out by default. This is particularly useful for . Add columns to pg_stat_replication to report replication delay times (Thomas Munro) The new columns are write_lag, flush_lag, and replay_lag. Allow specification of the recovery stopping point by Log Sequence Number (LSN) in recovery.conf (Michael Paquier) Previously the stopping point could only be selected by timestamp or XID. Allow users to disable pg_stop_backup()'s waiting for all WAL to be archived (David Steele) An optional second argument to pg_stop_backup() controls that behavior. Allow creation of temporary replication slots (Petr Jelinek) Temporary slots are automatically removed on session exit or error. Improve performance of hot standby replay with better tracking of Access Exclusive locks (Simon Riggs, David Rowley) Speed up two-phase commit recovery performance (Stas Kelvich, Nikhil Sontakke, Michael Paquier) Add XMLTABLE function that converts XML-formatted data into a row set (Pavel Stehule, Álvaro Herrera) Fix regular expressions' character class handling for large character codes, particularly Unicode characters above U+7FF (Tom Lane) Previously, such characters were never recognized as belonging to locale-dependent character classes such as [[:alpha:]]. Add table partitioning syntax that automatically creates partition constraints and handles routing of tuple insertions and updates (Amit Langote) The syntax supports range and list partitioning. Add AFTER trigger transition tables to record changed rows (Kevin Grittner, Thomas Munro) Transition tables are accessible from triggers written in server-side languages. Allow restrictive row-level security policies (Stephen Frost) Previously all security policies were permissive, meaning that any matching policy allowed access. A restrictive policy must match for access to be granted. These policy types can be combined. When creating a foreign-key constraint, check for REFERENCES permission on only the referenced table (Tom Lane) Previously REFERENCES permission on the referencing table was also required. This appears to have stemmed from a misreading of the SQL standard. Since creating a foreign key (or any other type of) constraint requires ownership privilege on the constrained table, additionally requiring REFERENCES permission seems rather pointless. Allow default permissions on schemas (Matheus Oliveira) This is done using the ALTER DEFAULT PRIVILEGES command. Add CREATE SEQUENCE AS command to create a sequence matching an integer data type (Peter Eisentraut) This simplifies the creation of sequences matching the range of base columns. Allow COPY view FROM source on views with INSTEAD INSERT triggers (Haribabu Kommi) The triggers are fed the data rows read by COPY. Allow the specification of a function name without arguments in DDL commands, if it is unique (Peter Eisentraut) For example, allow DROP FUNCTION on a function name without arguments if there is only one function with that name. This behavior is required by the SQL standard. Allow multiple functions, operators, and aggregates to be dropped with a single DROP command (Peter Eisentraut) Support IF NOT EXISTS in CREATE SERVER, CREATE USER MAPPING, and CREATE COLLATION (Anastasia Lubennikova, Peter Eisentraut) Make VACUUM VERBOSE report the number of skipped frozen pages and oldest xmin (Masahiko Sawada, Simon Riggs) This information is also included in output. Improve speed of VACUUM's removal of trailing empty heap pages (Claudio Freire, Álvaro Herrera) Add full text search support for JSON and JSONB (Dmitry Dolgov) The functions ts_headline() and to_tsvector() can now be used on these data types. Add support for EUI-64 MAC addresses, as a new data type macaddr8 (Haribabu Kommi) This complements the existing support for EUI-48 MAC addresses (type macaddr). Add identity columns for assigning a numeric value to columns on insert (Peter Eisentraut) These are similar to SERIAL columns, but are SQL standard compliant. Allow ENUM values to be renamed (Dagfinn Ilmari Mannsåker) This uses the syntax ALTER TYPE ... RENAME VALUE. Properly treat array pseudotypes (anyarray) as arrays in to_json() and to_jsonb() (Andrew Dunstan) Previously columns declared as anyarray (particularly those in the pg_stats view) were converted to JSON strings rather than arrays. Add operators for multiplication and division of money values with int8 values (Peter Eisentraut) Previously such cases would result in converting the int8 values to float8 and then using the money-and-float8 operators. The new behavior avoids possible precision loss. But note that division of money by int8 now truncates the quotient, like other integer-division cases, while the previous behavior would have rounded. Check for overflow in the money type's input function (Peter Eisentraut) Add simplified regexp_match() function (Emre Hasegeli) This is similar to regexp_matches(), but it only returns results from the first match so it does not need to return a set, making it easier to use for simple cases. Add a version of jsonb's delete operator that takes an array of keys to delete (Magnus Hagander) Make json_populate_record() and related functions process JSON arrays and objects recursively (Nikita Glukhov) With this change, array-type fields in the destination SQL type are properly converted from JSON arrays, and composite-type fields are properly converted from JSON objects. Previously, such cases would fail because the text representation of the JSON value would be fed to array_in() or record_in(), and its syntax would not match what those input functions expect. Add function txid_current_if_assigned() to return the current transaction ID or NULL if no transaction ID has been assigned (Craig Ringer) This is different from txid_current(), which always returns a transaction ID, assigning one if necessary. Unlike that function, this function can be run on standby servers. Add function txid_status() to check if a transaction was committed (Craig Ringer) This is useful for checking after an abrupt disconnection whether your previous transaction committed and you just didn't receive the acknowledgement. Allow make_date() to interpret negative years as BC years (Álvaro Herrera) Make to_timestamp() and to_date() reject out-of-range input fields (Artur Zakirov) For example, previously to_date('2009-06-40','YYYY-MM-DD') was accepted and returned 2009-07-10. It will now generate an error. Allow PL/Python's cursor() and execute() functions to be called as methods of their plan-object arguments (Peter Eisentraut) This allows a more object-oriented programming style. Allow PL/pgSQL's GET DIAGNOSTICS statement to retrieve values into array elements (Tom Lane) Previously, a syntactic restriction prevented the target variable from being an array element. Allow PL/Tcl functions to return composite types and sets (Karl Lehenbauer) Add a subtransaction command to PL/Tcl (Victor Wagner) This allows PL/Tcl queries to fail without aborting the entire function. Add server parameters and , to allow initialization functions to be called on PL/Tcl startup (Tom Lane) Allow specification of multiple host names or addresses in libpq connection strings and URIs (Robert Haas, Heikki Linnakangas) libpq will connect to the first responsive server in the list. Allow libpq connection strings and URIs to request a read/write host, that is a master server rather than a standby server (Victor Wagner, Mithun Cy) This is useful when multiple host names are specified. It is controlled by libpq connection parameter target_session_attrs. Allow the password file name to be specified as a libpq connection parameter (Julian Markwort) Previously this could only be specified via an environment variable. Add function PQencryptPasswordConn() to allow creation of more types of encrypted passwords on the client side (Michael Paquier, Heikki Linnakangas) Previously only MD5-encrypted passwords could be created using PQencryptPassword(). This new function can also create SCRAM-SHA-256-encrypted passwords. Change ecpg preprocessor version from 4.12 to 10 (Tom Lane) Henceforth the ecpg version will match the PostgreSQL distribution version number. Add conditional branch support to psql (Corey Huinker) This feature adds psql meta-commands \if, \elif, \else, and \endif. This is primarily helpful for scripting. Add psql \gx meta-command to execute (\g) a query in expanded mode (\x) (Christoph Berg) Expand psql variable references in backtick-executed strings (Tom Lane) This is particularly useful in the new psql conditional branch commands. Prevent psql's special variables from being set to invalid values (Daniel Vérité, Tom Lane) Previously, setting one of psql's special variables to an invalid value silently resulted in the default behavior. \set on a special variable now fails if the proposed new value is invalid. As a special exception, \set with an empty or omitted new value, on a boolean-valued special variable, still has the effect of setting the variable to on; but now it actually acquires that value rather than an empty string. \unset on a special variable now explicitly sets the variable to its default value, which is also the value it acquires at startup. In sum, a control variable now always has a displayable value that reflects what psql is actually doing. Add variables showing server version and psql version (Fabien Coelho) Improve psql's \d (display relation) and \dD (display domain) commands to show collation, nullable, and default properties in separate columns (Peter Eisentraut) Previously they were shown in a single Modifiers column. Make the various \d commands handle no-matching-object cases more consistently (Daniel Gustafsson) They now all print the message about that to stderr, not stdout, and the message wording is more consistent. Improve psql's tab completion (Jeff Janes, Ian Barwick, Andreas Karlsson, Sehrope Sarkuni, Thomas Munro, Kevin Grittner, Dagfinn Ilmari Mannsåker) Add pgbench option --log-prefix to control the log file prefix (Masahiko Sawada) Allow pgbench's meta-commands to span multiple lines (Fabien Coelho) A meta-command can now be continued onto the next line by writing backslash-return. Remove restriction on placement of -M option relative to other command line options (Tom Lane) Add pg_receivewal option -Z/--compress to specify compression (Michael Paquier) Add pg_recvlogical option --endpos to specify the ending position (Craig Ringer) This complements the existing --startpos option. Rename initdb options --noclean and --nosync to be spelled --no-clean and --no-sync (Vik Fearing, Peter Eisentraut) The old spellings are still supported. Allow pg_restore to exclude schemas (Michael Banck) This adds a new -N/--exclude-schema option. Add --no-blobs option to pg_dump (Guillaume Lelarge) This suppresses dumping of large objects. Add pg_dumpall option --no-role-passwords to omit role passwords (Robins Tharakan, Simon Riggs) This allows use of pg_dumpall by non-superusers; without this option, it fails due to inability to read passwords. Support using synchronized snapshots when dumping from a standby server (Petr Jelinek) Issue fsync() on the output files generated by pg_dump and pg_dumpall (Michael Paquier) This provides more security that the output is safely stored on disk before the program exits. This can be disabled with the new --no-sync option. Allow pg_basebackup to stream write-ahead log in tar mode (Magnus Hagander) The WAL will be stored in a separate tar file from the base backup. Make pg_basebackup use temporary replication slots (Magnus Hagander) Temporary replication slots will be used by default when pg_basebackup uses WAL streaming with default options. Be more careful about fsync'ing in all required places in pg_basebackup and pg_receivewal (Michael Paquier) Add pg_basebackup option --no-sync to disable fsync (Michael Paquier) Improve pg_basebackup's handling of which directories to skip (David Steele) Add wait option for 's promote operation (Peter Eisentraut) Add long options for pg_ctl wait (--wait) and no-wait (--no-wait) (Vik Fearing) Add long option for pg_ctl server options (--options) (Peter Eisentraut) Make pg_ctl start --wait detect server-ready by watching postmaster.pid, not by attempting connections (Tom Lane) The postmaster has been changed to report its ready-for-connections status in postmaster.pid, and pg_ctl now examines that file to detect whether startup is complete. This is more efficient and reliable than the old method, and it eliminates postmaster log entries about rejected connection attempts during startup. Reduce pg_ctl's reaction time when waiting for postmaster start/stop (Tom Lane) pg_ctl now probes ten times per second when waiting for a postmaster state change, rather than once per second. Ensure that pg_ctl exits with nonzero status if an operation being waited for does not complete within the timeout (Peter Eisentraut) The start and promote operations now return exit status 1, not 0, in such cases. The stop operation has always done that. Change to two-part release version numbering (Peter Eisentraut, Tom Lane) Release numbers will now have two parts (e.g., 10.1) rather than three (e.g., 9.6.3). Major versions will now increase just the first number, and minor releases will increase just the second number. Release branches will be referred to by single numbers (e.g., 10 rather than 9.6). This change is intended to reduce user confusion about what is a major or minor release of PostgreSQL. Improve behavior of pgindent (Piotr Stefaniak, Tom Lane) We have switched to a new version of pg_bsd_indent based on recent improvements made by the FreeBSD project. This fixes numerous small bugs that led to odd C code formatting decisions. Most notably, lines within parentheses (such as in a multi-line function call) are now uniformly indented to match the opening paren, even if that would result in code extending past the right margin. Allow the ICU library to optionally be used for collation support (Peter Eisentraut) The ICU library has versioning that allows detection of collation changes between versions. It is enabled via configure option --with-icu. The default still uses the operating system's native collation library. Automatically mark all PG_FUNCTION_INFO_V1 functions as DLLEXPORT-ed on Windows (Laurenz Albe) If third-party code is using extern function declarations, they should also add DLLEXPORT markers to those declarations. Remove SPI functions SPI_push(), SPI_pop(), SPI_push_conditional(), SPI_pop_conditional(), and SPI_restore_connection() as unnecessary (Tom Lane) Their functionality now happens automatically. There are now no-op macros by these names so that external modules don't need to be updated immediately, but eventually such calls should be removed. A side effect of this change is that SPI_palloc() and allied functions now require an active SPI connection; they do not degenerate to simple palloc() if there is none. That previous behavior was not very useful and posed risks of unexpected memory leaks. Allow shared memory to be dynamically allocated (Thomas Munro, Robert Haas) Add slab-like memory allocator for efficient fixed-size allocations (Tomas Vondra) Use POSIX semaphores rather than SysV semaphores on Linux and FreeBSD (Tom Lane) This avoids platform-specific limits on SysV semaphore usage. Improve support for 64-bit atomics (Andres Freund) Enable 64-bit atomic operations on ARM64 (Roman Shaposhnik) Switch to using clock_gettime(), if available, for duration measurements (Tom Lane) gettimeofday() is still used if clock_gettime() is not available. Add more robust random number generators to be used for cryptographically secure uses (Magnus Hagander, Michael Paquier, Heikki Linnakangas) If no strong random number generator can be found, configure will fail unless the --disable-strong-random option is used. However, with this option, pgcrypto functions requiring a strong random number generator will be disabled. Allow WaitLatchOrSocket() to wait for socket connection on Windows (Andres Freund) tupconvert.c functions no longer convert tuples just to embed a different composite-type OID in them (Ashutosh Bapat, Tom Lane) The majority of callers don't care about the composite-type OID; but if the result tuple is to be used as a composite Datum, steps should be taken to make sure the correct OID is inserted in it. Remove SCO and Unixware ports (Tom Lane) Overhaul documentation build process (Alexander Lakhin) Use XSLT to build the PostgreSQL documentation (Peter Eisentraut) Previously Jade, DSSSL, and JadeTex were used. Build HTML documentation using XSLT stylesheets by default (Peter Eisentraut) Allow file_fdw to read from program output as well as files (Corey Huinker, Adam Gomaa) In postgres_fdw, push aggregate functions to the remote server, when possible (Jeevan Chalke, Ashutosh Bapat) This reduces the amount of data that must be passed from the remote server, and offloads aggregate computation from the requesting server. In postgres_fdw, push joins to the remote server in more cases (David Rowley, Ashutosh Bapat, Etsuro Fujita) Properly support OID columns in postgres_fdw tables (Etsuro Fujita) Previously OID columns always returned zeros. Allow btree_gist and btree_gin to index enum types (Andrew Dunstan) This allows enums to be used in exclusion constraints. Add indexing support to btree_gist for the UUID data type (Paul Jungwirth) Add amcheck which can check the validity of B-tree indexes (Peter Geoghegan) Show ignored constants as $N rather than ? in pg_stat_statements (Lukas Fittl) Improve cube's handling of zero-dimensional cubes (Tom Lane) This also improves handling of infinite and NaN values. Allow pg_buffercache to run with fewer locks (Ivan Kartyshov) This makes it less disruptive when run on production systems. Add pgstattuple function pgstathashindex() to view hash index statistics (Ashutosh Sharma) Use GRANT permissions to control pgstattuple function usage (Stephen Frost) This allows DBAs to allow non-superusers to run these functions. Reduce locking when pgstattuple examines hash indexes (Amit Kapila) Add pageinspect function page_checksum() to show a page's checksum (Tomas Vondra) Add pageinspect function bt_page_items() to print page items from a page image (Tomas Vondra) Add hash index support to pageinspect (Jesper Pedersen, Ashutosh Sharma) The following individuals (in alphabetical order) have contributed to this release as patch authors, committers, reviewers, testers, or reporters of issues. Adam Brightwell Adam Brusselback Adam Gomaa Adam Sah Adrian Klaver Aidan Van Dyk Aleksander Alekseev Alexander Korotkov Alexander Lakhin Alexander Sosna Alexey Bashtanov Alexey Grishchenko Alexey Isayko Álvaro Hernández Tortosa Álvaro Herrera Amit Kapila Amit Khandekar Amit Langote Amul Sul Anastasia Lubennikova Andreas Joseph Krogh Andreas Karlsson Andreas Scherbaum Andreas Seltenreich Andres Freund Andrew Dunstan Andrew Gierth Andrew Wheelwright Andrey Borodin Andrey Lizenko Andy Abelisto Antonin Houska Ants Aasma Arjen Nienhuis Arseny Sher Artur Zakirov Ashutosh Bapat Ashutosh Sharma Ashwin Agrawal Atsushi Torikoshi Ayumi Ishii Basil Bourque Beena Emerson Ben de Graaff Benedikt Grundmann Bernd Helmle Brad DeJong Brandur Leach Breen Hagan Bruce Momjian Bruno Wolff III Catalin Iacob Chapman Flack Chen Huajun Choi Doo-Won Chris Bandy Chris Richards Chris Ruprecht Christian Ullrich Christoph Berg Chuanting Wang Claudio Freire Clinton Adams Const Zhang Constantin Pan Corey Huinker Craig Ringer Cynthia Shang Dagfinn Ilmari Mannsåker Daisuke Higuchi Damian Quiroga Dan Wood Dang Minh Huong Daniel Gustafsson Daniel Vérité Daniel Westermann Daniele Varrazzo Danylo Hlynskyi Darko Prelec Dave Cramer Dave Page David Christensen David Fetter David Johnston David Rader David Rowley David Steele Dean Rasheed Denis Smirnov Denish Patel Dennis Björklund Devrim Gündüz Dilip Kumar Dilyan Palauzov Dima Pavlov Dimitry Ivanov Dmitriy Sarafannikov Dmitry Dolgov Dmitry Fedin Don Morrison Egor Rogov Eiji Seki Emil Iggland Emre Hasegeli Enrique Meneses Erik Nordström Erik Rijkers Erwin Brandstetter Etsuro Fujita Eugen Konkov Eugene Kazakov Euler Taveira Fabien Coelho Fabrízio de Royes Mello Feike Steenbergen Felix Gerzaguet Filip Jirsák Fujii Masao Gabriele Bartolini Gabrielle Roth Gao Zengqi Gerdan Santos Gianni Ciolli Gilles Darold Giuseppe Broccolo Graham Dutton Greg Atkins Greg Burek Grigory Smolkin Guillaume Lelarge Hans Buschmann Haribabu Kommi Heikki Linnakangas Henry Boehlert Huan Ruan Ian Barwick Igor Korot Ildus Kurbangaliev Ivan Kartyshov Jaime Casanova Jakob Egger James Parks Jarred Ward Jason Li Jason O'Donnell Jason Petersen Jeevan Chalke Jeevan Ladhe Jeff Dafoe Jeff Davis Jeff Janes Jelte Fennema Jeremy Finzel Jeremy Schneider Jeroen van der Ham Jesper Pedersen Jim Mlodgenski Jim Nasby Jinyu Zhang Joe Conway Joel Jacobson John Harvey Jon Nelson Jordan Gigov Josh Berkus Josh Soref Julian Markwort Julien Rouhaud Junseok Yang Justin Muise Justin Pryzby Kacper Zuk KaiGai Kohei Karen Huddleston Karl Lehenbauer Karl O. Pinc Keith Fiske Kevin Grittner Kim Rose Carlsen Konstantin Evteev Konstantin Knizhnik Kuntal Ghosh Kurt Kartaltepe Kyle Conroy Kyotaro Horiguchi Laurenz Albe Leonardo Cecchi Ludovic Vaugeois-Pepin Lukas Fittl Magnus Hagander Maksim Milyutin Maksym Sobolyev Marc Rassbach Marc-Olaf Jaschke Marcos Castedo Marek Cvoren Mark Dilger Mark Kirkwood Mark Pether Marko Tiikkaja Markus Winand Marllius Ribeiro Marti Raudsepp Martín Marqués Masahiko Sawada Matheus Oliveira Mathieu Fenniak Merlin Moncure Michael Banck Michael Day Michael Meskes Michael Overmeyer Michael Paquier Mike Palmiotto Milos Urbanek Mithun Cy Moshe Jacobson Murtuza Zabuawala Naoki Okano Nathan Bossart Nathan Wagner Neha Khatri Neha Sharma Neil Anderson Nicolas Baccelli Nicolas Guini Nicolas Thauvin Nikhil Sontakke Nikita Glukhov Nikolaus Thiel Nikolay Nikitin Nikolay Shaplov Noah Misch Noriyoshi Shinoda Olaf Gawenda Oleg Bartunov Oskari Saarenmaa Otar Shavadze Paresh More Paul Jungwirth Paul Ramsey Pavan Deolasee Pavel Golub Pavel Hanák Pavel Raiskup Pavel Stehule Peng Sun Peter Eisentraut Peter Geoghegan Petr Jelínek Philippe Beaudoin Pierre-Emmanuel André Piotr Stefaniak Prabhat Sahu QL Zhuo Radek Slupik Rafa de la Torre Rafia Sabih Ragnar Ouchterlony Rahila Syed Rajkumar Raghuwanshi Regina Obe Richard Pistole Robert Haas Robins Tharakan Rod Taylor Roman Shaposhnik Rushabh Lathia Ryan Murphy Sandeep Thakkar Scott Milliken Sean Farrell Sebastian Luque Sehrope Sarkuni Sergey Burladyan Sergey Koposov Shay Rojansky Shinichi Matsuda Sho Kato Simon Riggs Simone Gotti Spencer Thomason Stas Kelvich Stepan Pesternikov Stephen Frost Steve Randall Steve Singer Steven Fackler Steven Winfield Suraj Kharage Sveinn Sveinsson Sven R. Kunze Tahir Fakhroutdinov Taiki Kondo Takayuki Tsunakawa Takeshi Ideriha Tatsuo Ishii Tatsuro Yamada Teodor Sigaev Thom Brown Thomas Kellerer Thomas Munro Tim Goodaire Tobias Bussmann Tom Dunstan Tom Lane Tom van Tilburg Tomas Vondra Tomonari Katsumata Tushar Ahuja Vaishnavi Prabakaran Venkata Balaji Nagothi Vicky Vergara Victor Wagner Vik Fearing Vinayak Pokale Viren Negi Vitaly Burovoy Vladimir Kunshchikov Vladimir Rusinov Yi Wen Wong Yugo Nagata Zhen Ming Yang Zhou Digoal