PostgreSQL 11

Release history

Every indexed release keeps its original occurrences. CVE counts below are mentions in its notes, including follow-up corrections; they do not count newly fixed vulnerabilities.

Initial release changes

Original entries from 11.0, including feature changes and compatibility notes. Categories aid browsing; they are not upstream classifications.

170 of 170 original entries.

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

  Make pg_dump dump the properties of a database, not just its contents (Haribabu Kommi)

  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.

  Original release occurrence · 11.0/migration/001

• Consider syntactic form when disambiguating function versus column references Compatibility Migration

  Consider syntactic form when disambiguating function versus column references (Tom Lane)

  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.

  Original release occurrence · 11.0/migration/002

• Fully enforce uniqueness of table and domain constraint names Compatibility Migration

  Fully enforce uniqueness of table and domain constraint names (Tom Lane)

  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.

  Original release occurrence · 11.0/migration/003

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

  Make power(numeric, numeric) and power(float8, float8) handle NaN inputs according to the POSIX standard (Tom Lane, Dang Minh Huong)

  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.

  Original release occurrence · 11.0/migration/004

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

  Prevent to_number() from consuming characters when the template separator does not match (Oliver Ford)

  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.

  Original release occurrence · 11.0/migration/005

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

  Fix to_date(), to_number(), and to_timestamp() to skip a character for each template character (Tom Lane)

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

  Original release occurrence · 11.0/migration/006

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

  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.

  Original release occurrence · 11.0/migration/007

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

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

  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.

  Original release occurrence · 11.0/migration/008

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

  In the extended query protocol, make statement_timeout apply to each Execute message separately, not to all commands before Sync (Tatsuo Ishii, Andres Freund)

  Original release occurrence · 11.0/migration/009

• Remove the relhaspkey column from system catalog pg_class Compatibility Migration

  Remove the relhaspkey column from system catalog pg_class (Peter Eisentraut)

  Applications needing to check for a primary key should consult pg_index.

  Original release occurrence · 11.0/migration/010

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

  Replace system catalog pg_proc's proisagg and proiswindow columns with prokind (Peter Eisentraut)

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

  Original release occurrence · 11.0/migration/011

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

  Correct information schema column tables.table_type to return FOREIGN instead of FOREIGN TABLE (Peter Eisentraut)

  This new output matches the SQL standard.

  Original release occurrence · 11.0/migration/012

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

  Change the ps process display labels for background workers to match the pg_stat_activity.backend_type labels (Peter Eisentraut)

  Original release occurrence · 11.0/migration/013

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

  Cause large object permission checks to happen during large object open, lo_open(), not when a read or write is attempted (Tom Lane, Michael Paquier)

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

  Original release occurrence · 11.0/migration/014

• Prevent non-superusers from reindexing shared catalogs Compatibility Migration

  Prevent non-superusers from reindexing shared catalogs (Michael Paquier, Robert Haas)

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

  Original release occurrence · 11.0/migration/015

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

  Remove deprecated adminpack functions pg_file_read(), pg_file_length(), and pg_logfile_rotate() (Stephen Frost)

  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.

  Original release occurrence · 11.0/migration/016

• Honor the capitalization of double-quoted command options Compatibility Migration

  Honor the capitalization of double-quoted command options (Daniel Gustafsson)

  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.

  Original release occurrence · 11.0/migration/017

• Remove server parameter replacement_sort_tuples Compatibility Migration

  Remove server parameter replacement_sort_tuples (Peter Geoghegan)

  Replacement sorts were determined to be no longer useful.

  Original release occurrence · 11.0/migration/018

• Remove WITH clause in CREATE FUNCTION Compatibility Migration

  Remove WITH clause in CREATE FUNCTION (Michael Paquier)

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

  Original release occurrence · 11.0/migration/019

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

  In PL/pgSQL trigger functions, the OLD and NEW variables now read as NULL when not assigned (Tom Lane)

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

  Original release occurrence · 11.0/migration/020

• Allow the creation of partitions based on hashing a key column Features

  Allow the creation of partitions based on hashing a key column (Amul Sul)

  Original release occurrence · 11.0/changes/001

• Support indexes on partitioned tables Features

  Support indexes on partitioned tables (Álvaro Herrera, Amit Langote)

  An “index” on a partitioned table is not a physical index across the whole partitioned table, but rather a template for automatically creating similar indexes on each partition of the table.

  If the partition key is part of the index's column set, a partitioned index may be declared UNIQUE. It will represent a valid uniqueness constraint across the whole partitioned table, even though each physical index only enforces uniqueness within its own partition.

  The new command ALTER INDEX ATTACH PARTITION causes an existing index on a partition to be associated with a matching index template for its partitioned table. This provides flexibility in setting up a new partitioned index for an existing partitioned table.

  Original release occurrence · 11.0/changes/002

• Allow foreign keys on partitioned tables Features

  Allow foreign keys on partitioned tables (Álvaro Herrera)

  Original release occurrence · 11.0/changes/003

• Allow FOR EACH ROW triggers on partitioned tables Features

  Allow FOR EACH ROW triggers on partitioned tables (Álvaro Herrera)

  Creation of a trigger on a partitioned table automatically creates triggers on all existing and future partitions. This also allows deferred unique constraints on partitioned tables.

  Original release occurrence · 11.0/changes/004

• Allow partitioned tables to have a default partition Features

  Allow partitioned tables to have a default partition (Jeevan Ladhe, Beena Emerson, Ashutosh Bapat, Rahila Syed, Robert Haas)

  The default partition will store rows that don't match any of the other defined partitions, and is searched accordingly.

  Original release occurrence · 11.0/changes/005

