PostgreSQL 8.3

• Release notes
• Released: 2008-02-04 (announcement)
• Final minor version: 8.3.23
• EOL: 2013-02
• Documentation: PostgreSQL 8.3

Major features

• native full text search functionality (commit 140d4ebc)
• HOT updates (commit 282d2a03)
• ENUM data type
• UUID data type
• XML data type support

Backend changes

• "distributed" checkpoints added (commit 867e2c91)

Source configuration

• option --with-ossp-uuid added (commit 74496bc2)

Hooks

Following hooks added:

• ExplainOneQuery_hook (commit 604ffd28)
• explain_get_index_name_hook (commit 604ffd28)
• get_relation_info_hook (commit 604ffd28)
• join_search_hook (commit cdf0231c)
• planner_hook (commit 604ffd28)

Performance

• concurrent large sequential scans can now share disk reads (commit a04a4235)

SPI

• following functions added (commit 66888f74):
  • SPI_prepare_cursor()
  • SPI_scroll_cursor_fetch()
  • SPI_scroll_cursor_move()

SQL changes

Added:

• ALTER OPERATOR FAMILY (commit a33cf104)
• ALTER TEXT SEARCH CONFIGURATION (initial commit 140d4ebc)
• ALTER TEXT SEARCH DICTIONARY (initial commit 140d4ebc)
• ALTER TEXT SEARCH PARSER (initial commit 140d4ebc)
• ALTER TEXT SEARCH TEMPLATE (initial commit 140d4ebc)
• ALTER VIEW (commit a5589813)
• CREATE OPERATOR FAMILY (commit a33cf104)
• CREATE TEXT SEARCH CONFIGURATION (initial commit 140d4ebc)
• CREATE TEXT SEARCH DICTIONARY (initial commit 140d4ebc)
• CREATE TEXT SEARCH PARSER (initial commit 140d4ebc)
• CREATE TEXT SEARCH TEMPLATE (initial commit 140d4ebc)
• DISCARD (initial commit d13e903b)
• DROP OPERATOR FAMILY (commit a33cf104)
• DROP TEXT SEARCH CONFIGURATION (initial commit 140d4ebc)
• DROP TEXT SEARCH DICTIONARY (initial commit 140d4ebc)
• DROP TEXT SEARCH PARSER (initial commit 140d4ebc)
• DROP TEXT SEARCH TEMPLATE (initial commit 140d4ebc)

Modified:

• ALTER DATABASE
  • ALTER DATABASE ... { SET ... FROM CURRENT | RESET ALL } syntax added (commit e7889b83)
• ALTER FUNCTION
  • options COST and ROWS added (commit 5a7471c3)
  • ALTER FUNCTION ... [ SET | RESET ] syntax added (commit 2abae34a)
  • ALTER FUNCTION ... SET ... FROM CURRENT  syntax added (commit e7889b83)
• ALTER LANGUAGE
  • ALTER LANGUAGE ... OWNER TO ... syntax added (commit 55a7cf80)
  • ALTER [ PROCEDURAL ] LANGUAGE ... syntax added (commit 55a7cf80)
• ALTER ROLE
  • ALTER ROLE ... { SET ... FROM CURRENT | RESET ALL } syntax added (commit e7889b83)
• ALTER SEQUENCE
  • ALTER SEQUENCE ... RENAME TO ... syntax added (commit a5589813)
• ALTER USER
  • ALTER USER ... { SET ... FROM CURRENT | RESET ALL } syntax added (commit e7889b83)
• CLOSE
  • CLOSE ALL syntax added (commit d13e903b)
• COMMENT ON
  • COMMENT ON OPERATOR FAMILY added (commit a33cf104)
  • COMMENT ON TEXT SEARCH CONFIGURATION added (commit 140d4ebc)
  • COMMENT ON TEXT SEARCH DICTIONARY added (commit 140d4ebc)
  • COMMENT ON TEXT SEARCH PARSER added (commit 140d4ebc)
  • COMMENT ON TEXT SEARCH TEMPLATE added (commit 140d4ebc)
• CREATE FUNCTION
  • options COST and ROWS added (commit 5a7471c3)
  • CREATE FUNCTION ... SET syntax added (commit 2abae34a)
• CREATE INDEX
  • options ASC / DESC / NULLS FIRST / NULLS LAST (for btree indexes) added (commit 44317582)
• CREATE OPERATOR CLASS
  • CREATE OPERATOR CLASS ... FAMILY ... syntax added (commit a33cf104)
• CREATE TYPE
  • CREATE TYPE ... AS ENUM ... syntax added (commit 57690c68)
  • options TYPMOD_IN and TYPMOD_OUT added (commit 5725b9d9)
• DEALLOCATE
  • DEALLOCATE ALL syntax added (commit d13e903b)
• DECLARE
  • FOR { READ ONLY | UPDATE [ OF ... ] } syntax removed (commit 6808f1b1)
• DELETE
  • WHERE CURRENT OF cursor_name syntax added (commit 6808f1b1)
