Changes from PolarDB PostgreSQL 11 to 14

Overview

PolarDB for PostgreSQL is an enterprise-grade relational database management system with wide adoption and strong community support. Each new major version introduces numerous improvements and features to enhance performance, usability, and security. Upgrading to PolarDB for PostgreSQL 14 significantly improves database performance and user experience.

PolarDB for PostgreSQL 14 introduces new query optimization algorithms and storage engine enhancements that improve query speed and concurrent processing. This allows the database to handle large data volumes more quickly, increasing responsiveness and overall performance. This version also adds many new and enhanced features to provide a better user experience and more powerful developer tools. For example, it improves support for the JSON data type to simplify data processing and queries, provides richer monitoring and diagnostic tools to help you understand and optimize database performance, and strengthens security and reliability with a stricter access control policy and permission management features to protect your data from potential threats. It also includes improved backup and recovery, making data protection and restoration easier.

Upgrading to PolarDB for PostgreSQL 14 also means you stay current with the PostgreSQL community, which provides more resources and support to help you solve problems and learn database best practices. We recommend that you upgrade to PolarDB for PostgreSQL 14.

During an upgrade from PolarDB for PostgreSQL 11 to PolarDB for PostgreSQL 14, most data types, built-in functions, table columns, and objects do not change significantly, with most modifications being minor improvements. You can achieve nearly 100% compatibility by using methods like logical replication. We recommend using DTS for this purpose. The following sections detail the specific changes.

System tables and built-in columns

• Removes the special behavior of the oid column. In version 11, you could use WITH OIDS during table creation to specify a normally invisible oid column. Version 14 removes this capability. However, you can still explicitly declare a column with the oid data type. Operations on columns created with WITH OIDS must be adjusted. The hidden oid columns in system catalogs are now regular columns. As a result, SELECT * now outputs these columns, whereas in previous versions, they were displayed only when explicitly selected.

• Table columns of the name type are now marked with the C collation by default. Comparison operators for the name data type can now use any collation, not just the C collation. To maintain the previous query semantics, table columns of type name are now explicitly marked as having the C collation. A side effect of this change is that regular expression operators on name columns now use the C collation by default, instead of the database's collation, to determine the behavior of locale-dependent regular expression patterns like \w. If you want to use non-C behavior for a regular expression on a name column, append an explicit COLLATE clause. For user-defined name columns, another option is to specify a different collation during table creation, but this only shifts the backward incompatibility to the comparison operators.

• Treats object name columns in information_schema views as type name instead of varchar. According to the SQL standard, object name columns in information_schema views are declared with the domain type sql_identifier. In PostgreSQL, the underlying catalog columns are actually of type name. This change makes sql_identifier a domain over name instead of varchar. This eliminates semantic mismatches in comparison and sorting behavior and can greatly improve the performance of queries on information_schema views that filter on object name columns.

  Note

  For inequality restrictions, such as SELECT ... FROM information_schema.tables WHERE table_name < 'foo';, the query now uses the comparison semantics of the C locale by default, instead of the database's default collation. Sorting these columns will also follow the C collation. You can add a COLLATE "default" clause to force the previous behavior and its associated inefficiency.

• Removes the obsolete pg_constraint.consrc column. This column had long been deprecated because it was not updated to reflect other catalog changes, such as column renames. The recommended way to get the text of a check constraint expression from pg_constraint is pg_get_expr(conbin, conrelid). The pg_get_constraintdef() function is also a useful alternative.

• Removes the obsolete pg_attrdef.adsrc column. This column had long been deprecated because it was not updated to reflect other catalog changes, such as column renames. The recommended way to get the text of a default-value expression from pg_attrdef is pg_get_expr(adbin, adrelid).

Operators and regular expressions

• Removes the factorial operators ! and !!, and the numeric_fac( ) function. The factorial() function is still supported.

• Removes support for postfix (right-unary) operators. If you are dumping a postfix operator, pg_dump and pg_upgrade will issue a warning.

• Prevents the containment operators (<@ and @>) for intarray from using a GiST index. A heap scan is now faster than a full GiST index scan. You should drop any indexes created for this purpose.

• Refactors the code for geometric functions and operators. This may lead to more accurate but slightly different results compared to version 11.

  Note

  Cases involving NaN, underflow, overflow, and division by zero are now handled more consistently.

• Allows the \D and \W wildcards to match newlines in newline-sensitive regular expression modes. Previously, they did not match newlines in this mode, which was inconsistent with the behavior of other common regular expression engines. You can use [^[:digit:]] or [^[:word:]] to restore the old behavior.

• Ignores constraints when matching a regular expression back-reference. For example, in (^\d+).*\1, the ^ constraint should be applied at the beginning of the string, but not when matching \1.

• Disallows \w as the start or end of a range in a regular expression character class.

Functions and expressions

• The factorial function no longer accepts negative numbers. In version 11, it returned 1 for such cases.

• Improves the output performance of real and double precision values by using a new algorithm. In version 11, floating-point values were rounded to 6 digits (for real) or 15 digits (for double precision) by default, adjusted by the value of extra_float_digits. In version 14, if extra_float_digits is greater than zero (the default), the output includes only the minimum number of digits required to preserve the exact binary value. When extra_float_digits is set to zero or less, the behavior is the same as in version 11. In addition, the format of floating-point exponents is now consistent across all platforms: a two-digit exponent is used unless a three-digit one is required.

