PostgreSQL 13

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 13.0, including feature changes and compatibility notes. Categories aid browsing; they are not upstream classifications.

178 of 178 original entries.

• Change SIMILAR TO ... ESCAPE NULL to return NULL Compatibility Migration

  Change SIMILAR TO ... ESCAPE NULL to return NULL (Tom Lane) §

  This new behavior matches the SQL specification. Previously a null ESCAPE value was taken to mean using the default escape string (a backslash character). This also applies to substring(text FROM pattern ESCAPE text). The previous behavior has been retained in old views by keeping the original function unchanged.

  Original release occurrence · 13.0/migration/001

• Make json[b]_to_tsvector() fully check the spelling of its string option Compatibility Migration

  Make json[b]_to_tsvector() fully check the spelling of its string option (Dominik Czarnota) §

  Original release occurrence · 13.0/migration/002

• Change the way non-default effective_io_concurrency values affect concurrency Compatibility Migration

  Change the way non-default effective_io_concurrency values affect concurrency (Thomas Munro) §

  Previously, this value was adjusted before setting the number of concurrent requests. The value is now used directly. Conversion of old values to new ones can be done using:

  SELECT round(sum(OLDVALUE / n::float)) AS newvalue FROM generate_series(1, OLDVALUE) s(n);
  

  Original release occurrence · 13.0/migration/003

• Prevent display of auxiliary processes in pg_stat_ssl and pg_stat_gssapi system views Compatibility Migration

  Prevent display of auxiliary processes in pg_stat_ssl and pg_stat_gssapi system views (Euler Taveira) §

  Queries that join these views to pg_stat_activity and wish to see auxiliary processes will need to use left joins.

  Original release occurrence · 13.0/migration/004

• Rename various wait events to improve consistency Compatibility Migration

  Rename various wait events to improve consistency (Fujii Masao, Tom Lane) § § § § §

  Original release occurrence · 13.0/migration/005

• Fix ALTER FOREIGN TABLE ... RENAME COLUMN to return a more appropriate command tag Compatibility Migration

  Fix ALTER FOREIGN TABLE ... RENAME COLUMN to return a more appropriate command tag (Fujii Masao) §

  Previously it returned ALTER TABLE; now it returns ALTER FOREIGN TABLE.

  Original release occurrence · 13.0/migration/006

• Fix ALTER MATERIALIZED VIEW ... RENAME COLUMN to return a more appropriate command tag Compatibility Migration

  Fix ALTER MATERIALIZED VIEW ... RENAME COLUMN to return a more appropriate command tag (Fujii Masao) §

  Previously it returned ALTER TABLE; now it returns ALTER MATERIALIZED VIEW.

  Original release occurrence · 13.0/migration/007

• Rename configuration parameter wal_keep_segments to wal_keep_size Compatibility Migration

  Rename configuration parameter wal_keep_segments to wal_keep_size (Fujii Masao) §

  This determines how much WAL to retain for standby servers. It is specified in megabytes, rather than number of files as with the old parameter. If you previously used wal_keep_segments, the following formula will give you an approximately equivalent setting:

  wal_keep_size = wal_keep_segments * wal_segment_size (typically 16MB)
  

  Original release occurrence · 13.0/migration/008

• Remove support for defining operator classes using pre-PostgreSQL 8.0 syntax Compatibility Migration

  Remove support for defining operator classes using pre-PostgreSQL 8.0 syntax (Daniel Gustafsson) §

  Original release occurrence · 13.0/migration/009

• Remove support for defining foreign key constraints using pre-PostgreSQL 7.3 syntax Compatibility Migration

  Remove support for defining foreign key constraints using pre-PostgreSQL 7.3 syntax (Daniel Gustafsson) §

  Original release occurrence · 13.0/migration/010

• Remove support for "opaque" pseudo-types used by pre-PostgreSQL 7.3 servers Compatibility Migration

  Remove support for "opaque" pseudo-types used by pre-PostgreSQL 7.3 servers (Daniel Gustafsson) §

  Original release occurrence · 13.0/migration/011

• Remove support for upgrading unpackaged (pre-9.1) extensions Compatibility Migration

  Remove support for upgrading unpackaged (pre-9.1) extensions (Tom Lane) §

  The FROM option of CREATE EXTENSION is no longer supported. Any installations still using unpackaged extensions should upgrade them to a packaged version before updating to PostgreSQL 13.

  Original release occurrence · 13.0/migration/012

• Remove support for posixrules files in the timezone database Compatibility Migration

  Remove support for posixrules files in the timezone database (Tom Lane) §

  IANA's timezone group has deprecated this feature, meaning that it will gradually disappear from systems' timezone databases over the next few years. Rather than have a behavioral change appear unexpectedly with a timezone data update, we have removed PostgreSQL's support for this feature as of version 13. This affects only the behavior of POSIX-style time zone specifications that lack an explicit daylight savings transition rule; formerly the transition rule could be determined by installing a custom posixrules file, but now it is hard-wired. The recommended fix for any affected installations is to start using a geographical time zone name.

  Original release occurrence · 13.0/migration/013

• In ltree, when an lquery pattern contains adjacent asterisks with braces, e.g., *{2}.*{3}, properly interpret that as *{5} Compatibility Migration

  In ltree, when an lquery pattern contains adjacent asterisks with braces, e.g., *{2}.*{3}, properly interpret that as *{5} (Nikita Glukhov) §

  Original release occurrence · 13.0/migration/014

• Fix pageinspect's bt_metap() to return more appropriate data types that are less likely to overflow Compatibility Migration

  Fix pageinspect's bt_metap() to return more appropriate data types that are less likely to overflow (Peter Geoghegan) §

  Original release occurrence · 13.0/migration/015

• Allow pruning of partitions to happen in more cases Features

  Allow pruning of partitions to happen in more cases (Yuzuko Hosoya, Amit Langote, Álvaro Herrera) § §

  Original release occurrence · 13.0/changes/001

• Allow partitionwise joins to happen in more cases Features

  Allow partitionwise joins to happen in more cases (Ashutosh Bapat, Etsuro Fujita, Amit Langote, Tom Lane) § §

  For example, partitionwise joins can now happen between partitioned tables even when their partition bounds do not match exactly.

  Original release occurrence · 13.0/changes/002