• UPDATE statements that change a partition key column now cause affected rows to be moved to the appropriate partitions Features

  UPDATE statements that change a partition key column now cause affected rows to be moved to the appropriate partitions (Amit Khandekar)

  Original release occurrence · 11.0/changes/006

• Allow INSERT, UPDATE, and COPY on partitioned tables to properly route rows to foreign partitions Features

  Allow INSERT, UPDATE, and COPY on partitioned tables to properly route rows to foreign partitions (Etsuro Fujita, Amit Langote)

  This is supported by postgres_fdw foreign tables. Since the ExecForeignInsert callback function is called for this in a different way than it used to be, foreign data wrappers must be modified to cope with this change.

  Original release occurrence · 11.0/changes/007

• Allow faster partition elimination during query processing Performance

  Allow faster partition elimination during query processing (Amit Langote, David Rowley, Dilip Kumar)

  This speeds access to partitioned tables with many partitions.

  Original release occurrence · 11.0/changes/008

• Allow partition elimination during query execution Features

  Allow partition elimination during query execution (David Rowley, Beena Emerson)

  Previously, partition elimination only happened at planning time, meaning many joins and prepared queries could not use partition elimination.

  Original release occurrence · 11.0/changes/009

• In an equality join between partitioned tables, allow matching partitions to be joined directly Features

  In an equality join between partitioned tables, allow matching partitions to be joined directly (Ashutosh Bapat)

  This feature is disabled by default but can be enabled by changing enable_partitionwise_join.

  Original release occurrence · 11.0/changes/010

• Allow aggregate functions on partitioned tables to be evaluated separately for each partition, subsequently merging the results Features

  Allow aggregate functions on partitioned tables to be evaluated separately for each partition, subsequently merging the results (Jeevan Chalke, Ashutosh Bapat, Robert Haas)

  This feature is disabled by default but can be enabled by changing enable_partitionwise_aggregate.

  Original release occurrence · 11.0/changes/011

• Allow postgres_fdw to push down aggregates to foreign tables that are partitions Features

  Allow postgres_fdw to push down aggregates to foreign tables that are partitions (Jeevan Chalke)

  Original release occurrence · 11.0/changes/012

• Allow parallel building of a btree index Features

  Allow parallel building of a btree index (Peter Geoghegan, Rushabh Lathia, Heikki Linnakangas)

  Original release occurrence · 11.0/changes/013

• Allow hash joins to be performed in parallel using a shared hash table Features

  Allow hash joins to be performed in parallel using a shared hash table (Thomas Munro)

  Original release occurrence · 11.0/changes/014

• Allow UNION to run each SELECT in parallel if the individual SELECTs cannot be parallelized Features

  Allow UNION to run each SELECT in parallel if the individual SELECTs cannot be parallelized (Amit Khandekar, Robert Haas, Amul Sul)

  Original release occurrence · 11.0/changes/015

• Allow partition scans to more efficiently use parallel workers Features

  Allow partition scans to more efficiently use parallel workers (Amit Khandekar, Robert Haas, Amul Sul)

  Original release occurrence · 11.0/changes/016

• Allow LIMIT to be passed to parallel workers Features

  Allow LIMIT to be passed to parallel workers (Robert Haas, Tom Lane)

  This allows workers to reduce returned results and use targeted index scans.

  Original release occurrence · 11.0/changes/017

• Allow single-evaluation queries, e.g., WHERE clause aggregate queries, and functions in the target list to be parallelized Features

  Allow single-evaluation queries, e.g., WHERE clause aggregate queries, and functions in the target list to be parallelized (Amit Kapila, Robert Haas)

  Original release occurrence · 11.0/changes/018

• Add server parameter parallel_leader_participation to control whether the leader also executes subplans Features

  Add server parameter parallel_leader_participation to control whether the leader also executes subplans (Thomas Munro)

  The default is enabled, meaning the leader will execute subplans.

  Original release occurrence · 11.0/changes/019

• Allow parallelization of commands CREATE TABLE ... AS, SELECT INTO, and CREATE MATERIALIZED VIEW Features

  Allow parallelization of commands CREATE TABLE ... AS, SELECT INTO, and CREATE MATERIALIZED VIEW (Haribabu Kommi)

  Original release occurrence · 11.0/changes/020

• Improve performance of sequential scans with many parallel workers Performance

  Improve performance of sequential scans with many parallel workers (David Rowley)

  Original release occurrence · 11.0/changes/021

• Add reporting of parallel workers' sort activity in EXPLAIN Features

  Add reporting of parallel workers' sort activity in EXPLAIN (Robert Haas, Tom Lane)

  Original release occurrence · 11.0/changes/022

• Allow B-tree indexes to include columns that are not part of the search key or unique constraint, but are available to be read by index-only scans Features

  Allow B-tree indexes to include columns that are not part of the search key or unique constraint, but are available to be read by index-only scans (Anastasia Lubennikova, Alexander Korotkov, Teodor Sigaev)

  This is enabled by the new INCLUDE clause of CREATE INDEX. It facilitates building “covering indexes” that optimize specific types of queries. Columns can be included even if their data types don't have B-tree support.

  Original release occurrence · 11.0/changes/023

• Improve performance of monotonically increasing index additions Performance

  Improve performance of monotonically increasing index additions (Pavan Deolasee, Peter Geoghegan)

  Original release occurrence · 11.0/changes/024

• Improve performance of hash index scans Performance

  Improve performance of hash index scans (Ashutosh Sharma)

  Original release occurrence · 11.0/changes/025

