PostgreSQL 9.4

• Release notes (source file)
• Released: 2014-12-18 (announcement)
• Final minor version: 9.4.26
• EOL: 2020-02-13 (announcement)
• Documentation: PostgreSQL 9.4

Major features

• ALTER SYSTEM command added (commit 65d6e4cb)
• logical decoding support (commit b89e1510)
• replication slots added (initial commit 858ec118)
• background workers can be dynamically registered, started and terminated (commit 7f7485a0)
• jsonb data type (initial commit d9134d0a)

Backend changes

Loadable modules

• function prototypes no longer required for functions marked with PG_FUNCTION_INFO_V1 (commit e7128e8d)

Memory management

• Linux huge TLB page support added (initial commit 1a3458b6)

Performance

• the following ALTER TABLE commands no longer require an ACCESS EXCLUSIVE lock (commit e5550d5f):
  • ALTER COLUMN [RE]SET attribute_option
  • ALTER COLUMN SET STATISTICS
  • CLUSTER ON
  • SET WITHOUT CLUSTER
  • VALIDATE CONSTRAINT

Storage parameters

• user_catalog_table added (commit 66abc260)

System catalog

• enable modification of user-defined tables in the pg_catalog schema (commit 8e18d04d)

Triggers

• Event triggers
  • support for PL/Tcl event trigger functions added (commit a5036ca9)
  • support for PL/Perl event trigger functions added (commit e5dc4cc2)

Views

• materialized views can be refreshed concurrently (commit cc1965a9)
• check_option option added (commit 4cbe3ac3)
• views containing non-updatable columns can be automatically updated (commit cab5dc5d)
• securiy barrier views can be updated (commit 842faa71)

Source configuration

• option --enable-tap-tests added (commit 16381b2a)
• option --with-extra-version added (commit 46328916)
• option --with-krb5 removed (commit 98de86e4)

Supported platform changes

• IRIX support removed (commit ea91a6be)

SQL changes

General

• WITH ORDINALITY syntax added (commit c62736cc)
• FILTER clause for aggregate function calls (commit b560ec1b)
• support for ordered-set (WITHIN GROUP) aggregates (commit 8d65da1f)
• support for VARIADIC aggregate functions (commit 0d3f4406)
• polymorphic aggregates can have non-polymorphic state data types (commit f0fedfe8)

Modified:

• ALTER AGGREGATE
  • support for VARIADIC aggregate functions (commit 0d3f4406)
  • support for ordered-set (WITHIN GROUP) aggregate functions (commit 8d65da1f)
• ALTER FOREIGN TABLE
  • support for enabling/disabling triggers added (commit 7cbe57c3)
• ALTER INDEX
  • ALTER INDEX ... ALL IN TABLESPACE ... syntax added (initial commit fbe19ee3)
• ALTER MATERIALIZED VIEW
  • ALTER MATERIALIZED VIEW ALL IN TABLESPACE ... SET TABLESPACE ... syntax added (commit d9b2bc45)
• ALTER TABLE
  • ALTER TABLE ... ALL IN TABLESPACE ... syntax added (initial commit fbe19ee3)
  • ALTER TABLE ... REPLICA IDENTITY ...  syntax added (initial commit 07cacba9)
• COPY
  • FORCE NULL option added (commit 3b5e03dc)
  • COPY operations into tables with DEFAULT nextval() speeded up (commit 4d1e2aeb)
• CREATE AGGREGATE
  • options for moving-aggregate optimization added (commit a9d9acbf)
  • options enabling polymorphic aggregates to have non-polymorphic state data types added (commit f0fedfe8)
  • support for VARIADIC aggregate functions (commit 0d3f4406)
  • support for ordered-set (WITHIN GROUP) aggregate functions (commit 8d65da1f)
  • option SSPACE added forproviding the approximate average size of the aggregate's state value (commit 6cb86143)
• CREATE TABLESPACE
  • CREATE TABLESPACE ... WITH ( options ) syntax added (commit 5254958e)
• CREATE VIEW
  • WITH [ CASCADED | LOCAL ] CHECK OPTION syntax added (commit 4cbe3ac3)
• DISCARD
  • DISCARD SEQUENCES syntax added (commit d90ced8b)
• DROP AGGREGATE
  • support for VARIADIC aggregates added (commit 0d3f4406)
• EXPLAIN
  • EXPLAIN ANALYZE now shows planning time (commit 9347baa5)
  • EXPLAIN ANALYZE now shows exact and lossy block counts in bitmap heap scans (commit 2bb1f14b)
  • total runtime label renamed to execution time (commit 5f86cbd7)
  • now shows  the grouping columns in Agg and Group nodes (commit f2609905)