• Support row-level BEFORE triggers on partitioned tables Features

  Support row-level BEFORE triggers on partitioned tables (Álvaro Herrera) §

  However, such a trigger is not allowed to change which partition is the destination.

  Original release occurrence · 13.0/changes/003

• Allow partitioned tables to be logically replicated via publications Features

  Allow partitioned tables to be logically replicated via publications (Amit Langote) § §

  Previously, partitions had to be replicated individually. Now a partitioned table can be published explicitly, causing all its partitions to be published automatically. Addition/removal of a partition causes it to be likewise added to or removed from the publication. The CREATE PUBLICATION option publish_via_partition_root controls whether changes to partitions are published as their own changes or their parent's.

  Original release occurrence · 13.0/changes/004

• Allow logical replication into partitioned tables on subscribers Features

  Allow logical replication into partitioned tables on subscribers (Amit Langote) §

  Previously, subscribers could only receive rows into non-partitioned tables.

  Original release occurrence · 13.0/changes/005

• Allow whole-row variables (that is, table.*) to be used in partitioning expressions Features

  Allow whole-row variables (that is, table.*) to be used in partitioning expressions (Amit Langote) §

  Original release occurrence · 13.0/changes/006

• More efficiently store duplicates in B-tree indexes Features

  More efficiently store duplicates in B-tree indexes (Anastasia Lubennikova, Peter Geoghegan) §

  This allows efficient B-tree indexing of low-cardinality columns by storing duplicate keys only once. Users upgrading with pg_upgrade will need to use REINDEX to make an existing index use this feature.

  Original release occurrence · 13.0/changes/007

• Allow GiST and SP-GiST indexes on box columns to support ORDER BY box <-> point queries Features

  Allow GiST and SP-GiST indexes on box columns to support ORDER BY box <-> point queries (Nikita Glukhov) § §

  Original release occurrence · 13.0/changes/008

• Allow GIN indexes to more efficiently handle ! (NOT) clauses in tsquery searches Features

  Allow GIN indexes to more efficiently handle ! (NOT) clauses in tsquery searches (Nikita Glukhov, Alexander Korotkov, Tom Lane, Julien Rouhaud) §

  Original release occurrence · 13.0/changes/009

• Allow index operator classes to take parameters Features

  Allow index operator classes to take parameters (Nikita Glukhov) §

  Original release occurrence · 13.0/changes/010

• Allow CREATE INDEX to specify the GiST signature length and maximum number of integer ranges Features

  Allow CREATE INDEX to specify the GiST signature length and maximum number of integer ranges (Nikita Glukhov) §

  Indexes created on four and eight-byte integer array, tsvector, pg_trgm, ltree, and hstore columns can now control these GiST index parameters, rather than using the defaults.

  Original release occurrence · 13.0/changes/011

• Prevent indexes that use non-default collations from being added as a table's unique or primary key constraint Features

  Prevent indexes that use non-default collations from being added as a table's unique or primary key constraint (Tom Lane) §

  The index's collation must match that of the underlying column, but ALTER TABLE previously failed to check this.

  Original release occurrence · 13.0/changes/012

• Improve the optimizer's selectivity estimation for containment/match operators Features

  Improve the optimizer's selectivity estimation for containment/match operators (Tom Lane) §

  Original release occurrence · 13.0/changes/013

• Allow setting the statistics target for extended statistics Features

  Allow setting the statistics target for extended statistics (Tomas Vondra) §

  This is controlled with the new command option ALTER STATISTICS ... SET STATISTICS. Previously this was computed based on more general statistics target settings.

  Original release occurrence · 13.0/changes/014

• Allow use of multiple extended statistics objects in a single query Features

  Allow use of multiple extended statistics objects in a single query (Tomas Vondra) § §

  Original release occurrence · 13.0/changes/015

• Allow use of extended statistics objects for OR clauses and IN/ANY constant lists Features

  Allow use of extended statistics objects for OR clauses and IN/ANY constant lists (Pierre Ducroquet, Tomas Vondra) § § §

  Original release occurrence · 13.0/changes/016

• Allow functions in FROM clauses to be pulled up (inlined) if they evaluate to constants Features

  Allow functions in FROM clauses to be pulled up (inlined) if they evaluate to constants (Alexander Kuzmenkov, Aleksandr Parfenov) § §

  Original release occurrence · 13.0/changes/017

• Implement incremental sorting Performance

  Implement incremental sorting (James Coleman, Alexander Korotkov, Tomas Vondra) § §

  If an intermediate query result is known to be sorted by one or more leading keys of a required sort ordering, the additional sorting can be done considering only the remaining keys, if the rows are sorted in batches that have equal leading keys.

  If necessary, this can be controlled using enable_incremental_sort.

  Original release occurrence · 13.0/changes/018

• Improve the performance of sorting inet values Performance

  Improve the performance of sorting inet values (Brandur Leach) §

  Original release occurrence · 13.0/changes/019

• Allow hash aggregation to use disk storage for large aggregation result sets Performance

  Allow hash aggregation to use disk storage for large aggregation result sets (Jeff Davis) § § §

  Previously, hash aggregation was avoided if it was expected to use more than work_mem memory. Now, a hash aggregation plan can be chosen despite that. The hash table will be spilled to disk if it exceeds work_mem times hash_mem_multiplier.

  This behavior is normally preferable to the old behavior, in which once hash aggregation had been chosen, the hash table would be kept in memory no matter how large it got — which could be very large if the planner had misestimated. If necessary, behavior similar to that can be obtained by increasing hash_mem_multiplier.

  Original release occurrence · 13.0/changes/020