• Add predicate locking for hash, GiST and GIN indexes Features

  Add predicate locking for hash, GiST and GIN indexes (Shubham Barai)

  This reduces the likelihood of serialization conflicts in serializable-mode transactions.

  Original release occurrence · 11.0/changes/026

• Add prefix-match operator text ^@ text, which is supported by SP-GiST Features

  Add prefix-match operator text ^@ text, which is supported by SP-GiST (Ildus Kurbangaliev)

  This is similar to using var LIKE 'word%' with a btree index, but it is more efficient.

  Original release occurrence · 11.0/changes/027

• Allow polygons to be indexed with SP-GiST Features

  Allow polygons to be indexed with SP-GiST (Nikita Glukhov, Alexander Korotkov)

  Original release occurrence · 11.0/changes/028

• Allow SP-GiST to use lossy representation of leaf keys Features

  Allow SP-GiST to use lossy representation of leaf keys (Teodor Sigaev, Heikki Linnakangas, Alexander Korotkov, Nikita Glukhov)

  Original release occurrence · 11.0/changes/029

• Improve selection of the most common values for statistics Features

  Improve selection of the most common values for statistics (Jeff Janes, Dean Rasheed)

  Previously, the most common values (MCVs) were identified based on their frequency compared to all column values. Now, MCVs are chosen based on their frequency compared to the non-MCV values. This improves the robustness of the algorithm for both uniform and non-uniform distributions.

  Original release occurrence · 11.0/changes/030

• Improve selectivity estimates for >= and <= Features

  Improve selectivity estimates for >= and <= (Tom Lane)

  Previously, such cases used the same selectivity estimates as > and <, respectively, unless the comparison constants are MCVs. This change is particularly helpful for queries involving BETWEEN with small ranges.

  Original release occurrence · 11.0/changes/031

• Reduce var = var to var IS NOT NULL where equivalent Features

  Reduce var = var to var IS NOT NULL where equivalent (Tom Lane)

  This leads to better selectivity estimates.

  Original release occurrence · 11.0/changes/032

• Improve optimizer's row count estimates for EXISTS and NOT EXISTS queries Features

  Improve optimizer's row count estimates for EXISTS and NOT EXISTS queries (Tom Lane)

  Original release occurrence · 11.0/changes/033

• Make the optimizer account for evaluation costs and selectivity of HAVING clauses Features

  Make the optimizer account for evaluation costs and selectivity of HAVING clauses (Tom Lane)

  Original release occurrence · 11.0/changes/034

• Add Just-in-Time (JIT) compilation of some parts of query plans to improve execution speed Performance

  Add Just-in-Time (JIT) compilation of some parts of query plans to improve execution speed (Andres Freund)

  This feature requires LLVM to be available. It is not currently enabled by default, even in builds that support it.

  Original release occurrence · 11.0/changes/035

• Allow bitmap scans to perform index-only scans when possible Performance

  Allow bitmap scans to perform index-only scans when possible (Alexander Kuzmenkov)

  Original release occurrence · 11.0/changes/036

• Update the free space map during VACUUM Performance

  Update the free space map during VACUUM (Claudio Freire)

  This allows free space to be reused more quickly.

  Original release occurrence · 11.0/changes/037

• Allow VACUUM to avoid unnecessary index scans Performance

  Allow VACUUM to avoid unnecessary index scans (Masahiko Sawada, Alexander Korotkov)

  Original release occurrence · 11.0/changes/038

• Improve performance of committing multiple concurrent transactions Performance

  Improve performance of committing multiple concurrent transactions (Amit Kapila)

  Original release occurrence · 11.0/changes/039

• Reduce memory usage for queries using set-returning functions in their target lists Performance

  Reduce memory usage for queries using set-returning functions in their target lists (Andres Freund)

  Original release occurrence · 11.0/changes/040

• Improve the speed of aggregate computations Performance

  Improve the speed of aggregate computations (Andres Freund)

  Original release occurrence · 11.0/changes/041

• Allow postgres_fdw to push UPDATEs and DELETEs using joins to foreign servers Performance

  Allow postgres_fdw to push UPDATEs and DELETEs using joins to foreign servers (Etsuro Fujita)

  Previously, only non-join UPDATEs and DELETEs were pushed.

  Original release occurrence · 11.0/changes/042

• Add support for large pages on Windows Performance

  Add support for large pages on Windows (Takayuki Tsunakawa, Thomas Munro)

  This is controlled by the huge_pages configuration parameter.

  Original release occurrence · 11.0/changes/043

• Show memory usage in output from log_statement_stats, log_parser_stats, log_planner_stats, and log_executor_stats Features

  Show memory usage in output from log_statement_stats, log_parser_stats, log_planner_stats, and log_executor_stats (Justin Pryzby, Peter Eisentraut)

  Original release occurrence · 11.0/changes/044

• Add column pg_stat_activity.backend_type to show the type of a background worker Features

  Add column pg_stat_activity.backend_type to show the type of a background worker (Peter Eisentraut)

  The type is also visible in ps output.

  Original release occurrence · 11.0/changes/045

• Make log_autovacuum_min_duration log skipped tables that are concurrently being dropped Features

  Make log_autovacuum_min_duration log skipped tables that are concurrently being dropped (Nathan Bossart)

  Original release occurrence · 11.0/changes/046

• Add information_schema columns related to table constraints and triggers Features

  Add information_schema columns related to table constraints and triggers (Peter Eisentraut)

  Specifically, triggers.action_order, triggers.action_reference_old_table, and triggers.action_reference_new_table are now populated, where before they were always null. Also, table_constraints.enforced now exists but is not yet usefully populated.

  Original release occurrence · 11.0/changes/047

