PostgreSQL

Major hash index improvements necessitated this requirement. pg_upgrade will create a script to assist with this.

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.

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 “[.quote]#xlog”# terminology is no longer used in any user-facing places.

There was previously an inconsistent mixture of the two terminologies.

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 Section 37.4.8.

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_constructor`s.

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.

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.

Also, sequences created for SERIAL columns now generate positive 32-bit wide values, whereas previous versions generated 64-bit wide values. This has no visible effect if the values are only stored in a column.

The output of psql’s \d command for a sequence has been redesigned, too.

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).

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.

Previously some pg_ctl actions didn’t wait for completion, and required the use of -w to do so.

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.

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 ssl_dh_params_file.

The password_encryption 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.

These replace min_parallel_relation_size, which was found to be too generic.

These settings are really lists of file names, but they were previously treated as lists of SQL identifiers, which have different parsing rules.

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.

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.

This functionality has been replaced by new server parameters pltcl.start_proc and pltclu.start_proc, which are easier to use and more similar to features available in other PLs.

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.

This removes configure’s --disable-integer-datetimes option. Floating-point timestamps have few advantages and have not been the default since PostgreSQL 8.3.

This protocol hasn’t had client support since PostgreSQL 6.3.

This module provided compatibility with the version of full text search that shipped in pre-8.3 PostgreSQL releases.

These had been deprecated since PostgreSQL 9.1. Instead, use CREATE EXTENSION and DROP EXTENSION directly.

Extensions providing C-coded functions must now conform to version 1 calling conventions. Version 0 has been deprecated since 2001.

This change allows B-tree index pages to be searched by separate parallel workers.

This allows a single index scan to dispatch parallel workers to process different areas of the heap.

This parameter can be set lower than max_worker_processes to reserve worker processes for purposes other than parallel queries.

This makes hash indexes crash-safe and replicatable. The former warning message about their use is removed.

A new CREATE INDEX option enables auto-summarization of the previous BRIN page range when a new page range is created.

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 `UPDATE`s and `DELETE`s.

For example, changing a table’s effective_io_concurrency setting can now be done with a more lightweight lock.

Lock promotion can now be controlled through two new server parameters, max_pred_locks_per_relation and max_pred_locks_per_page.

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.

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.

This is particularly helpful for queries that process many rows.

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.

New roles pg_monitor, pg_read_all_settings, pg_read_all_stats, and pg_stat_scan_tables allow simplified permission configuration.

The previous default was an empty prefix.

The new functions are pg_ls_logdir() and pg_ls_waldir() and can be executed by non-superusers with the proper permissions.

Also, when logging failure to bind a listening socket, include the specific address we attempted to bind to.

These are now DEBUG1-level messages.

This also changes the verbosity of client_min_messages debug levels.

This change enables reporting of numerous low-level wait conditions, including latch waits, file reads/writes/fsyncs, client reads/writes, and synchronous replication.

This simplifies monitoring. A new column backend_type identifies the process type.

This makes the output more consistent.

This provides better security than the existing md5 negotiation and storage method.

This was necessary to support additional password hashing options.

This shows the file contents, not the currently active settings.

All the RADIUS related parameters are now plural and support a comma-separated list of servers.

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.

This reduces the risk of data loss after a power failure.

Any sanity-check failure generates a fatal error on the standby.

A larger WAL segment size allows for fewer archive_command invocations and fewer WAL files to manage.

Logical replication allows more flexibility than physical replication does, including replication between different major versions of PostgreSQL and selective replication.

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.

Specifically, the defaults were changed for wal_level, max_wal_senders, max_replication_slots, and hot_standby to make them suitable for these usages out-of-the-box.

Previously `pg_hba.conf’s replication connection lines were commented out by default. This is particularly useful for [.refentrytitlepg_basebackup#]#.

The new columns are write_lag, flush_lag, and replay_lag.

Previously the stopping point could only be selected by timestamp or XID.

An optional second argument to pg_stop_backup() controls that behavior.

Temporary slots are automatically removed on session exit or error.

Previously, such characters were never recognized as belonging to locale-dependent character classes such as [[:alpha:]].

The syntax supports range and list partitioning.

Transition tables are accessible from triggers written in server-side languages.

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.

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.

This is done using the ALTER DEFAULT PRIVILEGES command.

This simplifies the creation of sequences matching the range of base columns.

The triggers are fed the data rows read by COPY.

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.

This information is also included in log_autovacuum_min_duration output.

The functions ts_headline() and to_tsvector() can now be used on these data types.

This complements the existing support for EUI-48 MAC addresses (type macaddr).

These are similar to SERIAL columns, but are SQL standard compliant.

This uses the syntax ALTER TYPE ... RENAME VALUE.

Previously columns declared as anyarray (particularly those in the pg_stats view) were converted to JSON strings rather than arrays.

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.

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.

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.

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.

This is useful for checking after an abrupt disconnection whether your previous transaction committed and you just didn’t receive the acknowledgment.

For example, previously to_date('2009-06-40','YYYY-MM-DD') was accepted and returned 2009-07-10. It will now generate an error.

This allows a more object-oriented programming style.

Previously, a syntactic restriction prevented the target variable from being an array element.

This allows PL/Tcl queries to fail without aborting the entire function.

libpq will connect to the first responsive server in the list.

This is useful when multiple host names are specified. It is controlled by libpq connection parameter target_session_attrs.

Previously this could only be specified via an environment variable.

Previously only MD5-encrypted passwords could be created using PQencryptPassword(). This new function can also create SCRAM-SHA-256-encrypted passwords.

Henceforth the ecpg version will match the PostgreSQL distribution version number.

This feature adds psql meta-commands \if, \elif, \else, and \endif. This is primarily helpful for scripting.

This is particularly useful in the new psql conditional branch commands.

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.

Previously they were shown in a single “[.quote]#Modifiers”# column.

They now all print the message about that to stderr, not stdout, and the message wording is more consistent.

A meta-command can now be continued onto the next line by writing backslash-return.

This complements the existing --startpos option.

The old spellings are still supported.

This adds a new -N/--exclude-schema option.

This suppresses dumping of large objects.

This allows use of pg_dumpall by non-superusers; without this option, it fails due to inability to read passwords.

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.

The WAL will be stored in a separate tar file from the base backup.

Temporary replication slots will be used by default when pg_basebackup uses WAL streaming with default options.

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.

pg_ctl now probes ten times per second when waiting for a postmaster state change, rather than once per second.

The start and promote operations now return exit status 1, not 0, in such cases. The stop operation has always done that.

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.

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.

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.

If third-party code is using extern function declarations, they should also add DLLEXPORT markers to those declarations.

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.

This avoids platform-specific limits on SysV semaphore usage.

gettimeofday() is still used if clock_gettime() is not available.

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.

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.

Previously Jade, DSSSL, and JadeTex were used.

This reduces the amount of data that must be passed from the remote server, and offloads aggregate computation from the requesting server.

Previously OID columns always returned zeros.

This allows enums to be used in exclusion constraints.

This also improves handling of infinite and NaN values.

This makes it less disruptive when run on production systems.

This allows DBAs to allow non-superusers to run these functions.