Postgres Changelog - All Versions

This is a complete, one-page listing of changes across all Postgres versions. This page was generated on February 10, 2017 and contains information for 323 versions of Postgres. This is version 1.11, and was created by Greg Sabino Mullane.

Postgres 9.6
9.6.2 (2017-02-09)
9.6.1 (2016-10-27)
9.6 (2016-09-29)
Postgres 9.5
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.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.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.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 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 Version 9.6.

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

Changes

Postgres version 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 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 comand-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 (-t) (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)

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

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

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

Changes

Postgres version 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 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 Version 9.5.

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

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

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

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

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

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

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

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

Changes

Postgres version 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 Version 9.4.

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

Changes

Postgres version 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 Version 9.4.

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

Changes

Postgres version 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 Version 9.4.

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

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

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

Changes

Postgres version 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

  • (9.4) 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.

  • (9.4) Allow dynamic allocation of shared memory segments (Robert Haas, Amit Kapila)

    This feature is illustrated in the test_shm_mq module.

  • (9.4) 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".

  • (9.4) Improve randomness of the database system identifier (Tom Lane)

  • (9.4) Make VACUUM properly report dead but not-yet-removable rows to the statistics collector (Hari Babu)

    Previously these were reported as live rows.

Indexes
  • (9.4) 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.

  • (9.4) Improve speed of multi-key GIN lookups (Alexander Korotkov, Heikki Linnakangas)

  • (9.4) Add GiST index support for inet and cidr data types (Emre Hasegeli)

    Such indexes improve subnet and supernet lookups and ordering comparisons.

  • (9.4) Fix rare race condition in B-tree page deletion (Heikki Linnakangas)

  • (9.4) Make the handling of interrupted B-tree page splits more robust (Heikki Linnakangas)

General Performance
  • (9.4) Allow multiple backends to insert into WAL buffers concurrently (Heikki Linnakangas)

    This improves parallel write performance.

  • (9.4) Conditionally write only the modified portion of updated rows to WAL (Amit Kapila)

  • (9.4) Improve performance of aggregate functions used as window functions (David Rowley, Florian Pflug, Tom Lane)

  • (9.4) Improve speed of aggregates that use numeric state values (Hadi Moshayedi)

  • (9.4) 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.

  • (9.4) Improve speed of COPY with default nextval() columns (Simon Riggs)

  • (9.4) Improve speed of accessing many different sequences in the same session (David Rowley)

  • (9.4) Raise hard limit on the number of tuples held in memory during sorting and B-tree index builds (Noah Misch)

  • (9.4) Reduce memory allocated by PL/pgSQL DO blocks (Tom Lane)

  • (9.4) Make the planner more aggressive about extracting restriction clauses from mixed AND/OR clauses (Tom Lane)

  • (9.4) 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.

  • (9.4) 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
  • (9.4) Add pg_stat_archiver system view to report WAL archiver activity (Gabriele Bartolini)

  • (9.4) 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.

  • (9.4) 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
  • (9.4) 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.

  • (9.4) Improve the default ssl_ciphers setting (Marko Kreen)

  • (9.4) 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.

  • (9.4) Make log_connections show SSL encryption information (Andreas Kunert)

  • (9.4) Improve SSL renegotiation handling (Álvaro Herrera)

Server Settings
  • (9.4) 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.

  • (9.4) Add autovacuum_work_mem configuration parameter to control the amount of memory used by autovacuum workers (Peter Geoghegan)

  • (9.4) 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.

  • (9.4) 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).

  • (9.4) 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.

  • (9.4) 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.

  • (9.4) Increase the default settings of work_mem and maintenance_work_mem by four times (Bruce Momjian)

    The new defaults are 4MB and 64MB respectively.

  • (9.4) Increase the default setting of effective_cache_size to 4GB (Bruce Momjian, Tom Lane)

  • (9.4) Allow printf-style space padding to be specified in log_line_prefix (David Rowley)

  • (9.4) Allow terabyte units (TB) to be used when specifying configuration variable values (Simon Riggs)

  • (9.4) Show PIDs of lock holders and waiters and improve information about relations in log_lock_waits log messages (Christian Kruse)

  • (9.4) Reduce server logging level when loading shared libraries (Peter Geoghegan)

    The previous level was LOG, which was too verbose for libraries loaded per-session.

  • (9.4) 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

  • (9.4) 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.

  • (9.4) 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.

  • (9.4) Add recovery_target option immediate to stop WAL recovery as soon as a consistent state is reached (MauMau, Heikki Linnakangas)

  • (9.4) 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.

  • (9.4) pg_switch_xlog() now clears any unused trailing space in the old WAL file (Heikki Linnakangas)

    This improves the compression ratio for WAL files.

  • (9.4) Report failure return codes from external recovery commands (Peter Eisentraut)

  • (9.4) Reduce spinlock contention during WAL replay (Heikki Linnakangas)

  • (9.4) 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

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:

  • (9.4) Add support for logical decoding of WAL data, to allow database changes to be streamed out in a customizable format (Andres Freund)

  • (9.4) Add new wal_level setting logical to enable logical change-set encoding in WAL (Andres Freund)

  • (9.4) Add table-level parameter REPLICA IDENTITY to control logical replication (Andres Freund)

  • (9.4) Add relation option user_catalog_table to identify user-created tables involved in logical change-set encoding (Andres Freund)

  • (9.4) Add pg_recvlogical application to receive logical-decoding data (Andres Freund)

  • (9.4) Add test_decoding module to illustrate logical decoding at the SQL level (Andres Freund)

Queries

  • (9.4) 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().

  • (9.4) Add ROWS FROM() syntax to allow horizontal concatenation of set-returning functions in the FROM clause (Andrew Gierth)

  • (9.4) 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.

  • (9.4) Ensure that SELECT ... FOR UPDATE NOWAIT does not wait in corner cases involving already-concurrently-updated tuples (Craig Ringer and Thomas Munro)

