PostgreSQL 9.5

• Release notes (source file)
• Released: 2016-01-07 (announcement)
• Final minor version: 9.5.25 (2021-02-11)
• EOL: 2021-02-11 (EOL notice)
• Documentation: PostgreSQL 9.5

Major features

• INSERTs which would generate constraint conflicts can be turned into UPDATEs or ignored ("UPSERT") (commit 168d5805)
• GROUP BY analysis features GROUPING SETS, CUBE and ROLLUP
• row level security (RLS) added (commit 491c029d)
• replication origins for logical decoding (commit 5aa23504)
• Block Range Indexes (BRIN) (initial commit 7516f525)
• Performance improvements for sorting and for multi-CPU machines

Useful links

• PostgreSQL 9.5 observability (interactive chart from DataEgret)

SQL changes

Added:

• ALTER POLICY (commit 491c029d)
• CREATE POLICY (commit 491c029d)
• CREATE TRANSFORM (commit cac76582)
• DROP POLICY (commit 491c029d)
• DROP TRANSFORM (commit cac76582)
• IMPORT FOREIGN SCHEMA (initial commit 59efda3e)

Modified:

• ALTER AGGREGATE
  • ALTER AGGREGATE ... OWNER TO { CURRENT_USER | SESSION_USER } syntax added (commit 31eae602)
• ALTER COLLATION
  • ALTER COLLATION ... OWNER TO { CURRENT_USER | SESSION_USER } syntax added (commit 31eae602)
• ALTER CONVERSION
  • ALTER CONVERSION ... OWNER TO { CURRENT_USER | SESSION_USER } syntax added (commit 31eae602)
• ALTER DATABASE
  • options ALLOW_CONNECTIONS and IS_TEMPLATE added (commit fbb1d7d7)
  • ALTER DATABASE ... OWNER TO { CURRENT_USER | SESSION_USER } syntax added (commit 31eae602)
• ALTER DOMAIN
  • ALTER DOMAIN ... OWNER TO { CURRENT_USER | SESSION_USER } syntax added (commit 31eae602)
• ALTER EXTENSION
  • support for TRANSFORM member objects added (commit cac76582)
• ALTER FOREIGN TABLE
  • ALTER FOREIGN TABLE ... INHERIT / NO INHERIT syntax added (commit cb1ca4d8)
  • ALTER FOREIGN TABLE ... SET WITH / WITHOUT OIDS syntax added (commit cb1ca4d8)
  • ALTER FOREIGN TABLE ... ALTER COLUMN ... SET STORAGE syntax added (commit cb1ca4d8)
  • CURRENT_USER / SESSION_USER can now be used to specify user names (commit 31eae602)
  • support for CHECK constraints added (commit fc2ac1fb)
• ALTER GROUP
  • ALTER GROUP { CURRENT_USER | SESSION_USER } ... syntax added (commit 31eae602)
• ALTER LANGUAGE
  • ALTER LANGUAGE ... OWNER TO { CURRENT_USER | SESSION_USER } syntax added (commit 31eae602)
• ALTER LARGE OBJECT
  • ALTER LARGE OBJECT ... OWNER TO { CURRENT_USER | SESSION_USER } syntax added (commit 31eae602)
• ALTER OPERATOR
  • ALTER OPERATOR ... OWNER TO { CURRENT_USER | SESSION_USER } syntax added (commit 31eae602)
• ALTER OPERATOR CLASS
  • ALTER OPERATOR CLASS ... OWNER TO { CURRENT_USER | SESSION_USER } syntax added (commit 31eae602)
• ALTER ROLE
  • CURRENT_USER and SESSION_USER are now accepted in place of a user name (commit 31eae602)
  • option BYPASSRLS/NOBYPASSRLS added (commit 491c029d)
• ALTER SCHEMA
  • ALTER SCHEMA ... OWNER TO { CURRENT_USER | SESSION_USER } syntax added (commit 31eae602)
• ALTER SEQUENCE
  • ALTER SEQUENCE ... OWNER TO { CURRENT_USER | SESSION_USER } syntax added (commit 31eae602)
• ALTER SERVER
  • CURRENT_USER and SESSION_USER options added to ALTER SERVER ... RENAME TO ... clause (commit 31eae602)
