Contents
- Major features
- SQL changes
- Performance changes
- Backend changes
- Data type changes
- System catalogue changes
- System functions
- Configuration changes
- libpq changes
- Procedural language changes
- Foreign data wrapper changes
- Information schema
- Server utilities
- Core utilities
- psql changes
- Contrib modules
- Pre-release test versions
PostgreSQL 9.2
- Release notes
- Released: 2012-09-10 (announcement)
- Final minor version: 9.2.24
- EOL: 2017-11
Major features
- Cascading replication (initial commit 52861058)
- Range types
- Index-only scans (initial commit a2822fb9)
- JSON data type
SQL changes
General:
- constraints
CHECK
constraints can be delaredNOT VALID
(commit 89779524)
- views
security_barrier
option added (commit 0e4611c0)
Added
COLLATION FOR
expression (commit 6688d287)
Modified:
ALTER DEFAULT PRIVILEGES
- support for altering privileges on types added (commit 72920557)
ALTER DOMAIN
ALTER FOREIGN DATA WRAPPER
ALTER FOREIGN DATA WRAPPER ... RENAME TO ...
clause added (commit 5bcf8ede)
ALTER FOREIGN TABLE
ALTER INDEX
ALTER INDEX IF EXISTS ...
syntax added (commit b8a91d9d)
ALTER SEQUENCE
ALTER SEQUENCE IF EXISTS ...
syntax added (commit b8a91d9d)
ALTER SERVER
ALTER SERVER ... RENAME TO ...
clause added (commit 5bcf8ede)
ALTER TABLE
ALTER TABLE [ IF EXISTS] ... RENAME CONSTRAINT ... TO ...
syntax added (commit 39d74e34)
ALTER VIEW
CREATE FOREIGN TABLE
- column-level
OPTIONS
clause added (commit c4096c76)
- column-level
CREATE TABLE
GLOBAL
andLOCAL
options deprecated forCREATE TEMPORARY TABLE
(commit c3bc76bd)
CREATE VIEW
CREATE VIEW ... WITH ( view_option_name [= view_option_value] )
syntax added (commit 0e4611c0)
DROP INDEX
DROP INDEX CONCURRENTLY
syntax added (commit 8cb53654)
EXPLAIN
TIMING
option added (commit af7914c6)
SECURITY LABEL
- support for global objects (databases, tablespaces and roles) added (commit 463f2625)
Performance changes
ALTER TABLE ... ALTER TYPE
can skip table/index rebuilds for the following type changes:
Backend changes
General
- pidfile specified in
external_pid_file
will be created as world-readable (commit 223d14a9) - checkpointer process added (commit bf405ba8)
postmaster
- pipe added between postmaster and each backend for easier detection of postmaster death (commit 89fd72cb)
Locking
- fast-path locks added (commit 3cba8999)
Memory management
- Memory barrier support added (commit 0c8eda62)
Storage parameters
Added:
log_autovacuum_min_duration
(commit 9d3b5024)
Hooks
Following hooks added:
emit_log_hook
(commit 19dbc346)post_parse_analyze_hook
(commit a40fa613)
Data type changes
General:
System catalogue changes
Tables
Added:
pg_range
(commit 4429f6a9)pg_shseclabel
(commit 463f2625)
Modified:
pg_am
pg_attribute
- column
attfdwoptions
added (commit c4096c76)
- column
pg_class
- column
relallvisible
added (commit e6858e66)
- column
pg_constraint
- column
conisonly
renamed toconnoinherit
(commit 09ff76fc)
- column
pg_locks
- column
fastpath
added (commit 3cba8999)
- column
pg_proc
pg_statistic
- following columns added (commit 0e5e167a)
stakind5
staop5
stanumbers5
stavalues5
- following columns added (commit 0e5e167a)
pg_tablespace
- column
spclocation
removed and replaced by functionpg_tablespace_location()
(commit 16d8e594)
- column
Views
Modified:
pg_stat_activity
pg_stat_bgwriter
pg_stat_database
pg_stat_replication
- column
procpid
renamed topid
(commit 4f42b546)
- column
pg_stats
- following columns added (commit 0e5e167a):
most_common_elems
most_common_elem_freqs
elem_count_histogram
- following columns added (commit 0e5e167a):
System functions
Added:
acldefault()
(commit b376ec6f)array_to_json()
(commit 39909d1d)has_type_privilege()
(commit 72920557)pg_export_snapshot()
(commit bb446b68)pg_opfamily_is_visible()
(commit b59d2fe4)pg_tablespace_location()
(commit 16d8e594)pg_trigger_depth()
(commit 74ab96a4)pg_xlog_location_diff()
(commit bc5ac368)row_to_json()
(commit 39909d1d)
Modified:
pg_cancel_backend()
- non-superusers can terminate their own sessions in other backends (commit c60ca19d)
pg_size_pretty()
numeric
input format added (commit 4a2d7ad7)
pg_terminate_backend()
- non-superusers can terminate their own sessions in other backends (commit c60ca19d)
string_agg()
Configuration changes
Added:
enable_indexonlyscan
(commit a2822fb9)include_if_exists
directive (commit 6d09b210)ssl_ca_file
(commit a445cb92)ssl_cert_file
(commit a445cb92)ssl_crl_dir
(commit a445cb92)ssl_crl_file
(commit a445cb92)temp_file_limit
(commit 23e5b16c)track_io_timing
(initial commit 40b9b957)
Modified:
synchronous_commit
remote_write
setting added (initial commit 443b4821)
Removed:
silent_mode
(commit f7ea6bea)wal_sender_delay
(commit cff75130)
libpq changes
PQsetSingleRowMode()
added (commit ea56ed9a)
Procedural language changes
PL/pgSQL
GET STACKED DIAGNOSTICS
command added (commit 3d4890c0)
Foreign data wrapper changes
- planning API improvements providing more flexibility in join planning (commit b1495393)
- collection of statistics for foreign tables enabled (commit 263d9de6)
- per-column options for foreign tables added (initial commit 9088d1b9)
RENAME
option added forALTER FOREIGN DATA WRAPPER
andALTER
SERVER (commit 5bcf8ede)CREATE TABLE (LIKE ...)
able to use foreign table definitions (commit a9f2e31c)
Information schema
- view
role_udt_grant
added (commit d34e142c) - view
udt_privileges
added (commit d34e142c) - view
user_defined_types
added (commit d34e142c)
Server utilities
Modified:
initdb
- identifies server time zone and writes value to
postgresql.conf
(commit ca4af308) - options
--auth-local
and--auth-host
added (commit 8a02339e)
- identifies server time zone and writes value to
Core utilities
Added:
pg_receivexlog
(commit d9bae531)
Modified:
createuser
dropuser
- option
--if-exists
added (commit 7fe33a51)
- option
pg_basebackup
- able to make base backups from standby servers (commit 8366c780)
pg_dump
- option
--section
added (commit a4cd6abc)
- option
pg_restore
- option
--section
added (commit a4cd6abc)
- option
psql changes
auto
option added to\x
command (commit 95d2af16)\ir
command added (commit c7f23494)\setenv
command added (commit e6d9e210).psqlrc-X.X
(whereX.X
represents a PostgreSQL major version) processing added (commit 2795592e)PSQL_HISTORY
andPSQLRC
environment variables now recognised (commit 34c97844)COMP_KEYWORD_CASE
variable added to control keyword case in tab completion (commit db84ba65)
Contrib modules
Added:
pg_test_timing
(commit cee52386)tcn
(commit 6e3323d4)
Modified:
file_fdw
force_not_null
option added (commit 86a3f2d4)
pgbench
pg_stat_statements
- SQL text normalization (commit 7313cc01)
- dirtied buffer counts added (commit 22543674)
- columns
blk_read_time
andblk_write_time
added, which will be populated iftrack_io_timing
is enabled (commit 5b4f3466)
pg_upgrade
- environment variables specific to
pg_upgrade
renamed to start with PG; PGPORT now ignored and replaced byPGPORTOLD
/PGPORTNEW
(commit a88f4496) - uses port 50432 by default (commit a88f4496)
- general logging improvements (commit 717f6d60)
- old cluster will only be locked if link mode used (commit 2127aac6)
- script to incrementally generate more accurate statistics added (commit faec2815)
-g
/-G
/-l
logging options removed (commit 717f6d60)-o
/-O
options added (commit 0dc3f57b)-r
/--retain
option added (commit 717f6d60)-v
/--verbose
behaviour improved (commit 717f6d60)- support upgrading clusters without a postgres database (commit a50d860a)
- environment variables specific to
sepgsql
- allow
sepgsql
to honor database labels (commit 291873c1) - perform
sepgsql
permission checks during the creation of various objects (commit e1042a34) sepgsql_setcon()
and related functions to control thesepgsql
security domain added (commit 523176cb)- user space access cache added to improve performance (commit 4232c4b4)
- allow
vacuumlo
-l
option added (initial commit b69f2e36)
Pre-release test versions
Initial commit marking the start of PostgreSQL 9.2
development is c9627922 (2011-06-11).