Contents

PostgreSQL 13

PostgreSQL 13 is the latest major PostgreSQL version.

Minor version releases

SQL changes

Modified:

  • ALTER FOREIGN TABLE
    • ALTER FOREIGN TABLE ... RENAME COLUMN returns ALTER FOREIGN TABLE (instead of ALTER TABLE) on successful command completion (commit 979766c0)
  • ALTER FUNCTION
    • NO DEPENDS ON EXTENSION syntax added (commit 5fc70394)
  • ALTER INDEX
    • NO DEPENDS ON EXTENSION syntax added (commit 5fc70394)
  • ALTER MATERIALIZED VIEW
    • ALTER MATERIALIZED VIEW ... RENAME COLUMN returns ALTER MATERIALIZED VIEW (instead of ALTER TABLE) on successful command completion (commit 979766c0)
    • NO DEPENDS ON EXTENSION syntax added (commit 5fc70394)
  • ALTER STATISTICS
    • ALTER STATISTICS … SET STATISTICS … clause added (commit d06215d0)
  • ALTER TABLE
  • ALTER TRIGGER
    • NO DEPENDS ON EXTENSION syntax added (commit 5fc70394)
  • ALTER TYPE
    • ALTER TYPE ... SET ( property = value [, ... ] ) syntax added (commit fe30e7eb)
  • CREATE DATABASE
    • LOCALE option added, combining the existing LC_COLLATE and LC_CTYPE options (commit 06140c20)
  • CREATE EXTENSION
    • CREATE EXTENSION … FROM … syntax removed (commit 70a77320)
  • CREATE INDEX
    • implicit support for long-deprecated pre-PostgreSQL opclass names removed (commit 84eca14b)
  • CREATE LANGUAGE
    • removal of pg_pltemplate and creation of the concept of trusted extensions makes it possible for untrusted versions of non-core languages to be installed (commit commit tag must have a commit value)
  • CREATE PUBLICATION
    • parameter publish_via_partition_root added (commit 83fd4532)
  • DROP DATABASE
    • can now take the option FORCE to drop a database even if other users are connected (commit 1379fd53)
  • EXPLAIN
  • FETCH FIRST n ROWS [ WITH TIES ] syntax added (commit 357889eb)

Backend changes

  • promotion of a standby will take priority over paused recovery (commit 496ee647)

Indexing changes

  • incremental sorting added (commit d2d8a229)

Vacuum changes

  • VACUUM PARALLEL option added (commit 40d964ec)
  • autovacuum can now be triggered by the number of INSERT operations (commit b07642db)
  • additional information about errors encountered during a vacuum operation will be displayed (commit b61d161c)

Logging changes

  • maximum length of parameters logged for prepared statement can now be set (commit 0b34e7d3)
  • CSV log output includes the backend type (commit 70a7b477)

Partitioning changes

  • ROW expressions can now be used to define partitions (commit bb4114a4)
  • expanded range of cases where partition pruning can be applied (commit 4e85642d)
  • expanded range of cases where partitionwise joins can be applied (commits c8434d64 and 981643dc)
  • BEFORE row-level triggers permitted on partitioned tables (commit 487e9861)
  • partitioned tables can be logically replicated via publications (commits 17b9e7f9 and 83fd4532)
  • partitioned tables can now be replication targets in logical replication (commit f1ac27bf)

System catalogue changes

Tables

Modified:

Removed:

Views

Added:

Modified:

System function changes

Added:

Data types

Added:

Removed:

  • support for the deprecated opaque pseudo-type removed (commit bb03010b)

Configuration changes

Added:

Modified:

Replication changes

psql changes

  • \warn command added (commit 02e95a50)
  • following operator-related slash commands added (commit b0b5e20c):
    • \dAc - list operator classes
    • \dAf - list operator families
    • \dAo - list operators of operator families
    • \dAp - list procedures of operator families
  • \d+ now shows whether an object is permanent, temporary or unlogged (commit 9a2ea618)
  • \d output for TOAST tables improved (commit eb5472da)
  • \e now inserts an unterminated query into psql's query buffer, making it possible to view or further edit the query before submission  (commit d1c866e5)
  • \g and \gx can accept additional formatting options, e.g. \g (format=csv csv_fieldsep=',') (commit b63c293b)
  • "invisible" PROMPT2 prompt configuration option added (commit 7f338369)
  • %x included by default in PROMPT1 and PROMPT2 to show transaction status (commit dcdbb5a5)

Server utilities

  • pg_rewind
    • option --write-recovery-conf added (commit 927474ce)
    • option -c/--restore-target-wal to use the target server's restore_command to retrieve required WAL (commit a7e8ece4)
    • crash recovery will now be run automatically before rewinding, option --no-ensure-shutdown can disable this (commit 5adafaf1)
  • pg_upgrade
    • default new bindir is now the same location as the pg_upgrade binary being executed, removing the need to explicitly specify -B newbindir in most cases (commit 959f6d6a)
  • pg_waldump
    • option --quiet added to suppress non-error output (commit ac44367e)
    • reports more detailed information about PREPARE TRANSACTION records (commit 7b8a899b)

Core utilities

  • createuser
    • deprecated options --adduser and --no-adduser removed (commit 4fa5edcd)
  • dropdb
    • option --force added (commit 80e05a08)
  • pg_basebackup
    • backup manifests generated for base backups, which can be verified with the new utility pg_verifybackup (commit 0d8c9c12) 
    • total estimated backup size displayed by default (commit fab13dc5)
    • option --no-estimate-size added (commit fab13dc5)
  • pgbench
    • options --partitions and --partition-method added (commit b1c1aa53)
    • --init-steps option accepts G to generate data on the server side, rather than have it generated by the client (commit a386942b)
    • --show-script option added to dump contents of built-in scripts (commit 5823677a)
    • command \aset added, like \gset but able  to store all results from combined SQL queries into separate variables (commit 9d8ef988)
  • pg_dump
  • reindexdb
  • vacuumdb
    • option --parallel added (commit 47bc9ced)

Contrib module changes

Modified:

  • adminpack
    • function pg_file_sync() added (commit d694e0bb)
  • dict_int
    • ABSVAL parameter added (commit 806eb92c)
  • ltree
    • support for binary I/O of ltree, lquery, and ltxtquery types (commit 949a9f04)
    • fixes for NOT handling (commit 70dc4c50)
    • ability to quantify non-'*' items to use a numeric range ({}) of matches (commit 70dc4c50)
  • pageinspect
    • some types returned by the bt_metap() function fixed to prevent overflows (commit 691e8b2e)
    • btree index deduplication processing columns added to function output (commit 93ee38ea)
  • pg_stat_statements
    • configuration option pg_stat_statements.track_utility to track WAL usage statistics (commit 6b466bf5)
    • configuration option pg_stat_statements.track_planning to track planning statistics (commit 17e03282)
  • postgres_fdw
    • options sslkey and sslcert added at server and user mapping level to enable different users to authenticate with different certificates (commit f5fd995a)
    • passwordless non-superuser connections permitted, mainly to facilitate passwordless SSL authentication (commit 6136e94d)
  • sepgsql
    • mandatory access control for TRUNCATE added (commit 4f66c93f)

Following contrib modules marked as trusted extensions (commit eb67623c):

Other changes

  • support for non-ELF BSD systems removed (commit c72f9b95)

Testing

Test modules

Added:

Pre-release test versions

Initial commit marking the start of PostgreSQL 13 development is 615cebc9 (2019-07-01).