Postgres Changelog - All Versions

This is a complete, one-page listing of changes across all Postgres versions. This page was generated on October 05, 2017 and contains information for 339 versions of Postgres. This is version 1.13, and was created by Greg Sabino Mullane.

Postgres 10
10 (2017-10-05)
Postgres 9.6
9.6.5 (2017-08-31)
9.6.4 (2017-08-10)
9.6.3 (2017-05-11)
9.6.2 (2017-02-09)
9.6.1 (2016-10-27)
9.6 (2016-09-29)
Postgres 9.5
9.5.9 (2017-08-31)
9.5.8 (2017-08-10)
9.5.7 (2017-05-11)
9.5.6 (2017-02-09)
9.5.5 (2016-10-27)
9.5.4 (2016-08-11)
9.5.3 (2016-05-12)
9.5.2 (2016-03-31)
9.5.1 (2016-02-11)
9.5 (2016-01-07)
Postgres 9.4
9.4.14 (2017-08-31)
9.4.13 (2017-08-10)
9.4.12 (2017-05-11)
9.4.11 (2017-02-09)
9.4.10 (2016-10-27)
9.4.9 (2016-08-11)
9.4.8 (2016-05-12)
9.4.7 (2016-03-31)
9.4.6 (2016-02-11)
9.4.5 (2015-10-08)
9.4.4 (2015-06-12)
9.4.3 (2015-06-04)
9.4.2 (2015-05-22)
9.4.1 (2015-02-05)
9.4 (2014-12-18)
Postgres 9.3
9.3.19 (2017-08-31)
9.3.18 (2017-08-10)
9.3.17 (2017-05-11)
9.3.16 (2017-02-09)
9.3.15 (2016-10-27)
9.3.14 (2016-08-11)
9.3.13 (2016-05-12)
9.3.12 (2016-03-31)
9.3.11 (2016-02-11)
9.3.10 (2015-10-08)
9.3.9 (2015-06-12)
9.3.8 (2015-06-04)
9.3.7 (2015-05-22)
9.3.6 (2015-02-05)
9.3.5 (2014-07-24)
9.3.4 (2014-03-20)
9.3.3 (2014-02-20)
9.3.2 (2013-12-05)
9.3.1 (2013-10-10)
9.3 (2013-09-09)
Postgres 9.2
9.2.23 (2017-08-31)
9.2.22 (2017-08-10)
9.2.21 (2017-05-11)
9.2.20 (2017-02-09)
9.2.19 (2016-10-27)
9.2.18 (2016-08-11)
9.2.17 (2016-05-12)
9.2.16 (2016-03-31)
9.2.15 (2016-02-11)
9.2.14 (2015-10-08)
9.2.13 (2015-06-12)
9.2.12 (2015-06-04)
9.2.11 (2015-05-22)
9.2.10 (2015-02-05)
9.2.9 (2014-07-24)
9.2.8 (2014-03-20)
9.2.7 (2014-02-20)
9.2.6 (2013-12-05)
9.2.5 (2013-10-10)
9.2.4 (2013-04-04)
9.2.3 (2013-02-07)
9.2.2 (2012-12-06)
9.2.1 (2012-09-24)
9.2 (2012-09-10)
Postgres 9.1
9.1.24 (2016-10-27)
9.1.23 (2016-08-11)
9.1.22 (2016-05-12)
9.1.21 (2016-03-31)
9.1.20 (2016-02-11)
9.1.19 (2015-10-08)
9.1.18 (2015-06-12)
9.1.17 (2015-06-04)
9.1.16 (2015-05-22)
9.1.15 (2015-02-05)
9.1.14 (2014-07-24)
9.1.13 (2014-03-20)
9.1.12 (2014-02-20)
9.1.11 (2013-12-05)
9.1.10 (2013-10-10)
9.1.9 (2013-04-04)
9.1.8 (2013-02-07)
9.1.7 (2012-12-06)
9.1.6 (2012-09-24)
9.1.5 (2012-08-17)
9.1.4 (2012-06-04)
9.1.3 (2012-02-27)
9.1.2 (2011-12-05)
9.1.1 (2011-09-26)
9.1 (2011-09-12)
Postgres 9.0
9.0.23 (2015-10-08)
9.0.22 (2015-06-12)
9.0.21 (2015-06-04)
9.0.20 (2015-05-22)
9.0.19 (2015-02-05)
9.0.18 (2014-07-24)
9.0.17 (2014-03-20)
9.0.16 (2014-02-20)
9.0.15 (2013-12-05)
9.0.14 (2013-10-10)
9.0.13 (2013-04-04)
9.0.12 (2013-02-07)
9.0.11 (2012-12-06)
9.0.10 (2012-09-24)
9.0.9 (2012-08-17)
9.0.8 (2012-06-04)
9.0.7 (2012-02-27)
9.0.6 (2011-12-05)
9.0.5 (2011-09-26)
9.0.4 (2011-04-18)
9.0.3 (2011-01-31)
9.0.2 (2010-12-16)
9.0.1 (2010-10-04)
9.0 (2010-09-20)
Postgres 8.4
8.4.22 (2014-07-24)
8.4.21 (2014-03-20)
8.4.20 (2014-02-20)
8.4.19 (2013-12-05)
8.4.18 (2013-10-10)
8.4.17 (2013-04-04)
8.4.16 (2013-02-07)
8.4.15 (2012-12-06)
8.4.14 (2012-09-24)
8.4.13 (2012-08-17)
8.4.12 (2012-06-04)
8.4.11 (2012-02-27)
8.4.10 (2011-12-05)
8.4.9 (2011-09-26)
8.4.8 (2011-04-18)
8.4.7 (2011-01-31)
8.4.6 (2010-12-16)
8.4.5 (2010-10-04)
8.4.4 (2010-05-17)
8.4.3 (2010-03-15)
8.4.2 (2009-12-14)
8.4.1 (2009-09-09)
8.4 (2009-07-01)
Postgres 8.3
8.3.23 (2013-02-07)
8.3.22 (2012-12-06)
8.3.21 (2012-09-24)
8.3.20 (2012-08-17)
8.3.19 (2012-06-04)
8.3.18 (2012-02-27)
8.3.17 (2011-12-05)
8.3.16 (2011-09-26)
8.3.15 (2011-04-18)
8.3.14 (2011-01-31)
8.3.13 (2010-12-16)
8.3.12 (2010-10-04)
8.3.11 (2010-05-17)
8.3.10 (2010-03-15)
8.3.9 (2009-12-14)
8.3.8 (2009-09-09)
8.3.7 (2009-03-16)
8.3.6 (2009-02-02)
8.3.5 (2008-11-03)
8.3.4 (2008-09-22)
8.3.3 (2008-06-12)
8.3.2 (never released!)
8.3.1 (2008-03-17)
8.3 (2008-02-04)
Postgres 8.2
8.2.23 (2011-12-05)
8.2.22 (2011-09-26)
8.2.21 (2011-04-18)
8.2.20 (2011-01-31)
8.2.19 (2010-12-16)
8.2.18 (2010-10-04)
8.2.17 (2010-05-17)
8.2.16 (2010-03-15)
8.2.15 (2009-12-14)
8.2.14 (2009-09-09)
8.2.13 (2009-03-16)
8.2.12 (2009-02-02)
8.2.11 (2008-11-03)
8.2.10 (2008-09-22)
8.2.9 (2008-06-12)
8.2.8 (never released!)
8.2.7 (2008-03-17)
8.2.6 (2008-01-07)
8.2.5 (2007-09-17)
8.2.4 (2007-04-23)
8.2.3 (2007-02-07)
8.2.2 (2007-02-05)
8.2.1 (2007-01-08)
8.2 (2006-12-05)
Postgres 8.1
8.1.23 (2010-12-16)
8.1.22 (2010-10-04)
8.1.21 (2010-05-17)
8.1.20 (2010-03-15)
8.1.19 (2009-12-14)
8.1.18 (2009-09-09)
8.1.17 (2009-03-16)
8.1.16 (2009-02-02)
8.1.15 (2008-11-03)
8.1.14 (2008-09-22)
8.1.13 (2008-06-12)
8.1.12 (never released!)
8.1.11 (2008-01-07)
8.1.10 (2007-09-17)
8.1.9 (2007-04-23)
8.1.8 (2007-02-07)
8.1.7 (2007-02-05)
8.1.6 (2007-01-08)
8.1.5 (2006-10-16)
8.1.4 (2006-05-23)
8.1.3 (2006-02-14)
8.1.2 (2006-01-09)
8.1.1 (2005-12-12)
8.1 (2005-11-08)
Postgres 8.0
8.0.26 (2010-10-04)
8.0.25 (2010-05-17)
8.0.24 (2010-03-15)
8.0.23 (2009-12-14)
8.0.22 (2009-09-09)
8.0.21 (2009-03-16)
8.0.20 (2009-02-02)
8.0.19 (2008-11-03)
8.0.18 (2008-09-22)
8.0.17 (2008-06-12)
8.0.16 (never released!)
8.0.15 (2008-01-07)
8.0.14 (2007-09-17)
8.0.13 (2007-04-23)
8.0.12 (2007-02-07)
8.0.11 (2007-02-05)
8.0.10 (2007-01-08)
8.0.9 (2006-10-16)
8.0.8 (2006-05-23)
8.0.7 (2006-02-14)
8.0.6 (2006-01-09)
8.0.5 (2005-12-12)
8.0.4 (2005-10-04)
8.0.3 (2005-05-09)
8.0.2 (2005-04-07)
8.0.1 (2005-01-31)
8.0 (2005-01-19)
Postgres 7.4
7.4.30 (2010-10-04)
7.4.29 (2010-05-17)
7.4.28 (2010-03-15)
7.4.27 (2009-12-14)
7.4.26 (2009-09-09)
7.4.25 (2009-03-16)
7.4.24 (2009-02-02)
7.4.23 (2008-11-03)
7.4.22 (2008-09-22)
7.4.21 (2008-06-12)
7.4.20 (never released!)
7.4.19 (2008-01-07)
7.4.18 (2007-09-17)
7.4.17 (2007-04-23)
7.4.16 (2007-02-05)
7.4.15 (2007-01-08)
7.4.14 (2006-10-16)
7.4.13 (2006-05-23)
7.4.12 (2006-02-14)
7.4.11 (2006-01-09)
7.4.10 (2005-12-12)
7.4.9 (2005-10-04)
7.4.8 (2005-05-09)
7.4.7 (2005-01-31)
7.4.6 (2004-10-22)
7.4.5 (2004-08-18)
7.4.4 (2004-08-16)
7.4.3 (2004-06-14)
7.4.2 (2004-03-08)
7.4.1 (2003-12-22)
7.4 (2003-11-17)
Postgres 7.3
7.3.21 (2008-01-07)
7.3.20 (2007-09-17)
7.3.19 (2007-04-23)
7.3.18 (2007-02-05)
7.3.17 (2007-01-08)
7.3.16 (2006-10-16)
7.3.15 (2006-05-23)
7.3.14 (2006-02-14)
7.3.13 (2006-01-09)
7.3.12 (2005-12-12)
7.3.11 (2005-10-04)
7.3.10 (2005-05-09)
7.3.9 (2005-01-31)
7.3.8 (2004-10-22)
7.3.7 (2004-08-16)
7.3.6 (2004-03-02)
7.3.5 (2003-12-03)
7.3.4 (2003-07-24)
7.3.3 (2003-05-22)
7.3.2 (2003-02-04)
7.3.1 (2002-12-18)
7.3 (2002-11-27)
Postgres 7.2
7.2.8 (2005-05-09)
7.2.7 (2005-01-31)
7.2.6 (2004-10-22)
7.2.5 (2004-08-16)
7.2.4 (2003-01-30)
7.2.3 (2002-10-01)
7.2.2 (2002-08-23)
7.2.1 (2002-03-21)
7.2 (2002-02-04)
Postgres 7.1
7.1.3 (2001-08-15)
7.1.2 (2001-05-11)
7.1.1 (2001-05-05)
7.1 (2001-04-13)
Postgres 7.0
7.0.3 (2000-11-11)
7.0.2 (2000-06-05)
7.0.1 (2000-06-01)
7.0 (2000-05-08)
Postgres 6.5
6.5.3 (1999-10-13)
6.5.2 (1999-09-15)
6.5.1 (1999-07-15)
6.5 (1999-06-09)
Postgres 6.4
6.4.2 (1998-12-20)
6.4.1 (1998-12-18)
6.4 (1998-10-30)
Postgres 6.3
6.3.2 (1998-04-07)
6.3.1 (1998-03-23)
6.3 (1998-03-01)
Postgres 6.2
6.2.1 (1997-10-17)
6.2 (1997-10-02)
Postgres 6.1
6.1.1 (1997-07-22)
6.1 (1997-06-08)
Postgres 6.0
and earlier...

6.0 (1997-01-29)
1.09 (1996-11-04)
1.02 (1996-08-01)
1.01 (1996-02-23)
1.0 (1995-09-05)
0.03 (1995-07-21)
0.02 (1995-05-25)
0.01 (1995-05-01)

Postgres version 10.0

Release date: 2017-10-05

 Overview

Major enhancements in PostgreSQL 10 include:

The above items are explained in more detail in the sections below.

 Migration to Version 10

A dump/restore using pg_dumpall, or use of pg_upgrade, is required for those wishing to migrate data from any previous release.

Version 10 contains a number of changes that may affect compatibility with previous releases. Observe the following incompatibilities:

 Changes

