E.9. Release 10

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 acknowledgment from standby servers irrespective of the order they appear in synchronous_standby_names (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 wal_level , max_wal_senders , max_replication_slots , and hot_standby 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 pg_basebackup .

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 log_autovacuum_min_duration 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 acknowledgment.

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

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)