PostgreSQL 9.1

• Release notes
• Released: 2011-09-12 (announcement)
• Final minor version: 9.1.24
• EOL: 2016-10

Major features

• extension support added (commit d9572c4e)
• unlogged tables (commit 53dbc27c)
• transaction-level advisory locks added (commit 62c7bd31)
• read-only foreign data wrapper (FDW) support added (initial commit 0d692a0d)
• per-column collation support (commit 414c5a2e)
• serial snapshot isolation (commit dafaa3ef)
• synchronous replication support (initial commit a8a8a3e0)

SQL changes

General:

• support for INSERT/UPDATE/DELETE in WITH clauses added (commit 389af951)

Added:

• ALTER COLLATION (commit b313bca0)
• ALTER EXTENSION (commit d9572c4e)
• ALTER FOREIGN TABLE (commit 0d692a0d)
• CREATE COLLATION (commit b313bca0)
• CREATE EXTENSION (commit d9572c4e)
• CREATE FOREIGN TABLE (commit 0d692a0d)
• DROP COLLATION (commit b313bca0)
• DROP EXTENSION (commit d9572c4e)
• DROP FOREIGN TABLE (commit 0d692a0d)
• SECURITY LABEL (commit 4d355a83)

Modified:

• ALTER CONVERSION
  • ALTER CONVERSION ... SET SCHEMA ... syntax added (commit 55109313)
• ALTER OPERATOR
  • ALTER OPERATOR ... SET SCHEMA ... syntax added (commit 55109313)
• ALTER OPERATOR CLASS
  • ALTER OPERATOR CLASS ... SET SCHEMA ... syntax added (commit 55109313)
• ALTER OPERATOR FAMILY
  • ALTER OPERATOR FAMILY ... SET SCHEMA ... syntax added (commit 55109313)
  • FOR SEARCH / FOR ORDER BY syntax added (commit 725d52d0)
• ALTER ROLE
  • option REPLICATION/NOREPLICATION added (commit 9b8aff8c)
• ALTER TABLE
  • NOT VALID option added to ALTER TABLE ... ADD constraint commit 722bf701)
  • ALTER TABLE VALIDATE constraint syntax added (commit 722bf701)
  • ALTER TABLE name {OF type | NOT OF type} syntax added (commit 68739ba8)
  • ALTER TABLE ... ADD { UNIQUE | PRIMARY KEY } USING INDEX syntax added (commit 88452d5b)
  • ALTER TABLE ... SET DATA TYPE ... no longer requires a table rewrite for some data type combinations (commit d31e2a49)
• ALTER TEXT SEARCH CONFIGURATION
  • ALTER TEXT SEARCH CONFIGURATION ... SET SCHEMA ... syntax added (commit 55109313)
• ALTER TEXT SEARCH DICTIONARY
  • ALTER TEXT SEARCH DICTIONARY ... SET SCHEMA ... syntax added (commit 55109313)
• ALTER TEXT SEARCH PARSER
  • ALTER TEXT SEARCH PARSER ... SET SCHEMA ... syntax added (commit 55109313)
• ALTER TEXT SEARCH TEMPLATE
  • ALTER TEXT SEARCH TEMPLATE ... SET SCHEMA ... syntax added (commit 55109313)
• ALTER TYPE
  
  • ALTER TYPE ... ADD VALUE ... [ BEFORE | AFTER ] syntax added, enabling new values to be added to an existing ENUM type (commit 84c123be)
  • ALTER TYPE ... [ADD | DROP | ALTER | RENAME] ATTRIBUTE ... syntax added (commit e440e12c)
  • ALTER TYPE ... ADD ATTRIBUTE ... CASCADE syntax added (commit a0e8df52)
• ALTER USER
  • option REPLICATION/NOREPLICATION added (commit 9b8aff8c)
