PostgreSQL 8.4

• Release notes
• Released: 2009-07-01 (announcement)
• Final minor version: 8.4.22
• EOL: 2014-07
• Documentation: PostgreSQL 8.4

Major features

• Windowing functions (initial commit 95b07bc7)
• column-level permissions
• pg_restore: parallel database restore (commit 775f1b37)
• per-database collation
• common table expressions (commit 44d5be0e)
• recursive queries
• initial support for foreign data wrappers via the SQL/MED standard (commit cae565e5)

Data type changes

• boolean
  • on and off accepted as input values (commit 05a7db05)
• UUID
  • input accepts an optional hyphen after every fourth digit (commit 84aa7972)

SQL changes

General

• support for default values in function arguments added (commit 455dffbb)
• support for VARIADIC functions added (commit d89737d3)

Added:

• ALTER FOREIGN DATA WRAPPER (commit cae565e5)
• ALTER SERVER (commit cae565e5)
• ALTER USER MAPPING (commit cae565e5)
• CREATE FOREIGN DATA WRAPPER (commit cae565e5)
• CREATE SERVER (commit cae565e5)
• CREATE USER MAPPING (commit cae565e5)
• DROP FOREIGN DATA WRAPPER (commit cae565e5)
• DROP SERVER (commit cae565e5)
• DROP USER MAPPING (commit cae565e5)
• TABLE (commit b09a1a29)

Modified:

• ALTER DATABASE
  • ALTER DATABASE ... SET TABLESPACE ... syntax added (commit 6517f377)
• ALTER SEQUENCE
  • ALTER SEQUENCE ... START [ WITH ] ... syntax added (commit caede71b)
  • ALTER SEQUENCE ... RESTART [ WITH ] ... syntax added (commit 10a3471b)
• ALTER TABLE
  • ALTER TABLE ... ALTER COLUMN ... SET DATA TYPE syntax added (commit 1471e384)
• ALTER TYPE
  • ALTER TYPE ... RENAME ... syntax added (commit 5507b22d)
• ALTER VIEW
  • following syntax added (commit a0b012a1):
    • ALTER VIEW ... ALTER COLUMN ... [ SET | DROP ] DEFAULT
    • ALTER VIEW ... OWNER TO ...
    • ALTER VIEW ... SET SCHEMA ...
• CLUSTER
  • option VERBOSE added (commit a3785555)
• CREATE CAST
  • CREATE CAST ... WITH INOUT ... syntax added (commit 092bc496)
• CREATE FUNCTION
  • RETURNS TABLE syntax added (commit 69a785b8)
  • WINDOW attribute added (commit 26ce4e85)
• CREATE TABLE AS
  • WITH [ NO ] DATA  syntax added (commit 8ecd5351)
• CREATE TYPE
  • option LIKE added (commit 3f936aac)
  • options CATEGORY and PREFERRED added (commit bac3e836)
• EXPLAIN
  • VERBOSE option now prints the target list of each plan node, rather than dumping the internal representation (commit 87a2f050)
• GRANT
  • TRUNCATE privilege for tables added (commit a0b76dc6)
• SELECT
  •  FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY syntax added (commit 361bfc35)
• TRUNCATE
  • TRUNCATE is now recursive by default (commit ca8100f9)
  • ONLY option added (commit ca8100f9)
  • RESTART IDENTITY/CONTINUE IDENTITY options added (commit 10a3471b)
• VALUES
  • OFFSET start [ ROW | ROWS ] / FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY SQL:2008 syntax added (commit 361bfc35)

Backend changes

Functions

• functions can now take default arguments (commit 455dffbb)

Hooks

Following hooks added:

• ExecutorEnd_hook (commit cd35e9d7)
• ExecutorRun_hook (commit 6cc88f0a)
• ExecutorStart_hook (commit cd35e9d7)
• get_attavgwidth_hook (commit 7b7df9f0)
• get_index_stats_hook (commit 7b7df9f0)
• get_relation_stats_hook (commit 7b7df9f0)
• shmem_startup_hook (commit dad75a62)

Source configuration