Utility Commands

  • (9.4) 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.

  • (9.4) 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.

  • (9.4) 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.

EXPLAIN
  • (9.4) Make EXPLAIN ANALYZE show planning time (Andreas Karlsson)

  • (9.4) Make EXPLAIN show the grouping columns in Agg and Group nodes (Tom Lane)

  • (9.4) Make EXPLAIN ANALYZE show exact and lossy block counts in bitmap heap scans (Etsuro Fujita)

Views
  • (9.4) 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.

  • (9.4) 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.

  • (9.4) 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.

  • (9.4) Allow security barrier views to be automatically updatable (Dean Rasheed)

Object Manipulation

  • (9.4) Support triggers on foreign tables (Ronan Dunklau)

  • (9.4) 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)

  • (9.4) Allow changing foreign key constraint deferrability via ALTER TABLE ... ALTER CONSTRAINT (Simon Riggs)

  • (9.4) 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.

  • (9.4) Allow tablespace options to be set in CREATE TABLESPACE (Vik Fearing)

    Formerly these options could only be set via ALTER TABLESPACE.

  • (9.4) 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.

  • (9.4) Fix DROP IF EXISTS to avoid errors for non-existent objects in more cases (Pavel Stehule, Dean Rasheed)

  • (9.4) 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

  • (9.4) 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.

  • (9.4) Add pg_lsn data type to represent a WAL log sequence number (LSN) (Robert Haas, Michael Paquier)

  • (9.4) Allow single-point polygons to be converted to circles (Bruce Momjian)

  • (9.4) 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.

  • (9.4) Allow 5+ digit years for non-ISO timestamp and date strings, where appropriate (Bruce Momjian)

  • (9.4) Add checks for overflow/underflow of interval values (Bruce Momjian)

JSON
  • (9.4) 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.

  • (9.4) 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().

  • (9.4) Add json_typeof() to return the data type of a json value (Andrew Tipton)

Functions

  • (9.4) 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.

  • (9.4) 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.

  • (9.4) Add SQL functions to allow large object reads/writes at arbitrary offsets (Pavel Stehule)

  • (9.4) Allow unnest() to take multiple arguments, which are individually unnested then horizontally concatenated (Andrew Gierth)

  • (9.4) 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().

  • (9.4) 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.

  • (9.4) Add timezone offset format specifier OF to to_char() (Bruce Momjian)

  • (9.4) Improve the random seed used for random() (Honza Horak)

  • (9.4) 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
  • (9.4) 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.

  • (9.4) Add function pg_filenode_relation() to allow for more efficient lookup of relation names from filenodes (Andres Freund)

  • (9.4) Add parameter_default column to information_schema.parameters view (Peter Eisentraut)

  • (9.4) Make information_schema.schemata show all accessible schemas (Peter Eisentraut)

    Previously it only showed schemas owned by the current user.

Aggregates
  • (9.4) Add control over which rows are passed into aggregate functions via the FILTER clause (David Fetter)

  • (9.4) Support ordered-set (WITHIN GROUP) aggregates (Atri Sharma, Andrew Gierth, Tom Lane)

  • (9.4) Add standard ordered-set aggregates percentile_cont(), percentile_disc(), mode(), rank(), dense_rank(), percent_rank(), and cume_dist() (Atri Sharma, Andrew Gierth)

  • (9.4) Support VARIADIC aggregate functions (Tom Lane)

  • (9.4) 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

  • (9.4) Add event trigger support to PL/Perl and PL/Tcl (Dimitri Fontaine)

  • (9.4) 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
  • (9.4) Add ability to retrieve the current PL/PgSQL call stack using GET DIAGNOSTICS (Pavel Stehule, Stephen Frost)

  • (9.4) Add option print_strict_params to display the parameters passed to a query that violated a STRICT constraint (Marko Tiikkaja)

  • (9.4) 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

  • (9.4) Make libpq's PQconndefaults() function ignore invalid service files (Steve Singer, Bruce Momjian)

    Previously it returned NULL if an incorrect service file was encountered.

  • (9.4) Accept TLS protocol versions beyond TLSv1 in libpq (Marko Kreen)

Client Applications

  • (9.4) Add createuser option -g to specify role membership (Chistopher Browne)

  • (9.4) Add vacuumdb option --analyze-in-stages to analyze in stages of increasing granularity (Peter Eisentraut)

    This allows minimal statistics to be created quickly.

  • (9.4) Make pg_resetxlog with option -n output current and potentially changed values (Rajeev Rastogi)

  • (9.4) Make initdb throw error for incorrect locale settings, rather than silently falling back to a default choice (Tom Lane)

  • (9.4) 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.

  • (9.4) 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.

  • (9.4) Allow sizeof() in ECPG C array definitions (Michael Meskes)

  • (9.4) Make ECPG properly handle nesting of C-style comments in both C and SQL text (Michael Meskes)

psql
  • (9.4) Suppress "No rows" output in psql expanded mode when the footer is disabled (Bruce Momjian)

  • (9.4) Allow Control-C to abort psql when it's hung at connection startup (Peter Eisentraut)

Backslash Commands
  • (9.4) Make psql's \db+ show tablespace options (Magnus Hagander)

  • (9.4) Make \do+ display the functions that implement the operators (Marko Tiikkaja)

  • (9.4) 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.

  • (9.4) Make \d show disabled system triggers (Bruce Momjian)

    Previously, if you disabled all triggers, only user triggers would show as disabled.

  • (9.4) Fix \copy to no longer require a space between stdin and a semicolon (Etsuro Fujita)

  • (9.4) Output the row count at the end of \copy, just like COPY already did (Kumar Rajeev Rastogi)

  • (9.4) 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.

  • (9.4) Show the SSL protocol version in \conninfo (Marko Kreen)

  • (9.4) Add tab completion for \pset (Pavel Stehule)

  • (9.4) Allow \pset with no arguments to show all settings (Gilles Darold)

  • (9.4) 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.

pg_dump
  • (9.4) 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.

  • (9.4) 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.