• Allow the server to specify more complex LDAP specifications in search+bind mode Features

  Allow the server to specify more complex LDAP specifications in search+bind mode (Thomas Munro)

  Specifically, ldapsearchfilter allows pattern matching using combinations of LDAP attributes.

  Original release occurrence · 11.0/changes/048

• Allow LDAP authentication to use encrypted LDAP Features

  Allow LDAP authentication to use encrypted LDAP (Thomas Munro)

  We already supported LDAP over TLS by using ldaptls=1. This new TLS LDAP method for encrypted LDAP is enabled with ldapscheme=ldaps or ldapurl=ldaps://.

  Original release occurrence · 11.0/changes/049

• Improve logging of LDAP errors Features

  Improve logging of LDAP errors (Thomas Munro)

  Original release occurrence · 11.0/changes/050

• Add default roles that enable file system access Features

  Add default roles that enable file system access (Stephen Frost)

  Specifically, the new roles are: pg_read_server_files, pg_write_server_files, and pg_execute_server_program. These roles now also control who can use server-side COPY and the file_fdw extension. Previously, only superusers could use these functions, and that is still the default behavior.

  Original release occurrence · 11.0/changes/051

• Allow access to file system functions to be controlled by GRANT/REVOKE permissions, rather than superuser checks Features

  Allow access to file system functions to be controlled by GRANT/REVOKE permissions, rather than superuser checks (Stephen Frost)

  Specifically, these functions were modified: pg_ls_dir(), pg_read_file(), pg_read_binary_file(), pg_stat_file().

  Original release occurrence · 11.0/changes/052

• Use GRANT/REVOKE to control access to lo_import() and lo_export() Features

  Use GRANT/REVOKE to control access to lo_import() and lo_export() (Michael Paquier, Tom Lane)

  Previously, only superusers were granted access to these functions.

  The compile-time option ALLOW_DANGEROUS_LO_FUNCTIONS has been removed.

  Original release occurrence · 11.0/changes/053

• Use view owner not session owner when preventing non-password access to postgres_fdw tables Features

  Use view owner not session owner when preventing non-password access to postgres_fdw tables (Robert Haas)

  PostgreSQL only allows superusers to access postgres_fdw tables without passwords, e.g., via peer. Previously, the session owner had to be a superuser to allow such access; now the view owner is checked instead.

  Original release occurrence · 11.0/changes/054

• Fix invalid locking permission check in SELECT FOR UPDATE on views Bug fixes

  Fix invalid locking permission check in SELECT FOR UPDATE on views (Tom Lane)

  Original release occurrence · 11.0/changes/055

• Add server setting ssl_passphrase_command to allow supplying of the passphrase for SSL key files Features

  Add server setting ssl_passphrase_command to allow supplying of the passphrase for SSL key files (Peter Eisentraut)

  Also add ssl_passphrase_command_supports_reload to specify whether the SSL configuration should be reloaded and ssl_passphrase_command called during a server configuration reload.

  Original release occurrence · 11.0/changes/056

• Add storage parameter toast_tuple_target to control the minimum tuple length before TOAST storage will be considered Features

  Add storage parameter toast_tuple_target to control the minimum tuple length before TOAST storage will be considered (Simon Riggs)

  The default TOAST threshold has not been changed.

  Original release occurrence · 11.0/changes/057

• Allow server options related to memory and file sizes to be specified in units of bytes Features

  Allow server options related to memory and file sizes to be specified in units of bytes (Beena Emerson)

  The new unit suffix is “B”. This is in addition to the existing units “kB”, “MB”, “GB” and “TB”.

  Original release occurrence · 11.0/changes/058

• Allow the WAL file size to be set during initdb Features

  Allow the WAL file size to be set during initdb (Beena Emerson)

  Previously, the 16MB default could only be changed at compile time.

  Original release occurrence · 11.0/changes/059

• Retain WAL data for only a single checkpoint Features

  Retain WAL data for only a single checkpoint (Simon Riggs)

  Previously, WAL was retained for two checkpoints.

  Original release occurrence · 11.0/changes/060

• Fill the unused portion of force-switched WAL segment files with zeros for improved compressibility Features

  Fill the unused portion of force-switched WAL segment files with zeros for improved compressibility (Chapman Flack)

  Original release occurrence · 11.0/changes/061

• Replicate TRUNCATE activity when using logical replication Features

  Replicate TRUNCATE activity when using logical replication (Simon Riggs, Marco Nenciarini, Peter Eisentraut)

  Original release occurrence · 11.0/changes/062

• Pass prepared transaction information to logical replication subscribers Features

  Pass prepared transaction information to logical replication subscribers (Nikhil Sontakke, Stas Kelvich)

  Original release occurrence · 11.0/changes/063

• Exclude unlogged tables, temporary tables, and pg_internal.init files from streaming base backups Features

  Exclude unlogged tables, temporary tables, and pg_internal.init files from streaming base backups (David Steele)

  There is no need to copy such files.

  Original release occurrence · 11.0/changes/064

• Allow checksums of heap pages to be verified during streaming base backup Features

  Allow checksums of heap pages to be verified during streaming base backup (Michael Banck)

  Original release occurrence · 11.0/changes/065

• Allow replication slots to be advanced programmatically, rather than be consumed by subscribers Features

  Allow replication slots to be advanced programmatically, rather than be consumed by subscribers (Petr Jelinek)

  This allows efficient advancement of replication slots when the contents do not need to be consumed. This is performed by pg_replication_slot_advance().

  Original release occurrence · 11.0/changes/066