• REFRESH MATERIALIZED VIEW
  • REFRESH MATERIALIZED VIEW CONCURRENTLY added (commit cc1965a9)
• SET
  • when SET LOCAL ... is executed outside of a transaction block, a WARNING (instead of an ERROR) will be issued (commit a6542a4b)
• SET CONSTRAINTS
  • when executed outside of a transaction block, a WARNING (instead of an ERROR) will be issued (commit a6542a4b)
• SET TRANSACTION
  • when executed outside of a transaction block, a WARNING (instead of an ERROR) will be issued (commit a6542a4b)

WAL changes

• when switching to a new WAL segment, pg_switch_xlog() now fills the remaining unused portion of the segment with zeros, removing the need for utilities such as pg_clearxlogtail (commit 9a20a9b2)

Data types

Added:

• jsonb (initial commit d9134d0a)
• line fully implemented (commit 261c7d4b)
• pg_lsn (commit 7d03a83f)

System catalogue changes

Tables

Modified:

• pg_aggregate - following columns added:
  • aggkind (commit 8d65da1f)
  • aggnumdirectargs (commit 8d65da1f)
  • aggmtransfn (commit a9d9acbf)
  • aggminvtransfn (commit a9d9acbf)
  • aggmfinalfn (commit a9d9acbf)
  • aggfinalextra (commit f0fedfe8)
  • aggmfinalextra (commit f0fedfe8)
  • aggtransspace (commit 6cb86143)
  • aggmtransspace (commit a9d9acbf)
  • aggmtranstype (commit a9d9acbf)
  • aggminitval (commit a9d9acbf)
• pg_class
  • column relreplident added (commit 07cacba9)
  • column reltoastidxid removed (commit 2ef085d0)
• pg_index
  • column indisreplident added (commit 07cacba9)
• pg_rewrite
  • column ev_attr removed (commit 277607d6)

Views

Added:

• pg_replication_slots (commit 858ec118)
• pg_stat_archiver (commit 9132b189)

Modified:

• pg_stat_activity
  • column backend_xid added (commit dd1a3bcc)
  • column backend_xmin added (commit dd1a3bcc)
• pg_stat_all_tables
  • column n_mod_since_analyze added; applies to pg_stat_sys_tables and pg_stat_user_tables as well (commit c87ff71f)
• pg_stat_replication
  • column backend_xmin added (commit dd1a3bcc)
  • data type of following columns changed from text to pg_lsn (commit 6615e774):
    • sent_location
    • write_location
    • flush_location
    • replay_location

System function changes

Added:

• cume_dist() (commit 8d65da1f)
• dense_rank() (commit 8d65da1f)
• json_array_elements_text() (commit 5264d915)
• json_build_array() (commit 10563990)
• json_build_object() (commit 10563990)
• json_object() (commit 10563990)
• json_object_agg() (commit 10563990)
• json_to_record() (commit 10563990)
• json_to_recordset() (commit 10563990)
• json_typeof() (commit 4d212bac)
• jsonb_array_elements() (commit d9134d0a)
• jsonb_array_elements_text() (commit d9134d0a)
• jsonb_array_length() (commit d9134d0a)
• jsonb_each() (commit d9134d0a)
• jsonb_each_text() (commit d9134d0a)
• jsonb_extract_path() (commit d9134d0a)
• jsonb_extract_path_text() (commit d9134d0a)
• jsonb_object_keys() (commit d9134d0a)
• jsonb_populate_record() (commit d9134d0a)
• jsonb_populate_recordset() (commit d9134d0a)
• jsonb_to_record() (commit f9c6d72c)
• jsonb_to_recordset() (commit f9c6d72c)
• jsonb_typeof() (commit d9134d0a)
• lo_from_bytea() (initial commit c50b7c09)
• lo_get() (commit c50b7c09)
• lo_put() (commit c50b7c09)
• make_date() (commit f901bb50)
• make_interval() (commit 84df54b2)
• make_time() (commit f901bb50)
• make_timestamp() (commit 84df54b2)
• make_timestamptz() (commit 84df54b2)
• mode() (commit 8d65da1f)
• percentile_cont() (commit 8d65da1f)
• percentile_disc() (commit 8d65da1f)
• percent_rank() (commit 8d65da1f)
• pg_create_logical_replication_slot() (commit b89e1510)
• pg_create_physical_replication_slot() (commit 858ec118)
• pg_drop_replication_slot() (commit 858ec118)
• pg_filenode_relation() (commit f01d1ae3)
• pg_last_xact_replay_timestamp() (commit 7ba6e4f0)
• pg_logical_slot_get_changes() (commit b89e1510)
• pg_logical_slot_get_binary_changes() (commit b89e1510)
• pg_logical_slot_peek_changes() (commit b89e1510)
• pg_logical_slot_peek_binary_changes() (commit b89e1510)
• pg_sleep_for() (commit 760c770f)
• pg_sleep_until() (commit 760c770f)
• rank() (commit 8d65da1f)
• to_regclass() (commit 0886fc6a)
• to_regoper() (commit 0886fc6a)
• to_regoperator() (commit dfc0219f)
• to_regproc() (commit 0886fc6a)
• to_regprocedure() (commit dfc0219f)