• When the input is a single NaN value, var_samp() and stddev_samp() for numeric types now return NULL. In version 11, these functions returned NaN.

• The behavior of random() and setseed() is now consistent across all platforms, which was not the case in version 11. The sequence of random() values generated after a setseed() call with a specific seed value may differ from the sequence in version 11. However, the sequence is now repeatable, which was not guaranteed in version 11 because other uses of random numbers within the server could interfere. The SQL random() function now has its own private session state to prevent this.

• Changes the return type of EXTRACT() from float8 to numeric. This avoids precision loss issues in some use cases. You can obtain the old behavior by using the date_part() function. In addition, in version 14, EXTRACT(date) throws an error for units that are not applicable to the date data type.

• Changes the SQL-style substring() to have standard-compliant greedy behavior. In cases where a pattern can match in multiple ways, version 14 matches the longest possible initial sub-pattern. For example, the pattern %#"aa*#" will select the last group of a's in the input, not the first.

• You must recreate user-defined objects that reference certain built-in array functions and their argument types. Specifically, array_append(), array_prepend(), array_cat(), array_position(), array_positions(), array_remove(), array_replace(), and width_bucket() accepted anyarray arguments in version 11 but accept anycompatiblearray in version 14. Therefore, user-defined objects like aggregates and operators that reference the signatures of these array functions must be dropped before an upgrade and recreated after the upgrade is complete.

• has_column_privilege() now returns false for checks on non-existent or dropped columns that use an attribute number. In version 11, such attribute numbers would return an invalid-column error.

• Fixes to_tsquery() and websearch_to_tsquery() to correctly parse query text containing discarded tokens. Some discarded tokens, such as underscores, caused these functions to produce incorrect tsquery output. For example, websearch_to_tsquery('"pg_class pg"') and to_tsquery('pg_class <-> pg') both output ( 'pg' & 'class' ) <-> 'pg' in version 11, but in version 14, both output 'pg' <-> 'class' <-> 'pg'.

• Fixes websearch_to_tsquery() to correctly parse quoted text with multiple adjacent discarded tokens. In version 11, quoted text containing multiple adjacent discarded tokens was treated as multiple tokens, resulting in incorrect tsquery output. For example, websearch_to_tsquery('"aaa: bbb"') outputs 'aaa' <2> 'bbb' in version 11 but outputs 'aaa' <-> 'bbb' in version 14.

• Fixes the handling of infinite window function ranges. In version 11, window frame clauses like 'inf' PRECEDING AND 'inf' FOLLOWING would return incorrect results.

• Prevents the tablefunc function normal_rand() from accepting negative values. Negative values produced undesirable results.

• Changes SIMILAR TO ... ESCAPE NULL to return NULL. This new behavior complies with the SQL specification. In version 11, a null ESCAPE value was treated as using the default escape string (a backslash character). The same rule applies to substring(text FROM pattern ESCAPE text). The version 11 behavior is preserved in old views by leaving the original function unchanged.

• Makes json[b]_to_tsvector() fully check the spelling of its string option.

• Removes the pretty-print effect from the results of xpath() or XMLTABLE. In some cases, these functions inserted extra whitespace (newlines and/or spaces) into node-set values. This behavior was removed because whitespace can be semantically significant depending on the use case.

Query optimization

• Changes how non-default effective_io_concurrency values affect concurrency. In version 11, this value was adjusted before setting the number of concurrent requests, whereas version 14 uses the value directly. You can use the following method to convert an old value to the new equivalent: SELECT round(sum(OLDVALUE / n::float)) AS newvalue FROM generate_series(1, OLDVALUE) s(n);.

• In ltree, when an lquery pattern contains adjacent asterisks in parentheses, such as *{2}.{3}, it is now correctly interpreted as {5}.

• In new B-tree indexes, the maximum length of an index entry is reduced by eight bytes to improve duplicate handling. This means that a REINDEX operation on an index from a version 11 database that has undergone pg_upgrade might fail.

DDL operations

• Fixes ALTER FOREIGN TABLE ... RENAME COLUMN to return a more appropriate command tag. In version 11, it returned ALTER TABLE. In version 14, it returns ALTER FOREIGN TABLE.

• Fixes ALTER MATERIALIZED VIEW ... RENAME COLUMN to return a more appropriate command tag. In version 11, it returned ALTER TABLE. In version 14, it returns ALTER MATERIALIZED VIEW.

• Disallows single quotes around the language name in CREATE/DROP LANGUAGE commands.

• Using DROP IF EXISTS FUNCTION/PROCEDURE/AGGREGATE/ROUTINE now throws an error if no argument list is provided and multiple matching objects exist. The error messages for this situation have also been improved.

Extensions and tools

• Disallows non-unique abbreviations in psql's \pset format command. In version 11, for example, \pset format a selected aligned; in version 14, this fails because it could also represent asciidoc.

• Removes the timetravel extension and the data types abstime, reltime, and tinterval. These types have long been superseded by SQL-standard types such as timestamp.

• Prevents auxiliary processes from appearing in the pg_stat_ssl and pg_stat_gssapi system views. If your queries join these views with pg_stat_activity and you expect to see auxiliary processes, you must now use a left join.