Below you will find a detailed account of the changes between PostgreSQL 10 and the previous major release.

 Server
 Parallel Queries
  • Support parallel B-tree index scans (Rahila Syed, Amit Kapila, Robert Haas, Rafia Sabih)

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

  • Support parallel bitmap heap scans (Dilip Kumar)

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

  • Allow merge joins to be performed in parallel (Dilip Kumar)

  • Allow non-correlated subqueries to be run in parallel (Amit Kapila)

  • Improve ability of parallel workers to return pre-sorted data (Rushabh Lathia)

  • Increase parallel query usage in procedural language functions (Robert Haas, Rafia Sabih)

  • Add max_parallel_workers server parameter to limit the number of worker processes that can be used for query parallelism (Julien Rouhaud)

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

 Indexes
  • Add write-ahead logging support to hash indexes (Amit Kapila)

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

  • Improve hash index performance (Amit Kapila, Mithun Cy, Ashutosh Sharma)

  • Add SP-GiST index support for INET and CIDR data types (Emre Hasegeli)

  • Add option to allow BRIN index summarization to happen more aggressively (Álvaro Herrera)

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

  • Add functions to remove and re-add BRIN summarization for BRIN index ranges (Álvaro Herrera)

    The new SQL function brin_summarize_range() updates BRIN index summarization for a specified range and brin_desummarize_range() removes it. This is helpful to update summarization of a range that is now smaller due to UPDATEs and DELETEs.

  • Improve accuracy in determining if a BRIN index scan is beneficial (David Rowley, Emre Hasegeli)

  • Allow faster GiST inserts and updates by reusing index space more efficiently (Andrey Borodin)

  • Reduce page locking during vacuuming of GIN indexes (Andrey Borodin)

 Locking
  • Reduce locking required to change table parameters (Simon Riggs, Fabrízio Mello)

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

  • Allow tuning of predicate lock promotion thresholds (Dagfinn Ilmari Mannsåker)

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

 Optimizer
  • Add multi-column optimizer statistics to compute the correlation ratio and number of distinct values (Tomas Vondra, David Rowley, Álvaro Herrera)

    New commands are CREATE STATISTICS, ALTER STATISTICS, and DROP STATISTICS. This feature is helpful in estimating query memory usage and when combining the statistics from individual columns.

  • Improve performance of queries affected by row-level security restrictions (Tom Lane)

    The optimizer now has more knowledge about where it can place RLS filter conditions, allowing better plans to be generated while still enforcing the RLS conditions safely.

 General Performance
  • Speed up aggregate functions that calculate a running sum using numeric-type arithmetic, including some variants of SUM(), AVG(), and STDDEV() (Heikki Linnakangas)

  • Improve performance of character encoding conversions by using radix trees (Kyotaro Horiguchi, Heikki Linnakangas)

  • Reduce expression evaluation overhead during query execution, as well as plan node calling overhead (Andres Freund)

    This is particularly helpful for queries that process many rows.

  • Allow hashed aggregation to be used with grouping sets (Andrew Gierth)

  • Use uniqueness guarantees to optimize certain join types (David Rowley)

  • Improve sort performance of the macaddr data type (Brandur Leach)

  • Reduce statistics tracking overhead in sessions that reference many thousands of relations (Aleksander Alekseev)

 Monitoring
  • Allow explicit control over EXPLAIN's display of planning and execution time (Ashutosh Bapat)

    By default planning and execution time are displayed by EXPLAIN ANALYZE and are not displayed in other cases. The new EXPLAIN option SUMMARY allows explicit control of this.

  • Add default monitoring roles (Dave Page)

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

  • Properly update the statistics collector during REFRESH MATERIALIZED VIEW (Jim Mlodgenski)

 Logging
  • Change the default value of log_line_prefix to include current timestamp (with milliseconds) and the process ID in each line of postmaster log output (Christoph Berg)

    The previous default was an empty prefix.

  • Add functions to return the log and WAL directory contents (Dave Page)

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

  • Add function pg_current_logfile() to read logging collector's current stderr and csvlog output file names (Gilles Darold)

  • Report the address and port number of each listening socket in the server log during postmaster startup (Tom Lane)

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

  • Reduce log chatter about the starting and stopping of launcher subprocesses (Tom Lane)

    These are now DEBUG1-level messages.

  • Reduce message verbosity of lower-numbered debug levels controlled by log_min_messages (Robert Haas)

    This also changes the verbosity of client_min_messages debug levels.

  • Add pg_stat_activity reporting of low-level wait states (Michael Paquier, Robert Haas, Rushabh Lathia)

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

  • Show auxiliary processes, background workers, and walsender processes in pg_stat_activity (Kuntal Ghosh, Michael Paquier)

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

  • Allow pg_stat_activity to show the SQL query being executed by parallel workers (Rafia Sabih)

  • Rename pg_stat_activity.wait_event_type values LWLockTranche and LWLockNamed to LWLock (Robert Haas)

    This makes the output more consistent.

 Authentication
  • Add SCRAM-SHA-256 support for password negotiation and storage (Michael Paquier, Heikki Linnakangas)

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

  • Change the password_encryption server parameter from boolean to enum (Michael Paquier)

    This was necessary to support additional password hashing options.

  • Add view pg_hba_file_rules to display the contents of pg_hba.conf (Haribabu Kommi)

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

  • Support multiple RADIUS servers (Magnus Hagander)

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

 Server Configuration
  • Allow SSL configuration to be updated during configuration reload (Andreas Karlsson, Tom Lane)

    This allows SSL to be reconfigured without a server restart, by using pg_ctl reload, SELECT pg_reload_conf(), or sending a SIGHUP signal. However, reloading the SSL configuration does not work if the server's SSL key requires a passphrase, as there is no way to re-prompt for the passphrase. The original configuration will apply for the life of the postmaster in that case.

  • Make the maximum value of bgwriter_lru_maxpages effectively unlimited (Jim Nasby)

 Reliability
  • After creating or unlinking files, perform an fsync on their parent directory (Michael Paquier)

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

 Write-Ahead Log (WAL)
  • Prevent unnecessary checkpoints and WAL archiving on otherwise-idle systems (Michael Paquier)

  • Add wal_consistency_checking server parameter to add details to WAL that can be sanity-checked on the standby (Kuntal Ghosh, Robert Haas)

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

  • Increase the maximum configurable WAL segment size to one gigabyte (Beena Emerson)

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

 Replication and Recovery
  • Add the ability to logically replicate tables to standby servers (Petr Jelinek)

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

  • Allow waiting for commit acknowledgement from standby servers irrespective of the order they appear in synchronous_standby_names (Masahiko Sawada)

    Previously the server always waited for the active standbys that appeared first in synchronous_standby_names. The new synchronous_standby_names keyword ANY allows waiting for any number of standbys irrespective of their ordering. This is known as quorum commit.

  • Reduce configuration changes necessary to perform streaming backup and replication (Magnus Hagander, Dang Minh Huong)

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

  • Enable replication from localhost connections by default in pg_hba.conf (Michael Paquier)

    Previously pg_hba.conf's replication connection lines were commented out by default. This is particularly useful for pg_basebackup.

  • Add columns to pg_stat_replication to report replication delay times (Thomas Munro)

    The new columns are write_lag, flush_lag, and replay_lag.

  • Allow specification of the recovery stopping point by Log Sequence Number (LSN) in recovery.conf (Michael Paquier)

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

  • Allow users to disable pg_stop_backup()'s waiting for all WAL to be archived (David Steele)

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

  • Allow creation of temporary replication slots (Petr Jelinek)

    Temporary slots are automatically removed on session exit or error.

  • Improve performance of hot standby replay with better tracking of Access Exclusive locks (Simon Riggs, David Rowley)

  • Speed up two-phase commit recovery performance (Stas Kelvich, Nikhil Sontakke, Michael Paquier)

 Queries
  • Add XMLTABLE function that converts XML-formatted data into a row set (Pavel Stehule, Álvaro Herrera)

  • Allow standard row constructor syntax in UPDATE ... SET (column_list) = row_constructor (Tom Lane)

    The row_constructor can now begin with the keyword ROW; previously that had to be omitted. Also, an occurrence of table_name.* within the row_constructor is now expanded into multiple columns, as in other uses of row_constructors.

  • Fix regular expressions' character class handling for large character codes, particularly Unicode characters above U+7FF (Tom Lane)

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

 Utility Commands
  • Add table partitioning syntax that automatically creates partition constraints and handles routing of tuple insertions and updates (Amit Langote)

    The syntax supports range and list partitioning.

  • Add AFTER trigger transition tables to record changed rows (Kevin Grittner, Thomas Munro)

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

  • Allow restrictive row-level security policies (Stephen Frost)

    Previously all security policies were permissive, meaning that any matching policy allowed access. A restrictive policy must match for access to be granted. These policy types can be combined.

  • When creating a foreign-key constraint, check for REFERENCES permission on only the referenced table (Tom Lane)

    Previously REFERENCES permission on the referencing table was also required. This appears to have stemmed from a misreading of the SQL standard. Since creating a foreign key (or any other type of) constraint requires ownership privilege on the constrained table, additionally requiring REFERENCES permission seems rather pointless.

  • Allow default permissions on schemas (Matheus Oliveira)

    This is done using the ALTER DEFAULT PRIVILEGES command.

  • Add CREATE SEQUENCE AS command to create a sequence matching an integer data type (Peter Eisentraut)

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

  • Allow COPY view FROM source on views with INSTEAD INSERT triggers (Haribabu Kommi)

    The triggers are fed the data rows read by COPY.

  • Allow the specification of a function name without arguments in DDL commands, if it is unique (Peter Eisentraut)

    For example, allow DROP FUNCTION on a function name without arguments if there is only one function with that name. This behavior is required by the SQL standard.

  • Allow multiple functions, operators, and aggregates to be dropped with a single DROP command (Peter Eisentraut)

  • Support IF NOT EXISTS in CREATE SERVER, CREATE USER MAPPING, and CREATE COLLATION (Anastasia Lubennikova, Peter Eisentraut)

  • Make VACUUM VERBOSE report the number of skipped frozen pages and oldest xmin (Masahiko Sawada, Simon Riggs)

    This information is also included in log_autovacuum_min_duration output.

  • Improve speed of VACUUM's removal of trailing empty heap pages (Claudio Freire, Álvaro Herrera)

 Data Types
  • Add full text search support for JSON and JSONB (Dmitry Dolgov)

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

  • Add support for EUI-64 MAC addresses, as a new data type macaddr8 (Haribabu Kommi)

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

  • Add identity columns for assigning a numeric value to columns on insert (Peter Eisentraut)

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

  • Allow ENUM values to be renamed (Dagfinn Ilmari Mannsåker)

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

  • Properly treat array pseudotypes (anyarray) as arrays in to_json() and to_jsonb() (Andrew Dunstan)

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

  • Add operators for multiplication and division of money values with int8 values (Peter Eisentraut)

    Previously such cases would result in converting the int8 values to float8 and then using the money-and-float8 operators. The new behavior avoids possible precision loss. But note that division of money by int8 now truncates the quotient, like other integer-division cases, while the previous behavior would have rounded.

  • Check for overflow in the money type's input function (Peter Eisentraut)

 Functions
  • Add simplified regexp_match() function (Emre Hasegeli)

    This is similar to regexp_matches(), but it only returns results from the first match so it does not need to return a set, making it easier to use for simple cases.

  • Add a version of jsonb's delete operator that takes an array of keys to delete (Magnus Hagander)

  • Make json_populate_record() and related functions process JSON arrays and objects recursively (Nikita Glukhov)

    With this change, array-type fields in the destination SQL type are properly converted from JSON arrays, and composite-type fields are properly converted from JSON objects. Previously, such cases would fail because the text representation of the JSON value would be fed to array_in() or record_in(), and its syntax would not match what those input functions expect.

  • Add function txid_current_ifassigned() to return the current transaction ID or NULL if no transaction ID has been assigned (Craig Ringer)

    This is different from txid_current(), which always returns a transaction ID, assigning one if necessary. Unlike that function, this function can be run on standby servers.

  • Add function txid_status() to check if a transaction was committed (Craig Ringer)

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

  • Allow make_date() to interpret negative years as BC years (Álvaro Herrera)

  • Make to_timestamp() and to_date() reject out-of-range input fields (Artur Zakirov)

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

 Server-Side Languages
  • Allow PL/Python's cursor() and execute() functions to be called as methods of their plan-object arguments (Peter Eisentraut)

    This allows a more object-oriented programming style.

  • Allow PL/pgSQL's GET DIAGNOSTICS statement to retrieve values into array elements (Tom Lane)

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

 PL/Tcl
  • Allow PL/Tcl functions to return composite types and sets (Karl Lehenbauer)

  • Add a subtransaction command to PL/Tcl (Victor Wagner)

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

  • Add server parameters pltcl.start_proc and pltclu.start_proc, to allow initialization functions to be called on PL/Tcl startup (Tom Lane)

 Client Interfaces
  • Allow specification of multiple host names or addresses in libpq connection strings and URIs (Robert Haas, Heikki Linnakangas)

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

  • Allow libpq connection strings and URIs to request a read/write host, that is a master server rather than a standby server (Victor Wagner, Mithun Cy)

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

  • Allow the password file name to be specified as a libpq connection parameter (Julian Markwort)

    Previously this could only be specified via an environment variable.

  • Add function PQencryptPasswordConn() to allow creation of more types of encrypted passwords on the client side (Michael Paquier, Heikki Linnakangas)

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

  • Change ecpg preprocessor version from 4.12 to 10 (Tom Lane)

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

 Client Applications
 psql
  • Add conditional branch support to psql (Corey Huinker)

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

  • Add psql \gx meta-command to execute (\g) a query in expanded mode (\x) (Christoph Berg)

  • Expand psql variable references in backtick-executed strings (Tom Lane)

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

  • Prevent psql's special variables from being set to invalid values (Daniel Vérité, Tom Lane)

    Previously, setting one of psql's special variables to an invalid value silently resulted in the default behavior. \set on a special variable now fails if the proposed new value is invalid. As a special exception, \set with an empty or omitted new value, on a boolean-valued special variable, still has the effect of setting the variable to on; but now it actually acquires that value rather than an empty string. \unset on a special variable now explicitly sets the variable to its default value, which is also the value it acquires at startup. In sum, a control variable now always has a displayable value that reflects what psql is actually doing.

  • Add variables showing server version and psql version (Fabien Coelho)

  • Improve psql's \d (display relation) and \dD (display domain) commands to show collation, nullable, and default properties in separate columns (Peter Eisentraut)

    Previously they were shown in a single Modifiers column.

  • Make the various \d commands handle no-matching-object cases more consistently (Daniel Gustafsson)

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

  • Improve psql's tab completion (Jeff Janes, Ian Barwick, Andreas Karlsson, Sehrope Sarkuni, Thomas Munro, Kevin Grittner, Dagfinn Ilmari Mannsåker)

  • Add pgbench option --log-prefix to control the log file prefix (Masahiko Sawada)

  • Allow pgbench's meta-commands to span multiple lines (Fabien Coelho)

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

  • Remove restriction on placement of -M option relative to other command line options (Tom Lane)

 Server Applications
  • Add pg_receivewal option -Z/--compress to specify compression (Michael Paquier)

  • Add pg_recvlogical option --endpos to specify the ending position (Craig Ringer)

    This complements the existing --startpos option.

  • Rename initdb options --noclean and --nosync to be spelled --no-clean and --no-sync (Vik Fearing, Peter Eisentraut)

    The old spellings are still supported.

  • Allow pg_restore to exclude schemas (Michael Banck)

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

  • Add --no-blobs option to pg_dump (Guillaume Lelarge)

    This suppresses dumping of large objects.

  • Add pg_dumpall option --no-role-passwords to omit role passwords (Robins Tharakan, Simon Riggs)

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

  • Support using synchronized snapshots when dumping from a standby server (Petr Jelinek)

  • Issue fsync() on the output files generated by pg_dump and pg_dumpall (Michael Paquier)

    This provides more security that the output is safely stored on disk before the program exits. This can be disabled with the new --no-sync option.

  • Allow pg_basebackup to stream write-ahead log in tar mode (Magnus Hagander)

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

  • Make pg_basebackup use temporary replication slots (Magnus Hagander)

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

  • Be more careful about fsync'ing in all required places in pg_basebackup and pg_receivewal (Michael Paquier)

  • Add pg_basebackup option --no-sync to disable fsync (Michael Paquier)

  • Improve pg_basebackup's handling of which directories to skip (David Steele)

 pg_ctl
  • Add wait option for pg_ctl's promote operation (Peter Eisentraut)

  • Add long options for pg_ctl wait (--wait) and no-wait (--no-wait) (Vik Fearing)

  • Add long option for pg_ctl server options (--options) (Peter Eisentraut)

  • Make pg_ctl start --wait detect server-ready by watching postmaster.pid, not by attempting connections (Tom Lane)

    The postmaster has been changed to report its ready-for-connections status in postmaster.pid, and pg_ctl now examines that file to detect whether startup is complete. This is more efficient and reliable than the old method, and it eliminates postmaster log entries about rejected connection attempts during startup.

  • Reduce pg_ctl's reaction time when waiting for postmaster start/stop (Tom Lane)

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

  • Ensure that pg_ctl exits with nonzero status if an operation being waited for does not complete within the timeout (Peter Eisentraut)

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

 Source Code
  • Change to two-part release version numbering (Peter Eisentraut, Tom Lane)

    Release numbers will now have two parts (e.g., 10.1) rather than three (e.g., 9.6.3). Major versions will now increase just the first number, and minor releases will increase just the second number. Release branches will be referred to by single numbers (e.g., 10 rather than 9.6). This change is intended to reduce user confusion about what is a major or minor release of PostgreSQL.

  • Improve behavior of pgindent (Piotr Stefaniak, Tom Lane)

    We have switched to a new version of pg_bsd_indent based on recent improvements made by the FreeBSD project. This fixes numerous small bugs that led to odd C code formatting decisions. Most notably, lines within parentheses (such as in a multi-line function call) are now uniformly indented to match the opening paren, even if that would result in code extending past the right margin.

  • Allow the ICU library to optionally be used for collation support (Peter Eisentraut)

    The ICU library has versioning that allows detection of collation changes between versions. It is enabled via configure option --with-icu. The default still uses the operating system's native collation library.

  • Automatically mark all PG_FUNCTION_INFO_V1 functions as DLLEXPORT-ed on Windows (Laurenz Albe)

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

  • Remove SPI functions SPI_push(), SPI_pop(), SPI_push_conditional(), SPI_pop_conditional(), and SPI_restore_connection() as unnecessary (Tom Lane)

    Their functionality now happens automatically. There are now no-op macros by these names so that external modules don't need to be updated immediately, but eventually such calls should be removed.

    A side effect of this change is that SPI_palloc() and allied functions now require an active SPI connection; they do not degenerate to simple palloc() if there is none. That previous behavior was not very useful and posed risks of unexpected memory leaks.

  • Allow shared memory to be dynamically allocated (Thomas Munro, Robert Haas)

  • Add slab-like memory allocator for efficient fixed-size allocations (Tomas Vondra)

  • Use POSIX semaphores rather than SysV semaphores on Linux and FreeBSD (Tom Lane)

    This avoids platform-specific limits on SysV semaphore usage.

  • Improve support for 64-bit atomics (Andres Freund)

  • Enable 64-bit atomic operations on ARM64 (Roman Shaposhnik)

  • Switch to using clock_gettime(), if available, for duration measurements (Tom Lane)

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

  • Add more robust random number generators to be used for cryptographically secure uses (Magnus Hagander, Michael Paquier, Heikki Linnakangas)

    If no strong random number generator can be found, configure will fail unless the --disable-strong-random option is used. However, with this option, pgcrypto functions requiring a strong random number generator will be disabled.

  • Allow WaitLatchOrSocket() to wait for socket connection on Windows (Andres Freund)

  • tupconvert.c functions no longer convert tuples just to embed a different composite-type OID in them (Ashutosh Bapat, Tom Lane)

    The majority of callers don't care about the composite-type OID; but if the result tuple is to be used as a composite Datum, steps should be taken to make sure the correct OID is inserted in it.

  • Remove SCO and Unixware ports (Tom Lane)

  • Overhaul documentation build process (Alexander Lakhin)

  • Use XSLT to build the PostgreSQL documentation (Peter Eisentraut)

    Previously Jade, DSSSL, and JadeTex were used.

  • Build HTML documentation using XSLT stylesheets by default (Peter Eisentraut)

 Additional Modules
  • Allow file_fdw to read from program output as well as files (Corey Huinker, Adam Gomaa)

  • In postgres_fdw, push aggregate functions to the remote server, when possible (Jeevan Chalke, Ashutosh Bapat)

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

  • In postgres_fdw, push joins to the remote server in more cases (David Rowley, Ashutosh Bapat, Etsuro Fujita)

  • Properly support OID columns in postgres_fdw tables (Etsuro Fujita)

    Previously OID columns always returned zeros.

  • Allow btree_gist and btree_gin to index enum types (Andrew Dunstan)

    This allows enums to be used in exclusion constraints.

  • Add indexing support to btree_gist for the UUID data type (Paul Jungwirth)

  • Add amcheck which can check the validity of B-tree indexes (Peter Geoghegan)

  • Show ignored constants as $N rather than ? in pg_stat_statements (Lukas Fittl)

  • Improve cube's handling of zero-dimensional cubes (Tom Lane)

    This also improves handling of infinite and NaN values.

  • Allow pg_buffercache to run with fewer locks (Ivan Kartyshov)

    This makes it less disruptive when run on production systems.

  • Add pgstattuple function pgstathashindex() to view hash index statistics (Ashutosh Sharma)

  • Use GRANT permissions to control pgstattuple function usage (Stephen Frost)

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

  • Reduce locking when pgstattuple examines hash indexes (Amit Kapila)

  • Add pageinspect function page_checksum() to show a page's checksum (Tomas Vondra)

  • Add pageinspect function bt_page_items() to print page items from a page image (Tomas Vondra)

  • Add hash index support to pageinspect (Jesper Pedersen, Ashutosh Sharma)

 Acknowledgments

The following individuals (in alphabetical order) have contributed to this release as patch authors, committers, reviewers, testers, or reporters of issues.