• ALTER TABLE
  • ALTER TABLE ... (ENABLE|DISABLE) ROW SECURITY added (commit 491c029d)
  • ALTER TABLE ... SET (LOGGED|UNLOGGED) added (commit f41872d0)
  • Support for ALTER TABLE IF EXISTS added to ALTER TABLE ... RENAME CONSTRAINT clause (commit 1d8198bb)
  • ALTER TABLE will now execute the table_rewrite event trigger (commit 618c9430)
• ALTER TYPE
  • ALTER TYPE ... OWNER TO { CURRENT_USER | SESSION_USER } syntax added (commit 31eae602)
• ALTER USER
  • CURRENT_USER and SESSION_USER are now accepted in place of a user name (commit 31eae602)
  • option BYPASSRLS/NOBYPASSRLS added (commit 491c029d)
• ALTER VIEW
  • ALTER VIEW ... OWNER TO { CURRENT_USER | SESSION_USER } syntax added (commit 31eae602)
• COMMENT ON
  • COMMENT ON CONSTRAINT ... ON DOMAIN ... added (commit 7eca575d)
  • COMMENT ON POLICY added (commit 143b39c1)
  • COMMENT ON TRANSFORM FOR added (commit cac76582)
• CREATE DATABASE
  • ALLOW_CONNECTIONS option added (commit fbb1d7d7)
  • IS_TEMPLATE option added (commit fbb1d7d7)
• CREATE FOREIGN TABLE
  • support for CHECK constraints added (commit fc2ac1fb)
  • support for the INHERITS clause added (commit cb1ca4d8)
• CREATE FUNCTION
  • TRANSFORM attribute added (commit cac76582)
• CREATE INDEX
  • CREATE INDEX IF NOT EXISTS syntax added (commit 08309aaf)
• CREATE MATERIALIZED VIEW
  • CREATE MATERIALIZED VIEW IF NOT EXISTS syntax added (commit e39b6f95)
• CREATE ROLE
  • option BYPASSRLS/NOBYPASSRLS added (commit 491c029d)
• CREATE SCHEMA
  • CURRENT_USER and SESSION_USER available for CREATE SCHEMA [...] AUTHORIZATION ... (commit 31eae602)
• CREATE TABLE AS
  • CREATE TABLE IF NOT EXISTS syntax added (commit e39b6f95)
• CREATE TABLESPACE
  • CREATE TABLESPACE ... OWNER { CURRENT_USER | SESSION_USER } option added (commit 31eae602)
• DROP OWNED
  • DROP OWNED BY { CURRENT_USER | SESSION_USER } syntax added (commit 31eae602)
• REASSIGN OWNED
  • REASSIGN OWNED BY { CURRENT_USER | SESSION_USER } TO { CURRENT_USER | SESSION_USER } syntax added (commit 31eae602)
• REINDEX
  • REINDEX SCHEMA ... option added  (commit fe263d11)
  • VERBOSE option added (commit ecd222e7)
• SELECT
  • SKIP LOCKED syntax added (commit df630b0d)
• UPDATE
  • UPDATE tab SET (col1,col2,...) = (SELECT ...), ... syntax added (commit 8f889b10)

Backend changes

General

• errors messages
  • possible matches for mistyped column names will be suggested, if found (commit e529cd4f)
• Event triggers
  • table_rewrite event added (commit 618c9430)

Hooks

Following hooks added:

• row_security_policy_hook_permissive (commit 0bf22e0c)
• row_security_policy_hook_restrictive (commit 0bf22e0c)
• set_join_pathlist_hook (commit e7cb7ee1)
• set_rel_pathlist_hook (commit c2ea2285)

Source configuration

• option --enable-cassert added (commit 3bdcf6a5)

System catalogue changes

Tables

Added:

• pg_policy (initial commit 491c029d)
• pg_replication_origin (commit 5aa23504)
• pg_transform (commit cac76582)

Modified:

• pg_authid
  • column rolcatupdate removed (commit bb8582ab)
  • column rolbypassrls added (commit 491c029d)
• pg_proc
  • column protrftypes added (commit cac76582)
• pg_seclabel
  • definition of provider column in index pg_seclabel_object_index changed to use text_pattern_ops (commit 0b28ea79)
  • columns provider and label set to NOT NULL (commit 82a532b3)
• pg_shseclabel
  • definition of provider column in index pg_shseclabel_object_index changed to use text_pattern_ops (commit 0b28ea79)
  • columns provider and label set to NOT NULL (commit 82a532b3)

Views

Added:

• pg_file_settings (commit a97e0c33)
• pg_policies (commit 491c029d)
• pg_replication_origin_status (commit 5aa23504)
• pg_stat_ssl (commit 9029f4b3)