Modified:

• chr()
  • now only accepts values that are valid UTF8 characters according to RFC 3629 (commit 7894ac50)
• pg_create_restore_point()
  • return value type changed from text to pg_lsn (commit 6f289c2b)
• pg_current_xlog_location()
  • return value type changed from text to pg_lsn (commit 6f289c2b)
• pg_stat_reset_shared()
  • option archiver added (commit 9132b189)
• pg_start_backup()
  • return value type changed from text to pg_lsn (commit 6f289c2b)
• pg_stop_backup()
  • return value type changed from text to pg_lsn (commit 6f289c2b)
• pg_switch_xlog()
  • return value type changed from text to pg_lsn (commit 6f289c2b)
• pg_xlogfile_name()
  • argument type changed from text to pg_lsn (commit 6f289c2b)
• pg_xlogfile_name_offset()
  • argument type changed from text to pg_lsn (commit 6f289c2b)
• pg_xlog_location_diff()
  • argument types changed from text to pg_lsn (commit 6f289c2b)
• random()
  • random seed improved (commit 98c50656)
• to_char()
  • timezone offset format specifier OF added to output the timezone's UTC offset (commit 7408c5d2)
• unnest()
  • multiple argument form added (commit 784e762e)

Configuration changes

Added:

• autovacuum_multixact_freeze_max_age (commit 801c2dc7)
• autovacuum_work_mem (commit 8693559c)
• dynamic_shared_memory_type (commit 0ac5e5a7)
• huge_pages (initial commit 1a3458b6)
• max_replication_slots (commit 858ec118)
• max_worker_processes (commit 6bc8ef0b)
• session_preload_libraries (commit 070518dd)
• ssl_ecdh_curve (commit 31647214)
• ssl_prefer_server_ciphers (commit ef326752)
• wal_log_hints (commit 50e54709)

Modified:

• effective_cache_size
  • default value changed to 4GB (commit b910d7ea)
• log_connections
  • SSL encryption information will also be logged (commits 4cba1f6b and d3c98069)
• log_line_prefix
  • printf-style padding specifications permitted (commit 4334639f)
• log_lock_waits
  • PIDs of lock holders and waiters will be displayed in the emitted log message (commit 588fb507)
• maintenance_work_mem
  • default value changed from 16MB to 64MB (commit 848ae330)
• wal_level
  • logical setting added (commit e55704d8)
• work_mem
  • default value changed from 1MB to 4MB (commit 848ae330)

Indexing changes

GIN

• index size reduced through compression of GIN posting lists (commit 36a35c55)
• speed of multi-key lookups improved (commits e20c70cb, 25b1dafa and 626a1206)
• improved search speed where one item is rare and the other is frequent (commit dbc649fd)

Authentication changes

SSL

• support for Elliptic Curve keys added (commit 31647214)
• new configuration option ssl_prefer_server_ciphers to prefer server above client cipher order (commit ef326752)

Kerberos

• native support for Kerberos removed (commit 98de86e4)

Replication changes

Replication configuration changes

Added:

• recovery_min_apply_delay (commit 36da3cfb)
• recovery_target (commit 71c6a8e3)

libpq changes