• Add timeline information to the backup_label file Features

  Add timeline information to the backup_label file (Michael Paquier)

  Also add a check that the WAL timeline matches the backup_label file's timeline.

  Original release occurrence · 11.0/changes/067

• Add host and port connection information to the pg_stat_wal_receiver system view Features

  Add host and port connection information to the pg_stat_wal_receiver system view (Haribabu Kommi)

  Original release occurrence · 11.0/changes/068

• Allow ALTER TABLE to add a column with a non-null default without doing a table rewrite Features

  Allow ALTER TABLE to add a column with a non-null default without doing a table rewrite (Andrew Dunstan, Serge Rielau)

  This is enabled when the default value is a constant.

  Original release occurrence · 11.0/changes/069

• Allow views to be locked by locking the underlying tables Features

  Allow views to be locked by locking the underlying tables (Yugo Nagata)

  Original release occurrence · 11.0/changes/070

• Allow ALTER INDEX to set statistics-gathering targets for expression indexes Features

  Allow ALTER INDEX to set statistics-gathering targets for expression indexes (Alexander Korotkov, Adrien Nayrat)

  In psql, \d+ now shows the statistics target for indexes.

  Original release occurrence · 11.0/changes/071

• Allow multiple tables to be specified in one VACUUM or ANALYZE command Features

  Allow multiple tables to be specified in one VACUUM or ANALYZE command (Nathan Bossart)

  Also, if any table mentioned in VACUUM uses a column list, then the ANALYZE keyword must be supplied; previously, ANALYZE was implied in such cases.

  Original release occurrence · 11.0/changes/072

• Add parenthesized options syntax to ANALYZE Features

  Add parenthesized options syntax to ANALYZE (Nathan Bossart)

  This is similar to the syntax supported by VACUUM.

  Original release occurrence · 11.0/changes/073

• Add CREATE AGGREGATE option to specify the behavior of the aggregate's finalization function Features

  Add CREATE AGGREGATE option to specify the behavior of the aggregate's finalization function (Tom Lane)

  This is helpful for allowing user-defined aggregate functions to be optimized and to work as window functions.

  Original release occurrence · 11.0/changes/074

• Allow the creation of arrays of domains Features

  Allow the creation of arrays of domains (Tom Lane)

  This also allows array_agg() to be used on domains.

  Original release occurrence · 11.0/changes/075

• Support domains over composite types Features

  Support domains over composite types (Tom Lane)

  Also allow PL/Perl, PL/Python, and PL/Tcl to handle composite-domain function arguments and results. Also improve PL/Python domain handling.

  Original release occurrence · 11.0/changes/076

• Add casts from JSONB scalars to numeric and boolean data types Features

  Add casts from JSONB scalars to numeric and boolean data types (Anastasia Lubennikova)

  Original release occurrence · 11.0/changes/077

• Add all window function framing options specified by SQL:2011 Features

  Add all window function framing options specified by SQL:2011 (Oliver Ford, Tom Lane)

  Specifically, allow RANGE mode to use PRECEDING and FOLLOWING to select rows having grouping values within plus or minus the specified offset. Add GROUPS mode to include plus or minus the number of peer groups. Frame exclusion syntax was also added.

  Original release occurrence · 11.0/changes/078

• Add SHA-2 family of hash functions Features

  Add SHA-2 family of hash functions (Peter Eisentraut)

  Specifically, sha224(), sha256(), sha384(), sha512() were added.

  Original release occurrence · 11.0/changes/079

• Add support for 64-bit non-cryptographic hash functions Features

  Add support for 64-bit non-cryptographic hash functions (Robert Haas, Amul Sul)

  Original release occurrence · 11.0/changes/080

• Allow to_char() and to_timestamp() to specify the time zone's offset from UTC in hours and minutes Features

  Allow to_char() and to_timestamp() to specify the time zone's offset from UTC in hours and minutes (Nikita Glukhov, Andrew Dunstan)

  This is done with format specifications TZH and TZM.

  Original release occurrence · 11.0/changes/081

• Add text search function websearch_to_tsquery() that supports a query syntax similar to that used by web search engines Features

  Add text search function websearch_to_tsquery() that supports a query syntax similar to that used by web search engines (Victor Drobny, Dmitry Ivanov)

  Original release occurrence · 11.0/changes/082

• Add functions json(b)_to_tsvector() to create a text search query for matching JSON/JSONB values Features

  Add functions json(b)_to_tsvector() to create a text search query for matching JSON/JSONB values (Dmitry Dolgov)

  Original release occurrence · 11.0/changes/083

• Add SQL-level procedures, which can start and commit their own transactions Features

  Add SQL-level procedures, which can start and commit their own transactions (Peter Eisentraut)

  They are created with the new CREATE PROCEDURE command and invoked via CALL.

  The new ALTER/DROP ROUTINE commands allow altering/dropping of all routine-like objects, including procedures, functions, and aggregates.

  Also, writing FUNCTION is now preferred over writing PROCEDURE in CREATE OPERATOR and CREATE TRIGGER, because the referenced object must be a function not a procedure. However, the old syntax is still accepted for compatibility.

  Original release occurrence · 11.0/changes/084

• Add transaction control to PL/pgSQL, PL/Perl, PL/Python, PL/Tcl, and SPI server-side languages Features

  Add transaction control to PL/pgSQL, PL/Perl, PL/Python, PL/Tcl, and SPI server-side languages (Peter Eisentraut)

  Transaction control is only available within top-transaction-level procedures and nested DO and CALL blocks that only contain other DO and CALL blocks.

  Original release occurrence · 11.0/changes/085