• Allow inserts, not only updates and deletes, to trigger vacuuming activity in autovacuum Performance

  Allow inserts, not only updates and deletes, to trigger vacuuming activity in autovacuum (Laurenz Albe, Darafei Praliaskouski) §

  Previously, insert-only activity would trigger auto-analyze but not auto-vacuum, on the grounds that there could not be any dead tuples to remove. However, a vacuum scan has other useful side-effects such as setting page-all-visible bits, which improves the efficiency of index-only scans. Also, allowing an insert-only table to receive periodic vacuuming helps to spread out the work of “freezing” old tuples, so that there is not suddenly a large amount of freezing work to do when the entire table reaches the anti-wraparound threshold all at once.

  If necessary, this behavior can be adjusted with the new parameters autovacuum_vacuum_insert_threshold and autovacuum_vacuum_insert_scale_factor, or the equivalent table storage options.

  Original release occurrence · 13.0/changes/021

• Add maintenance_io_concurrency parameter to control I/O concurrency for maintenance operations Performance

  Add maintenance_io_concurrency parameter to control I/O concurrency for maintenance operations (Thomas Munro) §

  Original release occurrence · 13.0/changes/022

• Allow WAL writes to be skipped during a transaction that creates or rewrites a relation, if wal_level is minimal Performance

  Allow WAL writes to be skipped during a transaction that creates or rewrites a relation, if wal_level is minimal (Kyotaro Horiguchi) §

  Relations larger than wal_skip_threshold will have their files fsync'ed rather than generating WAL. Previously this was done only for COPY operations, but the implementation had a bug that could cause data loss during crash recovery.

  Original release occurrence · 13.0/changes/023

• Improve performance when replaying DROP DATABASE commands when many tablespaces are in use Performance

  Improve performance when replaying DROP DATABASE commands when many tablespaces are in use (Fujii Masao) §

  Original release occurrence · 13.0/changes/024

• Improve performance for truncation of very large relations Performance

  Improve performance for truncation of very large relations (Kirk Jamison) §

  Original release occurrence · 13.0/changes/025

• Improve retrieval of the leading bytes of TOAST'ed values Performance

  Improve retrieval of the leading bytes of TOAST'ed values (Binguo Bao, Andrey Borodin) §

  Previously, compressed out-of-line TOAST values were fully fetched even when it's known that only some leading bytes are needed. Now, only enough data to produce the result is fetched.

  Original release occurrence · 13.0/changes/026

• Improve performance of LISTEN/NOTIFY Performance

  Improve performance of LISTEN/NOTIFY (Martijn van Oosterhout, Tom Lane) § § §

  Original release occurrence · 13.0/changes/027

• Speed up conversions of integers to text Performance

  Speed up conversions of integers to text (David Fetter) §

  Original release occurrence · 13.0/changes/028

• Reduce memory usage for query strings and extension scripts that contain many SQL statements Performance

  Reduce memory usage for query strings and extension scripts that contain many SQL statements (Amit Langote) §

  Original release occurrence · 13.0/changes/029

• Allow EXPLAIN, auto_explain, autovacuum, and pg_stat_statements to track WAL usage statistics Features

  Allow EXPLAIN, auto_explain, autovacuum, and pg_stat_statements to track WAL usage statistics (Kirill Bychik, Julien Rouhaud) § § §

  Original release occurrence · 13.0/changes/030

• Allow a sample of SQL statements, rather than all statements, to be logged Features

  Allow a sample of SQL statements, rather than all statements, to be logged (Adrien Nayrat) §

  A log_statement_sample_rate fraction of those statements taking more than log_min_duration_sample duration will be logged.

  Original release occurrence · 13.0/changes/031

• Add the backend type to csvlog and optionally log_line_prefix log output Features

  Add the backend type to csvlog and optionally log_line_prefix log output (Peter Eisentraut) §

  Original release occurrence · 13.0/changes/032

• Improve control of prepared statement parameter logging Features

  Improve control of prepared statement parameter logging (Alexey Bashtanov, Álvaro Herrera) § §

  The GUC setting log_parameter_max_length controls the maximum length of parameter values output during logging of non-error statements, while log_parameter_max_length_on_error does the same for logging of statements with errors. Previously, prepared statement parameters were never logged during errors.

  Original release occurrence · 13.0/changes/033

• Allow function call backtraces to be logged after errors Features

  Allow function call backtraces to be logged after errors (Peter Eisentraut, Álvaro Herrera) § §

  The new parameter backtrace_functions specifies which C functions should generate backtraces on error.

  Original release occurrence · 13.0/changes/034

• Make vacuum buffer counters 64-bits wide to avoid overflow Bug fixes

  Make vacuum buffer counters 64-bits wide to avoid overflow (Álvaro Herrera) §

  Original release occurrence · 13.0/changes/035

• Add leader_pid to pg_stat_activity to report a parallel worker's leader process Features

  Add leader_pid to pg_stat_activity to report a parallel worker's leader process (Julien Rouhaud) §

  Original release occurrence · 13.0/changes/036

• Add system view pg_stat_progress_basebackup to report the progress of streaming base backups Features

  Add system view pg_stat_progress_basebackup to report the progress of streaming base backups (Fujii Masao) §

  Original release occurrence · 13.0/changes/037

• Add system view pg_stat_progress_analyze to report ANALYZE progress Features

  Add system view pg_stat_progress_analyze to report ANALYZE progress (Álvaro Herrera, Tatsuro Yamada, Vinayak Pokale) §

  Original release occurrence · 13.0/changes/038

• Add system view pg_shmem_allocations to display shared memory usage Features

  Add system view pg_shmem_allocations to display shared memory usage (Andres Freund, Robert Haas) §

  Original release occurrence · 13.0/changes/039

• Add system view pg_stat_slru to monitor internal SLRU caches Features

  Add system view pg_stat_slru to monitor internal SLRU caches (Tomas Vondra) §

  Original release occurrence · 13.0/changes/040

• Allow track_activity_query_size to be set as high as 1MB Features

  Allow track_activity_query_size to be set as high as 1MB (Vyacheslav Makarov) §

  The previous maximum was 100kB.

  Original release occurrence · 13.0/changes/041

• Report a wait event while creating a DSM segment with posix_fallocate() Features

  Report a wait event while creating a DSM segment with posix_fallocate() (Thomas Munro) §

  Original release occurrence · 13.0/changes/042

• Add wait event VacuumDelay to report on cost-based vacuum delay Features

  Add wait event VacuumDelay to report on cost-based vacuum delay (Justin Pryzby) §

  Original release occurrence · 13.0/changes/043

