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 delared NOT 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 DOMAIN ... RENAME clause added (commit f90dd280)
  • ALTER DOMAIN ... DROP CONSTRAINT ... IF EXISTS clause added (commit 104e7dac)
• ALTER FOREIGN DATA WRAPPER
  • ALTER FOREIGN DATA WRAPPER ... RENAME TO ... clause added (commit 5bcf8ede)
• ALTER FOREIGN TABLE
  • ALTER FOREIGN TABLE IF EXISTS ... syntax added (commit b8a91d9d)
  • ALTER FOREIGN TABLE ... ALTER COLUMN ... OPTIONS (...) clause added (commit c4096c76)
  • ALTER FOREIGN FOREIGN TABLE ... SET STATISTICS / SET (...) / RESET (...) syntax added (commit 263d9de6)
• 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
  • ALTER VIEW IF EXISTS ... syntax added (commit b8a91d9d)
  • ALTER VIEW ... [ SET | RESET ] syntax added (commit 0e4611c0)
• CREATE FOREIGN TABLE
  • column-level OPTIONS clause added (commit c4096c76)
• CREATE TABLE
  • GLOBAL and LOCAL options deprecated for CREATE 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:
  • varbit to unconstrained varbit (commit f7d7dade)
  • numeric to unconstrained numeric, or increasing the numeric precision (commit 3cc08008)
  • interval, timestamp, and timestamptz type changes (commit c1389798)

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:

• JSON type added (initial commit 5384a73f)
• range type added (commit 4429f6a9)

System catalogue changes

Tables

Added:

• pg_range (commit 4429f6a9)
• pg_shseclabel (commit 463f2625)

Modified:

• pg_am
  • column amsearcharray added commit 9e8da0f7)
  • column amcanreturn added (initial commit a2822fb9)
• pg_attribute
  • column attfdwoptions added (commit c4096c76)
• pg_constraint
  • column conisonly renamed to connoinherit (commit 09ff76fc)
• pg_locks
  • column fastpath added (commit 3cba8999)
• pg_proc
  • column protransform added (commit 8f9fe6ed)
  • column proleakproof added (commit cd30728f)
• pg_statistic
  • following columns added (commit 0e5e167a)
    • stakind5
    • staop5
    • stanumbers5
    • stavalues5
• pg_tablespace
  • column spclocation removed and replaced by function pg_tablespace_location() (commit 16d8e594)

Views

Modified:

• pg_stat_activity
  • column pid added, renamed from procpid (commit 4f42b546)
  • column query added, replacing current_query (commit 4f42b546)
  • column state added (commit 4f42b546)
  • column state_change added (commit 4f42b546)
• pg_stat_bgwriter
  • column checkpoint_write_time added (commit b736aef2)
  • column checkpoint_sync_time added (commit b736aef2)
• pg_stat_database
  • Following columns renamed (commit 1dd89ead):
    • block_read_time renamed to blk_read_time
    • block_write_time renamed to blk_write_time
  • Following columns added:
    • conflicts (commit 40d9e94b)
    • deadlocks (commit 61cb8c5a)
    • temp_files (commit bc334748)
    • temp_bytes (commit bc334748)
• pg_stat_replication
  • column procpid renamed to pid (commit 4f42b546)
• pg_stats
  • following columns added (commit 0e5e167a):
    • most_common_elems
    • most_common_elem_freqs
    • elem_count_histogram

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()
  • bytea handling added (initial commit d5448c7d)

Configuration changes

Added:

• enable_indexonlyscan (commit a2822fb9)
• include_if_exists directive (commit 6d09b210)
• 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 for ALTER FOREIGN DATA WRAPPER and ALTER 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)

Core utilities

Added:

• pg_receivexlog (commit d9bae531)

Modified:

• createuser
  • option -i/--interactive added (commit a347f96b)
  • option --if-exists added (commit 7fe33a51)
• dropuser
  • option --if-exists added (commit 7fe33a51)
• pg_basebackup
  • able to make base backups from standby servers (commit 8366c780)
• pg_dump
  • option --section added (commit a4cd6abc)
• pg_restore
  • option --section added (commit a4cd6abc)

psql changes

• auto option added to \x command (commit 95d2af16)
• \ir command added (commit c7f23494)
• \setenv command added (commit e6d9e210)
• .psqlrc-X.X (where X.X represents a PostgreSQL major version) processing added (commit 2795592e)
• PSQL_HISTORY and PSQLRC 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
  • option --tablespace added (commit 7c263956)
  • option --index-tablespace added (commit 7c263956)
  • option --unlogged-tables added (commit 2d6fee09)
• pg_stat_statements
  • SQL text normalization (commit 7313cc01)
  • dirtied buffer counts added (commit 22543674)
  • columns blk_read_time and blk_write_time added, which will be populated if track_io_timing is enabled (commit 5b4f3466)
• pg_upgrade
  • environment variables specific to pg_upgrade renamed to start with PG; PGPORT now ignored and replaced by PGPORTOLD/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)
• 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 the sepgsql security domain added (commit 523176cb)
  • user space access cache added to improve performance (commit 4232c4b4)
• 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).