Modified:

• pg_replication_slots
  • column active_pid added (commit d811c037)
• pg_roles
  • column rolcatupdate removed (commit bb8582ab)
  • column rolbypassrls added (commit 491c029d)
• pg_settings
  • column pending_restart added (commit a486e357)
• pg_shadow
  • column usecatupd removed (commit bb8582ab)
  • column usebypassrls added (commit 491c029d)
• pg_stat_replication
  • column sent will now be NULL, not zero, if it has no valid value (commit 28c36678)
• pg_tables
  • column hasrowsecurity added (commit 491c029d)

System functions

Added:

• array_position() (initial commit 13dbc7a8)
• array_positions() (initial commit 13dbc7a8)
• jsonb_agg() (commit 7e354ab9)
• jsonb_build_array() (commit 7e354ab9)
• jsonb_build_object() (commit 7e354ab9)
• jsonb_object() (commit 7e354ab9)
• jsonb_object_agg() (commit 7e354ab9)
• jsonb_pretty() (commit c6947010)
• jsonb_set() (initial commit c6947010)
• jsonb_strip_nulls() (commit 237a8824)
• json_strip_nulls() (commit 237a8824)
• pg_get_object_address() (commit d7ee82e5)
• pg_identify_object_as_address() (commit a6762014)
• pg_last_committed_xact() (commit 73c986ad)
• pg_replication_origin_advance() (commit 5aa23504)
• pg_replication_origin_create() (commit 5aa23504)
• pg_replication_origin_drop() (commit 5aa23504)
• pg_replication_origin_oid() (commit 5aa23504)
• pg_replication_origin_progress() (commit 5aa23504)
• pg_replication_origin_session_is_setup() (commit 5aa23504)
• pg_replication_origin_session_progress() (commit 5aa23504)
• pg_replication_origin_session_reset() (commit 5aa23504)
• pg_replication_origin_session_setup() (commit 5aa23504)
• pg_replication_origin_xact_setup() (commit 5aa23504)
• pg_replication_origin_xact_reset() (commit 5aa23504)
• pg_xact_commit_timestamp() (commit 73c986ad)
• to_jsonb() (commit 7e354ab9)

Modified:

• generate_series()
  • variant which returns a series of numeric values added (commit 1871c892)
• pg_ls_dir()
  • options missing_ok and include_dot_dirs added (commit cb2acb10)
• pg_read_file()
  • option missing_ok added (commit cb2acb10)
• pg_read_binary_file()
  • option missing_ok added (commit cb2acb10)
• pg_stat_file()
  • option missing_ok added (commit cb2acb10)

Data types

• regnamespace added as an OID alias for namespaces (initial commit cb9fa802)
• regrole added as an OID alias for roles (commit 0c90f676)

Configuration changes

Added:

• cluster_name (commit 51adcaa0)
• gin_pending_list_limit (commit a1b395b6)
• max_wal_size (commit 88e98230)
• min_wal_size (commit 88e98230)
• operator_precedence_warning (commit c6b3c939)
• track_commit_timestamp (commit 73c986ad)
• wal_compression (commit 57aa5b2b)
• wal_retrieve_retry_interval (commit 5d2b45e3)

Modified:

• archive_mode
  • always mode added (commit ffd37740)
• debug_assertions
  • converted to a preset configuration parameter (commit 3bdcf6a5)

Removed:

• checkpoint_segments (commit 51adcaa0; replaced by min_wal_size and max_wal_size)
• ssl_renegotiation_limit (commit 426746b9)

Replication changes

General

• tablespace_map file introduced (commit 72d422a5)

Configuration

Added:

• recovery_target_action (commit aedccb1f)

Removed:

• pause_at_recovery_target (commit 51c11a70)

Procedural language changes

General:

• transform functions added, enabling the specification of conversion routines between SQL data types and data types of procedural languages (commit cac76582)

Foreign data wrapper changes

• support for importing foreign schemas added (initial commit 59efda3e)
• CHECK constraints can be placed on foreign tables (commit fc2ac1fb)
• foreign tables can participate in inheritance (initial commit cb1ca4d8)
• foreign data wrappers can implement join pushdown (initial commit 1a8a4e5c)

Server utilities

Added:

• pg_rewind (commit 61081e75)

Modified:

• pg_controldata
  • -D option added for consistency with other utilities when specifying the data directory (commit b0d81ade)