• Add wait events for WAL archive and recovery pause Features

  Add wait events for WAL archive and recovery pause (Fujii Masao) §

  The new events are BackupWaitWalArchive and RecoveryPause.

  Original release occurrence · 13.0/changes/044

• Add wait events RecoveryConflictSnapshot and RecoveryConflictTablespace to monitor recovery conflicts Features

  Add wait events RecoveryConflictSnapshot and RecoveryConflictTablespace to monitor recovery conflicts (Masahiko Sawada) §

  Original release occurrence · 13.0/changes/045

• Improve performance of wait events on BSD-based systems Performance

  Improve performance of wait events on BSD-based systems (Thomas Munro) §

  Original release occurrence · 13.0/changes/046

• Allow only superusers to view the ssl_passphrase_command setting Features

  Allow only superusers to view the ssl_passphrase_command setting (Insung Moon) §

  This was changed as a security precaution.

  Original release occurrence · 13.0/changes/047

• Change the server's default minimum TLS version for encrypted connections from 1.0 to 1.2 Features

  Change the server's default minimum TLS version for encrypted connections from 1.0 to 1.2 (Peter Eisentraut) §

  This choice can be controlled by ssl_min_protocol_version.

  Original release occurrence · 13.0/changes/048

• Tighten rules on which utility commands are allowed in read-only transaction mode Features

  Tighten rules on which utility commands are allowed in read-only transaction mode (Robert Haas) §

  This change also increases the number of utility commands that can run in parallel queries.

  Original release occurrence · 13.0/changes/049

• Allow allow_system_table_mods to be changed after server start Features

  Allow allow_system_table_mods to be changed after server start (Peter Eisentraut) §

  Original release occurrence · 13.0/changes/050

• Disallow non-superusers from modifying system tables when allow_system_table_mods is set Features

  Disallow non-superusers from modifying system tables when allow_system_table_mods is set (Peter Eisentraut) §

  Previously, if allow_system_table_mods was set at server start, non-superusers could issue INSERT/UPDATE/DELETE commands on system tables.

  Original release occurrence · 13.0/changes/051

• Enable support for Unix-domain sockets on Windows Features

  Enable support for Unix-domain sockets on Windows (Peter Eisentraut) §

  Original release occurrence · 13.0/changes/052

• Allow streaming replication configuration settings to be changed by reload Features

  Allow streaming replication configuration settings to be changed by reload (Sergei Kornilov) §

  Previously, a server restart was required to change primary_conninfo and primary_slot_name.

  Original release occurrence · 13.0/changes/053

• Allow WAL receivers to use a temporary replication slot when a permanent one is not specified Features

  Allow WAL receivers to use a temporary replication slot when a permanent one is not specified (Peter Eisentraut, Sergei Kornilov) § §

  This behavior can be enabled using wal_receiver_create_temp_slot.

  Original release occurrence · 13.0/changes/054

• Allow WAL storage for replication slots to be limited by max_slot_wal_keep_size Features

  Allow WAL storage for replication slots to be limited by max_slot_wal_keep_size (Kyotaro Horiguchi) §

  Replication slots that would require exceeding this value are marked invalid.

  Original release occurrence · 13.0/changes/055

• Allow standby promotion to cancel any requested pause Features

  Allow standby promotion to cancel any requested pause (Fujii Masao) §

  Previously, promotion could not happen while the standby was in paused state.

  Original release occurrence · 13.0/changes/056

• Generate an error if recovery does not reach the specified recovery target Features

  Generate an error if recovery does not reach the specified recovery target (Leif Gunnar Erlandsen, Peter Eisentraut) §

  Previously, a standby would promote itself upon reaching the end of WAL, even if the target was not reached.

  Original release occurrence · 13.0/changes/057

• Allow control over how much memory is used by logical decoding before it is spilled to disk Features

  Allow control over how much memory is used by logical decoding before it is spilled to disk (Tomas Vondra, Dilip Kumar, Amit Kapila) §

  This is controlled by logical_decoding_work_mem.

  Original release occurrence · 13.0/changes/058

• Allow recovery to continue even if invalid pages are referenced by WAL Features

  Allow recovery to continue even if invalid pages are referenced by WAL (Fujii Masao) §

  This is enabled using ignore_invalid_pages.

  Original release occurrence · 13.0/changes/059

• Allow VACUUM to process a table's indexes in parallel Features

  Allow VACUUM to process a table's indexes in parallel (Masahiko Sawada, Amit Kapila) §

  The new PARALLEL option controls this.

  Original release occurrence · 13.0/changes/060

• Allow FETCH FIRST to use WITH TIES to return any additional rows that match the last result row Features

  Allow FETCH FIRST to use WITH TIES to return any additional rows that match the last result row (Surafel Temesgen) §

  Original release occurrence · 13.0/changes/061

• Report planning-time buffer usage in EXPLAIN's BUFFER output Features

  Report planning-time buffer usage in EXPLAIN's BUFFER output (Julien Rouhaud) §

  Original release occurrence · 13.0/changes/062

• Make CREATE TABLE LIKE propagate a CHECK constraint's NO INHERIT property to the created table Features

  Make CREATE TABLE LIKE propagate a CHECK constraint's NO INHERIT property to the created table (Ildar Musin, Chris Travers) §

  Original release occurrence · 13.0/changes/063

• When using LOCK TABLE on a partitioned table, do not check permissions on the child tables Features

  When using LOCK TABLE on a partitioned table, do not check permissions on the child tables (Amit Langote) §

  Original release occurrence · 13.0/changes/064

• Allow OVERRIDING USER VALUE on inserts into identity columns Features

  Allow OVERRIDING USER VALUE on inserts into identity columns (Dean Rasheed) §

  Original release occurrence · 13.0/changes/065

• Add ALTER TABLE ... DROP EXPRESSION to allow removing the GENERATED property from a column Features

  Add ALTER TABLE ... DROP EXPRESSION to allow removing the GENERATED property from a column (Peter Eisentraut) §

  Original release occurrence · 13.0/changes/066