• Add the ability to define PL/pgSQL composite-type variables as not null, constant, or with initial values Features

  Add the ability to define PL/pgSQL composite-type variables as not null, constant, or with initial values (Tom Lane)

  Original release occurrence · 11.0/changes/086

• Allow PL/pgSQL to handle changes to composite types (e.g., record, row) that happen between the first and later function executions in the same session Features

  Allow PL/pgSQL to handle changes to composite types (e.g., record, row) that happen between the first and later function executions in the same session (Tom Lane)

  Previously, such circumstances generated errors.

  Original release occurrence · 11.0/changes/087

• Add extension jsonb_plpython to transform JSONB to/from PL/Python types Features

  Add extension jsonb_plpython to transform JSONB to/from PL/Python types (Anthony Bykov)

  Original release occurrence · 11.0/changes/088

• Add extension jsonb_plperl to transform JSONB to/from PL/Perl types Features

  Add extension jsonb_plperl to transform JSONB to/from PL/Perl types (Anthony Bykov)

  Original release occurrence · 11.0/changes/089

• Change libpq to disable compression by default Features

  Change libpq to disable compression by default (Peter Eisentraut)

  Compression is already disabled in modern OpenSSL versions, so that the libpq setting had no effect with such libraries.

  Original release occurrence · 11.0/changes/090

• Add DO CONTINUE option to ecpg's WHENEVER statement Features

  Add DO CONTINUE option to ecpg's WHENEVER statement (Vinayak Pokale)

  This generates a C continue statement, causing a return to the top of the contained loop when the specified condition occurs.

  Original release occurrence · 11.0/changes/091

• Add an ecpg mode to enable Oracle Pro*C-style handling of char arrays. Features

  Add an ecpg mode to enable Oracle Pro*C-style handling of char arrays.

  This mode is enabled with -C.

  Original release occurrence · 11.0/changes/092

• Add psql command \gdesc to display the names and types of the columns in a query result Features

  Add psql command \gdesc to display the names and types of the columns in a query result (Pavel Stehule)

  Original release occurrence · 11.0/changes/093

• Add psql variables to report query activity and errors Features

  Add psql variables to report query activity and errors (Fabien Coelho)

  Specifically, the new variables are ERROR, SQLSTATE, ROW_COUNT, LAST_ERROR_MESSAGE, and LAST_ERROR_SQLSTATE.

  Original release occurrence · 11.0/changes/094

• Allow psql to test for the existence of a variable Features

  Allow psql to test for the existence of a variable (Fabien Coelho)

  Specifically, the syntax :{?variable_name} allows a variable's existence to be tested in an \if statement.

  Original release occurrence · 11.0/changes/095

• Allow environment variable PSQL_PAGER to control psql's pager Features

  Allow environment variable PSQL_PAGER to control psql's pager (Pavel Stehule)

  This allows psql's default pager to be specified as a separate environment variable from the pager for other applications. PAGER is still honored if PSQL_PAGER is not set.

  Original release occurrence · 11.0/changes/096

• Make psql's \d+ command always show the table's partitioning information Features

  Make psql's \d+ command always show the table's partitioning information (Amit Langote, Ashutosh Bapat)

  Previously, partition information would not be displayed for a partitioned table if it had no partitions. Also indicate which partitions are themselves partitioned.

  Original release occurrence · 11.0/changes/097

• Ensure that psql reports the proper user name when prompting for a password Features

  Ensure that psql reports the proper user name when prompting for a password (Tom Lane)

  Previously, combinations of -U and a user name embedded in a URI caused incorrect reporting. Also suppress the user name before the password prompt when --password is specified.

  Original release occurrence · 11.0/changes/098

• Allow quit and exit to exit psql when given with no prior input Features

  Allow quit and exit to exit psql when given with no prior input (Bruce Momjian)

  Also print hints about how to exit when quit and exit are used alone on a line while the input buffer is not empty. Add a similar hint for help.

  Original release occurrence · 11.0/changes/099

• Make psql hint at using control-D when \q is entered alone on a line but ignored Features

  Make psql hint at using control-D when \q is entered alone on a line but ignored (Bruce Momjian)

  For example, \q does not exit when supplied in character strings.

  Original release occurrence · 11.0/changes/100

• Improve tab completion for ALTER INDEX RESET/SET Features

  Improve tab completion for ALTER INDEX RESET/SET (Masahiko Sawada)

  Original release occurrence · 11.0/changes/101

• Add infrastructure to allow psql to adapt its tab completion queries based on the server version Features

  Add infrastructure to allow psql to adapt its tab completion queries based on the server version (Tom Lane)

  Previously, tab completion queries could fail against older servers.

  Original release occurrence · 11.0/changes/102

• Add pgbench expression support for NULLs, booleans, and some functions and operators Features

  Add pgbench expression support for NULLs, booleans, and some functions and operators (Fabien Coelho)

  Original release occurrence · 11.0/changes/103

• Add \if conditional support to pgbench Features

  Add \if conditional support to pgbench (Fabien Coelho)

  Original release occurrence · 11.0/changes/104

• Allow the use of non-ASCII characters in pgbench variable names Features

  Allow the use of non-ASCII characters in pgbench variable names (Fabien Coelho)

  Original release occurrence · 11.0/changes/105

• Add pgbench option --init-steps to control the initialization steps performed Features

  Add pgbench option --init-steps to control the initialization steps performed (Masahiko Sawada)

  Original release occurrence · 11.0/changes/106

