PostgreSQL 9.0

• Release notes
• Released: 2010-09-20 (announcement)
• Final minor version: 9.0.23
• EOL: 2015-10
• Documentation: PostgreSQL 9.0 (index)

Major features

• streaming replication (initial commit 40f908bd)
• pg_upgrade
• DO statement
• deferrable unique constraints (commit 25d9bf2e)

SQL changes

Added:

• ALTER DEFAULT PRIVILEGES (commit 249724cb)
• ALTER LARGE OBJECT (commit f1325ce2)
• DO (commit 9048b731)

Modified:

• ALTER TABLESPACE
  • ALTER TABLESPACE ... { SET | RESET } ( tablespace_options ) syntax added (commit d86d51a9)
• CREATE DATABASE
  • LC_COLLATE option added (initial commit 61d96749)
  • LC_CTYPE option added (initial commit 61d96749)
• CREATE LANGUAGE
  • CREATE OR REPLACE LANGUAGE ... syntax added (commit 11b58470)
  • INLINE option added (commit 9048b731)
  • deprecated LANCOMPILER option removed (commit 9048b731)
• CREATE TABLE
  • CREATE TABLE ... (LIKE ... INCLUDING ALL) syntax added (commit faa1afc6)
  • CREATE TABLE ... (LIKE ... INCLUDING COMMENTS) syntax added (commit faa1afc6)
  • CREATE TABLE ... (LIKE ... INCLUDING STORAGE) syntax added (commit faa1afc6)
  • CREATE TABLE ... OF TYPE composite_type syntax added (commit e7b3349a)
  • UNIQUE, PRIMARY KEY and EXCLUDE constraints can be set [ NOT ] DEFERRABLE (commit 25d9bf2e)
• CREATE TABLE AS
  • row count now returned to client in the command tag (commit aa7e7ae9)
• CREATE TRIGGER
  • WHEN clause added (commit 7fc0f062)
  • individual columns can be specified for update triggers with the UPDATE OF syntax (commit b2734a0d)
• EXPLAIN
  • EXPLAIN (option [, ...]) syntax added (commit d4382c4a)
  • BUFFERS option added (commit cddca5ec)
  • COSTS option added (commit d4382c4a)
  • FORMAT option added with following output formats:
    
    • TEXT (default)
    • JSON (commit 9bd27b7c)
    • YAML (commit 324385d6)
    • XML (commit 9bd27b7c)
• FETCH
  • FROM and IN keywords made optional (commit 2ea179f3)
• MOVE
  • FROM and IN keywords made optional (commit 2ea179f3)