pg_basebackup
  • (9.4) Add pg_basebackup option --xlogdir to specify the pg_xlog directory location (Haribabu Kommi)

  • (9.4) 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.

  • (9.4) Allow network-stream base backups to be throttled (Antonin Houska)

    This can be controlled with the pg_basebackup --max-rate parameter.

Source Code

  • (9.4) 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.

  • (9.4) 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.

  • (9.4) Remove SnapshotNow and HeapTupleSatisfiesNow() (Robert Haas)

    All existing uses have been switched to more appropriate snapshot types. Catalog scans now use MVCC snapshots.

  • (9.4) Add an API to allow memory allocations over one gigabyte (Noah Misch)

  • (9.4) Add psprintf() to simplify memory allocation during string composition (Peter Eisentraut, Tom Lane)

  • (9.4) Support printf() size modifier z to print size_t values (Andres Freund)

  • (9.4) Change API of appendStringInfoVA() to better use vsnprintf() (David Rowley, Tom Lane)

  • (9.4) Allow new types of external toast datums to be created (Andres Freund)

  • (9.4) Add single-reader, single-writer, lightweight shared message queue (Robert Haas)

  • (9.4) Improve spinlock speed on x86_64 CPUs (Heikki Linnakangas)

  • (9.4) Remove spinlock support for unsupported platforms SINIX, Sun3, and NS32K (Robert Haas)

  • (9.4) Remove IRIX port (Robert Haas)

  • (9.4) Reduce the number of semaphores required by --disable-spinlocks builds (Robert Haas)

  • (9.4) Rewrite duplicate_oids Unix shell script in Perl (Andrew Dunstan)

  • (9.4) 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.

  • (9.4) Add make targets check-tests and installcheck-tests, which allow selection of individual tests to be run (Andrew Dunstan)

  • (9.4) Remove maintainer-check makefile rule (Peter Eisentraut)

    The default build rules now include all the formerly-optional tests.

  • (9.4) Improve support for VPATH builds of PGXS modules (Cédric Villemain, Andrew Dunstan, Peter Eisentraut)

  • (9.4) Upgrade to Autoconf 2.69 (Peter Eisentraut)

  • (9.4) Add a configure flag that appends custom text to the PG_VERSION string (Oskari Saarenmaa)

    This is useful for packagers building custom binaries.

  • (9.4) Improve DocBook XML validity (Peter Eisentraut)

  • (9.4) Fix various minor security and sanity issues reported by the Coverity scanner (Stephen Frost)

  • (9.4) Improve detection of invalid memory usage when testing PostgreSQL with Valgrind (Noah Misch)

  • (9.4) Improve sample Emacs configuration file emacs.samples (Peter Eisentraut)

    Also add .dir-locals.el to the top of the source tree.

  • (9.4) Allow pgindent to accept a command-line list of typedefs (Bruce Momjian)

  • (9.4) Make pgindent smarter about blank lines around preprocessor conditionals (Bruce Momjian)

  • (9.4) Avoid most uses of dlltool in Cygwin and Mingw builds (Marco Atzeri, Hiroshi Inoue)

  • (9.4) Support client-only installs in MSVC (Windows) builds (MauMau)

Additional Modules

  • (9.4) 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.

  • (9.4) 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.

  • (9.4) 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.

  • (9.4) Add option to auto_explain to include trigger execution time (Horiguchi Kyotaro)

  • (9.4) Fix pgstattuple to not report rows from uncommitted transactions as dead (Robert Haas)

  • (9.4) 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.

  • (9.4) Improve consistency of pgrowlocks output to honor snapshot rules more consistently (Robert Haas)

  • (9.4) 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.

  • (9.4) Allow pg_xlogdump to report a live log stream with --follow (Heikki Linnakangas)

  • (9.4) 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.

  • (9.4) Reduce vacuumlo client-side memory usage by using a cursor (Andrew Dunstan)

  • (9.4) Dramatically reduce memory consumption in pg_upgrade (Bruce Momjian)

  • (9.4) Pass pg_upgrade's user name (-U) option to generated analyze scripts (Bruce Momjian)

pgbench
  • (9.4) Remove line length limit for pgbench scripts (Sawada Masahiko)

    The previous line limit was BUFSIZ.

  • (9.4) Add long option names to pgbench (Fabien Coelho)

  • (9.4) Add pgbench option --rate to control the transaction rate (Fabien Coelho)

  • (9.4) Add pgbench option --progress to print periodic progress reports (Fabien Coelho)

pg_stat_statements
  • (9.4) 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.

  • (9.4) Allow reporting of pg_stat_statements's internal query hash identifier (Daniel Farina, Sameer Thakur, Peter Geoghegan)

  • (9.4) 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.

  • (9.4) 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.

  • (9.4) Save the statistics file into $PGDATA/pg_stat at server shutdown, rather than $PGDATA/global (Fujii Masao)

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

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

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

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

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

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

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

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

Changes

Postgres version 9.3.12

Release Date: 2016-03-31

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

Migration to Version 9.3.12

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

Changes

Postgres version 9.3.11

Release Date: 2016-02-11

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

Migration to Version 9.3.11

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

Changes

Postgres version 9.3.10

Release Date: 2015-10-08

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

Migration to Version 9.3.10

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

Changes

Postgres version 9.3.9

Release Date: 2015-06-12

This release contains a small number of fixes from 9.3.8. For information about new features in the 9.3 major release, see Version 9.3.

Migration to Version 9.3.9

A dump/restore is not required for those running 9.3.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.3.7, see Version 9.3.7.

Changes

Postgres version 9.3.8

Release Date: 2015-06-04

This release contains a small number of fixes from 9.3.7. For information about new features in the 9.3 major release, see Version 9.3.

Migration to Version 9.3.8

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

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

Changes

Postgres version 9.3.7

Release Date: 2015-05-22

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

Migration to Version 9.3.7

A dump/restore is not required for those running 9.3.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.3.6, see Version 9.3.6.