• Add an approximately Zipfian-distributed random generator to pgbench Features

  Add an approximately Zipfian-distributed random generator to pgbench (Alik Khilazhev)

  Original release occurrence · 11.0/changes/107

• Allow the random seed to be set in pgbench Features

  Allow the random seed to be set in pgbench (Fabien Coelho)

  Original release occurrence · 11.0/changes/108

• Allow pgbench to do exponentiation with pow() and power() Features

  Allow pgbench to do exponentiation with pow() and power() (Raúl Marín Rodríguez)

  Original release occurrence · 11.0/changes/109

• Add hashing functions to pgbench Features

  Add hashing functions to pgbench (Ildar Musin)

  Original release occurrence · 11.0/changes/110

• Make pgbench statistics more accurate when using --latency-limit and --rate Features

  Make pgbench statistics more accurate when using --latency-limit and --rate (Fabien Coelho)

  Original release occurrence · 11.0/changes/111

• Add an option to pg_basebackup that creates a named replication slot Features

  Add an option to pg_basebackup that creates a named replication slot (Michael Banck)

  The option --create-slot creates the named replication slot (--slot) when the WAL streaming method (--wal-method=stream) is used.

  Original release occurrence · 11.0/changes/112

• Allow initdb to set group read access to the data directory Features

  Allow initdb to set group read access to the data directory (David Steele)

  This is accomplished with the new initdb option --allow-group-access. Administrators can also set group permissions on the empty data directory before running initdb. Server variable data_directory_mode allows reading of data directory group permissions.

  Original release occurrence · 11.0/changes/113

• Add pg_verify_checksums tool to verify database checksums while offline Features

  Add pg_verify_checksums tool to verify database checksums while offline (Magnus Hagander)

  Original release occurrence · 11.0/changes/114

• Allow pg_resetwal to change the WAL segment size via --wal-segsize Features

  Allow pg_resetwal to change the WAL segment size via --wal-segsize (Nathan Bossart)

  Original release occurrence · 11.0/changes/115

• Add long options to pg_resetwal and pg_controldata Features

  Add long options to pg_resetwal and pg_controldata (Nathan Bossart, Peter Eisentraut)

  Original release occurrence · 11.0/changes/116

• Add pg_receivewal option --no-sync to prevent synchronous WAL writes, for testing Features

  Add pg_receivewal option --no-sync to prevent synchronous WAL writes, for testing (Michael Paquier)

  Original release occurrence · 11.0/changes/117

• Add pg_receivewal option --endpos to specify when WAL receiving should stop Features

  Add pg_receivewal option --endpos to specify when WAL receiving should stop (Michael Paquier)

  Original release occurrence · 11.0/changes/118

• Allow pg_ctl to send the SIGKILL signal to processes Features

  Allow pg_ctl to send the SIGKILL signal to processes (Andres Freund)

  This was previously unsupported due to concerns over possible misuse.

  Original release occurrence · 11.0/changes/119

• Reduce the number of files copied by pg_rewind Features

  Reduce the number of files copied by pg_rewind (Michael Paquier)

  Original release occurrence · 11.0/changes/120

• Prevent pg_rewind from running as root Features

  Prevent pg_rewind from running as root (Michael Paquier)

  Original release occurrence · 11.0/changes/121

• Add pg_dumpall option --encoding to control output encoding Features

  Add pg_dumpall option --encoding to control output encoding (Michael Paquier)

  pg_dump already had this option.

  Original release occurrence · 11.0/changes/122

• Add pg_dump option --load-via-partition-root to force loading of data into the partition's root table, rather than the original partition Features

  Add pg_dump option --load-via-partition-root to force loading of data into the partition's root table, rather than the original partition (Rushabh Lathia)

  This is useful if the system to be loaded to has different collation definitions or endianness, possibly requiring rows to be stored in different partitions than previously.

  Original release occurrence · 11.0/changes/123

• Add an option to suppress dumping and restoring database object comments Features

  Add an option to suppress dumping and restoring database object comments (Robins Tharakan)

  The new pg_dump, pg_dumpall, and pg_restore option is --no-comments.

  Original release occurrence · 11.0/changes/124

• Add PGXS support for installing include files Features

  Add PGXS support for installing include files (Andrew Gierth)

  This supports creating extension modules that depend on other modules. Formerly there was no easy way for the dependent module to find the referenced one's include files. Several existing contrib modules that define data types have been adjusted to install relevant files. Also, PL/Perl and PL/Python now install their include files, to support creation of transform modules for those languages.

  Original release occurrence · 11.0/changes/125

• Install errcodes.txt to allow extensions to access the list of error codes known to PostgreSQL Features

  Install errcodes.txt to allow extensions to access the list of error codes known to PostgreSQL (Thomas Munro)

  Original release occurrence · 11.0/changes/126

• Convert documentation to DocBook XML Features

  Convert documentation to DocBook XML (Peter Eisentraut, Alexander Lakhin, Jürgen Purtz)

  The file names still use an sgml extension for compatibility with back branches.

  Original release occurrence · 11.0/changes/127

• Use stdbool.h to define type bool on platforms where it's suitable, which is most Features

  Use stdbool.h to define type bool on platforms where it's suitable, which is most (Peter Eisentraut)

  This eliminates a coding hazard for extension modules that need to include stdbool.h.

  Original release occurrence · 11.0/changes/128

