PostgreSQL 8.2

• Release notes
• Released: 2006-12-05 (announcement)
• Final minor version: 8.2.23
• EOL: 2011-12
• Documentation: PostgreSQL 8.2

Major features

• GIN indexes
• returning clause for INSERT/UPDATE/DELETE operations
• multirow VALUES lists
• optional target-table alias in UPDATE/DELETE
• concurrent index creation (commit e093dcdd)
• storage parameters introduced
• advisory locking

Data type changes

General

• support for NULL elements in arrays added (initial commit cecb6075)

SQL changes

Added:

• DROP OWNED (commit cec3b0a9)
• REASSIGN OWNED (commit cec3b0a9)
• VALUES (commit 9caafda5)

Modified:

• ALTER AGGREGATE
  • syntax added supporting aggregate functions with multiple input arguments (commit 108fe473)
• ALTER INDEX
  • ALTER INDEX ... { SET | RESET } syntax added (commit 277807bd)
• ALTER SEQUENCE
  • ALTER SEQUENCE ... OWNED BY ... syntax added (commit 2b2a5072)
• ALTER TABLE
  • ALTER TABLE ... [ NO ] INHERIT syntax added (commit 8c092781)
• ANALYZE
  • takes a ShareUpdateExclusiveLock (previously: AccessShareLock) on the table being analyzed (commit da7540b9
• COMMENT ON
  • COMMENT ON ROLE added (commit f9a726aa)
  • COMMENT ON TABLESPACE added (commit f9a726aa)
• COPY
  • COPY (SELECT ...) TO ... syntax added (commit 85188ab8)
• CREATE AGGREGATE
  • new syntax CREATE AGGREGATE name (input_data_type [, ...]) (parameter_list) added (commit 3651a3e6)
• CREATE INDEX
  • CREATE INDEX ... WITH (fillfactor = ...) syntax added (commit 277807bd)
  • CREATE INDEX CONCURRENTLY ... syntax added (commit e093dcdd)
• CREATE SEQUENCE
  • CREATE SEQUENCE ... OWNED BY ... syntax added (commit 2b2a5072)
  • CREATE TABLE ... (WITH fillfactor = ...) syntax added (commit 277807bd)
• CREATE TABLE AS
  • CREATE TABLE AS ... (WITH fillfactor = ...) syntax added (commit 277807bd)
  • ON COMMIT clause added (commit 85c0eac1)
  • TABLESPACE option added (commit 85c0eac1)
• CREATE TYPE
  • stand-alone CREATE TYPE name syntax added for creating shell types (commit 8e68d783)
• DELETE
  • support for RETURNING clause added (commit 7a3e30e6)
• DROP AGGREGATE
  • DROP AGGREGATE IF EXISTS ... syntax added (commit bbcd0169)
  • support for multiple input arguments added (commit 108fe473)
• DROP CAST
  • DROP CAST IF EXISTS ... syntax added (commit bbcd0169)
• DROP CONVERSION
  • DROP CONVERSION IF EXISTS ... syntax added (commit daea4d8e)
• DROP DATABASE
  • DROP DATABASE IF EXISTS ... syntax added (commit 5b352d8e)
• DROP DOMAIN
  • DROP DOMAIN IF EXISTS ... syntax added (commit daea4d8e)
• DROP FUNCTION
  • DROP FUNCTION IF EXISTS ... syntax added (commit bbcd0169)
• DROP GROUP
  • DROP GROUP IF EXISTS ... syntax added (commit f8b54fe6)
• DROP INDEX
  • DROP INDEX IF EXISTS ... syntax added (commit daea4d8e)
• DROP LANGUAGE
  • DROP LANGUAGE IF EXISTS ... syntax added (commit bbcd0169)
• DROP OPERATOR
  • DROP OPERATOR IF EXISTS ... syntax added (commit bbcd0169)
• DROP OPERATOR CLASS
  • DROP OPERATOR CLASS IF EXISTS ... syntax added (commit bbcd0169)
• DROP ROLE
  • DROP ROLE IF EXISTS ... syntax added (commit f8b54fe6)
• DROP RULE
  • DROP RULE IF EXISTS ... syntax added (commit bbcd0169)
• DROP SCHEMA
  • DROP SCHEMA IF EXISTS ... syntax added (commit daea4d8e)
• DROP SEQUENCE
  • DROP SEQUENCE IF EXISTS ... syntax added (commit daea4d8e)
• DROP TABLE
  • DROP TABLE IF EXISTS ... syntax added (commit daea4d8e)
• DROP TABLESPACE
  • DROP TABLESPACE IF EXISTS ... syntax added (commit bbcd0169)
• DROP TRIGGER
  • DROP TRIGGER IF EXISTS ... syntax added (commit bbcd0169)
• DROP TYPE
  • DROP TYPE IF EXISTS ... syntax added (commit daea4d8e)
• DROP USER
  • DROP USER IF EXISTS ... syntax added (commit f8b54fe6)
• DROP VIEW
  • DROP VIEW IF EXISTS ... syntax added (commit daea4d8e)
• GRANT
  • CONNECT option added to GRANT ... ON DATABASE (initial commit 4899aaf2)
  • GRANT ... ON SEQUENCE syntax added (commit 4789e988)
  • GRANT ... ON RULE syntax deprecated (commit 7bae5a28)
• INSERT
  • support for multi-row VALUES clauses added (commit 9caafda5)
  • support for RETURNING clause added (commit 7a3e30e6)
• REVOKE
  • REVOKE ... ON RULE syntax deprecated (commit 7bae5a28)
• TRUNCATE
  • CASCADE/RESTRICT options added (commit 984a6ced)
• UPDATE
  • UPDATE ... SET ROW (col, ...) = (val, ...) syntax added (commit 6e8596a1)
  • support for RETURNING clause added (commit 7a3e30e6)

System catalogue changes

Tables

Added:

• pg_shdescription (commit f9a726aa).

Modified:

• pg_am
  • column amstorage added (commit e5734597)
  • column amclusterable added (commit e5734597)
  • column amconcurrent removed (commit 09d3670d)
• pg_database
  • column datvacuumxid removed (commit 48188e16)
• pg_index
  • column indisvalid added (commit e093dcdd)

Views

Added:

• pg_cursors (commit a5c43b88)
• pg_prepared_statements (commit 44b928e8)
• pg_timezone_abbrevs (commit d8b5c95c)
• pg_timezone_names (commit d8b5c95c)

Modified:

• pg_stat_activity
  • column waiting added (commit 9bf760f7)
• pg_settings
  • column unit added (commit b517e653)

System functions

Added:

• clock_timestamp() (commit e6004f01)
• justify_interval() (commit d69b1632)
• pg_advisory_lock() (commit 9b4cda0d)
• pg_advisory_lock_shared (commit 9b4cda0d)
• pg_advisory_unlock() (commit 9b4cda0d)
• pg_advisory_unlock_all() (commit 9b4cda0d)
• pg_advisory_unlock_shared() (commit 9b4cda0d)
• pg_current_xlog_insert_location() (commit e8ea9e95)
• pg_current_xlog_location() (commit 704ddaaa)
• pg_is_other_temp_schema() (commit 65ab9f4f)
• pg_my_temp_schema() (commit 65ab9f4f)
• pg_sleep() (commit 782eefc5)
• pg_switch_xlog() (commit 704ddaaa)
• pg_try_advisory_lock() (commit 9b4cda0d)
• pg_try_advisory_lock_shared() (commit 9b4cda0d)
• pg_xlogfile_name() (commit 704ddaaa)
• pg_xlogfile_name_offset() (commit 704ddaaa)
• shobj_description() (commit f9a726aa)
• statement_timestamp() (commit e6004f01)
• stddev_pop() (commit 0ebf1cc8)
• stddev_samp() (commit 0ebf1cc8)
• transaction_timestamp() (commit e6004f01)
• var_pop() (commit 0ebf1cc8)
• var_samp() (commit 0ebf1cc8)

Configuration changes

Added:

• allow_system_table_mods (commit 86c23a6e)
• archive_timeout (commit e8ea9e95)
• array_nulls (commit cecb6075)
• autovacuum_freeze_max_age (commit cecb6075)
• cluster_name (commit 370a709c)
• gin_fuzzy_search_limit (commit 8a3631f8)
• ignore_system_indexes (commit 86c23a6e)
• include directive (commit 5b8ac710)
• local_preload_libraries (commit abc3120e)
• post_auth_delay (commit 86c23a6e)
• seq_page_cost (commit eed6c9ed)
• timezone_abbreviations (commit d8b5c95c)
• update_process_title (commit 370a709c)

Modified:

• escape_string_warning
  • default changed to on (commit ce1106d2)
• preload_libraries
  • renamed to shared_preload_libraries (commit abc3120e)
• superuser_reserved_connections
  • default changed from 2 to 3 (commit 1a849526)

Removed:

• australian_timezones (commit d8b5c95c)

Indexing changes

• rtree index method removed in favour of GiST (commit 2a8d3d83)

Replication changes

• restartpoints introduced

Procedural languages

PL/pgSQL

Following hooks added:

• func_beg (commit 355865c5)
• func_end (commit 355865c5)
• func_setup (commit 355865c5)
• stmt_beg (commit 355865c5)
• stmt_end (commit 355865c5)

Information schema changes

• SQL:2003 support (commit f7ae9004)

Server utilities

• initdb
  • detects the operating system locale and set the appropriate default DateStyle value (commit 625d4b38)

Core utilities

Modified:

• pg_dump
  • option -t/--tables now pattern-matches in the same way as psql's \d command (commit 24e97528)
• pg_restore
  • option --no-data-for-failed-tables added (commit c61607bd)
  • option -1/--single-transaction added (commit 3957cf6e)

Contrib modules

General:

• uninstall scripts added for all contrib modules with install scripts (commit fe83b3eb)

Added:

• adminpack (commit fe59e566)
• hstore (commit 642194ba)
• isn (replacing isbn_issn; commit dff84dc7)
• pg_freespacemap (commit d5dd3d45)
• pgrowlocks (commit 66126f96)
• sslinfo (commit 6dc920de)

Modified:

• dblink
  • async query capability added (commit 52a3ed9f)
• pgcrypto
  • gen_random_bytes() function added (commit 1abf76e8)
• pgstattuple
  • following functions added (commit 74924d29):
    • pgstatindex()
    • pg_relpages()
    • bt_metap()
    • bt_page_items()
    • bt_page_stats()

Removed:

• isbn_issn (replaced by isn; commit dff84dc7)
• ora2pg (commit e6917b12)
• userlock (commit af7d257e)

Testing

• pg_regress converted from a shell script to C (commit a38c85bd)