Changes

Postgres version 9.3.6

Release Date: 2015-02-05

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

Migration to Version 9.3.6

A dump/restore is not required for those running 9.3.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" 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

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

Changes

Postgres version 9.3.5

Release Date: 2014-07-24

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

Migration to Version 9.3.5

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

However, this release corrects a logic error in pg_upgrade, as well as an index corruption problem in some GiST indexes. See the first two changelog entries below to find out whether your installation has been affected and what steps you should take if so.

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

Changes

Postgres version 9.3.4

Release Date: 2014-03-20

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

Migration to Version 9.3.4

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

However, the error fixed in the first changelog entry below could have resulted in corrupt data on standby servers. It may be prudent to reinitialize standby servers from fresh base backups after installing this update.

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

Changes

Postgres version 9.3.3

Release Date: 2014-02-20

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

Migration to Version 9.3.3

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

However, several of the issues corrected in this release could have resulted in corruption of foreign-key constraints; that is, there might now be referencing rows for which there is no matching row in the referenced table. It may be worthwhile to recheck such constraints after installing this update. The simplest way to do that is to drop and recreate each suspect constraint; however, that will require taking an exclusive lock on both tables, so it is unlikely to be acceptable in production databases. Alternatively, you can do a manual join query between the two tables to look for unmatched rows.

Note also the requirement for replication standby servers to be upgraded before their master server is upgraded.

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

Changes

Postgres version 9.3.2

Release Date: 2013-12-05

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

Migration to Version 9.3.2

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

However, this release corrects a number of potential data corruption issues. See the first three changelog entries below to find out whether your installation has been affected and what steps you can take if so.

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

Changes

Postgres version 9.3.1

Release Date: 2013-10-10

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

Migration to Version 9.3.1

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

However, if you use the hstore extension, see the first changelog entry.

Changes

Postgres version 9.3

Release Date: 2013-09-09

Overview

Major enhancements in PostgreSQL 9.3 include:

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

Migration to Version 9.3

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.3 contains a number of changes that may affect compatibility with previous releases. Observe the following incompatibilities:

Server Settings

  • (9.3) Rename replication_timeout to wal_sender_timeout (Amit Kapila)

    This setting controls the WAL sender timeout.

  • (9.3) Require superuser privileges to set commit_delay because it can now potentially delay other sessions (Simon Riggs)

  • (9.3) Allow in-memory sorts to use their full memory allocation (Jeff Janes)

    Users who have set work_mem based on the previous behavior may need to revisit that setting.

Other

  • (9.3) Throw an error if a tuple to be updated or deleted has already been updated or deleted by a BEFORE trigger (Kevin Grittner)

    Formerly, the originally-intended update was silently skipped, resulting in logical inconsistency since the trigger might have propagated data to other places based on the intended update. Now an error is thrown to prevent the inconsistent results from being committed. If this change affects your application, the best solution is usually to move the data-propagation actions to an AFTER trigger.

    This error will also be thrown if a query invokes a volatile function that modifies rows that are later modified by the query itself. Such cases likewise previously resulted in silently skipping updates.

  • (9.3) Change multicolumn ON UPDATE SET NULL/SET DEFAULT foreign key actions to affect all columns of the constraint, not just those changed in the UPDATE (Tom Lane)

    Previously, we would set only those referencing columns that correspond to referenced columns that were changed by the UPDATE. This was what was required by SQL-92, but more recent editions of the SQL standard specify the new behavior.

  • (9.3) Force cached plans to be replanned if the search_path changes (Tom Lane)

    Previously, cached plans already generated in the current session were not redone if the query was re-executed with a new search_path setting, resulting in surprising behavior.

  • (9.3) Fix to_number() to properly handle a period used as a thousands separator (Tom Lane)

    Previously, a period was considered to be a decimal point even when the locale says it isn't and the D format code is used to specify use of the locale-specific decimal point. This resulted in wrong answers if FM format was also used.

  • (9.3) Fix STRICT non-set-returning functions that have set-returning functions in their arguments to properly return null rows (Tom Lane)

    A null value passed to the strict function should result in a null output, but instead, that output row was suppressed entirely.

  • (9.3) Store WAL in a continuous stream, rather than skipping the last 16MB segment every 4GB (Heikki Linnakangas)

    Previously, WAL files with names ending in FF were not used because of this skipping. If you have WAL backup or restore scripts that took this behavior into account, they will need to be adjusted.

  • (9.3) In pg_constraint.confmatchtype, store the default foreign key match type (non-FULL, non-PARTIAL) as s for "simple" (Tom Lane)

    Previously this case was represented by u for "unspecified".

Changes

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

Server

Locking
  • (9.3) Prevent non-key-field row updates from blocking foreign key checks (Álvaro Herrera, Noah Misch, Andres Freund, Alexander Shulgin, Marti Raudsepp, Alexander Shulgin)

    This change improves concurrency and reduces the probability of deadlocks when updating tables involved in a foreign-key constraint. UPDATEs that do not change any columns referenced in a foreign key now take the new NO KEY UPDATE lock mode on the row, while foreign key checks use the new KEY SHARE lock mode, which does not conflict with NO KEY UPDATE. So there is no blocking unless a foreign-key column is changed.

  • (9.3) Add configuration variable lock_timeout to allow limiting how long a session will wait to acquire any one lock (Zoltán Böszörményi)

Indexes
  • (9.3) Add SP-GiST support for range data types (Alexander Korotkov)

  • (9.3) Allow GiST indexes to be unlogged (Jeevan Chalke)

  • (9.3) Improve performance of GiST index insertion by randomizing the choice of which page to descend to when there are multiple equally good alternatives (Heikki Linnakangas)

  • (9.3) Improve concurrency of hash index operations (Robert Haas)

