Migration to Version 11 v11

A dump/restore using pg_dumpall or pg_upgrade or logical replication is required for migrating data from any previous release. See Upgrading an Installation With pg_upgrade for information on migrating to new major releases.

Version 11 contains a number of changes that may affect compatibility with previous releases. Listed is the following incompatibilities:

• Make pg_dump dump the properties of a database, not just its contents.

  Previously, attributes of the database itself, such as database-level GRANT/REVOKE permissions and ALTER DATABASE SET variable settings, were only dumped by pg_dumpall. Now pg_dump --create and pg_restore --create will restore these database properties in addition to the objects within the database. pg_dumpall -g now only dumps role- and tablespace-related attributes. pg_dumpall's complete output (without -g) is unchanged.

  pg_dump and pg_restore, without --create, no longer dump/restore database-level comments and security labels; those are now treated as properties of the database.

  pg_dumpall's output script will now always create databases with their original locale and encoding, and hence will fail if the locale or encoding name is unknown to the destination system. Previously, CREATE DATABASE would be emitted without these specifications if the database locale and encoding matched the old cluster's defaults.

  pg_dumpall --clean now restores the original locale and encoding settings of the postgres and template1 databases, as well as those of user-created databases.

• Consider syntactic form when disambiguating function versus column references.

  When x is a table name or composite column, PostgreSQL has traditionally considered the syntactic forms f(x) and x.f to be equivalent, allowing tricks such as writing a function and then using it as though it were a computed-on- demand column. However, if both interpretations are feasible, the column interpretation was always chosen, leading to surprising results if the user intended the function interpretation. Now, if there is ambiguity, the interpretation that matches the syntactic form is chosen.

• Fully enforce uniqueness of table and domain constraint names.

  PostgreSQL expects the names of a table's constraints to be distinct, and likewise for the names of a domain's constraints. However, there was not rigid enforcement of this, and previously there were corner cases where duplicate names could be created.

• Make power(numeric, numeric) and power(float8, float8) handle NaN inputs according to the POSIX standard.

  POSIX says that NaN ^ 0 = 1 and 1 ^ NaN = 1, but all other cases with NaN input(s) should return NaN.power(numeric, numeric) just returned NaN in all such cases; now it honors the two exceptions. power(float8, float8) followed the standard if the C library does; but on some old Unix platforms the library doesn't, and there were also problems on some versions of Windows.

• Prevent to_number() from consuming characters when the template separator does not match.

  Specifically, SELECT to_number('1234', '9,999') used to return 134. It will now return 1234. L and TH now only consume characters that are not digits, positive/negative signs, decimal points, or commas.

• Fix to_date(), to_number(), and to_timestamp() to skip a character for each template character.

  Previously, they skipped one byte for each byte of template character, resulting in strange behavior if either string contained multibyte characters.

• Adjust the handling of backslashes inside double-quotes in template strings for to_char(), to_number(), and to_timestamp().

  Such a backslash now escapes the character after it, particularly a double-quote or another backslash.

• Correctly handle relative path expressions in xmltable(), xpath(), and other XML-handling functions.

  Per the SQL standard, relative paths start from the document node of the XML input document, not the root node as these functions previously did.

• In the extended query protocol, make statement_timeout apply to each Execute message separately, not to all commands before Sync.

• Remove the relhaspkey column from system catalog pg_class. Applications needing to check for a primary key should consult pg_index.

• Replace system catalog pg_proc's proisagg and proiswindow columns with prokind.

  This new column more clearly distinguishes functions, procedures, aggregates, and window functions.

• Correct information schema column tables.table_type to return FOREIGN instead of FOREIGN TABLE.

  This new output matches the SQL standard.

• Change the ps process display labels for background workers to match the pg_stat_activity.backend_type labels.

• Cause large object permission checks to happen during large object open, lo_open(), not when a read or write is attempted.

  If write access is requested and not available, an error will now be thrown even if the large object is never written to.

• Prevent non-superusers from reindexing shared catalogs.

  Previously, database owners were also allowed to do this, but now it is considered outside the bounds of their privileges.

• Remove deprecated adminpack functions pg_file_read(), pg_file_length(), and pg_logfile_rotate().

  Equivalent functionality is now present in the core backend. Existing adminpack installs will continue to have access to these functions until they are updated via ALTER EXTENSION ... UPDATE.

• Honor the capitalization of double-quoted command options.

  Previously, option names in certain SQL commands were forcibly lower-cased even if entered with double quotes; thus for example "FillFactor" would be accepted as an index storage option, though properly its name is lower-case. Such cases will now generate an error.

• Remove server parameter replacement_sort_tuples.

  Replacement sorts were determined to be no longer useful.

• Remove WITH clause in CREATE FUNCTION.

  PostgreSQL has long supported a more standard-compliant syntax for this capability.

• In PL/pgSQL trigger functions, the OLD and NEW variables now read as NULL when not assigned.

  Previously, references to these variables could be parsed but not executed.

• The SELECT DISTINCT...ORDER BY clause of the SELECT DISTINCT query behavior differs after upgrade.

  If SELECT DISTINCT is specified or if a SELECT statement includes the SELECT DISTINCT ...ORDER BY clause then all the expressions in ORDER BY must be present in the select list of the SELECT DISTINCT query (applicable when upgrading from version 9.6 to any higher version of Advanced Server).