• Fix bugs in multi-step ALTER TABLE commands Bug fixes

  Fix bugs in multi-step ALTER TABLE commands (Tom Lane) §

  IF NOT EXISTS clauses now work as expected, in that derived actions (such as index creation) do not execute if the column already exists. Also, certain cases of combining related actions into one ALTER TABLE now work when they did not before.

  Original release occurrence · 13.0/changes/067

• Add ALTER VIEW syntax to rename view columns Features

  Add ALTER VIEW syntax to rename view columns (Fujii Masao) §

  Renaming view columns was already possible, but one had to write ALTER TABLE RENAME COLUMN, which is confusing.

  Original release occurrence · 13.0/changes/068

• Add ALTER TYPE options to modify a base type's TOAST properties and support functions Features

  Add ALTER TYPE options to modify a base type's TOAST properties and support functions (Tomas Vondra, Tom Lane) §

  Original release occurrence · 13.0/changes/069

• Add CREATE DATABASE LOCALE option Features

  Add CREATE DATABASE LOCALE option (Peter Eisentraut) §

  This combines the existing options LC_COLLATE and LC_CTYPE into a single option.

  Original release occurrence · 13.0/changes/070

• Allow DROP DATABASE to disconnect sessions using the target database, allowing the drop to succeed Features

  Allow DROP DATABASE to disconnect sessions using the target database, allowing the drop to succeed (Pavel Stehule, Amit Kapila) §

  This is enabled by the FORCE option.

  Original release occurrence · 13.0/changes/071

• Add structure member tg_updatedcols to allow C-language update triggers to know which column(s) were updated Features

  Add structure member tg_updatedcols to allow C-language update triggers to know which column(s) were updated (Peter Eisentraut) §

  Original release occurrence · 13.0/changes/072

• Add polymorphic data types for use by functions requiring compatible arguments Features

  Add polymorphic data types for use by functions requiring compatible arguments (Pavel Stehule) §

  The new data types are anycompatible, anycompatiblearray, anycompatiblenonarray, and anycompatiblerange.

  Original release occurrence · 13.0/changes/073

• Add SQL data type xid8 to expose FullTransactionId Features

  Add SQL data type xid8 to expose FullTransactionId (Thomas Munro) §

  The existing xid data type is only four bytes so it does not provide the transaction epoch.

  Original release occurrence · 13.0/changes/074

• Add data type regcollation and associated functions, to represent OIDs of collation objects Features

  Add data type regcollation and associated functions, to represent OIDs of collation objects (Julien Rouhaud) §

  Original release occurrence · 13.0/changes/075

• Use the glibc version in some cases as a collation version identifier Features

  Use the glibc version in some cases as a collation version identifier (Thomas Munro) §

  If the glibc version changes, a warning will be issued about possible corruption of collation-dependent indexes.

  Original release occurrence · 13.0/changes/076

• Add support for collation versions on Windows Features

  Add support for collation versions on Windows (Thomas Munro) §

  Original release occurrence · 13.0/changes/077

• Allow ROW expressions to have their members extracted with suffix notation Features

  Allow ROW expressions to have their members extracted with suffix notation (Tom Lane) §

  For example, (ROW(4, 5.0)).f1 now returns 4.

  Original release occurrence · 13.0/changes/078

• Add alternate version of jsonb_set() with improved NULL handling Features

  Add alternate version of jsonb_set() with improved NULL handling (Andrew Dunstan) §

  The new function, jsonb_set_lax(), handles a NULL new value by either setting the specified key to a JSON null, deleting the key, raising an exception, or returning the jsonb value unmodified, as requested.

  Original release occurrence · 13.0/changes/079

• Add jsonpath .datetime() method Features

  Add jsonpath .datetime() method (Nikita Glukhov, Teodor Sigaev, Oleg Bartunov, Alexander Korotkov) §

  This function allows JSON values to be converted to timestamps, which can then be processed in jsonpath expressions. This change also adds jsonpath functions that support time-zone-aware output.

  Original release occurrence · 13.0/changes/080

• Add SQL functions NORMALIZE() to normalize Unicode strings, and IS NORMALIZED to check for normalization Features

  Add SQL functions NORMALIZE() to normalize Unicode strings, and IS NORMALIZED to check for normalization (Peter Eisentraut) §

  Original release occurrence · 13.0/changes/081

• Add min() and max() aggregates for pg_lsn Features

  Add min() and max() aggregates for pg_lsn (Fabrízio de Royes Mello) §

  These are particularly useful in monitoring queries.

  Original release occurrence · 13.0/changes/082

• Allow Unicode escapes, e.g., E'\unnnn' or U&'\nnnn', to specify any character available in the database encoding, even when the database encoding is not UTF-8 Features

  Allow Unicode escapes, e.g., E'\unnnn' or U&'\nnnn', to specify any character available in the database encoding, even when the database encoding is not UTF-8 (Tom Lane) §

  Original release occurrence · 13.0/changes/083

• Allow to_date() and to_timestamp() to recognize non-English month/day names Features

  Allow to_date() and to_timestamp() to recognize non-English month/day names (Juan José Santamaría Flecha, Tom Lane) §

  The names recognized are the same as those output by to_char() with the same format patterns.

  Original release occurrence · 13.0/changes/084

• Add datetime format patterns FF1 – FF6 to specify input or output of 1 to 6 fractional-second digits Features

  Add datetime format patterns FF1 – FF6 to specify input or output of 1 to 6 fractional-second digits (Alexander Korotkov, Nikita Glukhov, Teodor Sigaev, Oleg Bartunov) §

  These patterns can be used by to_char(), to_timestamp(), and jsonpath's .datetime().

  Original release occurrence · 13.0/changes/085

• Add SSSSS datetime format pattern as an SQL-standard alias for SSSS Features

  Add SSSSS datetime format pattern as an SQL-standard alias for SSSS (Nikita Glukhov, Alexander Korotkov) §

  Original release occurrence · 13.0/changes/086

• Add function gen_random_uuid() to generate version-4 UUIDs Features

  Add function gen_random_uuid() to generate version-4 UUIDs (Peter Eisentraut) §

  Previously UUID generation functions were only available in the external modules uuid-ossp and pgcrypto.

  Original release occurrence · 13.0/changes/087