• function PQhostaddr() added (commit 9f80f483)
• function PQconndefaults() now ignores invalid service files ((commit 9e0a97f1)
• support for TLS protocol versions beyond TLSv1 added (commit 820f08ca)

ECPG changes

• sizeof() operators can be used as array dimensions (commit 08d1b22b)
• properly handle nested C-style comments in C and SQL (commit 05b476c2)

Procedural language changes

PL/pgSQL

• the current PL/pgSQL call stack can be retrieved using GET DIAGNOSTICS (commit 83128325)
• option print_strict_params for displaying the parameters passed to a query which violated a STRICT constraint (commit 689746c0)
• configuration parameters plpgsql.extra_warnings and plpgsql.extra_errors for enabling additional PL/pgSQL warnings and errors (commit 7d8f1de1)

PL/Perl

• support for event trigger functions added (commit e5dc4cc2)

PL/Python

• PostgreSQL numeric values now converted to Python decimal (commit 7919398b)

PL/Tcl

• support for event trigger functions added (commit a5036ca9)

Foreign data wrapper changes

• trigger support added for foreign tables (commit 7cbe57c3)

Information schema

• column parameter_default added to view information_schema.parameters (commit 85ed91ee)
• information_schema.schemata now shows all accessible schemas, not just those owned by the current user (commit b34f8f40)

Server utilities

Modified:

• initdb
  • now throws error for incorrect locale settings, instead of silently falling back to a default setting (commit 31a26323)
• pg_ctl
  • now returns exit code 4 if the specified data directory is inaccessible (commit 11d205e2)
• pg_xlogdump
  • --follow option added, making it possible to report a live log stream

Core utilities

Added:

• pg_recvlogical (initial commit 8bdd12bb)

Modified:

• createuser
  • option -g/--role added (commit 6bea96dd)
• pg_basebackup
  • option --xlogdir added to specify the pg_xlog directory location (commit d1b88f6b)
  • option -T/--tablespace-mapping added for relocating tablespaces on the destination server (commit fb05f3ce)
  • option --max-rate added to enable throttling of backup transfer rate (commit ef5856fd)
• pg_dump
  • option --if-exists added (commit 9067310c)
• pg_dumpall
  • option --if-exists added (commit 9067310c)
• pg_receivexlog
  • option -S/--slot added (commit 858ec118)
• pg_restore
  • option --if-exists added (commit 9067310c)
  • multiple -I/--index, -P/--function, -T/--trigger and -n/--schema options now accepted (commit da85fb47)
• vacuumdb
  • option --analyze-in-stages added (commit c92c3d50)

psql

• SSL compression status now shown in login banner (commit d3c98069)
• \pset without arguments now shows all settings (commit c64e68fd)

Contrib modules

Added:

• pg_prewarm (commit c32afe53)
• test_decoding (commit b89e1510)
• test_shm_mq (commit 4db3744f)

Modified:

• auto_explain
  • option auto_explain.log_trigger added, which logs trigger execution time (commit e2a0fc53)
• cube
  • data stored more compactly (commit 08612f45)
• file_fdw
  • force_null option added (commit 3b5e03dc)
• hstore
  • following functions added (commit d9134d0a):
    • hstore_to_jsonb()
    • hstore_to_jsonb_loose()
• pgbench
  • --rate option added (initial commit fc9f4e9f)
  • --progress option added (initial commit 4a87f308)
  • hard-wired line length limit in custom script files removed (commit 61a07bae)
  • long options added (commit 79cddb18)
• pgcrypto
  • function gen_random_uuid() added (commit e6170126)
• pg_stat_statements
  • query text stored in file (commit f0d6f202)
  • records can be returned without the query text (commit f0d6f202)
  • internal query hash identifier exposed (commit 91484409)
  • DEALLOCATE no longer tracked (commit 3cd934f6)
  • statistics file saved to pg_stat directory instead of global (commit 654e8e44)
• pgstattuple
  • functions converted to use regclass types as the argument (commit 1dc11866)
  • rows from uncommitted transactions no longer counted as dead (commit 80c79ab2)
• pg_trgm
  • improved heuristics for regular expression index searches (commit 80a5cf64)
• pg_upgrade
  • -u/--user options renamed to -U/--username for consistency with other utilties (commit 457d6cf0)
  • memory consumption significantly reduced (commit 2a2e40aa)
• pg_xlogdump
  • --follow option added, making it possible to report a live log stream (commit ce9bb92f)
• postgres_fdw
  • extended pushdown of VARCHAR columns through improved handling of collation-related decisions (commit 76f965ff)
• uuid-ossp
  • support for BSD and e2fsprogs UUID libraries added (commit b8cc8f94)
• vacuumlo
  • cursor used to limit client-side memory usage (commit 67ccbb08)

Testing

• support for TAP tests added (initial commit 7d0f493f)

Pre-release test versions

Initial commit marking the start of PostgreSQL 9.4 development is 58ae1f45 (2013-06-14).