• UPDATE
  • WHERE CURRENT OF cursor_name syntax added (commit 6808f1b1)

System catalogue changes

Tables

Added:

• pg_enum  (commit 57690c68)
• pg_opfamily (commit a78fcfb5)
• pg_ts_config (commit 140d4ebc)
• pg_ts_config_map (commit 140d4ebc)
• pg_ts_dict (commit 140d4ebc)
• pg_ts_parser (commit 140d4ebc)
• pg_ts_template (commit 140d4ebc)

Modified:

• pg_am
  • column amsearchnulls added (commit f02a82b6)
• pg_amop
  • column amoplefttype added (commit a78fcfb5)
  • column amoprightype added (commit a78fcfb5)
  • column amopmethod added (commit a78fcfb5)
  • column amopsubtype removed (commit a78fcfb5)
• pg_constraint - following columns added (commit 7bddca34):
  • conpfeqop
  • conppeqop
  • conffeqop
• pg_index
  • column indcheckxmin added (commit 282d2a03)
  • column indisready added (commit 282d2a03)
  • column indoption added (commit 44317582)
• pg_language
  • column lanowner added (commit 55a7cf80)
• pg_opclass following columns changed (commit a78fcfb5):
  • column opcamid renamed to opcmethod
  • column opcfamily added
• pg_operator
  • column oprcanmerge added (commit a78fcfb5)
  • column oprlsortop removed (commit a78fcfb5)
  • column oprrsortop removed (commit a78fcfb5)
  • column oprltcmpop removed (commit a78fcfb5)
  • column oprgtcmpop removed (commit a78fcfb5)
• pg_pltemplate
  • column tmpldbacreate added (commit 55a7cf80)
• pg_proc
  • column procost added (commit 5a7471c3)
  • column prorows added (commit 5a7471c3)
  • column proconfig added (commit 2abae34a)
• pg_rewrite
  • column ev_enabled added (commit 0fe16500)
• pg_type
  • column typtype can contain value e to denote an ENUM (commit 57690c68)
  • columns typmodin and typmodout added (commit 5725b9d9)

Views

Added:

• pg_stat_bgwriter (commit 335feca4)

Modified:

• pg_locks
  • column virtualxid added (commit a9c4c9cd)
  • column virtualtransaction added (commit a9c4c9cd)
  • column transaction removed (commit a9c4c9cd)
• pg_stat_activity
  • column xact_start added (commit 886a02d1)
• pg_stat_all_tables
  • column n_tup_hot_upd added (commit 282d2a03)
  • columns n_live_tuples and n_dead_tuples added (commit 2955f1eb)
• pg_stat_database
  • following columns added (commit 51d7741d):
    • tup_returned
    • tup_fetched
    • tup_inserted
    • tup_updated
    • tup_deleted

System function changes

Added:

• convert_from() (commit 55613bf9)
• convert_to() (commit 55613bf9)
• cursor_to_xml() (commit 355e05ab)
• cursor_to_xmlschema() (commit 355e05ab)
• database_to_xml() (commit 0b75afda)
• database_to_xmlschema() (commit 0b75afda)
• database_to_xml_and_xmlschema() (commit 0b75afda)
• get_current_ts_config() (commit 140d4ebc)
• numnode() (commit 140d4ebc)
• querytree() (commit 140d4ebc)
• pg_stat_get_backend_xact_start() (commit 886a02d1)
• plainto_tsquery() (commit 140d4ebc)
• pg_stat_clear_snapshot() (commit aec4cf1c)
• query_to_xml() (commit 355e05ab)
• query_to_xmlschema() (commit 355e05ab)
• query_to_xml_and_xmlschema() (commit 355e05ab)
• regexp_matches() (commit 9eb78bee)
• regexp_split_to_array() (commit 9eb78bee)
• regexp_split_to_table() (commit 9eb78bee)
• schema_to_xml() (commit 0b75afda)
• schema_to_xmlschema() (commit 0b75afda)
• schema_to_xml_and_xmlschema() (commit 0b75afda)
• table_to_xml() (commit 355e05ab)
• table_to_xmlschema() (commit 355e05ab)
• table_to_xml_and_xmlschema() (commit 355e05ab
• to_tsquery() (commit 140d4ebc)
• to_tsvector() (commit 140d4ebc)
• ts_headline() (commit 140d4ebc)
• ts_lexize() (commit 140d4ebc)
• ts_parse() (commit 140d4ebc)
• ts_rank() (commit 140d4ebc)
• ts_rank_cd() (commit 140d4ebc)
• ts_rewrite() (commit 140d4ebc
• txid_current() (initial commit 1f92630f)
• txid_current_if_assigned() (initial commit 1f92630f)
• txid_current_snapshot() (initial commit 1f92630f)
• txid_snapshot_xip() (initial commit 1f92630f)
• txid_snapshot_xmax() (initial commit 1f92630f)
• txid_snapshot_xmin() (initial commit 1f92630f)
• txid_visible_in_snapshot() (initial commit 1f92630f)
• txid_status() (initial commit 1f92630f)
• xmlagg() (commit b4c8d490)
• xmlcomment() (commit 8c1de5fb)
• xmlconcat() (commit 8c1de5fb)
• xmlelement() (commit 8c1de5fb)
• xmlforest() (commit 8c1de5fb)
• xmlpi() (commit 8c1de5fb)
• xmlroot() (commit 8c1de5fb)
• xmlserialize() (commit 8c1de5fb)
• xpath() (initial commit e651bcf3)

Modified:

• length()
  • length(tsvector) variant added (commit 140d4ebc)
  • length(bytea, name) variant added (commit 55613bf9)
• width_bucket()
  • double precision variant added (commit cf57ef4e)

Data type changes

Added:

• ENUM (commit 57690c68)
• regconfig (commit 140d4ebc)
• regdictionary (commit 140d4ebc)
• UUID (commit a534068e)
• xml (initial commit 8c1de5fb)

Indexing changes

GIN

• extractQuery() method can signal that nothing can satisfy the query (commit d4c6da15)

Configuration changes

Added:

• archive_mode (commit f18dfc48)
• autovacuum_max_workers (commit e2a186b0)
• checkpoint_completion_target (commit 867e2c91)
• default_text_search_config (commit 140d4ebc)
• krb_realm (commit 4b606ee4)
• log_autovacuum_min_duration (commit ef23a774)
• log_checkpoints (commit 9fc25c05)
• log_temp_files (commit be8a4318)
• logging_collector replacing redirect_stderr (commit fd801f4f)
• log_timezone (commit bdd6b622)
• session_replication_role (commit 0fe16500)
• ssl_ciphers (commit c7b08050)
• synchronize_seqscans (commit 47df4f66)
• synchronous_commit (commit 4a78cdeb)
• temp_tablespaces (commit acfce502)
• trace_syncscan (commit a04a4235)
• track_activities replacing stats_command_string (commit 48f7e643)
• track_counts replacing stats_block_level and stats_row_level (commit 48f7e643)
• wal_writer_delay (commit ad429572)
• xmlbinary (commit 4b48ad4f)
• xmloption (commit 8c1de5fb)

Modified

• log_destination
  • option csvlog added (fd801f4f)

Removed:

• redirect_stderr (commit fd801f4f)
• stats_block_level replaced by track_counts (commit 48f7e643)
• stats_command_string replaced by track_activities (commit 48f7e643)
• stats_reset_on_server_start (commit 48f7e643)
• stats_row_level replaced by track_counts (commit 48f7e643)
• stats_start_collector (commit 48f7e643)

Logging changes

• log file output can optionally be generated in CSV format (fd801f4f)

Replication changes

Configuration

Added:

• log_restartpoints (commit f18dfc48)

Modified:

• restore_command
  • %r parameter added to provide last restartpoint (commit f18dfc48)

Procedural language changes

PL/pgSQL

• support for scrollable cursors added (commit f01b1965)
• RETURN QUERY syntax added (commit b2b9b4d5)

Server utilities

• initdb
  • the location of the pg_xlog directory can now be specified with option -X/--xlogdir (commit c3578a68)
• pg_ctl
  • option -c added to allow server crashes to produce core files (commit 226e9fff)
  • option -t added to control timeouts for server startup/shutdown; default: 60 seconds (commit 6e414a17)

Core utilities

• clusterdb
  • Ctrl-C interrupt handler added (commit 6e09df9d)
• createdb
  • --quiet option removed (commit 9539e64b)
• createuser
  • --quiet option removed (commit 9539e64b)
• dropdb
  • --quiet option removed (commit 9539e64b)
• dropuser
  • --quiet option removed (commit 9539e64b)
• reindexdb
  • Ctrl-C interrupt handler added (commit 6e09df9d)
• vacuumdb
  • Ctrl-C interrupt handler added (commit 6e09df9d)

Contrib modules

General:

• Most contrib module documentation moved from README files to the main documentation (commit c3c69ab4).

Added:

• dict_int (commit 5fcb0798)
• dict_xsyn (commit 5fcb0798)
• pageinspect (commit 64058429)
• pg_standby (commit 51be14e9)
• test_parser (commit 5fcb0798)
• uuid-ossp (commit 74496bc2)

Modified:

• hstore
  • GIN and GiST support added, along with other improvements (commit 754148d8)
• oid2name
  • -P option removed (commit 4192f2d8)
• pgbench
  • -P option removed (commit 4192f2d8)
• pg_buffercache
  • usagecount column added to show usage count statistics (commit b0194ab1)
• pgstattuple
  • following functions moved to new contrib module pageinspect (commit 64058429):
    • bt_metap()
    • bt_page_items()
    • bt_page_stats()
  • function execution restricted to superusers (commit 38c75ecf)
• pg_trgm
  • GIN index support added (commit 15f91f27)
• tsearch2
  • converted to provide backwards compatibility following the integration of full text search into core PostgreSQL (commit 90e3f2ac)
• xml2
  • marked as deprecated (commit 4e3cb9ca)