• SELECT
  • mixing of traditional and SQL-standard OFFSET / LIMIT syntax permitted (commit be4cd18f) 
  • expanded support for frame options (commit ec4be2ee
    • frames can start from CURRENT_ROW (in either RANGE or ROWS mode)
    • ROWS n PRECEDING and ROWS n FOLLOWING start and end points supported
  • SELECT ... FOR UPDATE / SHARE processing performed after applying LIMIT (commit 46e3a16b)
• SELECT INTO
  • row count now returned to client in the command tag (commit aa7e7ae9)
• VACUUM
  • VACUUM FULL now performs a full table rewrite (initial commit 0a469c87
  • parenthesized option syntax added (commit 5e66a51c)

Backend changes

General

• relation mapping infrastructure added (commit b9b8831a)

Data directory

• pg_filenode.map added (commit b9b8831a)

Vacuuming

• a full vacuum ("VACUUM FULL") now performs a full table rewrite (initial commit 0a469c87)

Hooks

Following hooks added:

• check_password_hook (commit c742b795)
• ProcessUtility_hook (commit a5495cd8)

Internals

• genbki.sh and Gen_fmgrtab.sh entirely replaced by genbki.pl and Gen_fmgrtab.pl (commit 64737e93)

System catalogue changes

Tables

Added:

• pg_db_role_setting (commit 2eda8dfb)
• pg_default_acl (commit 249724cb)
• pg_largeobject_metadata(commit f1325ce2)

Modified:

• pg_attribute
  • column attoptions added (commit 76a47c0e)
• pg_authid
  • column rolconfig removed (commit 2eda8dfb)
  • pg_sync_pg_authid trigger removed (commit a8bb8eb5)
• pg_auth_members
  • pg_sync_pg_authmembers trigger removed (commit a8bb8eb5)
• pg_constraint
  • column conindid added (commit c1b9ec24)
• pg_database
  • column datconfig removed (commit 2eda8dfb)
  • pg_sync_pg_database trigger removed (commit a8bb8eb5)
• pg_index
  • column indimmediate added (commit 25d9bf2e)
• pg_language
  • column laninline added (commit 9048b731)
• pg_pltemplate
  • column tmplinline added (commit 9048b731)
• pg_statistic
  • column stainherit added (commit 649b5ec7)
• pg_trigger
  • column tgconstrname removed (commit 9a915e59; note commit mistakenly refers to pgconstrname)

Removed:

• pg_listener (commit d1e02722)

Views

Modified:

• pg_stat_activity
  • column application_name added (commit 8217cfbd)
• pg_stats
  • column inherited added (commit 649b5ec7)

System functions

Added:

• aclexplode() (commit 36f887c4)
• has_sequence_privilege() (commit be6bca23)
• pg_indexes_size() (commit 4f15699d)
• pg_is_in_recovery() (commit efc16ea5)
• pg_listening_channels() (commit d1e02722)
• pg_notify() (commit d1e02722)
• pg_options_to_table() (commit cae565e5)
• pg_relation_filenode() (commit b9b8831a)
• pg_relation_filepath() (commit b9b8831a)
• pg_stat_reset_single_function_counters() (commit 083e1b0f)
• pg_stat_reset_single_table_counters() (commit 083e1b0f)
• pg_stat_reset_shared() (commit 7e40cdc0)
• pg_table_size() (commit 4f15699d)
• string_agg() (commit 9ea9918e)

Configuration changes

Added:

• application_name (initial commit 8217cfbd)
• bonjour (commit eeb6cb14)
• bytea_output (commit a2a8c7a6)
• enable_material (commit 5b89ef38)
• hot_standby (commit efc16ea5)
• lo_compat_privileges (commit f1325ce2)
• max_standby_archive_delay (commit efc16ea5)
• max_standby_streaming_delay (commit efc16ea5)
• max_wal_senders (commit 40f908bd)
• ssl_renegotiation_limit (commit 413d34be)
• trace_recovery_messages (commit efc16ea5)
• vacuum_defer_cleanup_age (commit efc16ea5)
• wal_level (commit 9b8a7332)
• wal_sender_delay (commit 40f908bd)

Modified:

• log_line_prefix
  • %e parameter added to show the current SQL state (commit f39df967)

Removed:

• add_missing_from (commit 289e2905)

Data type changes

Modified:

• bytea
  • hex-string input and output added (commit a2a8c7a6)

Indexing changes

GIN

• use red-black binary trees for index creation (commit 5209c084)

libpq changes

• application_name and fallback_application_name connection parameters added (initial commit 3dfcf8cc)

Replication changes

Configuration

Added:

• archive_cleanup_command (initial commit de66effe)
• primary_conninfo (commit 40f908bd)
• standby_mode (commit 40f908bd)
• trigger_file (commit 40f908bd)

Full text search changes

• prefix support for synonym dictionaries added (commit abd8c94f)
• filtering dictionary support added (commit a88a4801)
• underscores in email addresses supported (commit 89b0095e)
• URL parsing improved to be more standards-compliant (commit 2c265ade)

Procedural language changes

PL/pgSQL

• installed by default (commit 96c102fe)

PL/Perl

• support for DO blocks added (commit 42b2907d)
• various following utility functions added (commit 05672e50)

PL/Python

• support for DO blocks added (commit adb77640)
• support for Python 3 added (commit dd4cd55c)

Information schema changes

• function aclexplode() added to speed up information schema views (commit 36f887c4)

Server utilities

Modified:

• initdb
  • can be executed by pg_ctl (commit a37b001b)
• pg_config
  • --ldflags_ex option added (commit 291a9577)
• pg_ctl
  • init/initdb option added (commit a37b001b)
  • can be used safely to start the postmaster during a system reboot (commit 8f5500e6)

Core utilities

Added:

• vacuumdb
  • option --analyze-only added (commit 3ccb97b2)

Contrib modules

Added:

• passwordcheck (commit c742b795)
• pg_archivecleanup (commit 25fee5cf)
• pg_upgrade (commit c2e9b2f2)
• pg_upgrade_support (commit 9885206c)
• unaccent (commit 92e05bc6)

Modified:

• auto_explain
  • text of query added to output (commit fc5173ad)
• start-scripts
  • Linux: use /proc/self/oom_adj to disable the Linux out-of-memory (OOM) killer (commit d5e00298)

Pre-release test versions

Initial commit marking the start of PostgreSQL 9.0 development is 50a90fac (2010-02-17).