Adam Brightwell
Adam Brusselback
Adam Gomaa
Adam Sah
Adrian Klaver
Aidan Van Dyk
Aleksander Alekseev
Alexander Korotkov
Alexander Lakhin
Alexander Sosna
Alexey Bashtanov
Alexey Grishchenko
Alexey Isayko
Álvaro Hernández Tortosa
Álvaro Herrera
Amit Kapila
Amit Khandekar
Amit Langote
Amul Sul
Anastasia Lubennikova
Andreas Joseph Krogh
Andreas Karlsson
Andreas Scherbaum
Andreas Seltenreich
Andres Freund
Andrew Dunstan
Andrew Gierth
Andrew Wheelwright
Andrey Borodin
Andrey Lizenko
Andy Abelisto
Antonin Houska
Ants Aasma
Arjen Nienhuis
Arseny Sher
Artur Zakirov
Ashutosh Bapat
Ashutosh Sharma
Ashwin Agrawal
Atsushi Torikoshi
Ayumi Ishii
Basil Bourque
Beena Emerson
Ben de Graaff
Benedikt Grundmann
Bernd Helmle
Brad DeJong
Brandur Leach
Breen Hagan
Bruce Momjian
Bruno Wolff III
Catalin Iacob
Chapman Flack
Chen Huajun
Choi Doo-Won
Chris Bandy
Chris Richards
Chris Ruprecht
Christian Ullrich
Christoph Berg
Chuanting Wang
Claudio Freire
Clinton Adams
Const Zhang
Constantin Pan
Corey Huinker
Craig Ringer
Cynthia Shang
Dagfinn Ilmari Mannsåker
Daisuke Higuchi
Damian Quiroga
Dan Wood
Daniel Gustafsson
Daniel Vérité
Daniel Westermann
Daniele Varrazzo
Danylo Hlynskyi
Darko Prelec
Dave Cramer
Dave Page
David Christensen
David Fetter
David Johnston
David Rader
David Rowley
David Steele
Dean Rasheed
Denis Smirnov
Denish Patel
Dennis Björklund
Devrim Gündüz
Dilip Kumar
Dilyan Palauzov
Dima Pavlov
Dimitry Ivanov
Dmitriy Sarafannikov
Dmitry Dolgov
Dmitry Fedin
Don Morrison
Egor Rogov
Eiji Seki
Emil Iggland
Emre Hasegeli
Enrique Meneses
Erik Nordström
Erik Rijkers
Erwin Brandstetter
Etsuro Fujita
Eugen Konkov
Eugene Kazakov
Euler Taveira
Fabien Coelho
Fabrízio de Royes Mello
Fakhroutdinov Evgenievich
Feike Steenbergen
Felix Gerzaguet
Filip Jirsák
Fujii Masao
Gabriele Bartolini
Gabrielle Roth
Gao Zengqi
Gerdan Santos
Gianni Ciolli
Gilles Darold
Giuseppe Broccolo
Graham Dutton
Greg Atkins
Greg Burek
Grigory Smolkin
Guillaume Lelarge
Hans Buschmann
Haribabu Kommi
Heikki Linnakangas
Henry Boehlert
Huan Ruan
Huong Dangminh
Ian Barwick
Igor Korot
Ildus Kurbangaliev
Ivan Kartyshov
Jaime Casanova
Jakob Egger
James Parks
Jarred Ward
Jason Li
Jason O'Donnell
Jason Petersen
Jeevan Chalke
Jeevan Ladhe
Jeff Dafoe
Jeff Davis
Jeff Janes
Jelte Fennema
Jeremy Finzel
Jeremy Schneider
Jeroen van der Ham
Jesper Pedersen
Jim Mlodgenski
Jim Nasby
Jinyu Zhang
Joe Conway
Joel Jacobson
John Harvey
Jon Nelson
Jordan Gigov
Josh Berkus
Josh Soref
Julian Markwort
Julien Rouhaud
Junseok Yang
Justin Muise
Justin Pryzby
Kacper Zuk
KaiGai Kohei
Karen Huddleston
Karl Lehenbauer
Karl O. Pinc
Keith Fiske
Kevin Grittner
Kim Rose Carlsen
Konstantin Evteev
Konstantin Knizhnik
Kuntal Ghosh
Kurt Kartaltepe
Kyle Conroy
Kyotaro Horiguchi
Laurenz Albe
Leonardo Cecchi
Ludovic Vaugeois-Pepin
Lukas Fittl
Magnus Hagander
Maksim Milyutin
Maksym Sobolyev
Marc Rassbach
Marc-Olaf Jaschke
Marcos Castedo
Marek Cvoren
Mark Dilger
Mark Kirkwood
Mark Pether
Marko Tiikkaja
Markus Winand
Marllius Ribeiro
Marti Raudsepp
Martín Marqués
Masahiko Sawada
Matheus Oliveira
Mathieu Fenniak
Merlin Moncure
Michael Banck
Michael Day
Michael Meskes
Michael Overmeyer
Michael Paquier
Mike Palmiotto
Milos Urbanek
Mithun Cy
Moshe Jacobson
Murtuza Zabuawala
Naoki Okano
Nathan Bossart
Nathan Wagner
Neha Khatri
Neha Sharma
Neil Anderson
Nicolas Baccelli
Nicolas Guini
Nicolas Thauvin
Nikhil Sontakke
Nikita Glukhov
Nikolaus Thiel
Nikolay Nikitin
Nikolay Shaplov
Noah Misch
Noriyoshi Shinoda
Olaf Gawenda
Oleg Bartunov
Oskari Saarenmaa
Otar Shavadze
Paresh More
Paul Jungwirth
Paul Ramsey
Pavan Deolasee
Pavel Golub
Pavel Hanák
Pavel Raiskup
Pavel Stehule
Peng Sun
Peter Eisentraut
Peter Geoghegan
Petr Jelínek
Philippe Beaudoin
Pierre-Emmanuel André
Piotr Stefaniak
Prabhat Sahu
QL Zhuo
Radek Slupik
Rafa de la Torre
Rafia Sabih
Ragnar Ouchterlony
Rahila Syed
Rajkumar Raghuwanshi
Regina Obe
Richard Pistole
Robert Haas
Robins Tharakan
Rod Taylor
Roman Shaposhnik
Rushabh Lathia
Ryan Murphy
Sandeep Thakkar
Scott Milliken
Sean Farrell
Sebastian Luque
Sehrope Sarkuni
Sergey Burladyan
Sergey Koposov
Shay Rojansky
Shinichi Matsuda
Sho Kato
Simon Riggs
Simone Gotti
Spencer Thomason
Stas Kelvich
Stepan Pesternikov
Stephen Frost
Steve Randall
Steve Singer
Steven Fackler
Steven Winfield
Suraj Kharage
Sveinn Sveinsson
Sven R. Kunze
Taiki Kondo
Takayuki Tsunakawa
Takeshi Ideriha
Tatsuo Ishii
Tatsuro Yamada
Teodor Sigaev
Thom Brown
Thomas Kellerer
Thomas Munro
Tim Goodaire
Tobias Bussmann
Tom Dunstan
Tom Lane
Tom van Tilburg
Tomas Vondra
Tomonari Katsumata
Tushar Ahuja
Vaishnavi Prabakaran
Venkata Balaji Nagothi
Vicky Vergara
Victor Wagner
Vik Fearing
Vinayak Pokale
Viren Negi
Vitaly Burovoy
Vladimir Kunshchikov
Vladimir Rusinov
Yi Wen Wong
Yugo Nagata
Zhen Ming Yang
Zhou Digoal

Postgres version 9.6.5

Release date: 2017-08-31

This release contains a small number of fixes from 9.6.4. For information about new features in the 9.6 major release, see Section E.7.

 Migration to Version 9.6.5

A dump/restore is not required for those running 9.6.X.

However, if you are upgrading from a version earlier than 9.6.4, see Section E.3.

 Changes

Postgres version 9.6.4

Release date: 2017-08-10

This release contains a variety of fixes from 9.6.3. For information about new features in the 9.6 major release, see Section E.7.

 Migration to Version 9.6.4

A dump/restore is not required for those running 9.6.X.

However, if you use foreign data servers that make use of user passwords for authentication, see the first changelog entry below.

Also, if you are upgrading from a version earlier than 9.6.3, see Section E.4.

 Changes

Postgres version 9.6.3

Release date: 2017-05-11

This release contains a variety of fixes from 9.6.2. For information about new features in the 9.6 major release, see Section E.7.

 Migration to Version 9.6.3

A dump/restore is not required for those running 9.6.X.

However, if you use foreign data servers that make use of user passwords for authentication, see the first changelog entry below.

Also, if you are using third-party replication tools that depend on logical decoding, see the fourth changelog entry below.

Also, if you are upgrading from a version earlier than 9.6.2, see Section E.5.

 Changes

Postgres version 9.6.2

Release date: 2017-02-09

This release contains a variety of fixes from 9.6.1. For information about new features in the 9.6 major release, see Section E.7.

 Migration to Version 9.6.2

A dump/restore is not required for those running 9.6.X.

However, if your installation has been affected by the bug described in the first changelog entry below, then after updating you may need to take action to repair corrupted indexes.

Also, if you are upgrading from a version earlier than 9.6.1, see Section E.6.

 Changes

Postgres version 9.6.1

Release 9.6.1

Release date: 2016-10-27

This release contains a variety of fixes from 9.6.0. For information about new features in the 9.6 major release, see Version 9.6.

Migration to Version 9.6.1

A dump/restore is not required for those running 9.6.X.

However, if your installation has been affected by the bugs described in the first two changelog entries below, then after updating you may need to take action to repair corrupted free space maps and/or visibility maps.

Changes

Postgres version 9.6

Release 9.6

Release date: 2016-09-29

Overview

Major enhancements in PostgreSQL 9.6 include:

The above items are explained in more detail in the sections below.

Migration to Version 9.6

A dump/restore using pg_dumpall, or use of pg_upgrade, is required for those wishing to migrate data from any previous release.

Version 9.6 contains a number of changes that may affect compatibility with previous releases. Observe the following incompatibilities:

Changes

Below you will find a detailed account of the changes between PostgreSQL 9.6 and the previous major release.

Server

Parallel Queries
  • (9.6) Parallel queries (Robert Haas, Amit Kapila, David Rowley, many others)

    With 9.6, PostgreSQL introduces initial support for parallel execution of large queries. Only strictly read-only queries where the driving table is accessed via a sequential scan can be parallelized. Hash joins and nested loops can be performed in parallel, as can aggregation (for supported aggregates). Much remains to be done, but this is already a useful set of features.

    Parallel query execution is not (yet) enabled by default. To allow it, set the new configuration parameter max_parallel_workers_per_gather to a value larger than zero. Additional control over use of parallelism is available through other new configuration parameters force_parallel_mode, parallel_setup_cost, parallel_tuple_cost, and min_parallel_relation_size.

  • (9.6) Provide infrastructure for marking the parallel-safety status of functions (Robert Haas, Amit Kapila)

Indexes
  • (9.6) Allow GIN index builds to make effective use of maintenance_work_mem settings larger than 1 GB (Robert Abraham, Teodor Sigaev)

  • (9.6) Add pages deleted from a GIN index's pending list to the free space map immediately (Jeff Janes, Teodor Sigaev)

    This reduces bloat if the table is not vacuumed often.

  • (9.6) Add gin_clean_pending_list() function to allow manual invocation of pending-list cleanup for a GIN index (Jeff Janes)

    Formerly, such cleanup happened only as a byproduct of vacuuming or analyzing the parent table.

  • (9.6) Improve handling of dead index tuples in GiST indexes (Anastasia Lubennikova)

    Dead index tuples are now marked as such when an index scan notices that the corresponding heap tuple is dead. When inserting tuples, marked-dead tuples will be removed if needed to make space on the page.

  • (9.6) Add an SP-GiST operator class for type box (Alexander Lebedev)

Sorting
  • (9.6) Improve sorting performance by using quicksort, not replacement selection sort, when performing external sort steps (Peter Geoghegan)

    The new approach makes better use of the CPU cache for typical cache sizes and data volumes. Where necessary, the behavior can be adjusted via the new configuration parameter replacement_sort_tuples.

  • (9.6) Speed up text sorts where the same string occurs multiple times (Peter Geoghegan)

  • (9.6) Speed up sorting of uuid, bytea, and char(n) fields by using "abbreviated" keys (Peter Geoghegan)

    Support for abbreviated keys has also been added to the non-default operator classes text_pattern_ops, varchar_pattern_ops, and bpchar_pattern_ops. Processing of ordered-set aggregates can also now exploit abbreviated keys.

  • (9.6) Speed up CREATE INDEX CONCURRENTLY by treating TIDs as 64-bit integers during sorting (Peter Geoghegan)

Locking
  • (9.6) Reduce contention for the ProcArrayLock (Amit Kapila, Robert Haas)

  • (9.6) Improve performance by moving buffer content locks into the buffer descriptors (Andres Freund, Simon Riggs)

  • (9.6) Replace shared-buffer header spinlocks with atomic operations to improve scalability (Alexander Korotkov, Andres Freund)

  • (9.6) Use atomic operations, rather than a spinlock, to protect an LWLock's wait queue (Andres Freund)

  • (9.6) Partition the shared hash table freelist to reduce contention on multi-CPU-socket servers (Aleksander Alekseev)

  • (9.6) Reduce interlocking on standby servers during the replay of btree index vacuuming operations (Simon Riggs)

    This change avoids substantial replication delays that sometimes occurred while replaying such operations.

Optimizer Statistics
  • (9.6) Improve ANALYZE's estimates for columns with many nulls (Tomas Vondra, Alex Shulgin)

    Previously ANALYZE tended to underestimate the number of non-NULL distinct values in a column with many NULLs, and was also inaccurate in computing the most-common values.

  • (9.6) Improve planner's estimate of the number of distinct values in a query result (Tomas Vondra)

  • (9.6) Use foreign key relationships to infer selectivity for join predicates (Tomas Vondra, David Rowley)

    If a table t has a foreign key restriction, say (a,b) REFERENCES r (x,y), then a WHERE condition such as t.a = r.x AND t.b = r.y cannot select more than one r row per t row. The planner formerly considered these AND conditions to be independent and would often drastically misestimate selectivity as a result. Now it compares the WHERE conditions to applicable foreign key constraints and produces better estimates.

VACUUM
  • (9.6) Avoid re-vacuuming pages containing only frozen tuples (Masahiko Sawada, Robert Haas, Andres Freund)

    Formerly, anti-wraparound vacuum had to visit every page of a table, even pages where there was nothing to do. Now, pages containing only already-frozen tuples are identified in the table's visibility map, and can be skipped by vacuum even when doing transaction wraparound prevention. This should greatly reduce the cost of maintaining large tables containing mostly-unchanging data.

    If necessary, vacuum can be forced to process all-frozen pages using the new DISABLE_PAGE_SKIPPING option. Normally this should never be needed, but it might help in recovering from visibility-map corruption.

  • (9.6) Avoid useless heap-truncation attempts during VACUUM (Jeff Janes, Tom Lane)

    This change avoids taking an exclusive table lock in some cases where no truncation is possible. The main benefit comes from avoiding unnecessary query cancellations on standby servers.

General Performance
  • (9.6) Allow old MVCC snapshots to be invalidated after a configurable timeout (Kevin Grittner)

    Normally, deleted tuples cannot be physically removed by vacuuming until the last transaction that could "see" them is gone. A transaction that stays open for a long time can thus cause considerable table bloat because space cannot be recycled. This feature allows setting a time-based limit, via the new configuration parameter old_snapshot_threshold, on how long an MVCC snapshot is guaranteed to be valid. After that, dead tuples are candidates for removal. A transaction using an outdated snapshot will get an error if it attempts to read a page that potentially could have contained such data.

  • (9.6) Ignore GROUP BY columns that are functionally dependent on other columns (David Rowley)

    If a GROUP BY clause includes all columns of a non-deferred primary key, as well as other columns of the same table, those other columns are redundant and can be dropped from the grouping. This saves computation in many common cases.

  • (9.6) Allow use of an index-only scan on a partial index when the index's WHERE clause references columns that are not indexed (Tomas Vondra, Kyotaro Horiguchi)

    For example, an index defined by CREATE INDEX tidx_partial ON t(b) WHERE a > 0 can now be used for an index-only scan by a query that specifies WHERE a > 0 and does not otherwise use a. Previously this was disallowed because a is not listed as an index column.

  • (9.6) Perform checkpoint writes in sorted order (Fabien Coelho, Andres Freund)

    Previously, checkpoints wrote out dirty pages in whatever order they happen to appear in shared buffers, which usually is nearly random. That performs poorly, especially on rotating media. This change causes checkpoint-driven writes to be done in order by file and block number, and to be balanced across tablespaces.

  • (9.6) Where feasible, trigger kernel writeback after a configurable number of writes, to prevent accumulation of dirty data in kernel disk buffers (Fabien Coelho, Andres Freund)

    PostgreSQL writes data to the kernel's disk cache, from where it will be flushed to physical storage in due time. Many operating systems are not smart about managing this and allow large amounts of dirty data to accumulate before deciding to flush it all at once, causing long delays for new I/O requests until the flushing finishes. This change attempts to alleviate this problem by explicitly requesting data flushes after a configurable interval.

    On Linux, sync_file_range() is used for this purpose, and the feature is on by default on Linux because that function has few downsides. This flushing capability is also available on other platforms if they have msync() or posix_fadvise(), but those interfaces have some undesirable side-effects so the feature is disabled by default on non-Linux platforms.

    The new configuration parameters backend_flush_after, bgwriter_flush_after, checkpoint_flush_after, and wal_writer_flush_after control this behavior.

  • (9.6) Improve aggregate-function performance by sharing calculations across multiple aggregates if they have the same arguments and transition functions (David Rowley)

    For example, SELECT AVG(x), VARIANCE(x) FROM tab can use a single per-row computation for both aggregates.

  • (9.6) Speed up visibility tests for recently-created tuples by checking the current transaction's snapshot, not pg_clog, to decide if the source transaction should be considered committed (Jeff Janes, Tom Lane)

  • (9.6) Allow tuple hint bits to be set sooner than before (Andres Freund)

  • (9.6) Improve performance of short-lived prepared transactions (Stas Kelvich, Simon Riggs, Pavan Deolasee)

    Two-phase commit information is now written only to WAL during PREPARE TRANSACTION, and will be read back from WAL during COMMIT PREPARED if that happens soon thereafter. A separate state file is created only if the pending transaction does not get committed or aborted by the time of the next checkpoint.

  • (9.6) Improve performance of memory context destruction (Jan Wieck)

  • (9.6) Improve performance of resource owners with many tracked objects (Aleksander Alekseev)

  • (9.6) Improve speed of the output functions for timestamp, time, and date data types (David Rowley, Andres Freund)

  • (9.6) Avoid some unnecessary cancellations of hot-standby queries during replay of actions that take AccessExclusive locks (Jeff Janes)

  • (9.6) Extend relations multiple blocks at a time when there is contention for the relation's extension lock (Dilip Kumar)

    This improves scalability by decreasing contention.

  • (9.6) Increase the number of clog buffers for better scalability (Amit Kapila, Andres Freund)

  • (9.6) Speed up expression evaluation in PL/pgSQL by keeping ParamListInfo entries for simple variables valid at all times (Tom Lane)

  • (9.6) Avoid reducing the SO_SNDBUF setting below its default on recent Windows versions (Chen Huajun)

  • (9.6) Disable update_process_title by default on Windows (Takayuki Tsunakawa)

    The overhead of updating the process title is much larger on Windows than most other platforms, and it is also less useful to do it since most Windows users do not have tools that can display process titles.

Monitoring
  • (9.6) Add pg_stat_progress_vacuum system view to provide progress reporting for VACUUM operations (Amit Langote, Robert Haas, Vinayak Pokale, Rahila Syed)

  • (9.6) Add pg_control_system(), pg_control_checkpoint(), pg_control_recovery(), and pg_control_init() functions to expose fields of pg_control to SQL (Joe Conway, Michael Paquier)

  • (9.6) Add pg_config system view (Joe Conway)

    This view exposes the same information available from the pg_config command-line utility, namely assorted compile-time configuration information for PostgreSQL.

  • (9.6) Add a confirmed_flush_lsn column to the pg_replication_slots system view (Marko Tiikkaja)

  • (9.6) Add pg_stat_wal_receiver system view to provide information about the state of a hot-standby server's WAL receiver process (Michael Paquier)

  • (9.6) Add pg_blocking_pids() function to reliably identify which sessions block which others (Tom Lane)

    This function returns an array of the process IDs of any sessions that are blocking the session with the given process ID. Historically users have obtained such information using a self-join on the pg_locks view. However, it is unreasonably tedious to do it that way with any modicum of correctness, and the addition of parallel queries has made the old approach entirely impractical, since locks might be held or awaited by child worker processes rather than the session's main process.

  • (9.6) Add function pg_current_xlog_flush_location() to expose the current transaction log flush location (Tomas Vondra)

  • (9.6) Add function pg_notification_queue_usage() to report how full the NOTIFY queue is (Brendan Jurd)

  • (9.6) Limit the verbosity of memory context statistics dumps (Tom Lane)

    The memory usage dump that is output to the postmaster log during an out-of-memory failure now summarizes statistics when there are a large number of memory contexts, rather than possibly generating a very large report. There is also a "grand total" summary line now.

Authentication
  • (9.6) Add a BSD authentication method to allow use of the BSD Authentication service for PostgreSQL client authentication (Marisa Emerson)

    BSD Authentication is currently only available on OpenBSD.

  • (9.6) When using PAM authentication, provide the client IP address or host name to PAM modules via the PAM_RHOST item (Grzegorz Sampolski)

  • (9.6) Provide detail in the postmaster log for more types of password authentication failure (Tom Lane)

    All ordinarily-reachable password authentication failure cases should now provide specific DETAIL fields in the log.

  • (9.6) Support RADIUS passwords up to 128 characters long (Marko Tiikkaja)

  • (9.6) Add new SSPI authentication parameters compat_realm and upn_username to control whether NetBIOS or Kerberos realm names and user names are used during SSPI authentication (Christian Ullrich)

