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
INSERT
s which would generate constraint conflicts can be turned intoUPDATE
s or ignored ("UPSERT
") (commit 168d5805)GROUP BY
analysis featuresGROUPING SETS
,CUBE
andROLLUP
- row level security (RLS) added (commit 491c029d)
- replication origins for logical decoding
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
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 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
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
andSESSION_USER
options added toALTER 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 toALTER TABLE ... RENAME CONSTRAINT
clause (commit 1d8198bb) ALTER TABLE
will now execute thetable_rewrite
event trigger (commit 618c9430)
ALTER TYPE
ALTER TYPE ... OWNER TO { CURRENT_USER | SESSION_USER }
syntax added (commit 31eae602)
ALTER USER
ALTER VIEW
ALTER VIEW ... OWNER TO { CURRENT_USER | SESSION_USER }
syntax added (commit 31eae602)
COMMENT ON
CREATE DATABASE
CREATE FOREIGN TABLE
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 SCHEMA
CURRENT_USER
andSESSION_USER
available forCREATE 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
UPDATE
UPDATE tab SET (col1,col2,...) = (SELECT ...), ...
syntax added (commit 8f889b10)
Backend changes
General
- 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)
System catalogue changes
Tables
Added:
pg_policy
(initial commit 491c029d)pg_replication_origin
(commit 5aa23504)pg_transform
(commit cac76582)
Modified:
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)
- column
pg_roles
pg_settings
- column
pending_restart
added (commit a486e357)
- column
pg_shadow
pg_stat_replication
- column
sent
will now be NULL, not zero, if it has no valid value (commit 28c36678)
- column
pg_tables
- column
hasrowsecurity
added (commit 491c029d)
- column
System functions
Added:
array_position()
(initial commit 13dbc7a8)array_positions()
(initial commit 13dbc7a8)pg_last_committed_xact()
(commit 73c986ad)pg_xact_commit_timestamp()
(commit 73c986ad)
Modified:
pg_ls_dir()
- options
missing_ok
andinclude_dot_dirs
added (commit cb2acb10)
- options
pg_read_file()
- option
missing_ok
added (commit cb2acb10)
- option
pg_read_binary_file()
- option
missing_ok
added (commit cb2acb10)
- option
pg_stat_file()
- option
missing_ok
added (commit cb2acb10)
- option
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)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)
Removed:
checkpoint_segments
(commit 51adcaa0; replaced bymin_wal_size
andmax_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 ofprocedural 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:
Modified:
pg_controldata
-D
option added for consistency with other utilities when specifying the data directory (commit b0d81ade)
pg_ctl
pg_resetxlog
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)
- able to use a
pgbench
- converted from a contrib module (commit 81134af3)
pg_dump
- redundant
-i
/--ignore-version
option removed (commit 232cd63b)
- redundant
pg_dumpall
- redundant
-i
/--ignore-version
option removed (commit 232cd63b)
- redundant
pg_receivexlog
pg_restore
- redundant
-i
/--ignore-version
option removed (commit 232cd63b)
- redundant
reindexdb
vacuumdb
- option
-j
/--jobs
for parallel processing added (commit a1792320)
- option
psql changes
The following is a list of significant changes to psql:
- AsciiDoc output mode added (commit 9d9991c8)
- prompt option
%l
added, which displays the current statement line number (commit 51bb7956) \pset
optionpager_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):
citext
- fixes to
regexp_matches()
functions, requiring an extension version bump (commit b22527f2)
- fixes to
pg_buffercache
pinning_backends
column added to show information about buffer pins (commit f5779198)
pgcrypto
- function
pgp_armor_headers()
added (commit 32984d8f)
- function
pgstattuple
- function
pgstattuple_approx()
added (commit 5850b20f)
- function
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):