Optimizer
  • (9.3) Collect and use histograms of upper and lower bounds, as well as range lengths, for range types (Alexander Korotkov)

  • (9.3) Improve optimizer's cost estimation for index access (Tom Lane)

  • (9.3) Improve optimizer's hash table size estimate for doing DISTINCT via hash aggregation (Tom Lane)

  • (9.3) Suppress no-op Result and Limit plan nodes (Kyotaro Horiguchi, Amit Kapila, Tom Lane)

  • (9.3) Reduce optimizer overhead by not keeping plans on the basis of cheap startup cost when the optimizer only cares about total cost overall (Tom Lane)

General Performance
  • (9.3) Add COPY FREEZE option to avoid the overhead of marking tuples as frozen later (Simon Riggs, Jeff Davis)

  • (9.3) Improve performance of NUMERIC calculations (Kyotaro Horiguchi)

  • (9.3) Improve synchronization of sessions waiting for commit_delay (Peter Geoghegan)

    This greatly improves the usefulness of commit_delay.

  • (9.3) Improve performance of the CREATE TEMPORARY TABLE ... ON COMMIT DELETE ROWS option by not truncating such temporary tables in transactions that haven't touched any temporary tables (Heikki Linnakangas)

  • (9.3) Make vacuum recheck visibility after it has removed expired tuples (Pavan Deolasee)

    This increases the chance of a page being marked as all-visible.

  • (9.3) Add per-resource-owner lock caches (Jeff Janes)

    This speeds up lock bookkeeping at statement completion in multi-statement transactions that hold many locks; it is particularly useful for pg_dump.

  • (9.3) Avoid scanning the entire relation cache at commit of a transaction that creates a new relation (Jeff Janes)

    This speeds up sessions that create many tables in successive small transactions, such as a pg_restore run.

  • (9.3) Improve performance of transactions that drop many relations (Tomas Vondra)

Monitoring
  • (9.3) Add optional ability to checksum data pages and report corruption (Simon Riggs, Jeff Davis, Greg Smith, Ants Aasma)

    The checksum option can be set during initdb.

  • (9.3) Split the statistics collector's data file into separate global and per-database files (Tomas Vondra)

    This reduces the I/O required for statistics tracking.

  • (9.3) Fix the statistics collector to operate properly in cases where the system clock goes backwards (Tom Lane)

    Previously, statistics collection would stop until the time again reached the latest time previously recorded.

  • (9.3) Emit an informative message to postmaster standard error when we are about to stop logging there (Tom Lane)

    This should help reduce user confusion about where to look for log output in common configurations that log to standard error only during postmaster startup.

Authentication
  • (9.3) When an authentication failure occurs, log the relevant pg_hba.conf line, to ease debugging of unintended failures (Magnus Hagander)

  • (9.3) Improve LDAP error reporting and documentation (Peter Eisentraut)

  • (9.3) Add support for specifying LDAP authentication parameters in URL format, per RFC 4516 (Peter Eisentraut)

  • (9.3) Change the ssl_ciphers parameter to start with DEFAULT, rather than ALL, then remove insecure ciphers (Magnus Hagander)

    This should yield a more appropriate SSL cipher set.

  • (9.3) Parse and load pg_ident.conf once, not during each connection (Amit Kapila)

    This is similar to how pg_hba.conf is processed.

Server Settings
  • (9.3) Greatly reduce System V shared memory requirements (Robert Haas)

    On Unix-like systems, mmap() is now used for most of PostgreSQL's shared memory. For most users, this will eliminate any need to adjust kernel parameters for shared memory.

  • (9.3) Allow the postmaster to listen on multiple Unix-domain sockets (Honza Horák)

    The configuration parameter unix_socket_directory is replaced by unix_socket_directories, which accepts a list of directories.

  • (9.3) Allow a directory of configuration files to be processed (Magnus Hagander, Greg Smith, Selena Deckelmann)

    Such a directory is specified with include_dir in the server configuration file.

  • (9.3) Increase the maximum initdb-configured value for shared_buffers to 128MB (Robert Haas)

    This is the maximum value that initdb will attempt to set in postgresql.conf; the previous maximum was 32MB.

  • (9.3) Remove the external PID file, if any, on postmaster exit (Peter Eisentraut)

Replication and Recovery

  • (9.3) Allow a streaming replication standby to follow a timeline switch (Heikki Linnakangas)

    This allows streaming standby servers to receive WAL data from a slave newly promoted to master status. Previously, other standbys would require a resync to begin following the new master.

  • (9.3) Add SQL functions pg_is_in_backup() and pg_backup_start_time() (Gilles Darold)

    These functions report the status of base backups.

  • (9.3) Improve performance of streaming log shipping with synchronous_commit disabled (Andres Freund)

  • (9.3) Allow much faster promotion of a streaming standby to primary (Simon Riggs, Kyotaro Horiguchi)

  • (9.3) Add the last checkpoint's redo location to pg_controldata's output (Fujii Masao)

    This information is useful for determining which WAL files are needed for restore.

  • (9.3) Allow tools like pg_receivexlog to run on computers with different architectures (Heikki Linnakangas)

    WAL files can still only be replayed on servers with the same architecture as the primary; but they can now be transmitted to and stored on machines of any architecture, since the streaming replication protocol is now machine-independent.

  • (9.3) Make pg_basebackup --write-recovery-conf output a minimal recovery.conf file (Zoltán Böszörményi, Magnus Hagander)

    This simplifies setting up a standby server.

  • (9.3) Allow pg_receivexlog and pg_basebackup --xlog-method to handle streaming timeline switches (Heikki Linnakangas)

  • (9.3) Add wal_receiver_timeout parameter to control the WAL receiver's timeout (Amit Kapila)

    This allows more rapid detection of connection failure.

  • (9.3) Change the WAL record format to allow splitting the record header across pages (Heikki Linnakangas)

    The new format is slightly more compact, and is more efficient to write.

Queries

  • (9.3) Implement SQL-standard LATERAL option for FROM-clause subqueries and function calls (Tom Lane)

    This feature allows subqueries and functions in FROM to reference columns from other tables in the FROM clause. The LATERAL keyword is optional for functions.

  • (9.3) Add support for piping COPY and psql \copy data to/from an external program (Etsuro Fujita)

  • (9.3) Allow a multirow VALUES clause in a rule to reference OLD/NEW (Tom Lane)