Server Configuration
  • (9.6) Allow sessions to be terminated automatically if they are in idle-in-transaction state for too long (Vik Fearing)

    This behavior is controlled by the new configuration parameter idle_in_transaction_session_timeout. It can be useful to prevent forgotten transactions from holding locks or preventing vacuum cleanup for too long.

  • (9.6) Raise the maximum allowed value of checkpoint_timeout to 24 hours (Simon Riggs)

  • (9.6) Allow effective_io_concurrency to be set per-tablespace to support cases where different tablespaces have different I/O characteristics (Julien Rouhaud)

  • (9.6) Add log_line_prefix option %n to print the current time in Unix epoch form, with milliseconds (Tomas Vondra, Jeff Davis)

  • (9.6) Add syslog_sequence_numbers and syslog_split_messages configuration parameters to provide more control over the message format when logging to syslog (Peter Eisentraut)

  • (9.6) Merge the archive and hot_standby values of the wal_level configuration parameter into a single new value replica (Peter Eisentraut)

    Making a distinction between these settings is no longer useful, and merging them is a step towards a planned future simplification of replication setup. The old names are still accepted but are converted to replica internally.

  • (9.6) Add configure option --with-systemd to enable calling sd_notify() at server start and stop (Peter Eisentraut)

    This allows the use of systemd service units of type notify, which greatly simplifies the management of PostgreSQL under systemd.

  • (9.6) Allow the server's SSL key file to have group read access if it is owned by root (Christoph Berg)

    Formerly, we insisted the key file be owned by the user running the PostgreSQL server, but that is inconvenient on some systems (such as Debian) that are configured to manage certificates centrally. Therefore, allow the case where the key file is owned by root and has group read access. It is up to the operating system administrator to ensure that the group does not include any untrusted users.

Reliability
  • (9.6) Force backends to exit if the postmaster dies (Rajeev Rastogi, Robert Haas)

    Under normal circumstances the postmaster should always outlive its child processes. If for some reason the postmaster dies, force backend sessions to exit with an error. Formerly, existing backends would continue to run until their clients disconnect, but that is unsafe and inefficient. It also prevents a new postmaster from being started until the last old backend has exited. Backends will detect postmaster death when waiting for client I/O, so the exit will not be instantaneous, but it should happen no later than the end of the current query.

  • (9.6) Check for serializability conflicts before reporting constraint-violation failures (Thomas Munro)

    When using serializable transaction isolation, it is desirable that any error due to concurrent transactions should manifest as a serialization failure, thereby cueing the application that a retry might succeed. Unfortunately, this does not reliably happen for duplicate-key failures caused by concurrent insertions. This change ensures that such an error will be reported as a serialization error if the application explicitly checked for the presence of a conflicting key (and did not find it) earlier in the transaction.

  • (9.6) Ensure that invalidation messages are recorded in WAL even when issued by a transaction that has no XID assigned (Andres Freund)

    This fixes some corner cases in which transactions on standby servers failed to notice changes, such as new indexes.

  • (9.6) Prevent multiple processes from trying to clean a GIN index's pending list concurrently (Teodor Sigaev, Jeff Janes)

    This had been intentionally allowed, but it causes race conditions that can result in vacuum missing index entries it needs to delete.

Replication and Recovery

  • (9.6) Allow synchronous replication to support multiple simultaneous synchronous standby servers, not just one (Masahiko Sawada, Beena Emerson, Michael Paquier, Fujii Masao, Kyotaro Horiguchi)

    The number of standby servers that must acknowledge a commit before it is considered complete is now configurable as part of the synchronous_standby_names parameter.

  • (9.6) Add new setting remote_apply for configuration parameter synchronous_commit (Thomas Munro)

    In this mode, the master waits for the transaction to be applied on the standby server, not just written to disk. That means that you can count on a transaction started on the standby to see all commits previously acknowledged by the master.

  • (9.6) Add a feature to the replication protocol, and a corresponding option to pg_create_physical_replication_slot(), to allow reserving WAL immediately when creating a replication slot (Gurjeet Singh, Michael Paquier)

    This allows the creation of a replication slot to guarantee that all the WAL needed for a base backup will be available.

  • (9.6) Add a --slot option to pg_basebackup (Peter Eisentraut)

    This lets pg_basebackup use a replication slot defined for WAL streaming. After the base backup completes, selecting the same slot for regular streaming replication allows seamless startup of the new standby server.

  • (9.6) Extend pg_start_backup() and pg_stop_backup() to support non-exclusive backups (Magnus Hagander)

Queries

  • (9.6) Allow functions that return sets of tuples to return simple NULLs (Andrew Gierth, Tom Lane)

    In the context of SELECT FROM function(...), a function that returned a set of composite values was previously not allowed to return a plain NULL value as part of the set. Now that is allowed and interpreted as a row of NULLs. This avoids corner-case errors with, for example, unnesting an array of composite values.

  • (9.6) Fully support array subscripts and field selections in the target column list of an INSERT with multiple VALUES rows (Tom Lane)

    Previously, such cases failed if the same target column was mentioned more than once, e.g., INSERT INTO tab (x[1], x[2]) VALUES (...).

  • (9.6) When appropriate, postpone evaluation of SELECT output expressions until after an ORDER BY sort (Konstantin Knizhnik)

    This change ensures that volatile or expensive functions in the output list are executed in the order suggested by ORDER BY, and that they are not evaluated more times than required when there is a LIMIT clause. Previously, these properties held if the ordering was performed by an index scan or pre-merge-join sort, but not if it was performed by a top-level sort.

  • (9.6) Widen counters recording the number of tuples processed to 64 bits (Andreas Scherbaum)

    This change allows command tags, e.g. SELECT, to correctly report tuple counts larger than 4 billion. This also applies to PL/pgSQL's GET DIAGNOSTICS ... ROW_COUNT command.

  • (9.6) Avoid doing encoding conversions by converting through the MULE_INTERNAL encoding (Tom Lane)

    Previously, many conversions for Cyrillic and Central European single-byte encodings were done by converting to a related MULE_INTERNAL coding scheme and then to the destination encoding. Aside from being inefficient, this meant that when the conversion encountered an untranslatable character, the error message would confusingly complain about failure to convert to or from MULE_INTERNAL, rather than the user-visible encoding.

  • (9.6) Consider performing joins of foreign tables remotely only when the tables will be accessed under the same role ID (Shigeru Hanada, Ashutosh Bapat, Etsuro Fujita)

    Previously, the foreign join pushdown infrastructure left the question of security entirely up to individual foreign data wrappers, but that made it too easy for an FDW to inadvertently create subtle security holes. So, make it the core code's job to determine which role ID will access each table, and do not attempt join pushdown unless the role is the same for all relevant relations.

Utility Commands

  • (9.6) Allow COPY to copy the output of an INSERT/UPDATE/DELETE ... RETURNING query (Marko Tiikkaja)

    Previously, an intermediate CTE had to be written to get this result.

  • (9.6) Introduce ALTER object DEPENDS ON EXTENSION (Abhijit Menon-Sen)

    This command allows a database object to be marked as depending on an extension, so that it will be dropped automatically if the extension is dropped (without needing CASCADE). However, the object is not part of the extension, and thus will be dumped separately by pg_dump.

  • (9.6) Make ALTER object SET SCHEMA do nothing when the object is already in the requested schema, rather than throwing an error as it historically has for most object types (Marti Raudsepp)

  • (9.6) Add options to ALTER OPERATOR to allow changing the selectivity functions associated with an existing operator (Yury Zhuravlev)

  • (9.6) Add an IF NOT EXISTS option to ALTER TABLE ADD COLUMN (Fabrízio de Royes Mello)

  • (9.6) Reduce the lock strength needed by ALTER TABLE when setting fillfactor and autovacuum-related relation options (Fabrízio de Royes Mello, Simon Riggs)

  • (9.6) Introduce CREATE ACCESS METHOD to allow extensions to create index access methods (Alexander Korotkov, Petr Jelínek)

  • (9.6) Add a CASCADE option to CREATE EXTENSION to automatically create any extensions the requested one depends on (Petr Jelínek)

  • (9.6) Make CREATE TABLE ... LIKE include an OID column if any source table has one (Bruce Momjian)

  • (9.6) If a CHECK constraint is declared NOT VALID in a table creation command, automatically mark it as valid (Amit Langote, Amul Sul)

    This is safe because the table has no existing rows. This matches the longstanding behavior of FOREIGN KEY constraints.

  • (9.6) Fix DROP OPERATOR to clear pg_operator.oprcom and pg_operator.oprnegate links to the dropped operator (Roma Sokolov)

    Formerly such links were left as-is, which could pose a problem in the somewhat unlikely event that the dropped operator's OID was reused for another operator.

  • (9.6) Do not show the same subplan twice in EXPLAIN output (Tom Lane)

    In certain cases, typically involving SubPlan nodes in index conditions, EXPLAIN would print data for the same subplan twice.

  • (9.6) Disallow creation of indexes on system columns, except for OID columns (David Rowley)

    Such indexes were never considered supported, and would very possibly misbehave since the system might change the system-column fields of a tuple without updating indexes. However, previously there were no error checks to prevent them from being created.

Permissions Management

  • (9.6) Use the privilege system to manage access to sensitive functions (Stephen Frost)

    Formerly, many security-sensitive functions contained hard-wired checks that would throw an error if they were called by a non-superuser. This forced the use of superuser roles for some relatively pedestrian tasks. The hard-wired error checks are now gone in favor of making initdb revoke the default public EXECUTE privilege on these functions. This allows installations to choose to grant usage of such functions to trusted roles that do not need all superuser privileges.

  • (9.6) Create some built-in roles that can be used to grant access to what were previously superuser-only functions (Stephen Frost)

    Currently the only such role is pg_signal_backend, but more are expected to be added in future.

Data Types

  • (9.6) Improve full-text search to support searching for phrases, that is, lexemes appearing adjacent to each other in a specific order, or with a specified distance between them (Teodor Sigaev, Oleg Bartunov, Dmitry Ivanov)

    A phrase-search query can be specified in tsquery input using the new operators <-> and <N>. The former means that the lexemes before and after it must appear adjacent to each other in that order. The latter means they must be exactly N lexemes apart.

  • (9.6) Allow omitting one or both boundaries in an array slice specifier, e.g. array_col[3:] (Yury Zhuravlev)

    Omitted boundaries are taken as the upper or lower limit of the corresponding array subscript. This allows simpler specification for many common use-cases.

  • (9.6) Be more careful about out-of-range dates and timestamps (Vitaly Burovoy)

    This change prevents unexpected out-of-range errors for timestamp with time zone values very close to the implementation limits. Previously, the "same" value might be accepted or not depending on the timezone setting, meaning that a dump and reload could fail on a value that had been accepted when presented. Now the limits are enforced according to the equivalent UTC time, not local time, so as to be independent of timezone.

    Also, PostgreSQL is now more careful to detect overflow in operations that compute new date or timestamp values, such as date + integer.

  • (9.6) For geometric data types, make sure infinity and NaN component values are treated consistently during input and output (Tom Lane)

    Such values will now always print the same as they would in a simple float8 column, and be accepted the same way on input. Previously the behavior was platform-dependent.

  • (9.6) Upgrade the ispell dictionary type to handle modern Hunspell files and support more languages (Artur Zakirov)

  • (9.6) Implement look-behind constraints in regular expressions (Tom Lane)

    A look-behind constraint is like a lookahead constraint in that it consumes no text; but it checks for existence (or nonexistence) of a match ending at the current point in the string, rather than one starting at the current point. Similar features exist in many other regular-expression engines.

  • (9.6) In regular expressions, if an apparent three-digit octal escape \nnn would exceed 377 (255 decimal), assume it is a two-digit octal escape instead (Tom Lane)

    This makes the behavior match current Tcl releases.

  • (9.6) Add transaction ID operators xid <> xid and xid <> int4, for consistency with the corresponding equality operators (Michael Paquier)

Functions

  • (9.6) Add jsonb_insert() function to insert a new element into a jsonb array, or a not-previously-existing key into a jsonb object (Dmitry Dolgov)

  • (9.6) Improve the accuracy of the ln(), log(), exp(), and pow() functions for type numeric (Dean Rasheed)

  • (9.6) Add a scale(numeric) function to extract the display scale of a numeric value (Marko Tiikkaja)

  • (9.6) Add trigonometric functions that work in degrees (Dean Rasheed)

    For example, sind() measures its argument in degrees, whereas sin() measures in radians. These functions go to some lengths to deliver exact results for values where an exact result can be expected, for instance sind(30) = 0.5.

  • (9.6) Ensure that trigonometric functions handle infinity and NaN inputs per the POSIX standard (Dean Rasheed)

    The POSIX standard says that these functions should return NaN for NaN input, and should throw an error for out-of-range inputs including infinity. Previously our behavior varied across platforms.

  • (9.6) Make to_timestamp(float8) convert float infinity to timestamp infinity (Vitaly Burovoy)

    Formerly it just failed on an infinite input.

  • (9.6) Add new functions for tsvector data (Stas Kelvich)

    The new functions are ts_delete(), ts_filter(), unnest(), tsvector_to_array(), array_to_tsvector(), and a variant of setweight() that sets the weight only for specified lexeme(s).

  • (9.6) Allow ts_stat() and tsvector_update_trigger() to operate on values that are of types binary-compatible with the expected argument type, not just exactly that type; for example allow citext where text is expected (Teodor Sigaev)

  • (9.6) Add variadic functions num_nulls() and num_nonnulls() that count the number of their arguments that are null or non-null (Marko Tiikkaja)

    An example usage is CHECK(num_nonnulls(a,b,c) = 1) which asserts that exactly one of a,b,c is not NULL. These functions can also be used to count the number of null or nonnull elements in an array.

  • (9.6) Add function parse_ident() to split a qualified, possibly quoted SQL identifier into its parts (Pavel Stehule)

  • (9.6) In to_number(), interpret a V format code as dividing by 10 to the power of the number of digits following V (Bruce Momjian)

    This makes it operate in an inverse fashion to to_char().

  • (9.6) Make the to_reg*() functions accept type text not cstring (Petr Korobeinikov)

    This avoids the need to write an explicit cast in most cases where the argument is not a simple literal constant.

  • (9.6) Add pg_size_bytes() function to convert human-readable size strings to numbers (Pavel Stehule, Vitaly Burovoy, Dean Rasheed)

    This function converts strings like those produced by pg_size_pretty() into bytes. An example usage is SELECT oid::regclass FROM pg_class WHERE pg_total_relation_size(oid) > pg_size_bytes('10 GB').

  • (9.6) In pg_size_pretty(), format negative numbers similarly to positive ones (Adrian Vondendriesch)

    Previously, negative numbers were never abbreviated, just printed in bytes.

  • (9.6) Add an optional missing_ok argument to the current_setting() function (David Christensen)

    This allows avoiding an error for an unrecognized parameter name, instead returning a NULL.

  • (9.6) Change various catalog-inspection functions to return NULL for invalid input (Michael Paquier)

    pg_get_viewdef() now returns NULL if given an invalid view OID, and several similar functions likewise return NULL for bad input. Previously, such cases usually led to "cache lookup failed" errors, which are not meant to occur in user-facing cases.

  • (9.6) Fix pg_replication_origin_xact_reset() to not have any arguments (Fujii Masao)

    The documentation said that it has no arguments, and the C code did not expect any arguments, but the entry in pg_proc mistakenly specified two arguments.

Server-Side Languages

  • (9.6) In PL/pgSQL, detect mismatched CONTINUE and EXIT statements while compiling a function, rather than at execution time (Jim Nasby)

  • (9.6) Extend PL/Python's error-reporting and message-reporting functions to allow specifying additional message fields besides the primary error message (Pavel Stehule)

  • (9.6) Allow PL/Python functions to call themselves recursively via SPI, and fix the behavior when multiple set-returning PL/Python functions are called within one query (Alexey Grishchenko, Tom Lane)

  • (9.6) Fix session-lifespan memory leaks in PL/Python (Heikki Linnakangas, Haribabu Kommi, Tom Lane)

  • (9.6) Modernize PL/Tcl to use Tcl's "object" APIs instead of simple strings (Jim Nasby, Karl Lehenbauer)

    This can improve performance substantially in some cases. Note that PL/Tcl now requires Tcl 8.4 or later.

  • (9.6) In PL/Tcl, make database-reported errors return additional information in Tcl's errorCode global variable (Jim Nasby, Tom Lane)

    This feature follows the Tcl convention for returning auxiliary data about an error.

  • (9.6) Fix PL/Tcl to perform encoding conversion between the database encoding and UTF-8, which is what Tcl expects (Tom Lane)

    Previously, strings were passed through without conversion, leading to misbehavior with non-ASCII characters when the database encoding was not UTF-8.

Client Interfaces

  • (9.6) Add a nonlocalized version of the severity field in error and notice messages (Tom Lane)

    This change allows client code to determine severity of an error or notice without having to worry about localized variants of the severity strings.

  • (9.6) Introduce a feature in libpq whereby the CONTEXT field of messages can be suppressed, either always or only for non-error messages (Pavel Stehule)

    The default behavior of PQerrorMessage() is now to print CONTEXT only for errors. The new function PQsetErrorContextVisibility() can be used to adjust this.

  • (9.6) Add support in libpq for regenerating an error message with a different verbosity level (Alex Shulgin)

    This is done with the new function PQresultVerboseErrorMessage(). This supports psql's new \errverbose feature, and may be useful for other clients as well.

  • (9.6) Improve libpq's PQhost() function to return useful data for default Unix-socket connections (Tom Lane)

    Previously it would return NULL if no explicit host specification had been given; now it returns the default socket directory path.

  • (9.6) Fix ecpg's lexer to handle line breaks within comments starting on preprocessor directive lines (Michael Meskes)

Client Applications

  • (9.6) Add a --strict-names option to pg_dump and pg_restore (Pavel Stehule)

    This option causes the program to complain if there is no match for a -t or -n option, rather than silently doing nothing.

  • (9.6) In pg_dump, dump locally-made changes of privilege assignments for system objects (Stephen Frost)

    While it has always been possible for a superuser to change the privilege assignments for built-in or extension-created objects, such changes were formerly lost in a dump and reload. Now, pg_dump recognizes and dumps such changes. (This works only when dumping from a 9.6 or later server, however.)

  • (9.6) Allow pg_dump to dump non-extension-owned objects that are within an extension-owned schema (Martín Marqués)

    Previously such objects were ignored because they were mistakenly assumed to belong to the extension owning their schema.

  • (9.6) In pg_dump output, include the table name in object tags for object types that are only uniquely named per-table (for example, triggers) (Peter Eisentraut)