• Overhaul the way that initial system catalog contents are defined Features

  Overhaul the way that initial system catalog contents are defined (John Naylor)

  The initial data is now represented in Perl data structures, making it much easier to manipulate mechanically.

  Original release occurrence · 11.0/changes/129

• Prevent extensions from creating custom server parameters that take a quoted list of values Features

  Prevent extensions from creating custom server parameters that take a quoted list of values (Tom Lane)

  This cannot be supported at present because knowledge of the parameter's property would be required even before the extension is loaded.

  Original release occurrence · 11.0/changes/130

• Add ability to use channel binding when using SCRAM authentication Features

  Add ability to use channel binding when using SCRAM authentication (Michael Paquier)

  Channel binding is intended to prevent man-in-the-middle attacks, but SCRAM cannot prevent them unless it can be forced to be active. Unfortunately, there is no way to do that in libpq. Support for it is expected in future versions of libpq and in interfaces not built using libpq, e.g., JDBC.

  Original release occurrence · 11.0/changes/131

• Allow background workers to attach to databases that normally disallow connections Features

  Allow background workers to attach to databases that normally disallow connections (Magnus Hagander)

  Original release occurrence · 11.0/changes/132

• Add support for hardware CRC calculations on ARMv8 Features

  Add support for hardware CRC calculations on ARMv8 (Yuqi Gu, Heikki Linnakangas, Thomas Munro)

  Original release occurrence · 11.0/changes/133

• Speed up lookups of built-in functions by OID Performance

  Speed up lookups of built-in functions by OID (Andres Freund)

  The previous binary search has been replaced by a lookup array.

  Original release occurrence · 11.0/changes/134

• Speed up construction of query results Performance

  Speed up construction of query results (Andres Freund)

  Original release occurrence · 11.0/changes/135

• Improve speed of access to system caches Features

  Improve speed of access to system caches (Andres Freund)

  Original release occurrence · 11.0/changes/136

• Add a generational memory allocator which is optimized for serial allocation/deallocation Features

  Add a generational memory allocator which is optimized for serial allocation/deallocation (Tomas Vondra)

  This reduces memory usage for logical decoding.

  Original release occurrence · 11.0/changes/137

• Make the computation of pg_class.reltuples by VACUUM consistent with its computation by ANALYZE Features

  Make the computation of pg_class.reltuples by VACUUM consistent with its computation by ANALYZE (Tomas Vondra)

  Original release occurrence · 11.0/changes/138

• Update to use perltidy version 20170521 Features

  Update to use perltidy version 20170521 (Tom Lane, Peter Eisentraut)

  Original release occurrence · 11.0/changes/139

• Allow extension pg_prewarm to restore the previous shared buffer contents on startup Features

  Allow extension pg_prewarm to restore the previous shared buffer contents on startup (Mithun Cy, Robert Haas)

  This is accomplished by having pg_prewarm store the shared buffers' relation and block number data to disk occasionally during server operation, and at shutdown.

  Original release occurrence · 11.0/changes/140

• Add pg_trgm function strict_word_similarity() to compute the similarity of whole words Features

  Add pg_trgm function strict_word_similarity() to compute the similarity of whole words (Alexander Korotkov)

  The function word_similarity() already existed for this purpose, but it was designed to find similar parts of words, while strict_word_similarity() computes the similarity to whole words.

  Original release occurrence · 11.0/changes/141

• Allow btree_gin to index bool, bpchar, name and uuid data types Features

  Allow btree_gin to index bool, bpchar, name and uuid data types (Matheus Oliveira)

  Original release occurrence · 11.0/changes/142

• Allow cube and seg extensions to perform index-only scans using GiST indexes Features

  Allow cube and seg extensions to perform index-only scans using GiST indexes (Andrey Borodin)

  Original release occurrence · 11.0/changes/143

• Allow retrieval of negative cube coordinates using the ~> operator Features

  Allow retrieval of negative cube coordinates using the ~> operator (Alexander Korotkov)

  This is useful for KNN-GiST searches when looking for coordinates in descending order.

  Original release occurrence · 11.0/changes/144

• Add Vietnamese letter handling to the unaccent extension Features

  Add Vietnamese letter handling to the unaccent extension (Dang Minh Huong, Michael Paquier)

  Original release occurrence · 11.0/changes/145

• Enhance amcheck to check that each heap tuple has an index entry Features

  Enhance amcheck to check that each heap tuple has an index entry (Peter Geoghegan)

  Original release occurrence · 11.0/changes/146

• Have adminpack use the new default file system access roles Features

  Have adminpack use the new default file system access roles (Stephen Frost)

  Previously, only superusers could call adminpack functions; now role permissions are checked.

  Original release occurrence · 11.0/changes/147

• Widen pg_stat_statement's query ID to 64 bits Features

  Widen pg_stat_statement's query ID to 64 bits (Robert Haas)

  This greatly reduces the chance of query ID hash collisions. The query ID can now potentially display as a negative value.

  Original release occurrence · 11.0/changes/148

• Remove the contrib/start-scripts/osx scripts since they are no longer recommended (use contrib/start-scripts/macos instead) Features

  Remove the contrib/start-scripts/osx scripts since they are no longer recommended (use contrib/start-scripts/macos instead) (Tom Lane)

  Original release occurrence · 11.0/changes/149

• Remove the chkpass extension Features

  Remove the chkpass extension (Peter Eisentraut)

  This extension is no longer considered to be a usable security tool or example of how to write an extension.

  Original release occurrence · 11.0/changes/150

Security evidence

38 records from the official security matrix and release-note mentions. A fixed version is shown only when the security snapshot explicitly names this branch. A mention alone does not establish applicability or a new fix.