• Add greatest-common-denominator (gcd) and least-common-multiple (lcm) functions Features

  Add greatest-common-denominator (gcd) and least-common-multiple (lcm) functions (Vik Fearing) §

  Original release occurrence · 13.0/changes/088

• Improve the performance and accuracy of the numeric type's square root (sqrt) and natural log (ln) functions Performance

  Improve the performance and accuracy of the numeric type's square root (sqrt) and natural log (ln) functions (Dean Rasheed) § §

  Original release occurrence · 13.0/changes/089

• Add function min_scale() that returns the number of digits to the right of the decimal point that are required to represent a numeric value with full accuracy Features

  Add function min_scale() that returns the number of digits to the right of the decimal point that are required to represent a numeric value with full accuracy (Pavel Stehule) §

  Original release occurrence · 13.0/changes/090

• Add function trim_scale() to reduce the scale of a numeric value by removing trailing zeros Features

  Add function trim_scale() to reduce the scale of a numeric value by removing trailing zeros (Pavel Stehule) §

  Original release occurrence · 13.0/changes/091

• Add commutators of distance operators Features

  Add commutators of distance operators (Nikita Glukhov) §

  For example, previously only point <-> line was supported, now line <-> point works too.

  Original release occurrence · 13.0/changes/092

• Create xid8 versions of all transaction ID functions Features

  Create xid8 versions of all transaction ID functions (Thomas Munro) §

  The old xid-based functions still exist, for backward compatibility.

  Original release occurrence · 13.0/changes/093

• Allow get_bit() and set_bit() to set bits beyond the first 256MB of a bytea value Features

  Allow get_bit() and set_bit() to set bits beyond the first 256MB of a bytea value (Movead Li) §

  Original release occurrence · 13.0/changes/094

• Allow advisory-lock functions to be used in some parallel operations Features

  Allow advisory-lock functions to be used in some parallel operations (Tom Lane) §

  Original release occurrence · 13.0/changes/095

• Add the ability to remove an object's dependency on an extension Features

  Add the ability to remove an object's dependency on an extension (Álvaro Herrera) §

  The object can be a function, materialized view, index, or trigger. The syntax is ALTER .. NO DEPENDS ON.

  Original release occurrence · 13.0/changes/096

• Improve performance of simple PL/pgSQL expressions Performance

  Improve performance of simple PL/pgSQL expressions (Tom Lane, Amit Langote) §

  Original release occurrence · 13.0/changes/097

• Improve performance of PL/pgSQL functions that use immutable expressions Performance

  Improve performance of PL/pgSQL functions that use immutable expressions (Konstantin Knizhnik) §

  Original release occurrence · 13.0/changes/098

• Allow libpq clients to require channel binding for encrypted connections Features

  Allow libpq clients to require channel binding for encrypted connections (Jeff Davis) §

  Using the libpq connection parameter channel_binding forces the other end of the TLS connection to prove it knows the user's password. This prevents man-in-the-middle attacks.

  Original release occurrence · 13.0/changes/099

• Add libpq connection parameters to control the minimum and maximum TLS version allowed for an encrypted connection Features

  Add libpq connection parameters to control the minimum and maximum TLS version allowed for an encrypted connection (Daniel Gustafsson) § § §

  The settings are ssl_min_protocol_version and ssl_max_protocol_version. By default, the minimum TLS version is 1.2 (this represents a behavioral change from previous releases).

  Original release occurrence · 13.0/changes/100

• Allow use of passwords to unlock client certificates Features

  Allow use of passwords to unlock client certificates (Craig Ringer, Andrew Dunstan) §

  This is enabled by libpq's sslpassword connection parameter.

  Original release occurrence · 13.0/changes/101

• Allow libpq to use DER-encoded client certificates Features

  Allow libpq to use DER-encoded client certificates (Craig Ringer, Andrew Dunstan) §

  Original release occurrence · 13.0/changes/102

• Fix ecpg's EXEC SQL elif directive to work correctly Bug fixes

  Fix ecpg's EXEC SQL elif directive to work correctly (Tom Lane) §

  Previously it behaved the same as endif followed by ifdef, so that a successful previous branch of the same if construct did not prevent expansion of the elif branch or following branches.

  Original release occurrence · 13.0/changes/103

• Add transaction status (%x) to psql's default prompts Features

  Add transaction status (%x) to psql's default prompts (Vik Fearing) §

  Original release occurrence · 13.0/changes/104

• Allow the secondary psql prompt to be blank but the same width as the primary prompt Features

  Allow the secondary psql prompt to be blank but the same width as the primary prompt (Thomas Munro) §

  This is accomplished by setting PROMPT2 to %w.

  Original release occurrence · 13.0/changes/105

• Allow psql's \g and \gx commands to change \pset output options for the duration of that single command Features

  Allow psql's \g and \gx commands to change \pset output options for the duration of that single command (Tom Lane) §

  This feature allows syntax like \g (expand=on), which is equivalent to \gx.

  Original release occurrence · 13.0/changes/106

• Add psql commands to display operator classes and operator families Features

  Add psql commands to display operator classes and operator families (Sergey Cherkashin, Nikita Glukhov, Alexander Korotkov) §

  The new commands are \dAc, \dAf, \dAo, and \dAp.

  Original release occurrence · 13.0/changes/107

• Show table persistence in psql's \dt+ and related commands Features

  Show table persistence in psql's \dt+ and related commands (David Fetter) §

  In verbose mode, the table/index/view shows if the object is permanent, temporary, or unlogged.

  Original release occurrence · 13.0/changes/108

• Improve output of psql's \d for TOAST tables Features

  Improve output of psql's \d for TOAST tables (Justin Pryzby) §

  Original release occurrence · 13.0/changes/109

• Fix redisplay after psql's \e command Bug fixes

  Fix redisplay after psql's \e command (Tom Lane) §

  When exiting the editor, if the query doesn't end with a semicolon or \g, the query buffer contents will now be displayed.

  Original release occurrence · 13.0/changes/110