psql
  • (9.6) Support multiple -c and -f command-line options (Pavel Stehule, Catalin Iacob)

    The specified operations are carried out in the order in which the options are given, and then psql terminates.

  • (9.6) Add a \crosstabview command that prints the results of a query in a cross-tabulated display (Daniel Vérité)

    In the crosstab display, data values from one query result column are placed in a grid whose column and row headers come from other query result columns.

  • (9.6) Add an \errverbose command that shows the last server error at full verbosity (Alex Shulgin)

    This is useful after getting an unexpected error — you no longer need to adjust the VERBOSITY variable and recreate the failure in order to see error fields that are not shown by default.

  • (9.6) Add \ev and \sv commands for editing and showing view definitions (Petr Korobeinikov)

    These are parallel to the existing \ef and \sf commands for functions.

  • (9.6) Add a \gexec command that executes a query and re-submits the result(s) as new queries (Corey Huinker)

  • (9.6) Allow \pset C string to set the table title, for consistency with \C string (Bruce Momjian)

  • (9.6) In \pset expanded auto mode, do not use expanded format for query results with only one column (Andreas Karlsson, Robert Haas)

  • (9.6) Improve the headers output by the \watch command (Michael Paquier, Tom Lane)

    Include the \pset title string if one has been set, and shorten the prefabricated part of the header to be timestamp (every Ns). Also, the timestamp format now obeys psql's locale environment.

  • (9.6) Improve tab-completion logic to consider the entire input query, not only the current line (Tom Lane)

    Previously, breaking a command into multiple lines defeated any tab completion rules that needed to see words on earlier lines.

  • (9.6) Numerous minor improvements in tab-completion behavior (Peter Eisentraut, Vik Fearing, Kevin Grittner, Kyotaro Horiguchi, Jeff Janes, Andreas Karlsson, Fujii Masao, Thomas Munro, Masahiko Sawada, Pavel Stehule)

  • (9.6) Add a PROMPT option %p to insert the process ID of the connected backend (Julien Rouhaud)

  • (9.6) Introduce a feature whereby the CONTEXT field of messages can be suppressed, either always or only for non-error messages (Pavel Stehule)

    Printing CONTEXT only for errors is now the default behavior. This can be changed by setting the special variable SHOW_CONTEXT.

  • (9.6) Make \df+ show function access privileges and parallel-safety attributes (Michael Paquier)

pgbench
  • (9.6) SQL commands in pgbench scripts are now ended by semicolons, not newlines (Kyotaro Horiguchi, Tom Lane)

    This change allows SQL commands in scripts to span multiple lines. Existing custom scripts will need to be modified to add a semicolon at the end of each line that does not have one already. (Doing so does not break the script for use with older versions of pgbench.)

  • (9.6) Support floating-point arithmetic, as well as some built-in functions, in expressions in backslash commands (Fabien Coelho)

  • (9.6) Replace \setrandom with built-in functions (Fabien Coelho)

    The new built-in functions include random(), random_exponential(), and random_gaussian(), which perform the same work as \setrandom, but are easier to use since they can be embedded in larger expressions. Since these additions have made \setrandom obsolete, remove it.

  • (9.6) Allow invocation of multiple copies of the built-in scripts, not only custom scripts (Fabien Coelho)

    This is done with the new -b switch, which works similarly to -f for custom scripts.

  • (9.6) Allow changing the selection probabilities (weights) for scripts (Fabien Coelho)

    When multiple scripts are specified, each pgbench transaction randomly chooses one to execute. Formerly this was always done with uniform probability, but now different selection probabilities can be specified for different scripts.

  • (9.6) Collect statistics for each script in a multi-script run (Fabien Coelho)

    This feature adds an intermediate level of detail to existing global and per-command statistics printouts.

  • (9.6) Add a --progress-timestamp option to report progress with Unix epoch timestamps, instead of time since the run started (Fabien Coelho)

  • (9.6) Allow the number of client connections (-c) to not be an exact multiple of the number of threads (-j) (Fabien Coelho)

  • (9.6) When the -T option is used, stop promptly at the end of the specified time (Fabien Coelho)

    Previously, specifying a low transaction rate could cause pgbench to wait significantly longer than specified.

Server Applications

  • (9.6) Improve error reporting during initdb's post-bootstrap phase (Tom Lane)

    Previously, an error here led to reporting the entire input file as the "failing query"; now just the current query is reported. To get the desired behavior, queries in initdb's input files must be separated by blank lines.

  • (9.6) Speed up initdb by using just one standalone-backend session for all the post-bootstrap steps (Tom Lane)

  • (9.6) Improve pg_rewind so that it can work when the target timeline changes (Alexander Korotkov)

    This allows, for example, rewinding a promoted standby back to some state of the old master's timeline.

Source Code

  • (9.6) Remove obsolete heap_formtuple/heap_modifytuple/heap_deformtuple functions (Peter Geoghegan)

  • (9.6) Add macros to make AllocSetContextCreate() calls simpler and safer (Tom Lane)

    Writing out the individual sizing parameters for a memory context is now deprecated in favor of using one of the new macros ALLOCSET_DEFAULT_SIZES, ALLOCSET_SMALL_SIZES, or ALLOCSET_START_SMALL_SIZES. Existing code continues to work, however.

  • (9.6) Unconditionally use static inline functions in header files (Andres Freund)

    This may result in warnings and/or wasted code space with very old compilers, but the notational improvement seems worth it.

  • (9.6) Improve TAP testing infrastructure (Michael Paquier, Craig Ringer, Álvaro Herrera, Stephen Frost)

    Notably, it is now possible to test recovery scenarios using this infrastructure.

  • (9.6) Make trace_lwlocks identify individual locks by name (Robert Haas)

  • (9.6) Improve psql's tab-completion code infrastructure (Thomas Munro, Michael Paquier)

    Tab-completion rules are now considerably easier to write, and more compact.

  • (9.6) Nail the pg_shseclabel system catalog into cache, so that it is available for access during connection authentication (Adam Brightwell)

    The core code does not use this catalog for authentication, but extensions might wish to consult it.

  • (9.6) Restructure index access method API to hide most of it at the C level (Alexander Korotkov, Andrew Gierth)

    This change modernizes the index AM API to look more like the designs we have adopted for foreign data wrappers and tablesample handlers. This simplifies the C code and makes it much more practical to define index access methods in installable extensions. A consequence is that most of the columns of the pg_am system catalog have disappeared. New inspection functions have been added to allow SQL queries to determine index AM properties that used to be discoverable from pg_am.

  • (9.6) Add pg_init_privs system catalog to hold original privileges of initdb-created and extension-created objects (Stephen Frost)

    This infrastructure allows pg_dump to dump changes that an installation may have made in privileges attached to system objects. Formerly, such changes would be lost in a dump and reload, but now they are preserved.

  • (9.6) Change the way that extensions allocate custom LWLocks (Amit Kapila, Robert Haas)

    The RequestAddinLWLocks() function is removed, and replaced by RequestNamedLWLockTranche(). This allows better identification of custom LWLocks, and is less error-prone.

  • (9.6) Improve the isolation tester to allow multiple sessions to wait concurrently, allowing testing of deadlock scenarios (Robert Haas)

  • (9.6) Introduce extensible node types (KaiGai Kohei)

    This change allows FDWs or custom scan providers to store data in a plan tree in a more convenient format than was previously possible.

  • (9.6) Make the planner deal with post-scan/join query steps by generating and comparing Paths, replacing a lot of ad-hoc logic (Tom Lane)

    This change provides only marginal user-visible improvements today, but it enables future work on a lot of upper-planner improvements that were impractical to tackle using the old code structure.

  • (9.6) Support partial aggregation (David Rowley, Simon Riggs)

    This change allows the computation of an aggregate function to be split into separate parts, for example so that parallel worker processes can cooperate on computing an aggregate. In future it might allow aggregation across local and remote data to occur partially on the remote end.

  • (9.6) Add a generic command progress reporting facility (Vinayak Pokale, Rahila Syed, Amit Langote, Robert Haas)

  • (9.6) Separate out psql's flex lexer to make it usable by other client programs (Tom Lane, Kyotaro Horiguchi)

    This eliminates code duplication for programs that need to be able to parse SQL commands well enough to identify command boundaries. Doing that in full generality is more painful than one could wish, and up to now only psql has really gotten it right among our supported client programs.

    A new source-code subdirectory src/fe_utils/ has been created to hold this and other code that is shared across our client programs. Formerly such sharing was accomplished by symbolic linking or copying source files at build time, which was ugly and required duplicate compilation.

  • (9.6) Introduce WaitEventSet API to allow efficient waiting for event sets that usually do not change from one wait to the next (Andres Freund, Amit Kapila)

  • (9.6) Add a generic interface for writing WAL records (Alexander Korotkov, Petr Jelínek, Markus Nullmeier)

    This change allows extensions to write WAL records for changes to pages using a standard layout. The problem of needing to replay WAL without access to the extension is solved by having generic replay code. This allows extensions to implement, for example, index access methods and have WAL support for them.

  • (9.6) Support generic WAL messages for logical decoding (Petr Jelínek, Andres Freund)

    This feature allows extensions to insert data into the WAL stream that can be read by logical-decoding plugins, but is not connected to physical data restoration.

  • (9.6) Allow SP-GiST operator classes to store an arbitrary "traversal value" while descending the index (Alexander Lebedev, Teodor Sigaev)

    This is somewhat like the "reconstructed value", but it could be any arbitrary chunk of data, not necessarily of the same data type as the indexed column.

  • (9.6) Introduce a LOG_SERVER_ONLY message level for ereport() (David Steele)

    This level acts like LOG except that the message is never sent to the client. It is meant for use in auditing and similar applications.

  • (9.6) Provide a Makefile target to build all generated headers (Michael Paquier, Tom Lane)

    submake-generated-headers can now be invoked to ensure that generated backend header files are up-to-date. This is useful in subdirectories that might be built "standalone".

  • (9.6) Support OpenSSL 1.1.0 (Andreas Karlsson, Heikki Linnakangas)

Additional Modules

  • (9.6) Add configuration parameter auto_explain.sample_rate to allow contrib/auto_explain to capture just a configurable fraction of all queries (Craig Ringer, Julien Rouhaud)

    This allows reduction of overhead for heavy query traffic, while still getting useful information on average.

  • (9.6) Add contrib/bloom module that implements an index access method based on Bloom filtering (Teodor Sigaev, Alexander Korotkov)

    This is primarily a proof-of-concept for non-core index access methods, but it could be useful in its own right for queries that search many columns.

  • (9.6) In contrib/cube, introduce distance operators for cubes, and support kNN-style searches in GiST indexes on cube columns (Stas Kelvich)

  • (9.6) Make contrib/hstore's hstore_to_jsonb_loose() and hstore_to_json_loose() functions agree on what is a number (Tom Lane)

    Previously, hstore_to_jsonb_loose() would convert numeric-looking strings to JSON numbers, rather than strings, even if they did not exactly match the JSON syntax specification for numbers. This was inconsistent with hstore_to_json_loose(), so tighten the test to match the JSON syntax.

  • (9.6) Add selectivity estimation functions for contrib/intarray operators to improve plans for queries using those operators (Yury Zhuravlev, Alexander Korotkov)

  • (9.6) Make contrib/pageinspect's heap_page_items() function show the raw data in each tuple, and add new functions tuple_data_split() and heap_page_item_attrs() for inspection of individual tuple fields (Nikolay Shaplov)

  • (9.6) Add an optional S2K iteration count parameter to contrib/pgcrypto's pgp_sym_encrypt() function (Jeff Janes)

  • (9.6) Add support for "word similarity" to contrib/pg_trgm (Alexander Korotkov, Artur Zakirov)

    These functions and operators measure the similarity between one string and the most similar single word of another string.

  • (9.6) Add configuration parameter pg_trgm.similarity_threshold for contrib/pg_trgm's similarity threshold (Artur Zakirov)

    This threshold has always been configurable, but formerly it was controlled by special-purpose functions set_limit() and show_limit(). Those are now deprecated.

  • (9.6) Improve contrib/pg_trgm's GIN operator class to speed up index searches in which both common and rare keys appear (Jeff Janes)

  • (9.6) Improve performance of similarity searches in contrib/pg_trgm GIN indexes (Christophe Fornaroli)

  • (9.6) Add contrib/pg_visibility module to allow examining table visibility maps (Robert Haas)

  • (9.6) Add ssl_extension_info() function to contrib/sslinfo, to print information about SSL extensions present in the X509 certificate used for the current connection (Dmitry Voronin)

postgres_fdw
  • (9.6) Allow extension-provided operators and functions to be sent for remote execution, if the extension is whitelisted in the foreign server's options (Paul Ramsey)

    Users can enable this feature when the extension is known to exist in a compatible version in the remote database. It allows more efficient execution of queries involving extension operators.

  • (9.6) Consider performing sorts on the remote server (Ashutosh Bapat)

  • (9.6) Consider performing joins on the remote server (Shigeru Hanada, Ashutosh Bapat)

  • (9.6) When feasible, perform UPDATE or DELETE entirely on the remote server (Etsuro Fujita)

    Formerly, remote updates involved sending a SELECT FOR UPDATE command and then updating or deleting the selected rows one-by-one. While that is still necessary if the operation requires any local processing, it can now be done remotely if all elements of the query are safe to send to the remote server.

  • (9.6) Allow the fetch size to be set as a server or table option (Corey Huinker)

    Formerly, postgres_fdw always fetched 100 rows at a time from remote queries; now that behavior is configurable.

  • (9.6) Use a single foreign-server connection for local user IDs that all map to the same remote user (Ashutosh Bapat)

  • (9.6) Transmit query cancellation requests to the remote server (Michael Paquier, Etsuro Fujita)

    Previously, a local query cancellation request did not cause an already-sent remote query to terminate early.

Postgres version 9.5.9

Release date: 2017-08-31

This release contains a small number of fixes from 9.5.8. For information about new features in the 9.5 major release, see Section E.17.

 Migration to Version 9.5.9

A dump/restore is not required for those running 9.5.X.

However, if you are upgrading from a version earlier than 9.5.8, see Section E.9.

 Changes

Postgres version 9.5.8

Release 9.5.8

Release date: 2017-08-10

This release contains a variety of fixes from 9.5.7. For information about new features in the 9.5 major release, see Version 9.5.

Migration to Version 9.5.8

A dump/restore is not required for those running 9.5.X.

However, if you use foreign data servers that make use of user passwords for authentication, see the first changelog entry below.

Also, if you are upgrading from a version earlier than 9.5.7, see Version 9.5.7.

Changes

Postgres version 9.5.7

Release date: 2017-05-11

This release contains a variety of fixes from 9.5.6. For information about new features in the 9.5 major release, see Section E.17.

 Migration to Version 9.5.7

A dump/restore is not required for those running 9.5.X.

However, if you use foreign data servers that make use of user passwords for authentication, see the first changelog entry below.

Also, if you are using third-party replication tools that depend on logical decoding, see the fourth changelog entry below.

Also, if you are upgrading from a version earlier than 9.5.6, see Section E.11.

 Changes

Postgres version 9.5.6

Release date: 2017-02-09

This release contains a variety of fixes from 9.5.5. For information about new features in the 9.5 major release, see Section E.17.

 Migration to Version 9.5.6

A dump/restore is not required for those running 9.5.X.

However, if your installation has been affected by the bug described in the first changelog entry below, then after updating you may need to take action to repair corrupted indexes.

Also, if you are upgrading from a version earlier than 9.5.5, see Section E.12.

 Changes

Postgres version 9.5.5

Release 9.5.5

Release date: 2016-10-27

This release contains a variety of fixes from 9.5.4. For information about new features in the 9.5 major release, see Version 9.5.

Migration to Version 9.5.5

A dump/restore is not required for those running 9.5.X.

However, if your installation has been affected by the bug described in the first changelog entry below, then after updating you may need to take action to repair corrupted free space maps.

Also, if you are upgrading from a version earlier than 9.5.2, see Version 9.5.2.

Changes

Postgres version 9.5.4

Release 9.5.4

Release date: 2016-08-11

This release contains a variety of fixes from 9.5.3. For information about new features in the 9.5 major release, see Version 9.5.

Migration to Version 9.5.4

A dump/restore is not required for those running 9.5.X.

However, if you are upgrading from a version earlier than 9.5.2, see Version 9.5.2.

Changes

Postgres version 9.5.3

Release date: 2016-05-12

This release contains a variety of fixes from 9.5.2. For information about new features in the 9.5 major release, see Section E.17.

 Migration to Version 9.5.3

A dump/restore is not required for those running 9.5.X.

However, if you are upgrading from a version earlier than 9.5.2, see Section E.15.

 Changes

Postgres version 9.5.2

Release date: 2016-03-31

This release contains a variety of fixes from 9.5.1. For information about new features in the 9.5 major release, see Section E.17.

 Migration to Version 9.5.2

A dump/restore is not required for those running 9.5.X.

However, you may need to REINDEX some indexes after applying the update, as per the first changelog entry below.

 Changes

Postgres version 9.5.1

Release 9.5.1

Release date: 2016-02-11

This release contains a variety of fixes from 9.5.0. For information about new features in the 9.5 major release, see Version 9.5.

Migration to Version 9.5.1

A dump/restore is not required for those running 9.5.X.

Changes

Postgres version 9.5

Release 9.5

Release date: 2016-01-07

Overview

Major enhancements in PostgreSQL 9.5 include:

The above items are explained in more detail in the sections below.

Migration to Version 9.5

A dump/restore using pg_dumpall, or use of pg_upgrade, is required for those wishing to migrate data from any previous release.

Version 9.5 contains a number of changes that may affect compatibility with previous releases. Observe the following incompatibilities:

Changes

Below you will find a detailed account of the changes between PostgreSQL 9.5 and the previous major release.

Server

Indexes
  • (9.5) Add Block Range Indexes (BRIN) (Álvaro Herrera)

    BRIN indexes store only summary data (such as minimum and maximum values) for ranges of heap blocks. They are therefore very compact and cheap to update; but if the data is naturally clustered, they can still provide substantial speedup of searches.

  • (9.5) Allow queries to perform accurate distance filtering of bounding-box-indexed objects (polygons, circles) using GiST indexes (Alexander Korotkov, Heikki Linnakangas)

    Previously, to exploit such an index a subquery had to be used to select a large number of rows ordered by bounding-box distance, and the result then had to be filtered further with a more accurate distance calculation.

  • (9.5) Allow GiST indexes to perform index-only scans (Anastasia Lubennikova, Heikki Linnakangas, Andreas Karlsson)

  • (9.5) Add configuration parameter gin_pending_list_limit to control the size of GIN pending lists (Fujii Masao)

    This value can also be set on a per-index basis as an index storage parameter. Previously the pending-list size was controlled by work_mem, which was awkward because appropriate values for work_mem are often much too large for this purpose.

  • (9.5) Issue a warning during the creation of hash indexes because they are not crash-safe (Bruce Momjian)

General Performance
  • (9.5) Improve the speed of sorting of varchar, text, and numeric fields via "abbreviated" keys (Peter Geoghegan, Andrew Gierth, Robert Haas)

  • (9.5) Extend the infrastructure that allows sorting to be performed by inlined, non-SQL-callable comparison functions to cover CREATE INDEX, REINDEX, and CLUSTER (Peter Geoghegan)

  • (9.5) Improve performance of hash joins (Tomas Vondra, Robert Haas)

  • (9.5) Improve concurrency of shared buffer replacement (Robert Haas, Amit Kapila, Andres Freund)

  • (9.5) Reduce the number of page locks and pins during index scans (Kevin Grittner)

    The primary benefit of this is to allow index vacuums to be blocked less often.

  • (9.5) Make per-backend tracking of buffer pins more memory-efficient (Andres Freund)

  • (9.5) Improve lock scalability (Andres Freund)

    This particularly addresses scalability problems when running on systems with multiple CPU sockets.

  • (9.5) Allow the optimizer to remove unnecessary references to left-joined subqueries (David Rowley)

  • (9.5) Allow pushdown of query restrictions into subqueries with window functions, where appropriate (David Rowley)

  • (9.5) Allow a non-leakproof function to be pushed down into a security barrier view if the function does not receive any view output columns (Dean Rasheed)

  • (9.5) Teach the planner to use statistics obtained from an expression index on a boolean-returning function, when a matching function call appears in WHERE (Tom Lane)

  • (9.5) Make ANALYZE compute basic statistics (null fraction and average column width) even for columns whose data type lacks an equality function (Oleksandr Shulgin)

  • (9.5) Speed up CRC (cyclic redundancy check) computations and switch to CRC-32C (Abhijit Menon-Sen, Heikki Linnakangas)

  • (9.5) Improve bitmap index scan performance (Teodor Sigaev, Tom Lane)

  • (9.5) Speed up CREATE INDEX by avoiding unnecessary memory copies (Robert Haas)

  • (9.5) Increase the number of buffer mapping partitions (Amit Kapila, Andres Freund, Robert Haas)

    This improves performance for highly concurrent workloads.