Object Manipulation

  • (9.3) Add support for event triggers (Dimitri Fontaine, Robert Haas, Álvaro Herrera)

    This allows server-side functions written in event-enabled languages to be called when DDL commands are run.

  • (9.3) Allow foreign data wrappers to support writes (inserts/updates/deletes) on foreign tables (KaiGai Kohei)

  • (9.3) Add CREATE SCHEMA ... IF NOT EXISTS clause (Fabrízio de Royes Mello)

  • (9.3) Make REASSIGN OWNED also change ownership of shared objects (Álvaro Herrera)

  • (9.3) Make CREATE AGGREGATE complain if the given initial value string is not valid input for the transition datatype (Tom Lane)

  • (9.3) Suppress CREATE TABLE's messages about implicit index and sequence creation (Robert Haas)

    These messages now appear at DEBUG1 verbosity, so that they will not be shown by default.

  • (9.3) Allow DROP TABLE IF EXISTS to succeed when a non-existent schema is specified in the table name (Bruce Momjian)

    Previously, it threw an error if the schema did not exist.

  • (9.3) Provide clients with constraint violation details as separate fields (Pavel Stehule)

    This allows clients to retrieve table, column, data type, or constraint name error details. Previously such information had to be extracted from error strings. Client library support is required to access these fields.

ALTER
  • (9.3) Support IF NOT EXISTS option in ALTER TYPE ... ADD VALUE (Andrew Dunstan)

    This is useful for conditionally adding values to enumerated types.

  • (9.3) Add ALTER ROLE ALL SET to establish settings for all users (Peter Eisentraut)

    This allows settings to apply to all users in all databases. ALTER DATABASE SET already allowed addition of settings for all users in a single database. postgresql.conf has a similar effect.

  • (9.3) Add support for ALTER RULE ... RENAME (Ali Dar)

VIEWs
  • (9.3) Add materialized views (Kevin Grittner)

    Unlike ordinary views, where the base tables are read on every access, materialized views create physical tables at creation or refresh time. Access to the materialized view then reads from its physical table. There is not yet any facility for incrementally refreshing materialized views or auto-accessing them via base table access.

  • (9.3) Make simple views auto-updatable (Dean Rasheed)

    Simple views that reference some or all columns from a single base table are now updatable by default. More complex views can be made updatable using INSTEAD OF triggers or INSTEAD rules.

  • (9.3) Add CREATE RECURSIVE VIEW syntax (Peter Eisentraut)

    Internally this is translated into CREATE VIEW ... WITH RECURSIVE ....

  • (9.3) Improve view/rule printing code to handle cases where referenced tables are renamed, or columns are renamed, added, or dropped (Tom Lane)

    Table and column renamings can produce cases where, if we merely substitute the new name into the original text of a rule or view, the result is ambiguous. This change fixes the rule-dumping code to insert manufactured table and column aliases when needed to preserve the original semantics.

Data Types

  • (9.3) Increase the maximum size of large objects from 2GB to 4TB (Nozomi Anzai, Yugo Nagata)

    This change includes adding 64-bit-capable large object access functions, both in the server and in libpq.

  • (9.3) Allow text timezone designations, e.g. "America/Chicago", in the "T" field of ISO-format timestamptz input (Bruce Momjian)

JSON

Functions

  • (9.3) Add array_remove() and array_replace() functions (Marco Nenciarini, Gabriele Bartolini)

  • (9.3) Allow concat() and format() to properly expand VARIADIC-labeled arguments (Pavel Stehule)

  • (9.3) Improve format() to provide field width and left/right alignment options (Pavel Stehule)

  • (9.3) Make to_char(), to_date(), and to_timestamp() handle negative (BC) century values properly (Bruce Momjian)

    Previously the behavior was either wrong or inconsistent with positive/AD handling, e.g. with the format mask "IYYY-IW-DY".

  • (9.3) Make to_date() and to_timestamp() return proper results when mixing ISO and Gregorian week/day designations (Bruce Momjian)

  • (9.3) Cause pg_get_viewdef() to start a new line by default after each SELECT target list entry and FROM entry (Marko Tiikkaja)

    This reduces line length in view printing, for instance in pg_dump output.

  • (9.3) Fix map_sql_value_to_xml_value() to print values of domain types the same way their base type would be printed (Pavel Stehule)

    There are special formatting rules for certain built-in types such as boolean; these rules now also apply to domains over these types.

Server-Side Languages

PL/pgSQL Server-Side Language
  • (9.3) Allow PL/pgSQL to use RETURN with a composite-type expression (Asif Rehman)

    Previously, in a function returning a composite type, RETURN could only reference a variable of that type.

  • (9.3) Allow PL/pgSQL to access constraint violation details as separate fields (Pavel Stehule)

  • (9.3) Allow PL/pgSQL to access the number of rows processed by COPY (Pavel Stehule)

    A COPY executed in a PL/pgSQL function now updates the value retrieved by GET DIAGNOSTICS x = ROW_COUNT.

  • (9.3) Allow unreserved keywords to be used as identifiers everywhere in PL/pgSQL (Tom Lane)

    In certain places in the PL/pgSQL grammar, keywords had to be quoted to be used as identifiers, even if they were nominally unreserved.

