Contents
- Major features
- Useful links
- SQL changes
- Performance changes
- Backend changes
- Data type changes
- System catalogue changes
- System function changes
- Configuration changes
- Indexing changes
- Full text search changes
- Foreign data wrapper changes
- Users/roles
- Server utilities
- Core utilities
- psql changes
- Contrib modules
- Testing
- Pre-release test versions
PostgreSQL 9.6
- Release notes (source file)
- Released: 2016-09-29 (release announcement)
- Final minor version: 9.6.24 (2021-11-11)
- EOL: 2021-11 (EOL notice)
- Documentation: PostgreSQL 9.6
- GIT branch: REL9_6_STABLE
Major features
- Parallel execution of sequential scans, joins and aggregates
VACUUM FREEZE
avoids unnecessary page scans- synchronous replication can use multiple standby servers for increased reliability
- full text search able to search for phrases (multiple adjacent words)
postgres_fdw
supports remote joins, sorts,UPDATE
s, andDELETE
s
Useful links
- PostgreSQL 9.6 observability (interactive chart from DataEgret)
SQL changes
Added:
CREATE ACCESS METHOD
(commit 473b9328)DROP ACCESS METHOD
(commit 473b9328)
Modified:
ALTER EXTENSION
- support for
ACCESS METHOD
member objects added (commit e8bdee27; note this was actually added in PostgreSQL 9.6.1)
- support for
ALTER FUNCTION
ALTER INDEX
DEPENDS ON EXTENSION
syntax added (commit f2fcad27)
ALTER MATERIALIZED VIEW
DEPENDS ON EXTENSION
syntax added (commit f2fcad27)
ALTER OPERATOR
- options added for changing the selectivity functions associated with an existing operator (commit 321eed5f)
ALTER ROLE
- option
CREATEUSER
/NOCREATEUSER
removed (commit d371bebd)
- option
ALTER TABLE
ALTER TABLE ... ADD COLUMN ... IF NOT EXISTS
syntax added (commit 2cd40adb)
ALTER TRIGGER
DEPENDS ON EXTENSION
syntax added (commit f2fcad27)
ALTER USER
- option
CREATEUSER
/NOCREATEUSER
removed (commit d371bebd)
- option
COMMENT ON
COMMENT ON ACCESS METHOD
added (commit 4f04b66f)
COPY
COPY
can now copy the output of anINSERT
/UPDATE
/DELETE … RETURNING
query (commit 92e38182)
CREATE AGGREGATE
CREATE EXTENSION
CREATE EXTENSION … CASCADE
syntax added (commit b67aaf21)
CREATE FUNCTION
PARALLEL { UNSAFE | RESTRICTED | SAFE }
option added (commit 7aea8e4f)
CREATE GROUP
- option
CREATEUSER
/NOCREATEUSER
removed (commit d371bebd)
- option
CREATE ROLE
- option
CREATEUSER
/NOCREATEUSER
removed (commit d371bebd)
- option
CREATE USER
- option
CREATEUSER
/NOCREATEUSER
removed (commit d371bebd)
- option
INSERT
- support for array subscripts and field selections in the target column list added (commit a3c7a993)
VACUUM
- option
DISABLE_PAGE_SKIPPING
added (commit ede62e56)
- option
Performance changes
ALTER TABLE ... SET (fillfactor = ...)
can be performed at a reduced lock level (commit fcb4bfdd)ALTER TABLE ... SET (autovacuum* = ...)
can be performed at a reduced lock level (commit 47167b79)
Backend changes
General
- support for systemd service notifications added (commit 7d17e683)
Storage parameters
Added:
parallel_workers
(initial commit 25fe8b5f)
Hooks
Following hook added:
create_upper_paths_hook
(commit 5864d6a4)
Locking
- locks now identified by name if
trace_lwlocks
is used (commit aa65de04)
WAL
- Generic WAL interface added (commit 65578341)
Source configuration
- option
--with-systemd
added (commit 7d17e683)
Data type changes
System catalogue changes
Tables
Added:
pg_init_privs
(commit 6c268df1)
Modified:
pg_aggregate
pg_am
pg_proc
- column
proparallel
added (commit 7aea8e4f)
- column
Views
Added:
pg_config
(commit a5c43b88)pg_stat_wal_receiver
(commit b1a9bad9)pg_stat_progress_vacuum
(commit c16dc1ac)
Modified:
pg_replication_slots
- column
confirmed_flush_lsn
added (commit 3f811c2d)
- column
pg_stat_activity
System function changes
Added:
acosd()
(commit e1bd684a)array_to_tsvector()
(commit 6943a946)asind()
(commit e1bd684a)atand()
(commit e1bd684a)atan2d()
(commit e1bd684a)cosd()
(commit e1bd684a)cotd()
(commit e1bd684a)gin_clean_pending_list()
(commit 7f46eaf0)jsonb_insert()
(commit 0b62fd03)parse_ident()
(commit 3187d6de)pg_blocking_pids()
(commit 52f5d578)pg_control_checkpoint()
(commit dc7d70ea)pg_control_init()
(commit dc7d70ea)pg_control_recovery()
(commit dc7d70ea)pg_control_system()
(commit dc7d70ea)pg_current_xlog_flush_location()
(commit e63bb454)pg_index_column_has_property()
(commit ed0097e4)pg_index_has_property()
(commit ed0097e4)pg_indexam_has_property()
(commit ed0097e4)pg_logical_emit_message()
(commit 3fe3511d)pg_notification_queue_usage()
(commit a04bb65f)pg_size_bytes()
(commit 53874c52)pg_stat_get_backend_wait_event()
(commit 53be0b1a)pg_stat_get_backend_wait_event_type()
(commit 53be0b1a)scale()
(commit abb17339)setweight()
(commit 6943a946)sind()
(commit e1bd684a)tand()
(commit e1bd684a)ts_delete()
(commit 6943a946)ts_filter()
(commit 6943a946)tsvector_to_array()
(commit 6943a946)unnest(tsvector)
(commit 6943a946)
Modified
current_setting()
- option
missing_ok
added (commit 10fb48d6)
- option
extract()
- handling of infinite datetimes improved (commit 647d87c5)
pg_create_physical_replication_slot()
- optional parameter
immediately_reserve
added to retain WAL from the point of slot creation (commit 6fcd8851)
- optional parameter
pg_stop_backup()
- option
exclusive boolean
added (commit 71176854)
- option
to_char()
- negative sign ("
-
") no longer counted as a digit for time values (commit 2d87eedc)
- negative sign ("
Configuration changes
Added:
backend_flush_after
(commit 428b1d6b)bgwriter_flush_after
(commit 428b1d6b)checkpoint_flush_after
(commit 428b1d6b)force_parallel_mode
(commit 7c944bd9)idle_in_transaction_session_timeout
(commit c6dda1f4)max_parallel_workers_per_gather
(initial commit 80558c1f)min_parallel_relation_size
(commit 75be6646)parallel_setup_cost
(commit 3bd909b2)parallel_tuple_cost
(commit 3bd909b2)old_snapshot_threshold
(commit 848ef42b)syslog_sequence_numbers
(commit f4c454e9)syslog_split_messages
(commit fc201dfd)wal_writer_flush_after
(commit 7975c5e0)
Modified:
checkpoint_timeout
- maximum value raised to
1d
(commit f2dba881)
- maximum value raised to
log_line_prefix
%n
parameter added to show the Unix epoch (commit f828654e)
synchronous_commit
- permissible value
remote_apply
added (commit 314cbfc5)
- permissible value
Indexing changes
GIN
maintenance_work_mem
values of more than1GB
can be used forGIN
index builds (commit 30bb26b5)- pages deleted from a GIN index's pending list to the free space map immediately (commits e9568083 and dc943ad9)
Full text search changes
- Phrase full text search via the
<->
operator added (commit bb140506) - improve support for leading digits in email addresses and hostnames (commit 61d66c44)
Foreign data wrapper changes
UPDATE
orDELETE
commands can be executed entirely on the remote server (commit 0bf3ae88)- remote foreign joins performed only when the affected tables will be accessed under the same role ID (commit 45639a05)
- API support for parallel queries (commit 35746bc3)
Users/roles
- roles can no longer created with the
pg_
prefix (commit 29300789) - Initial default role "
pg_signal_backend
" added (commit 7a542700)
Server utilities
Modified:
initdb
- speed up by using only one backend for post-bootstrap steps (commit c4a8812c)
pg_rewind
- supports resynchronisation with a lower target timeline (commit e50cda78)
Core utilities
Modified:
pg_basebackup
- option
--slot
added to reserve a replication slot before the backup is started (commit 0dc848b0)
- option
pg_restore
psql changes
- support for multiple and mixed
-c
and-f
options added (commit d5563d7d) \crosstabview
added which prints the results of a query in a cross-tabulated display (initial commit c09b18f2)\errverbose
command added which shows the last server error at full verbosity (commit 3cc38ca7)\ev
and\sv
commands added for editing/showing view definitions (commit 8eb6407a)\gexec
command added which executes a query and re-submits the result(s) as new queries (commit 2bbe9112)- tab completion now considers the entire query, not just the current line (commit d854118c)
- prompt option
%p
added, which displays the PID of the connected backend (commit 275f05c9) - display of the
CONTEXT
field of messages can be controlled by the new variableSHOW_CONTEXT
(commit 0426f349) --reuse-previous
option added to the\connect
command (commit 9d924e9a)
Contrib modules
Added:
bloom
(commit 9ee014fc)pg_visibility
(commit ba0a198f)
Modified:
auto_explain
- option
auto_explain.sample_rate
added (commit 92f03fe7)
- option
citext
parallel query
support added (commit a89b4b1b)
cube
earthdistance
parallel query
support added (commit 50e5226b)
file_fdw
parallel query
support added (commit 35746bc3)
fuzzystrmatch
- all functions marked parallel safe (commit ffab82fb)
lo
parallel query
support added (e7880e5d)
pgcrypto
parallel query
support added (commit 0dbf3ce0)
pg_freespacemap
parallel query
support added (commit 42d4257a)
pg_prewarm
parallel query
support added (commit 6b3586ca)
pgrowlocks
parallel query
support added (commit 9164deea)
pg_trgm
postgres_fdw
- basic support added for remote joins (commits e4106b25 and aa09cd24)
- support added for remote sorts (commits f18c944b and ccd8f979)
- support added for directly modifying foreign tables with remote
UPDATE
s orDELETE
s (commit 0bf3ae88) - the
fetch_size
option can now be applied to individual tables as well as the foreign server (commit dc203dc3) - query cancellation requests will be transmitted to the remote server (commits f039eaac and 1b812afb)
- user mappings which map to the same user on the remote server will be consolidated into a single remote connection (commit 96198d94)
- extension functions and operators, if specified in the
extensions
option, can be pushed down to the remove server (commit d8949416)
seg
sslinfo
- function
ssl_extension_info()
added (commit 49124613)
- function
Testing
Test modules
Added:
snapshot_too_old
(commit 848ef42b)test_extensions
(commit b67aaf21)test_pg_dump
(commit 6bd356c3)
Pre-release test versions
Initial commit marking the start of PostgreSQL 9.6
development is cf8d65de (2015-06-30).