Monitoring
  • (9.5) Add per-table autovacuum logging control via new log_autovacuum_min_duration storage parameter (Michael Paquier)

  • (9.5) Add new configuration parameter cluster_name (Thomas Munro)

    This string, typically set in postgresql.conf, allows clients to identify the cluster. This name also appears in the process title of all server processes, allowing for easier identification of processes belonging to the same cluster.

  • (9.5) Prevent non-superusers from changing log_disconnections on connection startup (Fujii Masao)

SSL
  • (9.5) Check "Subject Alternative Names" in SSL server certificates, if present (Alexey Klyukin)

    When they are present, this replaces checks against the certificate's "Common Name".

  • (9.5) Add system view pg_stat_ssl to report SSL connection information (Magnus Hagander)

  • (9.5) Add libpq functions to return SSL information in an implementation-independent way (Heikki Linnakangas)

    While PQgetssl() can still be used to call OpenSSL functions, it is now considered deprecated because future versions of libpq might support other SSL implementations. When possible, use the new functions PQsslAttribute(), PQsslAttributeNames(), and PQsslInUse() to obtain SSL information in an SSL-implementation-independent way.

  • (9.5) Make libpq honor any OpenSSL thread callbacks (Jan Urbanski)

    Previously they were overwritten.

Server Settings
  • (9.5) Replace configuration parameter checkpoint_segments with min_wal_size and max_wal_size (Heikki Linnakangas)

    This change allows the allocation of a large number of WAL files without keeping them after they are no longer needed. Therefore the default for max_wal_size has been set to 1GB, much larger than the old default for checkpoint_segments. Also note that standby servers perform restartpoints to try to limit their WAL space consumption to max_wal_size; previously they did not pay any attention to checkpoint_segments.

  • (9.5) Control the Linux OOM killer via new environment variables PG_OOM_ADJUST_FILE and PG_OOM_ADJUST_VALUE (Gurjeet Singh)

    The previous OOM control infrastructure involved compile-time options LINUX_OOM_SCORE_ADJ and LINUX_OOM_ADJ, which are no longer supported. The new behavior is available in all builds.

  • (9.5) Allow recording of transaction commit time stamps when configuration parameter track_commit_timestamp is enabled (Álvaro Herrera, Petr Jelínek)

    Time stamp information can be accessed using functions pg_xact_commit_timestamp() and pg_last_committed_xact().

  • (9.5) Allow local_preload_libraries to be set by ALTER ROLE SET (Peter Eisentraut, Kyotaro Horiguchi)

  • (9.5) Allow autovacuum workers to respond to configuration parameter changes during a run (Michael Paquier)

  • (9.5) Make configuration parameter debug_assertions read-only (Andres Freund)

    This means that assertions can no longer be turned off if they were enabled at compile time, allowing for more efficient code optimization. This change also removes the postgres -A option.

  • (9.5) Allow setting effective_io_concurrency on systems where it has no effect (Peter Eisentraut)

  • (9.5) Add system view pg_file_settings to show the contents of the server's configuration files (Sawada Masahiko)

  • (9.5) Add pending_restart to the system view pg_settings to indicate a change has been made but will not take effect until a database restart (Peter Eisentraut)

  • (9.5) Allow ALTER SYSTEM values to be reset with ALTER SYSTEM RESET (Vik Fearing)

    This command removes the specified setting from postgresql.auto.conf.

Replication and Recovery

  • (9.5) Create mechanisms for tracking the progress of replication, including methods for identifying the origin of individual changes during logical replication (Andres Freund)

    This is helpful when implementing replication solutions.

  • (9.5) Rework truncation of the multixact commit log to be properly WAL-logged (Andres Freund)

    This makes things substantially simpler and more robust.

  • (9.5) Add recovery.conf parameter recovery_target_action to control post-recovery activity (Petr Jelínek)

    This replaces the old parameter pause_at_recovery_target.

  • (9.5) Add new archive_mode value always to allow standbys to always archive received WAL files (Fujii Masao)

  • (9.5) Add configuration parameter wal_retrieve_retry_interval to control WAL read retry after failure (Alexey Vasiliev, Michael Paquier)

    This is particularly helpful for warm standbys.

  • (9.5) Allow compression of full-page images stored in WAL (Rahila Syed, Michael Paquier)

    This feature reduces WAL volume, at the cost of more CPU time spent on WAL logging and WAL replay. It is controlled by a new configuration parameter wal_compression, which currently is off by default.

  • (9.5) Archive WAL files with suffix .partial during standby promotion (Heikki Linnakangas)

  • (9.5) Add configuration parameter log_replication_commands to log replication commands (Fujii Masao)

    By default, replication commands, e.g. IDENTIFY_SYSTEM, are not logged, even when log_statement is set to all.

  • (9.5) Report the processes holding replication slots in pg_replication_slots (Craig Ringer)

    The new output column is active_pid.

  • (9.5) Allow recovery.conf's primary_conninfo setting to use connection URIs, e.g. postgres:// (Alexander Shulgin)

Queries

  • (9.5) Allow INSERTs that would generate constraint conflicts to be turned into UPDATEs or ignored (Peter Geoghegan, Heikki Linnakangas, Andres Freund)

    The syntax is INSERT ... ON CONFLICT DO NOTHING/UPDATE. This is the Postgres implementation of the popular UPSERT command.

  • (9.5) Add GROUP BY analysis features GROUPING SETS, CUBE and ROLLUP (Andrew Gierth, Atri Sharma)

  • (9.5) Allow setting multiple target columns in an UPDATE from the result of a single sub-SELECT (Tom Lane)

    This is accomplished using the syntax UPDATE tab SET (col1, col2, ...) = (SELECT ...).

  • (9.5) Add SELECT option SKIP LOCKED to skip locked rows (Thomas Munro)

    This does not throw an error for locked rows like NOWAIT does.

  • (9.5) Add SELECT option TABLESAMPLE to return a subset of a table (Petr Jelínek)

    This feature supports the SQL-standard table sampling methods. In addition, there are provisions for user-defined table sampling methods.

  • (9.5) Suggest possible matches for mistyped column names (Peter Geoghegan, Robert Haas)

Utility Commands

  • (9.5) Add more details about sort ordering in EXPLAIN output (Marius Timmer, Lukas Kreft, Arne Scheffer)

    Details include COLLATE, DESC, USING, and NULLS FIRST/LAST.

  • (9.5) Make VACUUM log the number of pages skipped due to pins (Jim Nasby)

  • (9.5) Make TRUNCATE properly update the pg_stat* tuple counters (Alexander Shulgin)

REINDEX
  • (9.5) Allow REINDEX to reindex an entire schema using the SCHEMA option (Sawada Masahiko)

  • (9.5) Add VERBOSE option to REINDEX (Sawada Masahiko)

  • (9.5) Prevent REINDEX DATABASE and SCHEMA from outputting object names, unless VERBOSE is used (Simon Riggs)

  • (9.5) Remove obsolete FORCE option from REINDEX (Fujii Masao)

Object Manipulation

Foreign Tables
  • (9.5) Add support for IMPORT FOREIGN SCHEMA (Ronan Dunklau, Michael Paquier, Tom Lane)

    This command allows automatic creation of local foreign tables that match the structure of existing tables on a remote server.

  • (9.5) Allow CHECK constraints to be placed on foreign tables (Shigeru Hanada, Etsuro Fujita)

    Such constraints are assumed to be enforced on the remote server, and are not enforced locally. However, they are assumed to hold for purposes of query optimization, such as constraint exclusion.

  • (9.5) Allow foreign tables to participate in inheritance (Shigeru Hanada, Etsuro Fujita)

    To let this work naturally, foreign tables are now allowed to have check constraints marked as not valid, and to set storage and OID characteristics, even though these operations are effectively no-ops for a foreign table.

  • (9.5) Allow foreign data wrappers and custom scans to implement join pushdown (KaiGai Kohei)

Event Triggers
  • (9.5) Whenever a ddl_command_end event trigger is installed, capture details of DDL activity for it to inspect (Álvaro Herrera)

    This information is available through a set-returning function pg_event_trigger_ddl_commands(), or by inspection of C data structures if that function doesn't provide enough detail.

  • (9.5) Allow event triggers on table rewrites caused by ALTER TABLE (Dimitri Fontaine)

  • (9.5) Add event trigger support for database-level COMMENT, SECURITY LABEL, and GRANT/REVOKE (Álvaro Herrera)

  • (9.5) Add columns to the output of pg_event_trigger_dropped_objects (Álvaro Herrera)

    This allows simpler processing of delete operations.

Data Types

  • (9.5) Allow the xml data type to accept empty or all-whitespace content values (Peter Eisentraut)

    This is required by the SQL/XML specification.

  • (9.5) Allow macaddr input using the format xxxx-xxxx-xxxx (Herwin Weststrate)

  • (9.5) Disallow non-SQL-standard syntax for interval with both precision and field specifications (Bruce Momjian)

    Per the standard, such type specifications should be written as, for example, INTERVAL MINUTE TO SECOND(2). PostgreSQL formerly allowed this to be written as INTERVAL(2) MINUTE TO SECOND, but it must now be written in the standard way.

  • (9.5) Add selectivity estimators for inet/cidr operators and improve estimators for text search functions (Emre Hasegeli, Tom Lane)

  • (9.5) Add data types regrole and regnamespace to simplify entering and pretty-printing the OID of a role or namespace (Kyotaro Horiguchi)

JSON

Functions

  • (9.5) Add generate_series() for numeric values (Plato Malugin)

  • (9.5) Allow array_agg() and ARRAY() to take arrays as inputs (Ali Akbar, Tom Lane)

  • (9.5) Add functions array_position() and array_positions() to return subscripts of array values (Pavel Stehule)

  • (9.5) Add a point-to-polygon distance operator <-> (Alexander Korotkov)

  • (9.5) Allow multibyte characters as escapes in SIMILAR TO and SUBSTRING (Jeff Davis)

    Previously, only a single-byte character was allowed as an escape.

  • (9.5) Add a width_bucket() variant that supports any sortable data type and non-uniform bucket widths (Petr Jelínek)

  • (9.5) Add an optional missing_ok argument to pg_read_file() and related functions (Michael Paquier, Heikki Linnakangas)

  • (9.5) Allow => to specify named parameters in function calls (Pavel Stehule)

    Previously only := could be used. This requires removing the possibility for => to be a user-defined operator. Creation of user-defined => operators has been issuing warnings since PostgreSQL 9.0.

  • (9.5) Add POSIX-compliant rounding for platforms that use PostgreSQL-supplied rounding functions (Pedro Gimeno Fortea)

System Information Functions and Views
Aggregates
  • (9.5) Add min()/max() aggregates for inet/cidr data types (Haribabu Kommi)

  • (9.5) Use 128-bit integers, where supported, as accumulators for some aggregate functions (Andreas Karlsson)

Server-Side Languages

  • (9.5) Improve support for composite types in PL/Python (Ed Behn, Ronan Dunklau)

    This allows PL/Python functions to return arrays of composite types.

  • (9.5) Reduce lossiness of PL/Python floating-point value conversions (Marko Kreen)

  • (9.5) Allow specification of conversion routines between SQL data types and data types of procedural languages (Peter Eisentraut)

    This change adds new commands CREATE/DROP TRANSFORM. This also adds optional transformations between the hstore and ltree types to/from PL/Perl and PL/Python.

PL/pgSQL Server-Side Language
  • (9.5) Improve PL/pgSQL array performance (Tom Lane)

  • (9.5) Add an ASSERT statement in PL/pgSQL (Pavel Stehule)

  • (9.5) Allow more PL/pgSQL keywords to be used as identifiers (Tom Lane)

Client Applications

  • (9.5) Move pg_archivecleanup, pg_test_fsync, pg_test_timing, and pg_xlogdump from contrib to src/bin (Peter Eisentraut)

    This should result in these programs being installed by default in most installations.

  • (9.5) Add pg_rewind, which allows re-synchronizing a master server after failback (Heikki Linnakangas)

  • (9.5) Allow pg_receivexlog to manage physical replication slots (Michael Paquier)

    This is controlled via new --create-slot and --drop-slot options.

  • (9.5) Allow pg_receivexlog to synchronously flush WAL to storage using new --synchronous option (Furuya Osamu, Fujii Masao)

    Without this, WAL files are fsync'ed only on close.

  • (9.5) Allow vacuumdb to vacuum in parallel using new --jobs option (Dilip Kumar)

  • (9.5) In vacuumdb, do not prompt for the same password repeatedly when multiple connections are necessary (Haribabu Kommi, Michael Paquier)

  • (9.5) Add --verbose option to reindexdb (Sawada Masahiko)

  • (9.5) Make pg_basebackup use a tablespace mapping file when using tar format, to support symbolic links and file paths of 100+ characters in length on MS Windows (Amit Kapila)

  • (9.5) Add pg_xlogdump option --stats to display summary statistics (Abhijit Menon-Sen)

psql
  • (9.5) Allow psql to produce AsciiDoc output (Szymon Guz)

  • (9.5) Add an errors mode that displays only failed commands to psql's ECHO variable (Pavel Stehule)

    This behavior can also be selected with psql's -b option.

  • (9.5) Provide separate column, header, and border linestyle control in psql's unicode linestyle (Pavel Stehule)

    Single or double lines are supported; the default is single.

  • (9.5) Add new option %l in psql's PROMPT variables to display the current multiline statement line number (Sawada Masahiko)

  • (9.5) Add \pset option pager_min_lines to control pager invocation (Andrew Dunstan)

  • (9.5) Improve psql line counting used when deciding to invoke the pager (Andrew Dunstan)

  • (9.5) psql now fails if the file specified by an --output or --log-file switch cannot be written (Tom Lane, Daniel Vérité)

    Previously, it effectively ignored the switch in such cases.

  • (9.5) Add psql tab completion when setting the search_path variable (Jeff Janes)

    Currently only the first schema can be tab-completed.

  • (9.5) Improve psql's tab completion for triggers and rules (Andreas Karlsson)

Backslash Commands
  • (9.5) Add psql \? help sections variables and options (Pavel Stehule)

    \? variables shows psql's special variables and \? options shows the command-line options. \? commands shows the meta-commands, which is the traditional output and remains the default. These help displays can also be obtained with the command-line option --help=section.

  • (9.5) Show tablespace size in psql's \db+ (Fabrízio de Royes Mello)

  • (9.5) Show data type owners in psql's \dT+ (Magnus Hagander)

  • (9.5) Allow psql's \watch to output \timing information (Fujii Masao)

    Also prevent --echo-hidden from echoing \watch queries, since that is generally unwanted.

  • (9.5) Make psql's \sf and \ef commands honor ECHO_HIDDEN (Andrew Dunstan)

  • (9.5) Improve psql tab completion for \set, \unset, and :variable names (Pavel Stehule)

  • (9.5) Allow tab completion of role names in psql \c commands (Ian Barwick)

pg_dump
  • (9.5) Allow pg_dump to share a snapshot taken by another session using --snapshot (Simon Riggs, Michael Paquier)

    The remote snapshot must have been exported by pg_export_snapshot() or logical replication slot creation. This can be used to share a consistent snapshot across multiple pg_dump processes.

  • (9.5) Support table sizes exceeding 8GB in tar archive format (Tom Lane)

    The POSIX standard for tar format does not allow elements of a tar archive to exceed 8GB, but most modern implementations of tar support an extension that does allow it. Use the extension format when necessary, rather than failing.

  • (9.5) Make pg_dump always print the server and pg_dump versions (Jing Wang)

    Previously, version information was only printed in --verbose mode.

  • (9.5) Remove the long-ignored -i/--ignore-version option from pg_dump, pg_dumpall, and pg_restore (Fujii Masao)

pg_ctl
  • (9.5) Support multiple pg_ctl -o options, concatenating their values (Bruce Momjian)

  • (9.5) Allow control of pg_ctl's event source logging on MS Windows (MauMau)

    This only controls pg_ctl, not the server, which has separate settings in postgresql.conf.

  • (9.5) If the server's listen address is set to a wildcard value (0.0.0.0 in IPv4 or :: in IPv6), connect via the loopback address rather than trying to use the wildcard address literally (Kondo Yuta)

    This fix primarily affects Windows, since on other platforms pg_ctl will prefer to use a Unix-domain socket.

pg_upgrade
  • (9.5) Move pg_upgrade from contrib to src/bin (Peter Eisentraut)

    In connection with this change, the functionality previously provided by the pg_upgrade_support module has been moved into the core server.

  • (9.5) Support multiple pg_upgrade -o/-O options, concatenating their values (Bruce Momjian)

  • (9.5) Improve database collation comparisons in pg_upgrade (Heikki Linnakangas)

  • (9.5) Remove support for upgrading from 8.3 clusters (Bruce Momjian)

pgbench
  • (9.5) Move pgbench from contrib to src/bin (Peter Eisentraut)

  • (9.5) Fix calculation of TPS number "excluding connections establishing" (Tatsuo Ishii, Fabien Coelho)

    The overhead for connection establishment was miscalculated whenever the number of pgbench threads was less than the number of client connections. Although this is clearly a bug, we won't back-patch it into pre-9.5 branches since it makes TPS numbers not comparable to previous results.

  • (9.5) Allow counting of pgbench transactions that take over a specified amount of time (Fabien Coelho)

    This is controlled by a new --latency-limit option.

  • (9.5) Allow pgbench to generate Gaussian/exponential distributions using \setrandom (Kondo Mitsumasa, Fabien Coelho)

  • (9.5) Allow pgbench's \set command to handle arithmetic expressions containing more than one operator, and add % (modulo) to the set of operators it supports (Robert Haas, Fabien Coelho)