PL/Python Server-Side Language
  • (9.3) Add PL/Python result object string handler (Peter Eisentraut)

    This allows plpy.debug(rv) to output something reasonable.

  • (9.3) Make PL/Python convert OID values to a proper Python numeric type (Peter Eisentraut)

  • (9.3) Handle SPI errors raised explicitly (with PL/Python's RAISE) the same as internal SPI errors (Oskari Saarenmaa and Jan Urbanski)

Server Programming Interface (SPI)

  • (9.3) Prevent leakage of SPI tuple tables during subtransaction abort (Tom Lane)

    At the end of any failed subtransaction, the core SPI code now releases any SPI tuple tables that were created during that subtransaction. This avoids the need for SPI-using code to keep track of such tuple tables and release them manually in error-recovery code. Failure to do so caused a number of transaction-lifespan memory leakage issues in PL/pgSQL and perhaps other SPI clients. SPI_freetuptable() now protects itself against multiple freeing requests, so any existing code that did take care to clean up shouldn't be broken by this change.

  • (9.3) Allow SPI functions to access the number of rows processed by COPY (Pavel Stehule)

Client Applications

  • (9.3) Add command-line utility pg_isready to check if the server is ready to accept connections (Phil Sorber)

  • (9.3) Support multiple --table arguments for pg_restore, clusterdb, reindexdb, and vacuumdb (Josh Kupershmidt)

    This is similar to the way pg_dump's --table option works.

  • (9.3) Add --dbname option to pg_dumpall, pg_basebackup, and pg_receivexlog to allow specifying a connection string (Amit Kapila)

  • (9.3) Add libpq function PQconninfo() to return connection information (Zoltán Böszörményi, Magnus Hagander)

psql
  • (9.3) Adjust function cost settings so psql tab completion and pattern searching are more efficient (Tom Lane)

  • (9.3) Improve psql's tab completion coverage (Jeff Janes, Dean Rasheed, Peter Eisentraut, Magnus Hagander)

  • (9.3) Allow the psql --single-transaction mode to work when reading from standard input (Fabien Coelho, Robert Haas)

    Previously this option only worked when reading from a file.

  • (9.3) Remove psql warning when connecting to an older server (Peter Eisentraut)

    A warning is still issued when connecting to a server of a newer major version than psql's.

Backslash Commands
  • (9.3) Add psql command \watch to repeatedly execute a SQL command (Will Leinweber)

  • (9.3) Add psql command \gset to store query results in psql variables (Pavel Stehule)

  • (9.3) Add SSL information to psql's \conninfo command (Alastair Turner)

  • (9.3) Add "Security" column to psql's \df+ output (Jon Erdman)

  • (9.3) Allow psql command \l to accept a database name pattern (Peter Eisentraut)

  • (9.3) In psql, do not allow \connect to use defaults if there is no active connection (Bruce Momjian)

    This might be the case if the server had crashed.

  • (9.3) Properly reset state after failure of a SQL command executed with psql's \g file (Tom Lane)

    Previously, the output from subsequent SQL commands would unexpectedly continue to go to the same file.

Output
  • (9.3) Add a latex-longtable output format to psql (Bruce Momjian)

    This format allows tables to span multiple pages.

  • (9.3) Add a border=3 output mode to the psql latex format (Bruce Momjian)

  • (9.3) In psql's tuples-only and expanded output modes, no longer emit "(No rows)" for zero rows (Peter Eisentraut)

  • (9.3) In psql's unaligned, expanded output mode, no longer print an empty line for zero rows (Peter Eisentraut)

pg_dump
  • (9.3) Add pg_dump --jobs option to dump tables in parallel (Joachim Wieland)

  • (9.3) Make pg_dump output functions in a more predictable order (Joel Jacobson)

  • (9.3) Fix tar files emitted by pg_dump to be POSIX conformant (Brian Weaver, Tom Lane)

  • (9.3) Add --dbname option to pg_dump, for consistency with other client commands (Heikki Linnakangas)

    The database name could already be supplied last without a flag.

initdb
  • (9.3) Make initdb fsync the newly created data directory (Jeff Davis)

    This insures data integrity in event of a system crash shortly after initdb. This can be disabled by using --nosync.

  • (9.3) Add initdb --sync-only option to sync the data directory to durable storage (Bruce Momjian)

    This is used by pg_upgrade.

  • (9.3) Make initdb issue a warning about placing the data directory at the top of a file system mount point (Bruce Momjian)

Source Code

  • (9.3) Add infrastructure to allow plug-in background worker processes (Álvaro Herrera)

  • (9.3) Create a centralized timeout API (Zoltán Böszörményi)

  • (9.3) Create libpgcommon and move pg_malloc() and other functions there (Álvaro Herrera, Andres Freund)

    This allows libpgport to be used solely for portability-related code.

  • (9.3) Add support for list links embedded in larger structs (Andres Freund)

  • (9.3) Use SA_RESTART for all signals, including SIGALRM (Tom Lane)

  • (9.3) Ensure that the correct text domain is used when translating errcontext() messages (Heikki Linnakangas)

  • (9.3) Standardize naming of client-side memory allocation functions (Tom Lane)

  • (9.3) Provide support for "static assertions" that will fail at compile time if some compile-time-constant condition is not met (Andres Freund, Tom Lane)

  • (9.3) Support Assert() in client-side code (Andrew Dunstan)

  • (9.3) Add decoration to inform the C compiler that some ereport() and elog() calls do not return (Peter Eisentraut, Andres Freund, Tom Lane, Heikki Linnakangas)

  • (9.3) Allow options to be passed to the regression test output comparison utility via PG_REGRESS_DIFF_OPTS (Peter Eisentraut)

  • (9.3) Add isolation tests for CREATE INDEX CONCURRENTLY (Abhijit Menon-Sen)

  • (9.3) Remove typedefs for int2/int4 as they are better represented as int16/int32 (Peter Eisentraut)

  • (9.3) Fix install-strip on Mac OS X (Peter Eisentraut)

  • (9.3) Remove configure flag --disable-shared, as it is no longer supported (Bruce Momjian)

  • (9.3) Rewrite pgindent in Perl (Andrew Dunstan)

  • (9.3) Provide Emacs macro to set Perl formatting to match PostgreSQL's perltidy settings (Peter Eisentraut)

  • (9.3) Run tool to check the keyword list whenever the backend grammar is changed (Tom Lane)

  • (9.3) Change the way UESCAPE is lexed, to significantly reduce the size of the lexer tables (Heikki Linnakangas)

  • (9.3) Centralize flex and bison make rules (Peter Eisentraut)

    This is useful for pgxs authors.

  • (9.3) Change many internal backend functions to return object OIDs rather than void (Dimitri Fontaine)

    This is useful for event triggers.

  • (9.3) Invent pre-commit/pre-prepare/pre-subcommit events for transaction callbacks (Tom Lane)

    Loadable modules that use transaction callbacks might need modification to handle these new event types.

  • (9.3) Add function pg_identify_object() to produce a machine-readable description of a database object (Álvaro Herrera)

  • (9.3) Add post-ALTER-object server hooks (KaiGai Kohei)

  • (9.3) Implement a generic binary heap and use it for Merge-Append operations (Abhijit Menon-Sen)

  • (9.3) Provide a tool to help detect timezone abbreviation changes when updating the src/timezone/data files (Tom Lane)

  • (9.3) Add pkg-config support for libpq and ecpg libraries (Peter Eisentraut)

  • (9.3) Remove src/tools/backend, now that the content is on the PostgreSQL wiki (Bruce Momjian)

  • (9.3) Split out WAL reading as an independent facility (Heikki Linnakangas, Andres Freund)

  • (9.3) Use a 64-bit integer to represent WAL positions (XLogRecPtr) instead of two 32-bit integers (Heikki Linnakangas)

    Generally, tools that need to read the WAL format will need to be adjusted.

  • (9.3) Allow PL/Python to support platform-specific include directories (Peter Eisentraut)

  • (9.3) Allow PL/Python on OS X to build against custom versions of Python (Peter Eisentraut)

Additional Modules

  • (9.3) Add a Postgres foreign data wrapper contrib module to allow access to other Postgres servers (Shigeru Hanada)

    This foreign data wrapper supports writes.

  • (9.3) Add pg_xlogdump contrib program (Andres Freund)

  • (9.3) Add support for indexing of regular-expression searches in pg_trgm (Alexander Korotkov)

  • (9.3) Improve pg_trgm's handling of multibyte characters (Tom Lane)

    On a platform that does not have the wcstombs() or towlower() library functions, this could result in an incompatible change in the contents of pg_trgm indexes for non-ASCII data. In such cases, REINDEX those indexes to ensure correct search results.

  • (9.3) Add a pgstattuple function to report the size of the pending-insertions list of a GIN index (Fujii Masao)

  • (9.3) Make oid2name, pgbench, and vacuumlo set fallback_application_name (Amit Kapila)

  • (9.3) Improve output of pg_test_timing (Bruce Momjian)

  • (9.3) Improve output of pg_test_fsync (Peter Geoghegan)

  • (9.3) Create a dedicated foreign data wrapper, with its own option validator function, for dblink (Shigeru Hanada)

    When using this FDW to define the target of a dblink connection, instead of using a hard-wired list of connection options, the underlying libpq library is consulted to see what connection options it supports.

pg_upgrade
  • (9.3) Allow pg_upgrade to do dumps and restores in parallel (Bruce Momjian, Andrew Dunstan)

    This allows parallel schema dump/restore of databases, as well as parallel copy/link of data files per tablespace. Use the --jobs option to specify the level of parallelism.

  • (9.3) Make pg_upgrade create Unix-domain sockets in the current directory (Bruce Momjian, Tom Lane)

    This reduces the possibility that someone will accidentally connect during the upgrade.

  • (9.3) Make pg_upgrade --check mode properly detect the location of non-default socket directories (Bruce Momjian, Tom Lane)

  • (9.3) Improve performance of pg_upgrade for databases with many tables (Bruce Momjian)

  • (9.3) Improve pg_upgrade's logs by showing executed commands (Álvaro Herrera)

  • (9.3) Improve pg_upgrade's status display during copy/link (Bruce Momjian)

pgbench
  • (9.3) Add --foreign-keys option to pgbench (Jeff Janes)

    This adds foreign key constraints to the standard tables created by pgbench, for use in foreign key performance testing.

  • (9.3) Allow pgbench to aggregate performance statistics and produce output every --aggregate-interval seconds (Tomas Vondra)

  • (9.3) Add pgbench --sampling-rate option to control the percentage of transactions logged (Tomas Vondra)

  • (9.3) Reduce and improve the status message output of pgbench's initialization mode (Robert Haas, Peter Eisentraut)

  • (9.3) Add pgbench -q mode to print one output line every five seconds (Tomas Vondra)

  • (9.3) Output pgbench elapsed and estimated remaining time during initialization (Tomas Vondra)

  • (9.3) Allow pgbench to use much larger scale factors, by changing relevant columns from integer to bigint when the requested scale factor exceeds 20000 (Greg Smith)

Documentation

  • (9.3) Allow EPUB-format documentation to be created (Peter Eisentraut)

  • (9.3) Update FreeBSD kernel configuration documentation (Brad Davis)

  • (9.3) Improve WINDOW function documentation (Bruce Momjian, Florian Pflug)

  • (9.3) Add instructions for setting up the documentation tool chain on Mac OS X (Peter Eisentraut)

  • (9.3) Improve commit_delay documentation (Peter Geoghegan)

Postgres version 9.2.20

Release Date: 2017-02-09

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

Migration to Version 9.2.20

A dump/restore is not required for those running 9.2.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.2.11, see Version 9.2.11.

Changes

Postgres version 9.2.19

Release Date: 2016-10-27

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

Migration to Version 9.2.19

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

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

Changes

Postgres version 9.2.18

Release Date: 2016-08-11

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

Migration to Version 9.2.18

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

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

Changes

Postgres version 9.2.17

Release Date: 2016-05-12

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

Migration to Version 9.2.17

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

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

Changes

Postgres version 9.2.16

Release Date: 2016-03-31

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

Migration to Version 9.2.16

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

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

Changes

Postgres version 9.2.15

Release Date: 2016-02-11

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

Migration to Version 9.2.15

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

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

Changes