• option --disable-float4-byval added (commit 8472bf7a)
• option --disable-float8-byval added (commit 8472bf7a)
• option --with-blocksize added (commit 3c6248a8)
• option --with-segsize added (commit 3c6248a8
• option --with-wal-blocksize added (commit cf9f6c8d)
• option --with-wal-segsize added (commit cf9f6c8d

Triggers

• support for statement-level ON TRUNCATE triggers added (commit 7692d8d5)

System catalogue changes

Tables

Added:

• pg_foreign_data_wrapper (commit cae565e5)
• pg_foreign_server (commit cae565e5)
• pg_user_mapping (commit cae565e5)

Modified:

• pg_am
  • column amcanbackward added (commit e4fb8ff0)
  • column amkeytype added (commit 4adc2f72)
• pg_amop
  • column amopreqcheck removed (commit 9b5c8d45)
• pg_attribute
  • column attacl added (commit 3cb5d658)
• pg_cast
  • column castmethod added (commit 092bc496)
• pg_class
  • column relistemp added (commit df13324f)
  • column reltriggers replaced with boolean relhastriggers (commit e4718f2c)
  • columns relukeys, relfkeys, and relrefs removed (commit e4718f2c)
• pg_constraint
  • column conislocal added (commit cd902b33)
  • column coninhcount added (commit cd902b33)
• pg_database
  • column datcollate added (commit 61d96749)
  • column dattype added (commit 61d96749)
• pg_proc
  • column procost added (commit 5a7471c3)
  • column prorows added (commit 5a7471c3)
  • column provariadic added (commit 6563e9e2)
• pg_shdepend
  • column objsubid added (commit 3cb5d658)
• pg_type
  • columns typcategory and typispreferred added (commit bac3e836)

Removed:

• pg_autovacuum (commit 834a6da4)

Views

Added:

• pg_stat_user_functions (commit 93c701ed)
• pg_user_mappings (commit cae565e5)

Modified:

• pg_settings - following columns added:
  • enumvals (commit 52a8d4f8)
  • boot_val (commit 3bea93b3)
  • reset_val (commit 3bea93b3)
  • sourcefile (commit 3b9ec468)
  • sourceline (commit 3b9ec468)

System functions

Added:

• array_agg() (commit 3379fae6)
• array_fill() (commit 2c773296)
• array_length() (commit f98f6ee0)
• array_ndims() (commit 254aecb7)
• cardinality() (commit f98f6ee0)
• cume_dist() (commit 95b07bc7)
• current_catalog (commit 0fec77ae)
• current_query() (commit f96928fd)
• current_schema (commit 0fec77ae)
• dense_rank() (commit 95b07bc7)
• first_value() (commit 95b07bc7)
• generate_subscripts() (commit 1fcb977a)
• has_any_column_privilege() (commit 7449427a)
• has_column_privilege() (commit 7449427a)
• has_foreign_data_wrapper_privilege() (commit cae565e5)
• has_server_privilege() (commit cae565e5)
• lag() (commit 95b07bc7)
• last_value() (commit 95b07bc7)
• lead() (commit 95b07bc7)
• nth_value() (commit 95b07bc7)
• ntile() (commit 95b07bc7)
• percent_rank() (commit 95b07bc7)
• pg_conf_load_time() (commit 600da67f)
• pg_get_function_arguments() (commit 69a785b8)
• pg_get_function_identity_arguments() (commit 455dffbb)
• pg_get_function_result() (commit 69a785b8)
• pg_get_functiondef() (commit 2c863ca8)
• pg_get_keywords() (commit c63147d6)
• pg_options_to_table() (commit cae565e5)
• pg_stat_get_activity() (commit 0423de4d)
• pg_typeof() (commit b8fab241)
• quote_nullable() (commit 7de81124)
• rank() (commit 95b07bc7)
• row_number() (commit 95b07bc7)
• suppress_redundant_updates_trigger trigger function (commit f0dae704)
• unnest() (initial commit c889ebce)

Modified:

• generate_series()
  • variant which returns series of TIMESTAMP/TIMESTAMPTZ added (commit b6d15590)
• pg_get_expr()
  • 0 (InvalidOid) accepted as value for the relation parameter (commit 48938ab5)

Configuration changes

Added:

• cursor_tuple_fraction (commit db147b34)
• effective_io_concurrency (commit b7b8f0b6)
• IntervalStyle (commit df7641e2)
• segment_size (commit 7a97abe8)
• stats_temp_directory (commit 5b8eb2b4)
• track_activity_query_size (commit 995fb742)
• track_functions (commit 93c701ed)
• wal_block_size (commit 7a97abe8)
• wal_segment_size (commit 7a97abe8)

Modified:

• debug_pretty_print
  • default value changed from off to on (commit 9650830b)
• debug_print_parse
  • output appears at LOG message level (previously: DEBUG1) (commit 9650830b)
• debug_print_rewritten
  • output appears at LOG message level (previously: DEBUG1) (commit 9650830b)
• debug_print_plan
  • output appears at LOG message level (previously: DEBUG1) (commit 9650830b)
• default_statistics_target
  • default value increased from 10 to 100; maximum value increased from 1000 to 10000 (commit 65e3ea76)
• log_min_messages
  • default value changed from NOTICE to WARNING (commit 2abf130a)
• log_temp_files
  • settable by superusers only (commit 8ddb739e)
• max_prepared_transactions
  • default value changed from 5 to 0 (commit 8d4f2ecd)

Removed:

• explain_pretty_print (commit 87a2f050)
• krb_realm (commit 1b4e729e)
• krb_server_hostname (commit 1b4e729e)

Indexing changes

GIN indexes

• support for partial-match searches added (commit e6dbcb72)
• index operator lossiness determination pushed down to GIN opclass functions (commit 9b5c8d45)
• search tree depth limit for GIN bulk-insert operations added (commit e5efda44)
• "fastupdate" support added (commit ff301d6e)
• multi-column support added (commit 27cb66fd)

Logging changes

• epoch string no longer appended to log_filename if no format specifiers present (commit 7380b638)

Replication changes

Configuration

Added:

• recovery.conf parameter recovery_end_command (commit 9e403c25)

Removed:

• log_restartpoints (commit cdd46c76)

Full text search changes

• when doing GIN weighted lookups on full text indexes, the operator @@ can be used in place of @@@ (commit 9b5c8d45)
• selectivity estimation function for the text search @@ operator added (commit 4e57668d)
• support for the Nepali language / Devanagari alphabet improved (commit e43bb5be)

Authentication changes

pg_hba.conf

• pg_hba.conf now parsed in postmaster (commit 98723810)
• option clientcert added (commit 3c486fbd)
• some Kerberos options can be set in pg_hba.conf, overriding postgresql.conf settings (commit 32c469d7)
• former configuration parameters krb_realm and krb_server_hostname now pg_hba.conf options (commit 1b4e729e)

libpq changes

• sslmode connection parameter values verify-ca and verify-full added (commit e883d0b5)
• support for wildcard server certificates added (initial commit cb10467d)
• file locations for client certificates can now be specified (commit 5f3724dd)
• OpenSSL callbacks will now be unregistered when no database connections remain open (commit 4e816286)
• PQinitOpenSSL() function added (commit 97503a52)
• lo_import_with_oid() function added (commit 8436f9a0)

ECPG changes

• localization support added (initial commit e1bdd07c)
• ECPG parser is now automatically generated from the server parser (commit 4607c5ca)

Procedural language changes

PL/pgSQL

• RETURN QUERY EXECUTE syntax added (commit 47391591)
• RETURN QUERY [ EXECUTE ] sets FOUND and GET DIAGNOSTICS ROW_COUNT variables (commit 8c78f8e6)

psql changes

• \ef command added (commit 2c863ca8)

Server utilities

Modified:

• pg_config
  • --htmldir option added (commit 84ce707b)
• pg_ctl
  • restart option preserves command-line arguments (commit 067f1e5f)

Core utilities

Modified:

• clusterdb
  • option -v/--verbose added (commit a3785555)
  • option -w/--no-password added (commit 9de59fd1)
• createdb
  • option -w/--no-password added (commit 9de59fd1)
• createuser
  • option -w/--no-password added (commit 9de59fd1)
• dropdb
  • option -w/--no-password added (commit 9de59fd1)
• dropuser
  • option -w/--no-password added (commit 9de59fd1)
• pg_restore
  • parallel database restore added (commit 775f1b37)
  • option -w/--no-password added (commit 9de59fd1)
  • option --role added (commit b0a6ad70)
  • option --no-tablespaces added (commit 68528d37)
• reindexdb
  • option -w/--no-password added (commit 9de59fd1)
• vacuumdb
  • option --freeze added (commit 36a9cf38)
  • option -w/--no-password added (commit 9de59fd1)

Contrib modules

Added:

• auto_explain (commit e125e28e)
• btree_gin (commit 9151e1bb)
• citext (commit ab9907f5)
• pg_stat_statements (commit 7466eeac)

Modified:

• dblink
  • initial support for SQL/MED (commit 4334695b)
• fuzzystrmatch
  • levenshtein() function expanded to include optional cost values (commit 55f6e5f6)
• intagg
  • marked as deprecated (commit 32cc9e55)
• ltree
  • support for multibyte encoding added (commit 8eee65c9)
• pageinspect
  • function fsm_page_contents() added (commit 15c121b3)
  • function get_raw_page() expanded with variant get_raw_page(regclass, int4, int4) (commit 15c121b3)
• pgbench
  • -M option added for specifying the query protocol type to use (commit 49639a7b)
  • -T option added for specifying duration in seconds (commit cff4aa6a)
  • pgbench_ prefix added to the standard tables accounts, branches, history, and tellers (commit 48caf91b)
• pg_freespacemap
  • rewritten to match the new FSM implementation (commit 15c121b3)
• pg_standby
  • "Smart Failover" mode added, to consume all available WAL before bringing up the server (commit 9e403c25)
  • -l option removed (commit 8fd733bd)
• pgstattuple
  • pgstatindex() and pg_relpages() adapted to handle tables and indexes with over 2 billion pages (commit d287818e)
• pg_trgm
  • support for multibyte encoding added (commit b87b52bf)
• vacuumlo
  • option -w/--no-password added (commit 9de59fd1)

Testing

• pg_regress can be executed outside of the build tree (commit feae7856)