2014-??-?? Current as of 2014-05-10 Major enhancements in PostgreSQL 9.4 include: Allow materialized views to be refreshed without blocking reads Logical change-set extraction allows database changes to be optionally recorded in logical format Allow background workers to be dynamically registered, started and terminated Add structured (non-text) data type (JSONB) for storing JSON data Add SQL-level command ALTER SYSTEM to edit the postgresql.conf configuration file The above items are explained in more detail in the sections below. A dump/restore using pg_dumpall, or use of pg_upgrade, is required for those wishing to migrate data from any previous release. Version 9.4 contains a number of changes that may affect compatibility with previous releases. Observe the following incompatibilities: Cause consecutive whitespace in to_timestamp() and to_date() format strings to consume a corresponding number of characters in the input string (whitespace or not), then conditionally additional adjacent whitespace if not in FX mode (Jeevan Chalke) Previously consecutive whitespace characters in a non-FX format string behaved like a single whitespace character and consumed all adjacent whitespace in the input string. For example, previously format string space-space-space would consume only the first space in ' 12', while it will not consume all three characters. Tighten checks for multi-dimensional array input (Bruce Momjian) Previously an input array string that started with a single-element array dimension could later contain multi-dimensional segments, e.g. '{{1}, {2,3}}'::int[]. Change empty arrays returned by intarray to be zero-dimensional arrays (Bruce Momjian) Previously empty arrays were returned as one-dimensional empty arrays whose text representation looked the same as zero-dimensional arrays ({}). intarray's behavior in this area now matches the built-in array operators. NULL VARIADIC function arguments are now disallowed (Pavel Stehule) Such arguments must be cast to an array data type. SHOW TIME ZONE now outputs constant time zone offsets in POSIX-style zone format (Tom Lane) Previously it was returned in INTERVAL format. The new format can be passed to SET TIME ZONE. Rename EXPLAIN ANALYZE's "total runtime" output to "execution time" (Tom Lane) This was required now that planning time is also reported. Fix ts_rank_cd() to ignore stripped lexemes (Alex Hill) Previously, stripped lexemes got a default location and could be considered if mixed with non-stripped lexemes. Prevent CHECK constraints from referencing system columns, except tableoid (Amit Kapila) Restoring such check constraints will lead to restore errors. Use the last specified recovery_target if multiple are specified (Heikki Linnakangas) Remove system column pg_class.reltoastidxid (Michael Paquier) Remove support for native krb5 authentication (Magnus Hagander) The proper way to use Kerberos authentication is with GSSAPI. Have libpq's PQconnectdbParams() and PQpingParams() functions process zero-length strings as defaults (Adrian Vondendriesch) Previously, these functions treated zero-length string values as defaults only in some cases. Remove system column pg_rewrite.ev_attr (Kevin Grittner) Per-column rules have not been supported since PostgreSQL 7.3. Pg_upgrade now uses -U to specify the user name (Bruce Momjian) Previously, -u was used to specify the user name. Also spell the long option as --username, again for consistency with other tools. Below you will find a detailed account of the changes between PostgreSQL 9.4 and the previous major release. Have VACUUM properly report dead but not removable rows to the statistics collector (Hari Babu) Previously these were reported as live rows. Allow background workers to be dynamically registered, started and terminated (Robert Haas) worker_spi_launch() in worker_spi shows an example of its use. Allow dynamic allocation of shared memory segments (Robert Haas, Amit Kapila) This is illustrated in test_shm_mq. Improve SSL renegotiation handling (Álvaro Herrera) During immediate shutdown, send uncatchable termination signals to child processes that have not already shutdown (MauMau, Álvaro Herrera) This reduces the likelihood of orphaned child processes after postmaster shutdown. Improve randomness of the database system identifier (Tom Lane) Improve speed of multi-key GIN lookups (Alexander Korotkov, Heikki Linnakangas) Reduce GIN index size (Alexander Korotkov, Heikki Linnakangas) Indexes upgraded via pg_upgrade will work fine but will use the old larger GIN format; REINDEX will recreate the index in the new format. Add GiST index support for INET and CIDR data types (Emre Hasegeli) Such indexes improve subnet and supernet lookups and ordering comparisons. Fix race condition in B-tree page deletion (Heikki Linnakangas) Make the handling of interrupted B-tree page splits more robust (Heikki Linnakangas) Allow multiple backends to simultaneously insert into WAL buffers (Heikki Linnakangas) This improves parallel write performance. Conditionally write only the modified portion of updated rows to WAL (Amit Kapila) Improve performance of WINDOW aggregate functions (David Rowley, Florian Pflug, Tom Lane) Improve speed of aggregates which use NUMERICs (Hadi Moshayedi) These include SUM(), AVG(), STDDEV(), and VARIANCE(). Freeze tuples when tables are written with CLUSTER or VACUUM FULL (Robert Haas, Andres Freund) This avoids the need to freeze the tuples in the future. Improve speed of COPY with DEFAULT nextval() columns (Simon Riggs) Improve speed of accesessing many different sequences in the same session (David Rowley) Allow sorting and B-tree index builds to use over four gigabytes of memory (Noah Misch) Reduce memory allocated by DO blocks (Tom Lane) Have the optimizer be more aggressive in creating restrictions from mixed AND/OR clauses (Tom Lane) Auto-resize the catalog cache (Heikki Linnakangas) This reduces memory consumption for backends accessing only a few tables, and improves performance for backend accessing many tables. Expose the estimation of number of changed tuples since last analyze (Mark Kirkwood) This appears in pg_stat_all_tables.n_mod_since_analyze. Add pg_stat_archiver system view to report WAL archiver activity (Gabriele Bartolini) Add xid and xmin to system views pg_stat_activity and pg_stat_replication (Christian Kruse) Add support for SSL ECDH key exchange (Marko Kreen) Such keys are faster and have improved security over previous options. New variable ssl_ecdh_curve controls the curve that is used. Improve the default ssl_ciphers ciphers (Marko Kreen) Allow the server to specify the preferred SSL cipher order (Marko Kreen) This is controlled by the new configuration parameter ssl_prefer_server_ciphers. Have log_connections show SSL encryption information (Andreas Kunert) Add SQL-level command ALTER SYSTEM command to edit the postgresql.conf configuration file (Amit Kapila) Previously postgresql.conf could only be edited at the file system level. Add huge_pages configuration parameter to use huge memory pages on Linux (Christian Kruse, Richard Poole, Abhijit Menon-Sen) This can improve performance on large memory systems. Show PIDs of lock holders and waiters and improve relation information in log_lock_waits log messages (Christian Kruse) Add parameter autovacuum_work_mem to control the amount of memory used by autovacuum workers (Peter Geoghegan) Add max_worker_processes to limit the number of background workers (Robert Haas) This is helpful in configuring the standby server to have the required same number of worker processes as the primary. Add configuration parameter wal_log_hints to enable logging of hint bits (Sawada Masahiko) Hint bits are not normally logged, except when checksums are enabled. This is useful for tools like pg_rewind. Allow printf-style space padding to be specified in log_line_prefix (David Rowley) Add superuser-only session_preload_libraries configuration parameter to load libraries at session start (Peter Eisentraut) Such libraries are auto-LOAD'ed, unlike local_preload_libraries. Reduce server logging level when loading shared libraries (Peter Geoghegan) The previous level was LOG, which was too verbose for per-session libraries. Increase work_mem and maintenance_work_mem defaults by four times (Bruce Momjian) The new defaults are 4MB and 64MB respectively. Increase the default setting of effective_cache_size to 4GB (Bruce Momjian, Tom Lane) Allow terabyte units to be specified for configuration variable values (Simon Riggs) Have Windows ASCII-encoded databases and server process (e.g. postmaster) emit messages in the LC_CTYPE-defined language (Alexander Law, Noah Misch) Previously these messages were output using the Windows ANSI code page. Add recovery.conf parameter recovery_min_apply_delay to force delayed replication (Robert Haas, Fabrízio de Royes Mello, Simon Riggs) This is useful for delaying replaying of user errors on standby servers. Add recovery_target option immediate option to replay WAL stop recovery when a consistent state is reached, i.e. pg_stop_backup() (MauMau, Heikki Linnakangas) Improve recovery target processing (Heikki Linnakangas) The timestamp reported by pg_last_xact_replay_timestamp() now shows information about committed records, not commits being replayed. Recovering to restore points now replay the restore point, rather than stop just before the restore point. pg_switch_xlog() now clears the trailing unused space in the WAL file (Heikki Linnakangas) This improves the compression ratio for WAL files. Add replication slots to report the WAL activity on streaming standbys (Andres Freund, Robert Haas) Replication slots allow preservation of resources like WAL files on the primary that are needed by standby servers. Reduce spinlock contention during WAL replay (Heikki Linnakangas) Report failure return codes from external recovery commands (Peter Eisentraut) Write WAL records of running transactions more frequently (Andres Freund) This allows standby servers to start faster and cleanup resources more aggressively. Logical change-set extraction allows database changes to be optionally recorded in logical format in the WAL. This format can be easily processed by external tools. In previous releases, only binary changes were recorded in the WAL. To implement this feature, the following changes were made: Add new logical wal_level to enable logical change-set encoding in WAL (Andres Freund) This interacts with REPLICA IDENTITY. Allow pg_recvlogical to receive data logical decoding data (Andres Freund) Add test_decoding module to illustrate logical decoding at the SQL level (Andres Freund) Allow logical decoding via the walsender interface ? (Andres Freund) Add table-level parameter REPLICA IDENTITY to control logical replication (Andres Freund) Add relation option user_catalog_table to identify user-created tables involved in logical change-set encoding (Andres Freund) Add ROWS FROM syntax to allow horizontal concatenation of FROM-clause set-returning functions (Andrew Gierth) Add WITH ORDINALITY which numbers rows returned from FROM-clause functions (Andrew Gierth, David Fetter) This is particularly useful for functions like unnest(). Allow SELECT with an empty target list (Tom Lane) This was added for consistency, and so querying tables with no columns would not produce an error. Add DISCARD SEQUENCES command to discard cached sequence information (Fabrízio de Royes Mello, Robert Haas) DISCARD ALL will now also discard such information. Allow quoted strings matching the null string to be converted to NULL in COPY FROM in CSV mode (Ian Barwick, Michael Paquier) Previously only unquoted matching strings would be imported as NULLs. Issue warnings for SET outside of a transaction block, as they have no effect (Bruce Momjian) The cases are SET LOCAL/CONSTRAINTS/TRANSACTION and ABORT. Have EXPLAIN ANALYZE output planning time (Andreas Karlsson) Have EXPLAIN print the grouping columns in Agg and Group nodes (Tom Lane) Have EXPLAIN ANALYZE show bitmap heap scan exact/lossy block information (Etsuro Fujita) Allow materialized views to be refreshed without blocking reads (Kevin Grittner) This is done with REFRESH MATERIALIZED VIEW CONCURRENTLY. Allow auto-updates on views where only some columns are auto-updateable (Dean Rasheed) Previously the presence of a non-auto-updateable column prevented all columns from being auto-updated. Deletes are now supported on suitable views even if no auto-updateable columns are present. Allow control over whether INSERTs and UPDATEs can add rows to an auto-updateable view that would no longer appear in the view (Dean Rasheed) This is controlled with the new WITH CHECK OPTION. Allow security barrier views to be automatically updateable (Dean Rasheed) Allow triggers on foreign tables (Ronan Dunklau) Fix DROP IF EXISTS to more consistently not error for non-existent objects (Pavel Stehule, Dean Rasheed) Improve the internal definition of system relations (Andres Freund, Robert Haas) Previously, relations moved into the system catalog schema could not be modified. Allow ALTER TABLESPACE options to be also set by CREATE TABLESPACE (Vik Fearing) Allow CREATE AGGREGATE to supply the size of the aggregate's transition state data (Hadi Moshayedi) Allow moving groups of objects from one tablespace to another using ALTER TABLESPACE ... MOVE (Stephen Frost) Allow the changing of foreign key constraint via ALTER TABLE ... ALTER CONSTRAINT (Simon Riggs) Reduce lock levels of some ALTER TABLE commands (Simon Riggs, Noah Misch) Specifically, VALIDATE CONSTRAINT, CLUSTER ON, SET WITHOUT CLUSTER, ALTER COLUMN SET STATISTICS, ALTER COLUMN SET (attribute_option), ALTER COLUMN RESET (attribute_option). Fully-implement the line data type (Peter Eisentraut) The line segment data type (LSEG) has always been fully supported. Add PG_LSN data type to represent a WAL log sequence number (LSN) (Robert Haas, Michael Paquier) Allow single-point POLYGONs to be converted to CIRCLEs (Bruce Momjian) Allow 5+ digit years for non-ISO TIMESTAMP and DATE strings, where appropriate (Bruce Momjian) Add checks for overflow/underflow of INTERVAL values (Bruce Momjian) Add structured (non-text) data type (JSONB) for storing JSON data (Oleg Bartunov, Teodor Sigaev, Alexander Korotkov, Peter Geoghegan, and Andrew Dunstan) This allows for faster access to values in the JSON document and faster and more useful indexing of JSON. JSONB values are also typed as appropriate scalar SQL types. Add new JSON functions to allow for the construction of arbitrarily complex json trees (Andrew Dunstan, Laurence Rowe) New functions include json_array_elements_text(), json_build_array(), json_object(), json_object_agg(), json_to_record(), and json_to_recordset(). Add json_typeof() to return the data type of a JSON value (Andrew Tipton) Add pg_sleep_for(interval) and pg_sleep_until(timestamp) to specify sophisticated delays (Vik Fearing, Julien Rouhaud) pg_sleep() only supports delays specified in seconds. Add cardinality() function for arrays (Marko Tiikkaja) This returns the total number of elements in the array, or zero for an array with no elements. Add SQL functions to allow large object reads/writes at arbitrary offsets (Pavel Stehule) Allow unnest() to take multiple arguments, which are individually unnested then horizontally concatenated (Andrew Gierth) Add functions to construct times, dates, timestamps, timestamptzs, and intervals from individual values, rather than strings (Pavel Stehule) The functions being with make_, e.g. make_date(). Have to_char(TZ) return the proper value for constant time zone offsets (Tom Lane) Previously, to_char(CURRENT_TIMESTAMP, 'TZ') returned NULL if the TIME ZONE was set to a constant like -4. Add timezone offset output option OF to to_char() (Bruce Momjian) Improve the random seed used for random() (Honza Horak) Add functions for pg_class, pg_proc, pg_type, and pg_operator lookups that do not generate errors for non-existent objects (Yugo Nagata, Nozomi Anzai, Robert Haas) For example, to_regclass() does error-free lookups of pg_class, and returns NULL for lookup failures. Add function pg_filenode_relation() to allow for more efficient filenode to relation lookups (Andres Freund) Add information_schema column parameters.parameter_default (Peter Eisentraut) Have information_schema.schemata show all accessible schema (Peter Eisentraut) Previously it only showed owned schemas. Add aggregates percentile_cont(), percentile_disc(), rank(), dense_rank(), percent_rank(), cume_dist(), and mode() (Atri Sharma, Andrew Gierth) Add control over which values are passed into aggregate functions using the FILTER clause (David Fetter) Support ordered-set (WITHIN GROUP) aggregates (Atri Sharma, Andrew Gierth, Tom Lane) This allows aggregates that require sorted input. Allow polymorphic aggregates to have non-polymorphic state data types ? (Tom Lane) Allow VARIADIC aggregate functions (Tom Lane) Add event trigger support to PL/Perl and PL/Tcl (Dimitri Fontaine) Add ability to store the PL/PgSQL call stack into a variable using PG_CONTEXT (Pavel Stehule, Stephen Frost) Add option print_strict_params to output parameters passed to queries generating STRICT errors (Marko Tiikkaja) Add variables plpgsql.extra_warnings and plpgsql.extra_errorsto enable additional PL/pgSQL warnings and errors (Marko Tiikkaja, Petr Jelinek) Currently only shadowed variable errors/warnings are available. Handle domains over arrays like plain arrays in PL/Python (Rodolfo Campero) Previously they were treated as strings. Convert NUMERICs to decimal values in PL/Python (Szymon Guz, Ronan Dunklau) Previously these were converted to floats. Add libpq function PQhostaddr() to return the server's IP address (Fujii Masao) Make libpq's PQconndefaults() ignore invalid service files (Steve Singer, Bruce Momjian) Previously it returned NULL. Allow libpq to support TLS versions beyond TLSv1 (Marko Kreen) Document that libpq's PQclientEncoding() returns -1 for an encoding lookup failure (Bruce Momjian) Add createuser -g to specify role membership (Chistopher Browne) Allow vacuumdb --analyze-in-stages to analyze in stages of increasing granularity (Peter Eisentraut) This allows minimal statistics to be created quickly. Allow pg_xlogdump to report a live log stream with --follow (Heikki Linnakangas) Have pg_resetxlog -n output current and potentially changed values (Rajeev Rastogi) Allow sizeof() in ecpg C array definitions (Michael Meskes) Have ecpg properly handle nesting requirements in C and SQL mode for C-style comments (Michael Meskes) Have pg_ctl return 4 for an inaccessible data directory specification (Amit Kapila, Bruce Momjian) This more closely matches the Linux Standard Base (LSB) Core Specification. On Windows, interpret pg_ctl's non-absolute path -D directories relative to pg_ctl's current directory (Kumar Rajeev Rastogi) Allow field wrapping to psql's "extended" mode (Sergey Muraviov) Suppress "No rows" in psql expanded mode when the footer is disabled (Bruce Momjian) Allow Control-C to abort psql hung at connection startup (Peter Eisentraut) Have psql \db+ show tablespace options (Magnus Hagander) Have psql \do+ display the functions which implement the operators (Marko Tiikkaja) Have psql \d+ output an OID line only if an oid column exists in a table (Bruce Momjian) Previously, the presence or absence of an oid column was always reported. Have \d display disabled system triggers (Bruce Momjian) Previously if you disabled all triggers, only user triggers would show as disabled. Fix psql \copy to no longer require a space between stdin and a semicolon (Etsuro Fujita) Output the row count at the end of psql \copy just like COPY (Kumar Rajeev Rastogi) Fix psql \conninfo to display the server's IP address for clients that connect using hostaddr (Fujii Masao) Previously \conninfo could not display the server's IP address in such cases. Mention the SSL protocol version in psql's \conninfo (Marko Kreen) Add psql tab completion for \pset (Pavel Stehule) Allow psql \pset with no arguments to show all settings (Gilles Darold) In psql, output the written history file name (\s) without adding an absolute path prefix (Tom Lane) The previous output was inconsistent. Allow pg_restore options -I, -P, -T and -n to be specified multiple times (Heikki Linnakangas) This allows multiple restore object to be specified. Add IF EXISTS clauses when removing old objects during dump and restore (Pavel Stehule) This suppresses errors when removing old objects. The new --if-exists option to pg_dump, pg_dumpall, and pg_restore is only available when --clean is also specified. Add pg_basebackup option --xlogdir to specify the pg_xlog direction location (Haribabu Kommi) Allow pg_basebackup to relocate tablespaces in the backup copy (Steeve Lennmark) This is particularly useful for using pg_basebackup on the same machine as the primary. Allow network-streams base backups to be throttled (Antonin Houska) This can be controlled with the pg_basebackup --max-rate parameter. Improve the way tuples are frozen, to preserve forensic information (Robert Haas, Andres Freund) Code that inspects tuple flag bits will need to be modified. No longer require function prototypes for function marked with the PG_FUNCTION_INFO_V1 macro (Peter Eisentraut) This eliminates needless boilerplate prototypes whose lack generates compiler warnings. Remove SnapshotNow() and HeapTupleSatisfiesNow() (Robert Haas) All existing uses have been switched to more appropriate snapshot types. Catalog scans now use MVCC snapshots. Add API for memory allocations over four gigabytes (Noah Misch) Add psprintf() to simplify memory allocation during string composition (Peter Eisentraut, Tom Lane) Add printf() modifier "z" to specify size_t values (Andres Freund) Change API of appendStringInfoVA() to better use vsnprintf() (David Rowley, Tom Lane) Allow new types of external toast datums to be created (Andres Freund) Add single-reader, single-writer, lightweight shared message queue (Robert Haas) Improve spinlock speed on x86_64 CPUs (Heikki Linnakangas) Remove spinlock support for unsupported platforms SINIX, Sun3, and NS32K (Robert Haas) Remove IRIX port (Robert Haas) Reduce the number of semaphores required by --disable-spinlocks (Robert Haas) Rewrite duplicate_oids Unix shell script in Perl (Andrew Dunstan) Add Test Anything Protocol (TAP) tests for client programs (Peter Eisentraut) Add make targets check-tests and installcheck-tests which allow per-test specification (Andrew Dunstan) Remove maintainer-check makefile rule (Peter Eisentraut) Its functionality is now done by the default build rules. Improve support for VPATH builds of PGXS modules (Cédric Villemain, Andrew Dunstan) Upgrade to Autoconf 2.69 (Peter Eisentraut) Add configure flag that appends custom text to the PG_VERSION string (Oskari Saarenmaa) This is useful for packagers building custom binaries. Improve DocBook XML validity (Peter Eisentraut) Various security and sanity fixes reported by the Coverity scanner (Stephen Frost) Improve valgrind error reporting (Noah Misch) Improve Emacs configuration file emacs.samples (Peter Eisentraut) Also add .dir-locals.el to the top of the source tree. Allow pgindent to be supplied a command-line list of typedefs (Bruce Momjian) Pgindent is also now smarter about blank lines around preprocessor conditionals. Avoid most uses of dlltool in Cygwin and Mingw builds (Marco Atzeri, Hiroshi Inoue) Allow client-only installs for MSVC (Windows) builds (MauMau) Add pg_prewarm to preload relation data into the shared buffer cache (Robert Haas) This is useful at server start to produce more consistent performance. Add UUID random number generator gen_random_uuid() to pgcrypto (Oskari Saarenmaa) This allows the creation of version 4 UUIDs without requiring the installation of uuid-ossp. Add logging of trigger execution to auto_explain (Horiguchi Kyotaro) Fix pgstattuple to not report rows from uncommitted transactions as dead (Robert Haas) Have pgstattuple functions use regclass-type arguments (Satoshi Nagayasu) While text-type arguments are still supported, they will be removed in a later major release. Improve consistency of pgrowlocks output to honor snapshot rules more consistently (Robert Haas) Improve indexing of pg_trgm values to discourage indexing whitespace (Alexander Korotkov) Store cube data more compactly (Stas Kelvich) Existing data must be dumped/restored to use the new format. The old format can still be read. Reduce vacuumlo client-side memory usage by using a cursor (Andrew Dunstan) Dramatically reduce memory consumption in pg_upgrade (Bruce Momjian) Pass pg_upgrade user names (-U) to analyze scripts (Bruce Momjian) Allow pgbench to process script files of any line length (Sawada Masahiko) The previous line limit was BUFSIZ. Add pg_bench option (--rate) to control the transaction rate (Fabien Coelho) Add --progress output option to pgbench (Fabien Coelho) Add long options to pgbench (Fabien Coelho) Have pg_stat_statements use a flat file for query text storage (Peter Geoghegan) This removes the limitation on query text length, and allows a higher default number of unique statements to be tracked. Allow reporting of pg_stat_statements's internal query hash identifier (Daniel Farina, Sameer Thakur, Peter Geoghegan) Add the ability to retrieve all pg_stat_statements information except the query text (Peter Geoghegan) This allows monitoring tools to only fetch query text for newly created entries, as reported in queryid.