2013-XX-XX, CURRENT AS OF 2013-04-20 ADD HERE Major enhancements include: 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.3 contains a number of changes that may affect compatibility with previous releases. Observe the following incompatibilities: here Below you will find a detailed account of the changes between PostgreSQL 9.3 and the previous major release. Prevent non-key-field row updates from locking foreign key rows (AlvaroAacute;lvaro Herrera, Marti Raudsepp, Alexander Shulgin, Noah Misch and Andres Freund) This improves concurrency and reduces the probability of deadlocks. UPDATEs on non-key columns use the new SELECT FOR NO KEY UPDATE lock type, and foreign key checks use the new SELECT FOR KEY SHARE lock mode. Add cache of local locks (Jeff Janes) This speeds lock release at statement completion in transactions that hold many locks; it is particularly useful for pg_dump. Add SP-GiST support for range data types (Alexander Korotkov) Allow unlogged GiST indexes (Jeevan Chalke) Improve concurrency of hash indexes (Robert Haas) Improve the ability to detect indexable prefixes in regular expressions (Tom Lane) Collect and use histograms of lower and upper bounds for range types (Alexander Korotkov) Reduce optimizer overhead by discarding plans with unneeded cheaper startup costs (Tom Lane) Improve optimizer cost estimation for index access (Tom Lane) Add COPY FREEZE option to avoid the overhead of later marking tuples as committed (Simon Riggs, Jeff Davis) Improve performance of NUMERIC calculations (Kyotaro Horiguchi) Use a binary heap for Merge-Append operations (Abhijit Menon-Sen) Improve memory usage for in-memory sorts (Jeff Janes) Users who have set work_mem based on the previous behavior should revisit that setting. Improve performance for transactions creating, rebuilding, or dropping many relations (Jeff Janes, Tomas Vondra) Improve performance of the CREATE TABLE ... ON COMMIT DELETE ROWS clause by only issuing delete if the temporary table was accessed (Heikki Linnakangas) Allow heap-only tuple updates on system tables (Andres Freund) Have vacuum recheck visibility after it has removed expired tuples (Pavan Deolasee) This increases the chance of a page being marked as all-visible. Split pgstat file in per-database and global files (Tomas Vondra) This reduces the statistics management read and write overhead. Add optional ability to checksum data pages and report corruption (Simon Riggs, Jeff Davis, Greg Smith) The checksum option can be set during initdb. Allow pg_terminate_backend() to terminate other backends with the same role (Dan Farina) Previously, only superusers could terminate other sessions. Allow the statistics collector to operate properly in cases where the system clock goes backwards (Tom Lane) Previously statistics collection would stop until the time again reached the previously-stored latest time. Add function to report the size of the GIN pending index insertion list (Fujii Masao) The function is pgstatginindex() in pgstattuple. Improve LDAP error reporting and documentation (Peter Eisentraut) Add support for LDAP authentication to be specified in URL format (Peter Eisentraut) Change the ssl_ciphers parameter to start with DEFAULT, rather than ALL, then remove insecure ciphers (Magnus Hagander) It is assumed DEFAULT is more appropriate cipher set. Parse/load pg_ident.conf once, not during each connection (Amit Kapila) This is similar to how pg_hba.conf is processed. Dramatically reduce System V shared memory usage (Robert Haas) Instead, on Unix-like systems, mmap() is used for shared memory. This eliminates the requirement of increasing System V shared memory kernel parameters. Allow the postmaster to listen on multiple Unix-domain sockets (Honza Horak) This renames configuration parameter unix_socket_directory to unix_socket_directories, which accepts a list of directories. Add configuration variable lock_timeout to limit lock wait duration (Zoltán Böszörményi) Allow a directory of configuration files to be processed (Magnus Hagander, Greg Smith, Selena Deckelmann) The directory is specified as include_dir in server configuration file. Increase the maximum initdb-configured value for shared_buffers to 128MB (Robert Haas) This is the maximum value initdb attempts to set in postgresql.conf; the previous value was 32MB. Improve grouping of sessions waiting for commit_delay (Peter Geoghegan) This improves the usefulness of commit_delay. Require superuser privileges to set commit_delay because it can now potentially delay other sessions (Simon Riggs) BACKWARD COMPATIBILITY Remove the external PID file on postmaster exit (Peter Eisentraut) Have "session id" in log_line_prefix (%c) always output four hex digits after the period (Bruce Momjian) COMPATIBILITY Allow a streaming replication standbys to follow a timeline switch (Heikki Linnakangas) This allows streaming standbys to feed from newly-promoted slaves. Previously slaves required access to a WAL archive directory to accomplish this. Add SQL functions pg_backup_in_progress() and pg_backup_start_time() (Darold Gilles) These functions report the status of base backups. Improve performance of streaming replication log shipping (Andres Freund) Add the last checkpoint's redo location to pg_controldata's output (Fujii Masao) This information is useful for determining the WAL files needed for restore. Allow tooling like pg_receivexlog to run on computers with different architectures (Heikki Linnakangas) Have pg_basebackup --write-recovery-conf output a minimal recovery.conf (Zoltan Boszormenyi, Magnus Hagander) This simplifies setting up a standby server. Allow pg_receivexlog and pg_basebackup -X to handle streaming timeline switches (Heikki Linnakangas) Add wal_receiver_timeout parameter to control the WAL receiver timeout (Amit Kapila) This allows more rapid detection of connection failure. No longer set wal_receiver_status_interval? Rename replication_timeout to wal_sender_timeout (Amit Kapila) BACKWARD COMPATIBILITY This setting controls the WAL sender timeout. Allow recovery.conf to be relocated using configuration variable recovery_config_directory (Simon Riggs) Store WAL in a continuous stream, rather than skipping the last 16MB segment every 4GB (Heikki Linnakangas) Restructure WAL files to better handle timeline changes during recovery (Heikki Linnakangas) Restructure WAL files to use a more compact storage format (Heikki Linnakangas) Implement SQL-standard LATERAL for FROM-clause subqueries and function calls (Tom Lane) This is useful in allowing FROM subqueries and functions to reference output from tables in the FROM clause. The LATERAL keyword is optional for functions. Improve query string error location reporting (Tom Lane) Only CREATE SCHEMA IF NOT EXISTS? If so, remove it. Correct predicate locking for DROP INDEX CONCURRENTLY (Kevin Grittner) KEEP? Add support for piping COPY and psql \copy to/from an external program (Etsuro Fujita) Add support to event triggers (Dimitri Fontaine, Tom Lane) This allows C functions to be called when specific commands are run. Allow a multi-row VALUES clause in rules to reference OLD/NEW (Tom Lane) Add IF NOT EXISTS clause to CREATE SCHEMA (Fabrízio de Royes Mello) Throw an error if expiring tuple is again updated or deleted (Kevin Grittner) KEEP? Have DROP OWNED only remove user-matching GRANTs on shared objects, e.g. databases, tablespaces (AlvaroAacute;lvaro Herrera) Previously it tried to remove such objects, leading to an error. Have REASSIGN OWNED also change ownership of shared objects (AlvaroAacute;lvaro Herrera) No longer output messages about implicit index and sequence creation (Robert Haas) These messages now appear with DEBUG1-level output. Allow CREATE TABLE to succeed for a non-existent schema (Bruce Momjian) Previously, CREATE TABLE IF EXIST threw an error if the schema was nonexistent. Internally store default foreign key matches (non-FULL, non-PARTIAL) as "simple" (Tom Lane) These were previously stored as "&unspecified&". This changes the value stored in system column pg_constraint.confmatchtype. BACKWARD COMPATIBILITY CHANGE Change ON UPDATE SET NULL/SET DEFAULT foreign key actions to affect all referenced columns, not just those referenced in the UPDATE (Tom Lane) Previously only columns referenced in the UPDATE were set to null or DEFAULT. BACKWARD COMPATIBILITY CHANGE Provide clients with constraint violation details as separate fields (Pavel Stehule) This allows clients to retrieve table, column, data type, or constraint name error details. Previously such information had to be extracted from error strings. Client language support is required to access these fields. Add support for ALTER RULE ... RENAME (Ali Dar) Add ALTER ROLE ALL SET to add settings to all users (Peter Eisentraut) This allows settings to apply to all users in all databases. ALTER DATABASE SET already allowed addition of settings for all users in a single database. postgresql.conf has a similar effect. Add the ALTER TYPE ... IF NOT EXISTS clause when adding enumerated type labels (Andrew Dunstan) This is useful for conditional label creation in transaction blocks. When converting a table to a view, remove its system columns (Tom Lane) KEEP? Make simple views auto-updatable (Dean Rasheed) INSTEAD rules are still available for complex views. Allow views and rules to handle cases where referenced tables are renamed, or columns are renamed, added, or dropped (Tom Lane) HOW DOES IT HANDLE A DROPPED REFERENCED COLUMN? Add CREATE RECURSIVE VIEW syntax (Peter Eisentraut) Internally this is translated into CREATE VIEW ... WITH RECURSIVE. Add a materialized view relations (Kevin Grittner) Unlink ordinary views, where the base tables are read on every access, materialized views create physical tables at creation or refresh time. Access to the materialized view reads from these materialized physical tables. There is no facility for incrementally refreshing materialized views or auto-accessing them via base table access. Allow text timezone designations, e.g. "America/Chicago" when using the ISO "T" timestamptz format (Bruce Momjian) Increase the maximum length of large objects from 2GB to 4TB (Nozomi Anzai, Yugo Nagata) This change includes new libpq and server-side 64-bit-enabled functions. Add functions to convert values, records, and hstore data to JSON (Andrew Dunstan) Add operators and functions to extract values from JSON data strings (Andrew Dunstan) Allow JSON data strings to be converted into records (Andrew Dunstan) Add array_remove() and array_replace() functions (Marco Nenciarini, Gabriele Bartolini) Have to_char(), to_date(), and to_timestamp() properly handle negative century designations (CC) (Bruce Momjian) Previously the behavior was either wrong or inconsistent with positive/AD handling, e.g. format mask 'IYYY-IW-DY'. In to_date() and to_timestamp(), return proper results when mixing ISO and Gregorian week/day designations (Bruce Momjian) Allow concat() and format() to properly expand VARIADIC-labeled arguments (Pavel Stehule) Cause pg_get_viewdef() to default to wrapping after every SELECT target list and FROM entry (Marko Tiikkaja) This causes view output, like from pg_dump, to be more narrow. Have map_sql_value_to_xml_value() properly expand domains (Pavel Stehule) Improve format() to handle field width and left/right alignment (Pavel Stehule) Force cached functions to be replanned if the search_path changes (Tom Lane) Previously functions already run in the current session ignored changes (Bruce Momjian) Allow SPI functions to access the number of rows processed by COPY (Pavel Stehule) Allow greater flexibility in where keywords can be used in PL/pgSQL (Tom Lane) Allow PL/pgSQL to access the number of rows processed by COPY (Pavel Stehule) The command is GET DIAGNOSTICS x = ROW_COUNT. Allow PL/pgSQL to use RETURN with a composite-type expressions (Asif Rehman) Previously RETURN could only reference composite-type variables. Allow PL/Python to support platform-specific include directories (Peter Eisentraut) Allow PL/Python on OS X to build against custom versions of Python (Peter Eisentraut) Handle SPIErrors raised explicitly with PL/Python's RAISE the same as as internal SPI errors (Oskari Saarenmaa and Jan Urbanski) Add PL/Python result object string handler (Peter Eisentraut) This allows plpy.debug(rv) to output something reasonable. Have PL/Python convert oid values to a proper Python numeric type (Peter Eisentraut) Have initdb fsync the newly created data directory (Jeff Davis) This can be disabled by using --nosync. Add initdb --sync-only option to sync the data directory to durable storage (Bruce Momjian) This is used by pg_upgrade. Support multiple --table arguments for pg_restore, clusterdb, reindexdb, and vacuumdb (Josh Kupershmidt) This is similar to the pg_dump --table support. Add command-line utility pg_isready to check if the server is ready to accept connections (Phil Sorber) Have initdb issue a warning about placing the data directory at the top of file system mount points (Bruce Momjian) Add -d option to pg_dumpall, pg_basebackup, pg_receivexlog to specify the connection string (Amit Kapila) Allow pg_ctl --idempotent to a 'success' return code if the requested start/stop action fails, but the cluster is already in the requested state (Peter Eisentraut) Change pg_ctl to return an error code if start fails because the server is already running (Peter Eisentraut) Add libpq function PQconninfo() to return connection information (Zoltan Boszormenyi, Magnus Hagander) Allow the psql --single-transaction mode to work when reading from standard input (Fabien Coelho, Robert Haas) Previously this option only worked when reading from a file. Improve psql tab completion (Jeff Janes, Peter Eisentraut) Remove psql warning when connecting to an older server (Peter Eisentraut) The warning when connecting to a newer server was retained. Adjust function cost settings so psql tab completion and pattern searching is more efficient (Tom Lane) Add SSL information to psql's \conninfo command (Alastair Turner) In psql, do not allow \connect to use defaults if there is no active connection (Bruce Momjian) This might be the case if the server had crashed. Add "Security" label to psql \df+ output (Jon Erdman) Properly reset state if psql's "\g file" command failed (Tom Lane) Previously failed commands discarded output from subsequent commands. Add psql command \gset to store query results in psql variables (Pavel Stehule) Add psql \watch command to repeatedly execute commands (Will Leinweber) Allow psql \l to accept a database name pattern (Peter Eisentraut) Add a 'latex-longtable' output format to psql (Bruce Momjian) Longtable allows tables to span multiple pages. Add a border=3 output mode to the psql 'latex' format (Bruce Momjian) In psql tuples-only and expanded modes, no longer output "(No rows)" (Peter Eisentraut) In psql, no longer print an empty line for unaligned, expanded output for zero rows (Peter Eisentraut) Fix tar files emitted by pg_dump and pg_basebackup to be POSIX conformant (Brian Weaver, Tom Lane) Have pg_dump output functions in a predictable order (Joel Jacobson) Add -d/--dbname option to pg_dump, for consistency with other client commands (Heikki Linnakangas) The database name could already be supplied last without a flag. Add pg_dump --jobs to dump in parallel (Joachim Wieland) Remove typedefs for int2/int4 as they are better represented as int16/int32 (Peter Eisentraut) Create a centralized timeout API (Zoltán Böszörményi) Rewrite pgindent in Perl (Andrew Dunstan) Add compiler designations to indicate some ereport() and elog() calls do not return (Peter Eisentraut, Andres Freund, Tom Lane, Heikki Linnakangas) Fix install-strip on Mac OS X (Peter Eisentraut) Remove configure flag --disable-shared, as it is no longer used (Bruce Momjian) Add emacs macro to match Postgres perltidy formatting (Peter Eisentraut) Run tool to check the keyword list when the backend grammar is changed (Tom Lane) Standardize on naming of client-side memory allocation functions (Tom Lane) Centralize flex and bison 'make' rules (Peter Eisentraut) This is useful for pgxs authors. Add an embedded list interface (Andres Freund) Add infrastructure to better support plug-in background worker processes (AlvaroAacute;lvaro Herrera) Add Assert() functionality to client-side code for frontend code (Andrew Dunstan) Change many internal many backend functions to return OIDs rather than void (Dimitri Fontaine) This is useful for event triggers. Allow options to be passed to the regression test output comparison utility via PG_REGRESS_DIFF_OPTS (Peter Eisentraut) Create libpgcommon and move pg_malloc() and other functions there (AlvaroAacute;lvaro Herrera, Andres Freund) This allows libpgport to be used solely for porting code. Invent pre-commit/pre-prepare/pre-subcommit events for transaction callbacks (Tom Lane) Loadable modules that use transaction callbacks might need modification to handle these new event types. Add function pg_identify_object() to dump an object in machine-readable format (AlvaroAacute;lvaro Herrera) Improve ability to detect official timezone abbreviation changes (Tom Lane) Add pkg-config support libpq and ecpg libraries (Peter Eisentraut) Add post-ALTER-object server hooks (KaiGai Kohei) Remove src/tool/backend, now that the content is on the Postgres wiki (Bruce Momjian) Split out WAL reading as an independent facility (Andres Freund, Heikki Linnakangas) Have oid2name, pgbench, and vacuumlo set fallback_application_name (Amit Kapila) Improve output of pg_test_timing (Bruce Momjian) Improve pg_test_fsync output (Peter Geoghegan) Improve contrib/dblink option validator (Tom Lane) Details? Add a Postgres foreign data wrapper contrib module (Shigeru Hanada) Add pg_xlogdump contrib program (Andres Freund) Allow write to Postgres foreign tables (KaiGai Kohei) Other foreign data wrappers can now also potentially support write operations. Improve pg_trgm handling of multibyte characters (Tom Lane) Add support for indexing of regular-expression searches in contrib/pg_trgm (Alexander Korotkov) Increase pg_upgrade logging content by showing executed command (AlvaroAacute;lvaro Herrera) Have pg_upgrade create unix-domain sockets in the current directory (Bruce Momjian, Tom Lane) This reduces the possibility that someone will accidentally connect during the upgrade. Have pg_upgrade --check mode properly detect the location of non-default socket directories (Bruce Momjian, Tom Lane) Improve performance of pg_upgrade for databases with many tables (Bruce Momjian) Improve pg_upgrade's status display during copy/link (Bruce Momjian) Allow pg_upgrade --jobs to do parallelism (Bruce Momjian) This allows parallel schema dump/restore of databases, as well as parallel copy/link of data files per tablespace. Add --foreign-keys option to pgbench (Jeff Janes) This adds foreign key constraints to tables, for use in foreign key performance testing. Reduce and improve the status message output of pgbench's initialization mode (Robert Haas, Peter Eisentraut) Add pg_bench --sampling-rate option to control the percentage of transactions logged (Tomas Vondra) Add pgbench -q mode to print one output line every five seconds (Tomas Vondra) Output pgbench elapsed and estimated remaining time during initialization logging (Tomas Vondra) Allow pgbench to aggregate performance statistics and produce output every --aggregate-interval specified seconds (Tomas Vondra) Allow pgbench to use a larger scale factor (Greg Smith) Improve WINDOW function documentation (Bruce Momjian, Tom Lane) Add instructions for setting up the documentation tool chain on Mac OS X (Peter Eisentraut) Update FreeBSD kernel configuration documentation (Brad Davis) Allow EPUB-format documentation to be created (Peter Eisentraut) Improve commit_delay documentation (Peter Geoghegan)