• pg_ctl
  • environment variable PGCTLTIMEOUT added (commit 725f0ce2)
  • default shutdown mode changed from smart to fast (commit 0badb069)
  • support for multiple -o options (commit ebe30ad5)
• pg_resetxlog
  • option -c added for setting the oldest and newest transaction IDs for which the commit time can be retrieved (commit 73c986ad)
  • -D option added for consistency with other utilities when specifying the data directory (commit b0d81ade)
• pg_test_fsync
  • converted from a contrib module to a server utility (commit 00882d9e)
• pg_upgrade
  • converted to a server utility (commit 9fa8b0ee)
  • support for PostgreSQL 8.2 and earlier removed (commit 2209b392)
  • support for multiple -o/-O options (commit ebe30ad5)
  • improvements for database collation handling (commit 33755e8e)
• pg_xlogdump
  • converted from a contrib module to a server utility (commit b0a738f4)
  • --stats option added to display summary statistics (commit bdd5726c)

Core utilities

Modified:

• pg_archivecleanup
  • converted from a contrib module (commit 83aca89f)
• pg_basebackup
  • able to use a tablespace mapping file when using tar format (commit 72d422a5)
• pgbench
  • converted from a contrib module (commit 81134af3)
• pg_dump
  • redundant -i/--ignore-version option removed (commit 232cd63b)
• pg_dumpall
  • redundant -i/--ignore-version option removed (commit 232cd63b)
• pg_receivexlog
  • option --synchronous added (commit c4f99d20)
  • options --create-slot and --drop-slot added (commit d9f38c7a)
  • option --if-not-exists added (commit 0e8e48b0)
• pg_restore
  • redundant -i/--ignore-version option removed (commit 232cd63b)
• reindexdb
  • option --verbose added (commit 458a0770)
  • option --schema added (commit fe263d11)
• vacuumdb
  • option -j/--jobs for parallel processing added (commit a1792320)

psql changes

The following is a list of significant changes to psql:

• -b command line option and new ECHO mode errors which displays only failed commands (commit 5b214c5d)
• AsciiDoc output mode added (commit 9d9991c8)
• prompt option %l added, which displays the current statement line number (commit 51bb7956)
• \pset option pager_min_lines added (commit 7655f4cc)
• unicode column, header, and border linestyle controls added  (commit a2dabf0e)
• basic tab completion support for SET search_path (commit bd409519)
• \db+ shows tablespace size in PostgreSQL 9.2 and later (commit ee80f043)

Contrib modules

Added:

• hstore_plperl (commit cac76582)
• hstore_plpython (commit cac76582)
• ltree_plpython (commit cac76582)
• tsm_system_rows (commit 4d40494b)
• tsm_system_time (commit 56e121a5)

Modified:

• Following modules moved to src/test/modules/ (commit 22dfd116):
  • dummy_seclabel
  • test_shm_mq
  • test_parser
  • worker_spi
• citext
  • fixes to regexp_matches() functions, requiring an extension version bump (commit b22527f2)
• pg_buffercache
  • pinning_backends column added to show information about buffer pins (commit f5779198)
• pgcrypto
  • function pgp_armor_headers() added (commit 32984d8f)
• pgstattuple
  • function pgstattuple_approx() added (commit 5850b20f)
• test_decoding
  • option only-local added (commit 5aa23504)

Removed:

• pg_archivecleanup
  • converted to a core utility (commit 83aca89f)
• pgbench
  • converted to a core utility (commit 81134af3)
• pg_test_fsync
  • converted to a core utility (commit 00882d9e)
• pg_upgrade_support
  • functionality integrated into backend (commit 30982be4)
• pg_xlogdump
  • converted to a core utility (commit b0a738f4)
• test_parser
  • converted to a test module (commit 22dfd116)

Testing

Test modules

From this release, the new source directory src/test/modules/ contains a number of test modules (implemented as extensions and/or SQL tests). Some test modules formerly existed as contrib modules.

Added:

• brin module added (commit 672e3ec0)
• commit_ts module added (commit 73c986ad)
• test_ddl_deparse module added (commit b488c580)
• test_rls_hooks module added (commit 0bf22e0c)

Modified:

• Following former contrib modules converted to test modules (commit 22dfd116):
  • dummy_seclabel
  • test_parser
  • test_shm_mq
  • worker_spi

Pre-release test versions

Initial commit marking the start of PostgreSQL 9.5 development is ab76208e (2014-06-09).