• Add \warn command to psql Features

  Add \warn command to psql (David Fetter) §

  This is like \echo except that the text is sent to stderr instead of stdout.

  Original release occurrence · 13.0/changes/111

• Add the PostgreSQL home page to command-line --help output Features

  Add the PostgreSQL home page to command-line --help output (Peter Eisentraut) §

  Original release occurrence · 13.0/changes/112

• Allow pgbench to partition its “accounts” table Features

  Allow pgbench to partition its “accounts” table (Fabien Coelho) §

  This allows performance testing of partitioning.

  Original release occurrence · 13.0/changes/113

• Add pgbench command \aset, which behaves like \gset, but for multiple queries Features

  Add pgbench command \aset, which behaves like \gset, but for multiple queries (Fabien Coelho) §

  Original release occurrence · 13.0/changes/114

• Allow pgbench to generate its initial data server-side, rather than client-side Features

  Allow pgbench to generate its initial data server-side, rather than client-side (Fabien Coelho) §

  Original release occurrence · 13.0/changes/115

• Allow pgbench to show script contents using option --show-script Features

  Allow pgbench to show script contents using option --show-script (Fabien Coelho) §

  Original release occurrence · 13.0/changes/116

• Generate backup manifests for base backups, and verify them Features

  Generate backup manifests for base backups, and verify them (Robert Haas) § §

  A new tool pg_verifybackup can verify backups.

  Original release occurrence · 13.0/changes/117

• Have pg_basebackup estimate the total backup size by default Features

  Have pg_basebackup estimate the total backup size by default (Fujii Masao) §

  This computation allows pg_stat_progress_basebackup to show progress. If that is not needed, it can be disabled by using the --no-estimate-size option. Previously, this computation happened only if the --progress option was used.

  Original release occurrence · 13.0/changes/118

• Add an option to pg_rewind to configure standbys Features

  Add an option to pg_rewind to configure standbys (Paul Guo, Jimmy Yih, Ashwin Agrawal) §

  This matches pg_basebackup's --write-recovery-conf option.

  Original release occurrence · 13.0/changes/119

• Allow pg_rewind to use the target cluster's restore_command to retrieve needed WAL Features

  Allow pg_rewind to use the target cluster's restore_command to retrieve needed WAL (Alexey Kondratov) §

  This is enabled using the -c/--restore-target-wal option.

  Original release occurrence · 13.0/changes/120

• Have pg_rewind automatically run crash recovery before rewinding Features

  Have pg_rewind automatically run crash recovery before rewinding (Paul Guo, Jimmy Yih, Ashwin Agrawal) §

  This can be disabled by using --no-ensure-shutdown.

  Original release occurrence · 13.0/changes/121

• Increase the PREPARE TRANSACTION-related information reported by pg_waldump Features

  Increase the PREPARE TRANSACTION-related information reported by pg_waldump (Fujii Masao) §

  Original release occurrence · 13.0/changes/122

• Add pg_waldump option --quiet to suppress non-error output Features

  Add pg_waldump option --quiet to suppress non-error output (Andres Freund, Robert Haas) §

  Original release occurrence · 13.0/changes/123

• Add pg_dump option --include-foreign-data to dump data from foreign servers Features

  Add pg_dump option --include-foreign-data to dump data from foreign servers (Luis Carril) §

  Original release occurrence · 13.0/changes/124

• Allow vacuum commands run by vacuumdb to operate in parallel mode Features

  Allow vacuum commands run by vacuumdb to operate in parallel mode (Masahiko Sawada) §

  This is enabled with the new --parallel option.

  Original release occurrence · 13.0/changes/125

• Allow reindexdb to operate in parallel Features

  Allow reindexdb to operate in parallel (Julien Rouhaud) §

  Parallel mode is enabled with the new --jobs option.

  Original release occurrence · 13.0/changes/126

• Allow dropdb to disconnect sessions using the target database, allowing the drop to succeed Features

  Allow dropdb to disconnect sessions using the target database, allowing the drop to succeed (Pavel Stehule) §

  This is enabled with the -f option.

  Original release occurrence · 13.0/changes/127

• Remove --adduser and --no-adduser from createuser Features

  Remove --adduser and --no-adduser from createuser (Alexander Lakhin) §

  The long-supported preferred options for this are called --superuser and --no-superuser.

  Original release occurrence · 13.0/changes/128

• Use the directory of the pg_upgrade program as the default --new-bindir setting when running pg_upgrade Features

  Use the directory of the pg_upgrade program as the default --new-bindir setting when running pg_upgrade (Daniel Gustafsson) §

  Original release occurrence · 13.0/changes/129

• Add a glossary to the documentation Features

  Add a glossary to the documentation (Corey Huinker, Jürgen Purtz, Roger Harkavy, Álvaro Herrera) §

  Original release occurrence · 13.0/changes/130

• Reformat tables containing function and operator information for better clarity Features

  Reformat tables containing function and operator information for better clarity (Tom Lane) §

  Original release occurrence · 13.0/changes/131

• Upgrade to use DocBook 4.5 Features

  Upgrade to use DocBook 4.5 (Peter Eisentraut) §

  Original release occurrence · 13.0/changes/132

• Add support for building on Visual Studio 2019 Features

  Add support for building on Visual Studio 2019 (Haribabu Kommi) §

  Original release occurrence · 13.0/changes/133

• Add build support for MSYS2 Features

  Add build support for MSYS2 (Peter Eisentraut) §

  Original release occurrence · 13.0/changes/134

• Add compare_exchange and fetch_add assembly language code for Power PC compilers Features

  Add compare_exchange and fetch_add assembly language code for Power PC compilers (Noah Misch) §

  Original release occurrence · 13.0/changes/135

• Update Snowball stemmer dictionaries used by full text search Features

  Update Snowball stemmer dictionaries used by full text search (Panagiotis Mavrogiorgos) §

  This adds Greek stemming and improves Danish and French stemming.

  Original release occurrence · 13.0/changes/136

• Remove support for Windows 2000 Features

  Remove support for Windows 2000 (Michael Paquier) §

  Original release occurrence · 13.0/changes/137