• COMMENT ON
  • COMMENT ON COLLATION added (commit b313bca0)
  • COMMENT ON EXTENSION added (commit d9572c4e)
  • COMMENT ON FOREIGN DATA WRAPPER added (commit 50533a6d)
  • COMMENT ON FOREIGN TABLE added (commit 50533a6d)
  • COMMENT ON SERVER added (commit 50533a6d)
• COPY
  • option ENCODING added (commit 3cba8240)
• CREATE DOMAIN
  • option COLLATE added (commit 414c5a2e)
• CREATE INDEX
  • option COLLATE added (commit 414c5a2e)
• CREATE FOREIGN DATA WRAPPER
  • HANDLER option added (commit 327e0250)
• CREATE OPERATOR CLASS
  • FOR SEARCH / FOR ORDER BY syntax added (commit 725d52d0)
• CREATE ROLE
  • option REPLICATION/NOREPLICATION added (commit 9b8aff8c)
• CREATE TABLE
  • UNLOGGED option added (commit 53dbc27c)
  • CREATE TABLE IF NOT EXISTS syntax added (commit a3b012b5)
• CREATE TABLE AS
  • UNLOGGED option added (commit 53dbc27c)
• CREATE TRIGGER
  • CREATE TRIGGER ... INSTEAD OF ... syntax added (commit 2ec993a7)
• CREATE TYPE
  • option COLLATABLE added (commit 414c5a2e)
• INSERT
  • support for WITH clause added (commit 07f1264d)

Backend changes

Planner changes

• cost estimation for aggregates and window functions improved (commit e6a30a8c)

postmaster

• postmaster.pid
  • lines with port number and socket directory added (commit 075354ad)
  • lines with first listen_address and start time added (commit 30aeda43)
• undocumented -b option for use by pg_upgrade added (commit 76dd09bb)

Hooks

Following hooks added:

• ClientAuthentication_hook (commit 20709f81)
• ExecutorCheckPerms_hook (commit f4122a8d)
• ExecutorFinish_hook (commit a874fe7b)
• fmgr_hook (commit d368e1a2)
• needs_fmgr_hook (commit d368e1a2)
• object_access_hook (commit cc1ed40d)

Data type changes

Added:

• pg_node_tree (commit 303696c3)

Modified:

• ENUM types can have new values added (commit 84c123be)

System catalogue changes

Tables

Added:

• pg_collation (commit 414c5a2e)
• pg_extension (commit d9572c4e)
• pg_foreign_table (commit 0d692a0d)
• pg_seclabel (commit 4d355a83)

Modified:

• pg_am
  • column ampredlocks added (commit dafaa3ef)
  • column ambuildempty added (commit 7e2f9062)
  • column amindexnulls removed (commit 7e2f9062)
• pg_amop
  • column amoppurpose added (commit 725d52d0)
  • column amopsortfamily added (commit 725d52d0)
• pg_attribute
  • column attcollation added (commit 414c5a2e)
• pg_authid
  • column rolreplication added (commit 9b8aff8c)
• pg_class
  • column relpersistence added (commit 5f7b58fa)
  • column relistemp removed (commit 5f7b58fa)
• pg_enum
  • column enumsortorder added (commit 84c123be)
• pg_foreign_data_wrapper
  • column fdwhandler added (commit 327e0250)
• pg_index
  • column indisexclusion added (commit bd1ad1b0)
  • column indcollation added (commit 414c5a2e)
• pg_proc
  • column proargdefaults changed to type pg_node_tree (commit 303696c3)
• pg_rewrite
  • column ev_qual changed to type pg_node_tree (commit 303696c3)
  • column ev_action changed to type pg_node_tree (commit 303696c3)
• pg_type
  • column typcollation added (commit 414c5a2e)

Views

Added:

• pg_available_extensions (commit d9572c4e)
• pg_available_extension_versions (commit d9572c4e)
• pg_seclabels (commit 4d355a83)
• pg_stat_database_conflicts (commit 40d9e94b)
• pg_stat_replication (commit a755ea33)
• pg_stat_xact_all_tables (commit 46aa77c7)
• pg_stat_xact_sys_tables (commit 46aa77c7)
• pg_stat_xact_user_functions (commit 46aa77c7)
• pg_stat_xact_user_tables (commit 46aa77c7)

Modified:

• pg_roles
  • column rolreplication added (commit 9b8aff8c)
• pg_shadow
  • column userepl added (commit 9b8aff8c)
• pg_stat_activity
  • column client_hostname added (commit 4a25bc14)
• pg_stat_all_tables
  • following columns added; applies to pg_stat_sys_tables and pg_stat_user_tables as well (commit 946045f0):
    • vacuum_count
    • autovacuum_count
    • analyze_count
    • autoanalyze_count
• pg_stat_bgwriter
  • column buffers_backend_fsync added (commit 3134d886)
  • column stats_reset added (commit 4c468b37)
• pg_stat_database
  • column conflicts added (commit 40d9e94b)
  • column stats_reset added (commit 4c468b37)

System function changes

Added:

• concat() (commit 49b27ab5)
• concat_ws() (commit 49b27ab5)
• format() (commit 75048707)
• left() (commit 49b27ab5)
• pg_advisory_xact_lock() (commit 62c7bd31)
• pg_advisory_xact_lock_shared() (commit 62c7bd31)
• pg_collation_is_visible() (commit 414c5a2e)
• pg_create_restore_point() (commit c016ce72)
• pg_describe_object() (commit 6cc2deb8)
• pg_extension_update_paths() (commit 555353c0)
• pg_is_xlog_replay_paused() (initial commit 8c6e3adb)
• pg_try_advisory_xact_lock() (commit 62c7bd31)
• pg_try_advisory_xact_lock_shared() (commit 62c7bd31)
• pg_xlog_replay_resume() (initial commit 8c6e3adb)
• pg_xlog_replay_pause() (initial commit 8c6e3adb)
• reverse() (commit 49b27ab5)
• right() (commit 49b27ab5)
• xml_is_well_formed() (commit a0b7b717)
• xml_is_well_formed_content() (commit a0b7b717)
• xml_is_well_formed_document() (commit a0b7b717)
• xpath_exists() (commit 4dfc4578)

Modified:

• array_to_string()
  • optional third parameter specifying a string to represent NULL values added (commit 33f43725)
• string_to_array()
  • optional third parameter specifying a string to be treated as a NULL value added (commit 33f43725)
• pg_read_file()
  • able to read an entire file, rather than specified segments (commit 03db44ea)

Configuration changes

Added:

• default_transaction_deferrable (commit dafaa3ef)
• hot_standby_feedback (commit bca8b7f1)
• log_file_mode (commit 3ec694e1)
• max_pred_locks_per_transaction (initial commit dafaa3ef)
• quote_all_identifiers (commit ce68df46)
• replication_timeout (commit 754baa21)
• restart_after_crash (commit 5ffaa900)
• synchronous_standby_names (commit a8a8a3e0)
• transaction_deferrable (commit dafaa3ef)
• wal_receiver_status_interval (commit b186523f)

Modified:

• exit_on_error
  • changed to a documented setting (commit 5ffaa900)
• standard_conforming_strings
  • default value changed from off to on (commit 0839f312)
• synchronous_commit
  • local setting added (commit 240067b3)
• wal_sender_delay
  • default changed from 200ms to 1s (commit 40f908bd)
• wal_sync_method
  • default value on Linux changed to fdatasync (commit 576477e7)

Indexing changes

GIN

• recognition of duplicate search entries improved (commit 56a57473)
• support for indexing NULL anrd empty values added (commit 73912e7f)
• index scan cost estimation improved (commit 48c7d9f6)

Procedural language changes

General

• core procedural languages converted to extensions (commit 63b656b7)