Source Code

  • (9.5) Simplify WAL record format (Heikki Linnakangas)

    This allows external tools to more easily track what blocks are modified.

  • (9.5) Improve the representation of transaction commit and abort WAL records (Andres Freund)

  • (9.5) Add atomic memory operations API (Andres Freund)

  • (9.5) Allow custom path and scan methods (KaiGai Kohei, Tom Lane)

    This allows extensions greater control over the optimizer and executor.

  • (9.5) Allow foreign data wrappers to do post-filter locking (Etsuro Fujita)

  • (9.5) Foreign tables can now take part in INSERT ... ON CONFLICT DO NOTHING queries (Peter Geoghegan, Heikki Linnakangas, Andres Freund)

    Foreign data wrappers must be modified to handle this. INSERT ... ON CONFLICT DO UPDATE is not supported on foreign tables.

  • (9.5) Improve hash_create()'s API for selecting simple-binary-key hash functions (Teodor Sigaev, Tom Lane)

  • (9.5) Improve parallel execution infrastructure (Robert Haas, Amit Kapila, Noah Misch, Rushabh Lathia, Jeevan Chalke)

  • (9.5) Remove Alpha (CPU) and Tru64 (OS) ports (Andres Freund)

  • (9.5) Remove swap-byte-based spinlock implementation for ARMv5 and earlier CPUs (Robert Haas)

    ARMv5's weak memory ordering made this locking implementation unsafe. Spinlock support is still possible on newer gcc implementations with atomics support.

  • (9.5) Generate an error when excessively long (100+ character) file paths are written to tar files (Peter Eisentraut)

    Tar does not support such overly-long paths.

  • (9.5) Change index operator class for columns pg_seclabel.provider and pg_shseclabel.provider to be text_pattern_ops (Tom Lane)

    This avoids possible problems with these indexes when different databases of a cluster have different default collations.

  • (9.5) Change the spinlock primitives to function as compiler barriers (Robert Haas)

MS Windows
  • (9.5) Allow higher-precision time stamp resolution on Windows 8, Windows Server 2012, and later Windows systems (Craig Ringer)

  • (9.5) Install shared libraries to bin in MS Windows (Peter Eisentraut, Michael Paquier)

  • (9.5) Install src/test/modules together with contrib on MSVC builds (Michael Paquier)

  • (9.5) Allow configure's --with-extra-version option to be honored by the MSVC build (Michael Paquier)

  • (9.5) Pass PGFILEDESC into MSVC contrib builds (Michael Paquier)

  • (9.5) Add icons to all MSVC-built binaries and version information to all MS Windows binaries (Noah Misch)

    MinGW already had such icons.

  • (9.5) Add optional-argument support to the internal getopt_long() implementation (Michael Paquier, Andres Freund)

    This is used by the MSVC build.

Additional Modules

  • (9.5) Add statistics for minimum, maximum, mean, and standard deviation times to pg_stat_statements (Mitsumasa Kondo, Andrew Dunstan)

  • (9.5) Add pgcrypto function pgp_armor_headers() to extract PGP armor headers (Marko Tiikkaja, Heikki Linnakangas)

  • (9.5) Allow empty replacement strings in unaccent (Mohammad Alhashash)

    This is useful in languages where diacritic signs are represented as separate characters.

  • (9.5) Allow multicharacter source strings in unaccent (Tom Lane)

    This could be useful in languages where diacritic signs are represented as separate characters. It also allows more complex unaccent dictionaries.

  • (9.5) Add contrib modules tsm_system_rows and tsm_system_time to allow additional table sampling methods (Petr Jelínek)

  • (9.5) Add GIN index inspection functions to pageinspect (Heikki Linnakangas, Peter Geoghegan, Michael Paquier)

  • (9.5) Add information about buffer pins to pg_buffercache display (Andres Freund)

  • (9.5) Allow pgstattuple to report approximate answers with less overhead using pgstattuple_approx() (Abhijit Menon-Sen)

  • (9.5) Move dummy_seclabel, test_shm_mq, test_parser, and worker_spi from contrib to src/test/modules (Álvaro Herrera)

    These modules are only meant for server testing, so they do not need to be built or installed when packaging PostgreSQL.

Postgres version 9.4.14

Release date: 2017-08-31

This release contains a small number of fixes from 9.4.13. For information about new features in the 9.4 major release, see Section E.32.

 Migration to Version 9.4.14

A dump/restore is not required for those running 9.4.X.

However, if you are upgrading from a version earlier than 9.4.13, see Section E.19.

 Changes

Postgres version 9.4.13

Release date: 2017-08-10

This release contains a variety of fixes from 9.4.12. For information about new features in the 9.4 major release, see Section E.32.

 Migration to Version 9.4.13

A dump/restore is not required for those running 9.4.X.

However, if you use foreign data servers that make use of user passwords for authentication, see the first changelog entry below.

Also, if you are upgrading from a version earlier than 9.4.12, see Section E.20.

 Changes

Postgres version 9.4.12

Release 9.4.12

Release date: 2017-05-11

This release contains a variety of fixes from 9.4.11. For information about new features in the 9.4 major release, see Version 9.4.

Migration to Version 9.4.12

A dump/restore is not required for those running 9.4.X.

However, if you use foreign data servers that make use of user passwords for authentication, see the first changelog entry below.

Also, if you are using third-party replication tools that depend on "logical decoding", see the fourth changelog entry below.

Also, if you are upgrading from a version earlier than 9.4.11, see Version 9.4.11.

Changes

Postgres version 9.4.11

Release date: 2017-02-09

This release contains a variety of fixes from 9.4.10. For information about new features in the 9.4 major release, see Section E.32.

 Migration to Version 9.4.11

A dump/restore is not required for those running 9.4.X.

However, if your installation has been affected by the bug described in the first changelog entry below, then after updating you may need to take action to repair corrupted indexes.

Also, if you are upgrading from a version earlier than 9.4.10, see Section E.22.

 Changes

Postgres version 9.4.10

Release date: 2016-10-27

This release contains a variety of fixes from 9.4.9. For information about new features in the 9.4 major release, see Section E.32.

 Migration to Version 9.4.10

A dump/restore is not required for those running 9.4.X.

However, if your installation has been affected by the bug described in the first changelog entry below, then after updating you may need to take action to repair corrupted free space maps.

Also, if you are upgrading from a version earlier than 9.4.6, see Section E.26.

 Changes

Postgres version 9.4.9

Release date: 2016-08-11

This release contains a variety of fixes from 9.4.8. For information about new features in the 9.4 major release, see Section E.32.

 Migration to Version 9.4.9

A dump/restore is not required for those running 9.4.X.

However, if you are upgrading from a version earlier than 9.4.6, see Section E.26.

 Changes

Postgres version 9.4.8

Release 9.4.8

Release date: 2016-05-12

This release contains a variety of fixes from 9.4.7. For information about new features in the 9.4 major release, see Version 9.4.

Migration to Version 9.4.8

A dump/restore is not required for those running 9.4.X.

However, if you are upgrading from a version earlier than 9.4.6, see Version 9.4.6.

Changes

Postgres version 9.4.7

Release date: 2016-03-31

This release contains a variety of fixes from 9.4.6. For information about new features in the 9.4 major release, see Section E.32.

 Migration to Version 9.4.7

A dump/restore is not required for those running 9.4.X.

However, if you are upgrading from a version earlier than 9.4.6, see Section E.26.

 Changes

Postgres version 9.4.6

Release 9.4.6

Release date: 2016-02-11

This release contains a variety of fixes from 9.4.5. For information about new features in the 9.4 major release, see Version 9.4.

Migration to Version 9.4.6

A dump/restore is not required for those running 9.4.X.

However, if you are upgrading an installation that contains any GIN indexes that use the (non-default) jsonb_path_ops operator class, see the first changelog entry below.

Also, if you are upgrading from a version earlier than 9.4.4, see Version 9.4.4.

Changes

Postgres version 9.4.5

Release date: 2015-10-08

This release contains a variety of fixes from 9.4.4. For information about new features in the 9.4 major release, see Section E.32.

 Migration to Version 9.4.5

A dump/restore is not required for those running 9.4.X.

However, if you are upgrading from a version earlier than 9.4.4, see Section E.28.

 Changes

Postgres version 9.4.4

Release 9.4.4

Release date: 2015-06-12

This release contains a small number of fixes from 9.4.3. For information about new features in the 9.4 major release, see Version 9.4.

Migration to Version 9.4.4

A dump/restore is not required for those running 9.4.X.

However, if you are upgrading an installation that was previously upgraded using a pg_upgrade version between 9.3.0 and 9.3.4 inclusive, see the first changelog entry below.

Also, if you are upgrading from a version earlier than 9.4.2, see Version 9.4.2.

Changes

Postgres version 9.4.3

Release date: 2015-06-04

This release contains a small number of fixes from 9.4.2. For information about new features in the 9.4 major release, see Section E.32.

 Migration to Version 9.4.3

A dump/restore is not required for those running 9.4.X.

However, if you are upgrading from a version earlier than 9.4.2, see Section E.30.

 Changes

Postgres version 9.4.2

Release date: 2015-05-22

This release contains a variety of fixes from 9.4.1. For information about new features in the 9.4 major release, see Section E.32.

 Migration to Version 9.4.2

A dump/restore is not required for those running 9.4.X.

However, if you use contrib/citext's regexp_matches() functions, see the changelog entry below about that.

Also, if you are upgrading from a version earlier than 9.4.1, see Section E.31.

 Changes

Postgres version 9.4.1

Release 9.4.1

Release date: 2015-02-05

This release contains a variety of fixes from 9.4.0. For information about new features in the 9.4 major release, see Version 9.4.

Migration to Version 9.4.1

A dump/restore is not required for those running 9.4.X.

However, if you are a Windows user and are using the "Norwegian (Bokmål)" locale, manual action is needed after the upgrade to replace any "Norwegian (Bokmål)_Norway" or "norwegian-bokmal" locale names stored in PostgreSQL system catalogs with the plain-ASCII alias "Norwegian_Norway". For details see http://wiki.postgresql.org/wiki/Changes_To_Norwegian_Locale

Changes

Postgres version 9.4

Release date: 2014-12-18

 Overview

Major enhancements in PostgreSQL 9.4 include:

The above items are explained in more detail in the sections below.

 Migration to Version 9.4

A dump/restore using pg_dumpall, or use of pg_upgrade, is required for those wishing to migrate data from any previous release.

Version 9.4 contains a number of changes that may affect compatibility with previous releases. Observe the following incompatibilities:

 Changes

Below you will find a detailed account of the changes between PostgreSQL 9.4 and the previous major release.

 Server
  • Allow background worker processes to be dynamically registered, started and terminated (Robert Haas)

    The new worker_spi module shows an example of use of this feature.

  • Allow dynamic allocation of shared memory segments (Robert Haas, Amit Kapila)

    This feature is illustrated in the test_shm_mq module.

  • During crash recovery or immediate shutdown, send uncatchable termination signals (SIGKILL) to child processes that do not shut down promptly (MauMau, Álvaro Herrera)

    This reduces the likelihood of leaving orphaned child processes behind after postmaster shutdown, as well as ensuring that crash recovery can proceed if some child processes have become stuck.

  • Improve randomness of the database system identifier (Tom Lane)

  • Make VACUUM properly report dead but not-yet-removable rows to the statistics collector (Hari Babu)

    Previously these were reported as live rows.

 Indexes
  • Reduce GIN index size (Alexander Korotkov, Heikki Linnakangas)

    Indexes upgraded via pg_upgrade will work fine but will still be in the old, larger GIN format. Use REINDEX to recreate old GIN indexes in the new format.

  • Improve speed of multi-key GIN lookups (Alexander Korotkov, Heikki Linnakangas)

  • Add GiST index support for inet and cidr data types (Emre Hasegeli)

    Such indexes improve subnet and supernet lookups and ordering comparisons.

  • Fix rare race condition in B-tree page deletion (Heikki Linnakangas)

  • Make the handling of interrupted B-tree page splits more robust (Heikki Linnakangas)

 General Performance
  • Allow multiple backends to insert into WAL buffers concurrently (Heikki Linnakangas)

    This improves parallel write performance.

  • Conditionally write only the modified portion of updated rows to WAL (Amit Kapila)

  • Improve performance of aggregate functions used as window functions (David Rowley, Florian Pflug, Tom Lane)

  • Improve speed of aggregates that use numeric state values (Hadi Moshayedi)

  • Attempt to freeze tuples when tables are rewritten with CLUSTER or VACUUM FULL (Robert Haas, Andres Freund)

    This can avoid the need to freeze the tuples in the future.

  • Improve speed of COPY with default nextval() columns (Simon Riggs)

  • Improve speed of accessing many different sequences in the same session (David Rowley)

  • Raise hard limit on the number of tuples held in memory during sorting and B-tree index builds (Noah Misch)

  • Reduce memory allocated by PL/pgSQL DO blocks (Tom Lane)

  • Make the planner more aggressive about extracting restriction clauses from mixed AND/OR clauses (Tom Lane)

  • Disallow pushing volatile WHERE clauses down into DISTINCT subqueries (Tom Lane)

    Pushing down a WHERE clause can produce a more efficient plan overall, but at the cost of evaluating the clause more often than is implied by the text of the query; so don't do it if the clause contains any volatile functions.

  • Auto-resize the catalog caches (Heikki Linnakangas)

    This reduces memory consumption for sessions accessing only a few tables, and improves performance for sessions accessing many tables.

 Monitoring
  • Add pg_stat_archiver system view to report WAL archiver activity (Gabriele Bartolini)

  • Add n_mod_since_analyze columns to pg_stat_all_tables and related system views (Mark Kirkwood)

    These columns expose the system's estimate of the number of changed tuples since the table's last ANALYZE. This estimate drives decisions about when to auto-analyze.

  • Add backend_xid and backend_xmin columns to the system view pg_stat_activity, and a backend_xmin column to pg_stat_replication (Christian Kruse)

 SSL
  • Add support for SSL ECDH key exchange (Marko Kreen)

    This allows use of Elliptic Curve keys for server authentication. Such keys are faster and have better security than RSA keys. The new configuration parameter ssl_ecdh_curve controls which curve is used for ECDH.

  • Improve the default ssl_ciphers setting (Marko Kreen)

  • By default, the server not the client now controls the preference order of SSL ciphers (Marko Kreen)

    Previously, the order specified by ssl_ciphers was usually ignored in favor of client-side defaults, which are not configurable in most PostgreSQL clients. If desired, the old behavior can be restored via the new configuration parameter ssl_prefer_server_ciphers.

  • Make log_connections show SSL encryption information (Andreas Kunert)

  • Improve SSL renegotiation handling (Álvaro Herrera)

 Server Settings
  • Add new SQL command ALTER SYSTEM for changing postgresql.conf configuration file entries (Amit Kapila)

    Previously such settings could only be changed by manually editing postgresql.conf.

  • Add autovacuum_work_mem configuration parameter to control the amount of memory used by autovacuum workers (Peter Geoghegan)

  • Add huge_pages parameter to allow using huge memory pages on Linux (Christian Kruse, Richard Poole, Abhijit Menon-Sen)

    This can improve performance on large-memory systems.

  • Add max_worker_processes parameter to limit the number of background workers (Robert Haas)

    This is helpful in configuring a standby server to have the required number of worker processes (the same as the primary).

  • Add superuser-only session_preload_libraries parameter to load libraries at session start (Peter Eisentraut)

    In contrast to local_preload_libraries, this parameter can load any shared library, not just those in the $libdir/plugins directory.

  • Add wal_log_hints parameter to enable WAL logging of hint-bit changes (Sawada Masahiko)

    Hint bit changes are not normally logged, except when checksums are enabled. This is useful for external tools like pg_rewind.

  • Increase the default settings of work_mem and maintenance_work_mem by four times (Bruce Momjian)

    The new defaults are 4MB and 64MB respectively.

  • Increase the default setting of effective_cache_size to 4GB (Bruce Momjian, Tom Lane)

  • Allow printf-style space padding to be specified in log_line_prefix (David Rowley)

  • Allow terabyte units (TB) to be used when specifying configuration variable values (Simon Riggs)

  • Show PIDs of lock holders and waiters and improve information about relations in log_lock_waits log messages (Christian Kruse)

  • Reduce server logging level when loading shared libraries (Peter Geoghegan)

    The previous level was LOG, which was too verbose for libraries loaded per-session.

  • On Windows, make SQL_ASCII-encoded databases and server processes (e.g., postmaster) emit messages in the character encoding of the server's Windows user locale (Alexander Law, Noah Misch)

    Previously these messages were output in the Windows ANSI code page.

 Replication and Recovery
  • Add replication slots to coordinate activity on streaming standbys with the node they are streaming from (Andres Freund, Robert Haas)

    Replication slots allow preservation of resources like WAL files on the primary until they are no longer needed by standby servers.

  • Add recovery parameter recovery_min_apply_delay to delay replication (Robert Haas, Fabrízio de Royes Mello, Simon Riggs)

    Delaying replay on standby servers can be useful for recovering from user errors.

  • Add recovery_target option immediate to stop WAL recovery as soon as a consistent state is reached (MauMau, Heikki Linnakangas)

  • Improve recovery target processing (Heikki Linnakangas)

    The timestamp reported by pg_last_xact_replay_timestamp() now reflects already-committed records, not transactions about to be committed. Recovering to a restore point now replays the restore point, rather than stopping just before the restore point.

  • pg_switch_xlog() now clears any unused trailing space in the old WAL file (Heikki Linnakangas)

    This improves the compression ratio for WAL files.

  • Report failure return codes from external recovery commands (Peter Eisentraut)

  • Reduce spinlock contention during WAL replay (Heikki Linnakangas)

  • Write WAL records of running transactions more frequently (Andres Freund)

    This allows standby servers to start faster and clean up resources more aggressively.