• Remove support for non-ELF BSD systems Features

  Remove support for non-ELF BSD systems (Peter Eisentraut) §

  Original release occurrence · 13.0/changes/138

• Remove support for Python versions 2.5.X and earlier Features

  Remove support for Python versions 2.5.X and earlier (Peter Eisentraut) §

  Original release occurrence · 13.0/changes/139

• Remove support for OpenSSL 0.9.8 and 1.0.0 Features

  Remove support for OpenSSL 0.9.8 and 1.0.0 (Michael Paquier) §

  Original release occurrence · 13.0/changes/140

• Remove configure options --disable-float8-byval and --disable-float4-byval Features

  Remove configure options --disable-float8-byval and --disable-float4-byval (Peter Eisentraut) § §

  These were needed for compatibility with some version-zero C functions, but those are no longer supported.

  Original release occurrence · 13.0/changes/141

• Pass the query string to planner hook functions Features

  Pass the query string to planner hook functions (Pascal Legrand, Julien Rouhaud) §

  Original release occurrence · 13.0/changes/142

• Add TRUNCATE command hook Features

  Add TRUNCATE command hook (Yuli Khodorkovskiy) §

  Original release occurrence · 13.0/changes/143

• Add TLS init hook Features

  Add TLS init hook (Andrew Dunstan) §

  Original release occurrence · 13.0/changes/144

• Allow building with no predefined Unix-domain socket directory Features

  Allow building with no predefined Unix-domain socket directory (Peter Eisentraut) §

  Original release occurrence · 13.0/changes/145

• Reduce the probability of SysV resource key collision on Unix platforms Features

  Reduce the probability of SysV resource key collision on Unix platforms (Tom Lane) §

  Original release occurrence · 13.0/changes/146

• Use operating system functions to reliably erase memory that contains sensitive information Features

  Use operating system functions to reliably erase memory that contains sensitive information (Peter Eisentraut) §

  For example, this is used for clearing passwords stored in memory.

  Original release occurrence · 13.0/changes/147

• Add headerscheck script to test C header-file compatibility Features

  Add headerscheck script to test C header-file compatibility (Tom Lane) §

  Original release occurrence · 13.0/changes/148

• Implement internal lists as arrays, rather than a chain of cells Features

  Implement internal lists as arrays, rather than a chain of cells (Tom Lane) §

  This improves performance for queries that access many objects.

  Original release occurrence · 13.0/changes/149

• Change the API for TS_execute() Features

  Change the API for TS_execute() (Tom Lane, Pavel Borisov) § §

  TS_execute callbacks must now provide ternary (yes/no/maybe) logic. Calculating NOT queries accurately is now the default.

  Original release occurrence · 13.0/changes/150

• Allow extensions to be specified as trusted Features

  Allow extensions to be specified as trusted (Tom Lane) §

  Such extensions can be installed in a database by users with database-level CREATE privileges, even if they are not superusers. This change also removes the pg_pltemplate system catalog.

  Original release occurrence · 13.0/changes/151

• Allow non-superusers to connect to postgres_fdw foreign servers without using a password Features

  Allow non-superusers to connect to postgres_fdw foreign servers without using a password (Craig Ringer) §

  Specifically, allow a superuser to set password_required to false for a user mapping. Care must still be taken to prevent non-superusers from using superuser credentials to connect to the foreign server.

  Original release occurrence · 13.0/changes/152

• Allow postgres_fdw to use certificate authentication Features

  Allow postgres_fdw to use certificate authentication (Craig Ringer) §

  Different users can use different certificates.

  Original release occurrence · 13.0/changes/153

• Allow sepgsql to control access to the TRUNCATE command Features

  Allow sepgsql to control access to the TRUNCATE command (Yuli Khodorkovskiy) §

  Original release occurrence · 13.0/changes/154

• Add extension bool_plperl which transforms SQL booleans to/from PL/Perl booleans Features

  Add extension bool_plperl which transforms SQL booleans to/from PL/Perl booleans (Ivan Panchenko) §

  Original release occurrence · 13.0/changes/155

• Have pg_stat_statements treat SELECT ... FOR UPDATE commands as distinct from those without FOR UPDATE Features

  Have pg_stat_statements treat SELECT ... FOR UPDATE commands as distinct from those without FOR UPDATE (Andrew Gierth, Vik Fearing) §

  Original release occurrence · 13.0/changes/156

• Allow pg_stat_statements to optionally track the planning time of statements Features

  Allow pg_stat_statements to optionally track the planning time of statements (Julien Rouhaud, Pascal Legrand, Thomas Munro, Fujii Masao) § §

  Previously only execution time was tracked.

  Original release occurrence · 13.0/changes/157

• Overhaul ltree's lquery syntax to treat NOT (!) more logically Features

  Overhaul ltree's lquery syntax to treat NOT (!) more logically (Filip Rembialkowski, Tom Lane, Nikita Glukhov) § §

  Also allow non-* queries to use a numeric range ({}) of matches.

  Original release occurrence · 13.0/changes/158

• Add support for binary I/O of ltree, lquery, and ltxtquery types Features

  Add support for binary I/O of ltree, lquery, and ltxtquery types (Nino Floris) §

  Original release occurrence · 13.0/changes/159

• Add an option to dict_int to ignore the sign of integers Features

  Add an option to dict_int to ignore the sign of integers (Jeff Janes) §

  Original release occurrence · 13.0/changes/160

• Add adminpack function pg_file_sync() to allow fsync'ing a file Features

  Add adminpack function pg_file_sync() to allow fsync'ing a file (Fujii Masao) §

  Original release occurrence · 13.0/changes/161

• Add pageinspect functions to output t_infomask/t_infomask2 values in human-readable format Features

  Add pageinspect functions to output t_infomask/t_infomask2 values in human-readable format (Craig Ringer, Sawada Masahiko, Michael Paquier) § §

  Original release occurrence · 13.0/changes/162

• Add B-tree index de-duplication processing columns to pageinspect output Features

  Add B-tree index de-duplication processing columns to pageinspect output (Peter Geoghegan) §

  Original release occurrence · 13.0/changes/163

Security evidence

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