PL/pgSQL

• support for COLLATE clause in variable declarations (commit c9473258)
• FOREACH IN ARRAY looping syntax added (commit 6e02755b)

PL/Perl

• PostgreSQL arrays converted to PL/Perl arrays in input arguments (commit 87bb2ade)

PL/Python

• support for explicit subtransactions added (commit 22690719)
• support for table functions added (commit bc411f25)
• validator function added (commit 15f55cc3)
• following functions added (commit 1c51c7d5):
  • plpy.quote_ident
  • plpy.quote_literal
  • plpy.quote_nullable

Replication changes

General

• synchronous replication support added (initial commit a8a8a3e0)
• Streaming replication replay control functions added (initial commit 8c6e3adb)
• Recovery can now be paused after reaching the recovery target (initial commit 8c6e3adb)
• Named recovery targets (commit c016ce72)

Configuration

Added:

• pause_at_recovery_target (commit 8c6e3adb)
• recovery_target_name (commit c016ce72)

libpq changes

• client_encoding connection parameter added (commit 02e14562)
• requirepeer connection parameter added (initial commit 040aee29)
• function PQlibVersion() added (commit de9a4c27)

psql changes

• \conninfo command to display current connection info added (commit 013ed0bd)
• \sf command to display definition of a function added (commit b6e06942)
• \dL command to list available languages added (commit 9c5e2c12)
• \dn now ignores system schemas by default, \dnS includes them (commit e43fb604)
• \dO command to list available collations added (commit b313bca0)

Server utilities

Modified:

• pg_ctl
  • promote option added (commit 4695da5a)

Core utilities

Added:

• pg_basebackup (commit 048d148f)

Modified:

• pg_dump
  • option --no-unlogged-table-data added (commit 53dbc27c)
  • option --no-security-labels added (initial commit c8e0c321)
• pg_restore
  • option --no-security-labels added (initial commit c8e0c321)

Contrib modules

Added:

• auth_delay (commit fe7a32fc)
• dummy_seclabel (commit 22dfd116)
• file_fdw (commit 7c5d0ae7)
• pg_test_fsync (commit 606a3d54)
• sepgsql (commit 968bc6fa)

Modified:

• btree_gin
  • converted to an extension (commit 629b3af2)
• btree_gist
  • support for nearest-neighbour (kNN) search added (commit 8436489c)
  • support for searching on "not equals" added (commit ad17ff95)
  • converted to an extension (commit 629b3af2)
• cube
  • converted to an extension (commit 629b3af2)
• earthdistance
  • converted to an extension (commit 629b3af2)
• fuzzystrmatch
  • levenshtein_less_equal() function added (commit 604ab081)
  • support for multi-byte characters added to levenshtein() function added (commit 57d9aefc)
  • converted to an extension (commit 629b3af2)
• intarray
  • converted to an extension (commit 629b3af2)
• lo
  • converted to an extension (commit 629b3af2)
• pageinspect
  • converted to an extension (commit 629b3af2)
• pg_freespacemap
  • converted to an extension (commit 629b3af2)
• pgrowlocks
  • converted to an extension (commit 629b3af2)
• pgstattuple
  • converted to an extension (commit 629b3af2)
• pg_trgm
  • support for LIKE/ILIKE searches added (commit 6e2f3ae8)
  • converted to an extension (commit 629b3af2)
• seg
  • converted to an extension (commit 629b3af2)
• sslinfo
  • functions ssl_cipher() and ssl_version() added (commit 6d16902e)
• tablefunc
  • converted to an extension (commit 629b3af2)
• uuid-ossp
  • converted to an extension (commit 629b3af2)

Testing

General

• isolation tester added (commit dafaa3ef)

pg_regress

• option --multibyte renamed to --encoding (commit f536d419)

Pre-release test versions

Initial commit marking the start of PostgreSQL 9.1 development is b40466c3 (2010-07-09).