Logical decoding allows database changes to be streamed in a configurable format. The data is read from the WAL and transformed into the desired target format. To implement this feature, the following changes were made:

  • Add support for logical decoding of WAL data, to allow database changes to be streamed out in a customizable format (Andres Freund)

  • Add new wal_level setting logical to enable logical change-set encoding in WAL (Andres Freund)

  • Add table-level parameter REPLICA IDENTITY to control logical replication (Andres Freund)

  • Add relation option user_catalog_table to identify user-created tables involved in logical change-set encoding (Andres Freund)

  • Add pg_recvlogical application to receive logical-decoding data (Andres Freund)

  • Add test_decoding module to illustrate logical decoding at the SQL level (Andres Freund)

 Queries
  • Add WITH ORDINALITY syntax to number the rows returned from a set-returning function in the FROM clause (Andrew Gierth, David Fetter)

    This is particularly useful for functions like unnest().

  • Add ROWS FROM() syntax to allow horizontal concatenation of set-returning functions in the FROM clause (Andrew Gierth)

  • Allow SELECT to have an empty target list (Tom Lane)

    This was added so that views that select from a table with zero columns can be dumped and restored correctly.

  • Ensure that SELECT ... FOR UPDATE NOWAIT does not wait in corner cases involving already-concurrently-updated tuples (Craig Ringer and Thomas Munro)

 Utility Commands
  • Add DISCARD SEQUENCES command to discard cached sequence-related state (Fabrízio de Royes Mello, Robert Haas)

    DISCARD ALL will now also discard such information.

  • Add FORCE NULL option to COPY FROM, which causes quoted strings matching the specified null string to be converted to NULLs in CSV mode (Ian Barwick, Michael Paquier)

    Without this option, only unquoted matching strings will be imported as null values.

  • Issue warnings for commands used outside of transaction blocks when they can have no effect (Bruce Momjian)

    New warnings are issued for SET LOCAL, SET CONSTRAINTS, SET TRANSACTION and ABORT when used outside a transaction block.

  • Make EXPLAIN ANALYZE show planning time (Andreas Karlsson)

  • Make EXPLAIN show the grouping columns in Agg and Group nodes (Tom Lane)

  • Make EXPLAIN ANALYZE show exact and lossy block counts in bitmap heap scans (Etsuro Fujita)

 Views
  • Allow a materialized view to be refreshed without blocking other sessions from reading the view meanwhile (Kevin Grittner)

    This is done with REFRESH MATERIALIZED VIEW CONCURRENTLY.

  • Allow views to be automatically updated even if they contain some non-updatable columns (Dean Rasheed)

    Previously the presence of non-updatable output columns such as expressions, literals, and function calls prevented automatic updates. Now INSERTs, UPDATEs and DELETEs are supported, provided that they do not attempt to assign new values to any of the non-updatable columns.

  • Allow control over whether INSERTs and UPDATEs can add rows to an auto-updatable view that would not appear in the view (Dean Rasheed)

    This is controlled with the new CREATE VIEW clause WITH CHECK OPTION.

  • Allow security barrier views to be automatically updatable (Dean Rasheed)

 Object Manipulation
  • Support triggers on foreign tables (Ronan Dunklau)

  • Allow moving groups of objects from one tablespace to another using the ALL IN TABLESPACE ... SET TABLESPACE form of ALTER TABLE, ALTER INDEX, or ALTER MATERIALIZED VIEW (Stephen Frost)

  • Allow changing foreign key constraint deferrability via ALTER TABLE ... ALTER CONSTRAINT (Simon Riggs)

  • Reduce lock strength for some ALTER TABLE commands (Simon Riggs, Noah Misch, Robert Haas)

    Specifically, VALIDATE CONSTRAINT, CLUSTER ON, SET WITHOUT CLUSTER, ALTER COLUMN SET STATISTICS, ALTER COLUMN SET (attribute_option), ALTER COLUMN RESET (attribute_option) no longer require ACCESS EXCLUSIVE locks.

  • Allow tablespace options to be set in CREATE TABLESPACE (Vik Fearing)

    Formerly these options could only be set via ALTER TABLESPACE.

  • Allow CREATE AGGREGATE to define the estimated size of the aggregate's transition state data (Hadi Moshayedi)

    Proper use of this feature allows the planner to better estimate how much memory will be used by aggregates.

  • Fix DROP IF EXISTS to avoid errors for non-existent objects in more cases (Pavel Stehule, Dean Rasheed)

  • Improve how system relations are identified (Andres Freund, Robert Haas)

    Previously, relations once moved into the pg_catalog schema could no longer be modified or dropped.

 Data Types
  • Fully implement the line data type (Peter Eisentraut)

    The line segment data type (lseg) has always been fully supported. The previous line data type (which was enabled only via a compile-time option) is not binary or dump-compatible with the new implementation.

  • Add pg_lsn data type to represent a WAL log sequence number (LSN) (Robert Haas, Michael Paquier)

  • Allow single-point polygons to be converted to circles (Bruce Momjian)

  • Support time zone abbreviations that change UTC offset from time to time (Tom Lane)

    Previously, PostgreSQL assumed that the UTC offset associated with a time zone abbreviation (such as EST) never changes in the usage of any particular locale. However this assumption fails in the real world, so introduce the ability for a zone abbreviation to represent a UTC offset that sometimes changes. Update the zone abbreviation definition files to make use of this feature in timezone locales that have changed the UTC offset of their abbreviations since 1970 (according to the IANA timezone database). In such timezones, PostgreSQL will now associate the correct UTC offset with the abbreviation depending on the given date.

  • Allow 5+ digit years for non-ISO timestamp and date strings, where appropriate (Bruce Momjian)

  • Add checks for overflow/underflow of interval values (Bruce Momjian)

 JSON
  • Add jsonb, a more capable and efficient data type for storing JSON data (Oleg Bartunov, Teodor Sigaev, Alexander Korotkov, Peter Geoghegan, Andrew Dunstan)

    This new type allows faster access to values within a JSON document, and faster and more useful indexing of JSON columns. Scalar values in jsonb documents are stored as appropriate scalar SQL types, and the JSON document structure is pre-parsed rather than being stored as text as in the original json data type.

  • Add new JSON functions to allow for the construction of arbitrarily complex JSON trees (Andrew Dunstan, Laurence Rowe)

    New functions include json_array_elements_text(), json_build_array(), json_object(), json_object_agg(), json_to_record(), and json_to_recordset().

  • Add json_typeof() to return the data type of a json value (Andrew Tipton)

 Functions
  • Add pg_sleep_for(interval) and pg_sleep_until(timestamp) to specify delays more flexibly (Vik Fearing, Julien Rouhaud)

    The existing pg_sleep() function only supports delays specified in seconds.

  • Add cardinality() function for arrays (Marko Tiikkaja)

    This returns the total number of elements in the array, or zero for an array with no elements.

  • Add SQL functions to allow large object reads/writes at arbitrary offsets (Pavel Stehule)

  • Allow unnest() to take multiple arguments, which are individually unnested then horizontally concatenated (Andrew Gierth)

  • Add functions to construct times, dates, timestamps, timestamptzs, and intervals from individual values, rather than strings (Pavel Stehule)

    These functions' names are prefixed with make_, e.g. make_date().

  • Make to_char()'s TZ format specifier return a useful value for simple numeric time zone offsets (Tom Lane)

    Previously, to_char (CURRENT_TIMESTAMP, 'TZ') returned an empty string if the timezone was set to a constant like -4.

  • Add timezone offset format specifier OF to to_char() (Bruce Momjian)

  • Improve the random seed used for random() (Honza Horak)

  • Tighten validity checking for Unicode code points in chr(int) (Tom Lane)

    This function now only accepts values that are valid UTF8 characters according to RFC 3629.

 System Information Functions
  • Add functions for looking up objects in pg_class, pg_proc, pg_type, and pg_operator that do not generate errors for non-existent objects (Yugo Nagata, Nozomi Anzai, Robert Haas)

    For example, to_regclass() does a lookup in pg_class similarly to the regclass input function, but it returns NULL for a non-existent object instead of failing.

  • Add function pg_filenode_relation() to allow for more efficient lookup of relation names from filenodes (Andres Freund)

  • Add parameter_default column to information_schema.parameters view (Peter Eisentraut)

  • Make information_schema.schemata show all accessible schemas (Peter Eisentraut)

    Previously it only showed schemas owned by the current user.

 Aggregates
  • Add control over which rows are passed into aggregate functions via the FILTER clause (David Fetter)

  • Support ordered-set (WITHIN GROUP) aggregates (Atri Sharma, Andrew Gierth, Tom Lane)

  • Add standard ordered-set aggregates percentile_cont(), percentile_disc(), mode(), rank(), dense_rank(), percent_rank(), and cume_dist() (Atri Sharma, Andrew Gierth)

  • Support VARIADIC aggregate functions (Tom Lane)

  • Allow polymorphic aggregates to have non-polymorphic state data types (Tom Lane)

    This allows proper declaration in SQL of aggregates like the built-in aggregate array_agg().

 Server-Side Languages
  • Add event trigger support to PL/Perl and PL/Tcl (Dimitri Fontaine)

  • Convert numeric values to decimal in PL/Python (Szymon Guz, Ronan Dunklau)

    Previously such values were converted to Python float values, risking loss of precision.

 PL/pgSQL Server-Side Language
  • Add ability to retrieve the current PL/pgSQL call stack using GET DIAGNOSTICS (Pavel Stehule, Stephen Frost)

  • Add option print_strict_params to display the parameters passed to a query that violated a STRICT constraint (Marko Tiikkaja)

  • Add variables plpgsql.extra_warnings and plpgsql.extra_errors to enable additional PL/pgSQL warnings and errors (Marko Tiikkaja, Petr Jelinek)

    Currently only warnings/errors about shadowed variables are available.

 libpq
  • Make libpq's PQconndefaults() function ignore invalid service files (Steve Singer, Bruce Momjian)

    Previously it returned NULL if an incorrect service file was encountered.

  • Accept TLS protocol versions beyond TLSv1 in libpq (Marko Kreen)

 Client Applications
  • Add createuser option -g to specify role membership (Christopher Browne)

  • Add vacuumdb option --analyze-in-stages to analyze in stages of increasing granularity (Peter Eisentraut)

    This allows minimal statistics to be created quickly.

  • Make pg_resetxlog with option -n output current and potentially changed values (Rajeev Rastogi)

  • Make initdb throw error for incorrect locale settings, rather than silently falling back to a default choice (Tom Lane)

  • Make pg_ctl return exit code 4 for an inaccessible data directory (Amit Kapila, Bruce Momjian)

    This behavior more closely matches the Linux Standard Base (LSB) Core Specification.

  • On Windows, ensure that a non-absolute -D path specification is interpreted relative to pg_ctl's current directory (Kumar Rajeev Rastogi)

    Previously it would be interpreted relative to whichever directory the underlying Windows service was started in.

  • Allow sizeof() in ECPG C array definitions (Michael Meskes)

  • Make ECPG properly handle nesting of C-style comments in both C and SQL text (Michael Meskes)

 psql
  • Suppress No rows output in psql expanded mode when the footer is disabled (Bruce Momjian)

  • Allow Control-C to abort psql when it's hung at connection startup (Peter Eisentraut)

  • Make psql's \db+ show tablespace options (Magnus Hagander)

  • Make \do+ display the functions that implement the operators (Marko Tiikkaja)

  • Make \d+ output an OID line only if an oid column exists in the table (Bruce Momjian)

    Previously, the presence or absence of an oid column was always reported.

  • Make \d show disabled system triggers (Bruce Momjian)

    Previously, if you disabled all triggers, only user triggers would show as disabled.

  • Fix \copy to no longer require a space between stdin and a semicolon (Etsuro Fujita)

  • Output the row count at the end of \copy, just like COPY already did (Kumar Rajeev Rastogi)

  • Fix \conninfo to display the server's IP address for connections using hostaddr (Fujii Masao)

    Previously \conninfo could not display the server's IP address in such cases.

  • Show the SSL protocol version in \conninfo (Marko Kreen)

  • Add tab completion for \pset (Pavel Stehule)

  • Allow \pset with no arguments to show all settings (Gilles Darold)

  • Make \s display the name of the history file it wrote without converting it to an absolute path (Tom Lane)

    The code previously attempted to convert a relative file name to an absolute path for display, but frequently got it wrong.

  • Allow pg_restore options -I, -P, -T and -n to be specified multiple times (Heikki Linnakangas)

    This allows multiple objects to be restored in one operation.

  • Optionally add IF EXISTS clauses to the DROP commands emitted when removing old objects during a restore (Pavel Stehule)

    This change prevents unnecessary errors when removing old objects. The new --if-exists option for pg_dump, pg_dumpall, and pg_restore is only available when --clean is also specified.

  • Add pg_basebackup option --xlogdir to specify the pg_xlog directory location (Haribabu Kommi)

  • Allow pg_basebackup to relocate tablespaces in the backup copy (Steeve Lennmark)

    This is particularly useful for using pg_basebackup on the same machine as the primary.

  • Allow network-stream base backups to be throttled (Antonin Houska)

    This can be controlled with the pg_basebackup --max-rate parameter.

 Source Code
  • Improve the way tuples are frozen to preserve forensic information (Robert Haas, Andres Freund)

    This change removes the main objection to freezing tuples as soon as possible. Code that inspects tuple flag bits will need to be modified.

  • No longer require function prototypes for functions marked with the PG_FUNCTION_INFO_V1 macro (Peter Eisentraut)

    This change eliminates the need to write boilerplate prototypes. Note that the PG_FUNCTION_INFO_V1 macro must appear before the corresponding function definition to avoid compiler warnings.

  • Remove SnapshotNow and HeapTupleSatisfiesNow() (Robert Haas)

    All existing uses have been switched to more appropriate snapshot types. Catalog scans now use MVCC snapshots.

  • Add an API to allow memory allocations over one gigabyte (Noah Misch)

  • Add psprintf() to simplify memory allocation during string composition (Peter Eisentraut, Tom Lane)

  • Support printf() size modifier z to print size_t values (Andres Freund)

  • Change API of appendStringInfoVA() to better use vsnprintf() (David Rowley, Tom Lane)

  • Allow new types of external toast datums to be created (Andres Freund)

  • Add single-reader, single-writer, lightweight shared message queue (Robert Haas)

  • Improve spinlock speed on x86_64 CPUs (Heikki Linnakangas)

  • Remove spinlock support for unsupported platforms SINIX, Sun3, and NS32K (Robert Haas)

  • Remove IRIX port (Robert Haas)

  • Reduce the number of semaphores required by --disable-spinlocks builds (Robert Haas)

  • Rewrite duplicate_oids Unix shell script in Perl (Andrew Dunstan)

  • Add Test Anything Protocol (TAP) tests for client programs (Peter Eisentraut)

    Currently, these tests are run by make check-world only if the --enable-tap-tests option was given to configure. This might become the default behavior in some future release.

  • Add make targets check-tests and installcheck-tests, which allow selection of individual tests to be run (Andrew Dunstan)

  • Remove maintainer-check makefile rule (Peter Eisentraut)

    The default build rules now include all the formerly-optional tests.

  • Improve support for VPATH builds of PGXS modules (Cédric Villemain, Andrew Dunstan, Peter Eisentraut)

  • Upgrade to Autoconf 2.69 (Peter Eisentraut)

  • Add a configure flag that appends custom text to the PG_VERSION string (Oskari Saarenmaa)

    This is useful for packagers building custom binaries.

  • Improve DocBook XML validity (Peter Eisentraut)

  • Fix various minor security and sanity issues reported by the Coverity scanner (Stephen Frost)

  • Improve detection of invalid memory usage when testing PostgreSQL with Valgrind (Noah Misch)

  • Improve sample Emacs configuration file emacs.samples (Peter Eisentraut)

    Also add .dir-locals.el to the top of the source tree.

  • Allow pgindent to accept a command-line list of typedefs (Bruce Momjian)

  • Make pgindent smarter about blank lines around preprocessor conditionals (Bruce Momjian)

  • Avoid most uses of dlltool in Cygwin and Mingw builds (Marco Atzeri, Hiroshi Inoue)

  • Support client-only installs in MSVC (Windows) builds (MauMau)

 Additional Modules
  • Add pg_prewarm extension to preload relation data into the shared buffer cache at server start (Robert Haas)

    This allows reaching full operating performance more quickly.

  • Add UUID random number generator gen_random_uuid() to pgcrypto (Oskari Saarenmaa)

    This allows creation of version 4 UUIDs without requiring installation of uuid-ossp.

  • Allow uuid-ossp to work with the BSD or e2fsprogs UUID libraries, not only the OSSP UUID library (Matteo Beccati)

    This improves the uuid-ossp module's portability since it no longer has to have the increasingly-obsolete OSSP library. The module's name is now rather a misnomer, but we won't change it.

  • Add option to auto_explain to include trigger execution time (Horiguchi Kyotaro)

  • Fix pgstattuple to not report rows from uncommitted transactions as dead (Robert Haas)

  • Make pgstattuple functions use regclass-type arguments (Satoshi Nagayasu)

    While text-type arguments are still supported, they may be removed in a future major release.

  • Improve consistency of pgrowlocks output to honor snapshot rules more consistently (Robert Haas)

  • Improve pg_trgm's choice of trigrams for indexed regular expression searches (Alexander Korotkov)

    This change discourages use of trigrams containing whitespace, which are usually less selective.

  • Allow pg_xlogdump to report a live log stream with --follow (Heikki Linnakangas)

  • Store cube data more compactly (Stas Kelvich)

    Existing data must be dumped/restored to use the new format. The old format can still be read.

  • Reduce vacuumlo client-side memory usage by using a cursor (Andrew Dunstan)

  • Dramatically reduce memory consumption in pg_upgrade (Bruce Momjian)

  • Pass pg_upgrade's user name (-U) option to generated analyze scripts (Bruce Momjian)

  • Remove line length limit for pgbench scripts (Sawada Masahiko)

    The previous line limit was BUFSIZ.

  • Add long option names to pgbench (Fabien Coelho)

  • Add pgbench option --rate to control the transaction rate (Fabien Coelho)

  • Add pgbench option --progress to print periodic progress reports (Fabien Coelho)

  • Make pg_stat_statements use a file, rather than shared memory, for query text storage (Peter Geoghegan)

    This removes the previous limitation on query text length, and allows a higher number of unique statements to be tracked by default.

  • Allow reporting of pg_stat_statements's internal query hash identifier (Daniel Farina, Sameer Thakur, Peter Geoghegan)

  • Add the ability to retrieve all pg_stat_statements information except the query text (Peter Geoghegan)

    This allows monitoring tools to fetch query text only for just-created entries, improving performance during repeated querying of the statistics.

  • Make pg_stat_statements ignore DEALLOCATE commands (Fabien Coelho)

    It already ignored PREPARE, as well as planning time in general, so this seems more consistent.

  • Save the statistics file into $PGDATA/pg_stat at server shutdown, rather than $PGDATA/global (Fujii Masao)

Postgres version 9.3.19

Release date: 2017-08-31

This release contains a small number of fixes from 9.3.18. For information about new features in the 9.3 major release, see Section E.52.

 Migration to Version 9.3.19

A dump/restore is not required for those running 9.3.X.

However, if you are upgrading from a version earlier than 9.3.18, see Section E.34.

 Changes

Postgres version 9.3.18

Release date: 2017-08-10

This release contains a variety of fixes from 9.3.17. For information about new features in the 9.3 major release, see Section E.52.

 Migration to Version 9.3.18

A dump/restore is not required for those running 9.3.X.

However, if you use foreign data servers that make use of user passwords for authentication, see the first changelog entry below.

Also, if you are upgrading from a version earlier than 9.3.16, see Section E.36.

 Changes

Postgres version 9.3.17

Release date: 2017-05-11

This release contains a variety of fixes from 9.3.16. For information about new features in the 9.3 major release, see Section E.52.

 Migration to Version 9.3.17

A dump/restore is not required for those running 9.3.X.

However, if you use foreign data servers that make use of user passwords for authentication, see the first changelog entry below.

Also, if you are upgrading from a version earlier than 9.3.16, see Section E.36.

 Changes

Postgres version 9.3.16

Release date: 2017-02-09

This release contains a variety of fixes from 9.3.15. For information about new features in the 9.3 major release, see Section E.52.

 Migration to Version 9.3.16

A dump/restore is not required for those running 9.3.X.

However, if your installation has been affected by the bug described in the first changelog entry below, then after updating you may need to take action to repair corrupted indexes.

Also, if you are upgrading from a version earlier than 9.3.15, see Section E.37.

 Changes

Postgres version 9.3.15

Release date: 2016-10-27

This release contains a variety of fixes from 9.3.14. For information about new features in the 9.3 major release, see Section E.52.

 Migration to Version 9.3.15

A dump/restore is not required for those running 9.3.X.

However, if your installation has been affected by the bug described in the first changelog entry below, then after updating you may need to take action to repair corrupted free space maps.

Also, if you are upgrading from a version earlier than 9.3.9, see Section E.43.

 Changes

Postgres version 9.3.14

Release date: 2016-08-11

This release contains a variety of fixes from 9.3.13. For information about new features in the 9.3 major release, see Section E.52.

 Migration to Version 9.3.14

A dump/restore is not required for those running 9.3.X.

However, if you are upgrading from a version earlier than 9.3.9, see Section E.43.

 Changes

Postgres version 9.3.13

Release date: 2016-05-12

This release contains a variety of fixes from 9.3.12. For information about new features in the 9.3 major release, see Section E.52.

 Migration to Version 9.3.13

A dump/restore is not required for those running 9.3.X.

However, if you are upgrading from a version earlier than 9.3.9, see Section E.43.

 Changes