PostgreSQL 9.3beta1 Documentation
Prev | Up | Appendix E. Release Notes | Next

# E.1. Release 9.3

> Release Date: 2013-XX-XX, CURRENT AS OF 2013-05-03

## E.1.1. Overview

Major enhancements include:

ADD HERE ...

The above items are explained in more detail in the sections below.

## E.1.2. Migration to Version 9.3

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:

### E.1.2.1. Server Settings

- Rename `replication_timeout` to `wal_sender_timeout` (Amit Kapila)

  This setting controls the WAL sender timeout.

- Require superuser privileges to set `commit_delay` because it can now potentially delay other sessions (Simon Riggs)

- Allow in-memory sorts to use their full memory allocation (Jeff Janes)

  Users who have set `work_mem` based on the previous behavior should revisit that setting.

### E.1.2.2. Other

- Throw an error if expiring tuple is again updated or deleted (Kevin Grittner) DETAILS?

- 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`.

- 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`.

- Store WAL in a continuous stream, rather than skipping the last 16MB segment every 4GB (Heikki Linnakangas)

  Previously, WAL files with names ending in `FF` were not used. If you have WAL backup or restore scripts that took that skipping into account, they will need to be adjusted.

## E.1.3. Changes

Below you will find a detailed account of the changes between PostgreSQL 9.3 and the previous major release.

### E.1.3.1. Server

#### E.1.3.1.1. Locking

- Prevent non-key-field row updates from locking foreign key rows (Álvaro Herrera, Noah Misch, Andres Freund, Alexander Shulgin, Marti Raudsepp)

  This improves concurrency and reduces the probability of deadlocks. `UPDATE`s 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 configuration variable `lock_timeout` to limit lock wait duration (Zoltán Böszörményi)

- 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 and the restoration of such dumps.

#### E.1.3.1.2. Indexes

- Add `SP-GiST` support for range data types (Alexander Korotkov)

- Allow unlogged `GiST` indexes (Jeevan Chalke)

- Improve concurrency of hash indexes (Robert Haas)

#### E.1.3.1.3. Optimizer

- Collect and use histograms 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)

#### E.1.3.1.4. General Performance

- Add `COPY FREEZE` option to avoid the overhead of marking tuples as committed later (Simon Riggs, Jeff Davis)

- Improve performance of `NUMERIC` calculations (Kyotaro Horiguchi)

- Improve grouping of sessions waiting for `commit_delay` (Peter Geoghegan)

  This improves the usefulness and behavior of `commit_delay`.

- Improve performance for transactions creating, rebuilding, or dropping many relations (Jeff Janes, Tomas Vondra)

- Improve performance of the `CREATE TEMPORARY TABLE ... ON COMMIT DELETE ROWS` clause by only issuing delete if the temporary table was accessed (Heikki Linnakangas)

- 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 the `pg_stat_tmp` statistics file into per-database and global files (Tomas Vondra)

  This reduces the I/O overhead for statistics tracking.

#### E.1.3.1.5. Monitoring

- Add optional ability to checksum data pages and report corruption (Simon Riggs, Jeff Davis, Greg Smith, Ants Aasma)

  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.

#### E.1.3.1.6. Authentication

- 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 and load `pg_ident.conf` once, not during each connection (Amit Kapila)

  This is similar to how `pg_hba.conf` is processed.

#### E.1.3.1.7. Server Settings

- Dramatically reduce System V shared memory requirements (Robert Haas)

  Instead, on Unix-like systems, `mmap()` is used for shared memory. For most users, this will eliminate the need to adjust kernel parameters for shared memory.

- Allow the postmaster to listen on multiple Unix-domain sockets (Honza Horák)

  The configuration parameter `unix_socket_directory` is replaced by `unix_socket_directories`, which accepts a list of directories.

- Allow a directory of configuration files to be processed (Magnus Hagander, Greg Smith, Selena Deckelmann)

  Such a directory is specified with `include_dir` in the server configuration file.

- Increase the maximum initdb-configured value for `shared_buffers` to 128MB (Robert Haas)

  This is the maximum value that initdb will attempt to set in `postgresql.conf`; the previous maximum was 32MB.

- Remove the external PID file on postmaster exit (Peter Eisentraut)

### E.1.3.2. Replication and Recovery

- Allow a streaming replication standby 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_is_in_backup()` and `pg_backup_start_time()` (Gilles Darold)

  These functions report the status of base backups.

- Improve performance of streaming log shipping with `synchronous_commit` disabled (Andres Freund)

- Allow much faster promotion of a streaming standby to primary (Simon Riggs, Kyotaro Horiguchi)

- 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 tools like pg_receivexlog to run on computers with different architectures (Heikki Linnakangas)

  WAL files can still only be replayed on servers with the same architecture as the primary; but they can now be transmitted to and stored on machines of any architecture, since the streaming replication protocol is now machine-independent.

- Have pg_basebackup `--write-recovery-conf` output a minimal `recovery.conf` (Zoltán Böszörményi, Magnus Hagander)

  This simplifies setting up a standby server.

- Allow pg_receivexlog and pg_basebackup `--xlog-method` 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.

#### E.1.3.2.1. Write-Ahead Log (WAL)

- Change the WAL record format to allow splitting the record header across pages (Heikki Linnakangas)

  The new format is slightly more compact, and is more efficient to write.

### E.1.3.3. Queries

- Implement SQL-standard `LATERAL` option for `FROM`-clause subqueries and function calls (Tom Lane)

  This feature allows subqueries and functions in `FROM` to reference columns from other tables in the `FROM` clause. The `LATERAL` keyword is optional for functions.

- Add support for piping `COPY` and psql `\copy` to/from an external program (Etsuro Fujita)

### E.1.3.4. Object Manipulation

- Add support for event triggers (Dimitri Fontaine, Robert Haas, Álvaro Herrera)

  This allows C functions to be called when DDL commands are run.

- Allow a multi-row `VALUES` clause in a rule to reference `OLD`/`NEW` (Tom Lane)

- Add `CREATE SCHEMA ... IF NOT EXISTS` clause (Fabrízio de Royes Mello)

- Have `REASSIGN OWNED` also change ownership of shared objects (Álvaro Herrera)

#### E.1.3.4.1. `CREATE TABLE`

- Suppress messages about implicit index and sequence creation (Robert Haas)

  These messages now appear at `DEBUG1` verbosity, so that they will not be shown by default.

- Allow `DROP TABLE IF NOT EXISTS` to succeed when a non-existent schema is specified in the table name (Bruce Momjian)

  Previously, it threw an error if the schema did not exist.

#### E.1.3.4.2. Constraints

- 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 library support is required to access these fields.

#### E.1.3.4.3. `ALTER`

- Support `IF NOT EXISTS` option in `ALTER TYPE ... ADD VALUE` (Andrew Dunstan)

  This is useful for conditionally adding values to enumerated types.

- 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 support for `ALTER RULE ... RENAME` (Ali Dar)

#### E.1.3.4.4. `VIEWs`

- Add materialized views (Kevin Grittner)

  Unlike 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 then reads from its physical table. There is not yet any facility for incrementally refreshing materialized views or auto-accessing them via base table access.

- Make simple views auto-updatable (Dean Rasheed)

  Simple views that reference some or all columns from a single base table are now updatable by default. More complex views can be made updatable using `INSTEAD OF` triggers or `INSTEAD` rules.

- Improve view/rule printing code to handle cases where referenced tables are renamed, or columns are renamed, added, or dropped (Tom Lane)

  Table and column renamings can produce cases where, if we merely substitute the new name into the original text of a rule or view, the result is ambiguous. This patch fixes the rule-dumping code to insert table and column aliases if needed to preserve the original semantics.

- Add `CREATE RECURSIVE VIEW` syntax (Peter Eisentraut)

  Internally this is translated into `CREATE VIEW ... WITH RECURSIVE ...`.

### E.1.3.5. Data Types

- 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-capable large object access functions.

- Allow text timezone designations, e.g. "America/Chicago" when using the ISO "T" timestamptz format (Bruce Momjian)

#### E.1.3.5.1. `JSON`

- 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 functions to convert values, records, and hstore data to `JSON` (Andrew Dunstan)

### E.1.3.6. Functions

- Add `array_remove()` and `array_replace()` functions (Marco Nenciarini, Gabriele Bartolini)

- Allow `concat()` and `format()` to properly expand `VARIADIC`-labeled arguments (Pavel Stehule)

- Improve `format()` to handle field width and left/right alignment (Pavel Stehule)

- 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".

- Have `to_date()` and `to_timestamp()` return proper results when mixing ISO and Gregorian week/day designations (Bruce Momjian)

- Cause `pg_get_viewdef()` to start a new line by default after each `SELECT` target list entry and `FROM` entry (Marko Tiikkaja)

  This reduces line length in view printing, for instance in pg_dump output.

- Fix `map_sql_value_to_xml_value()` to print values of domain types the same way their base type would be printed (Pavel Stehule)

  There are special formatting rules for certain built-in types such as `boolean`; these rules now also apply to domains over these types.

- Force cached functions to be replanned if the `search_path` changes (Tom Lane)

  Previously functions already run in the current session ignored `search_path` changes.

### E.1.3.7. Server-Side Languages

- Allow SPI functions to access the number of rows processed by `COPY` (Pavel Stehule)

#### E.1.3.7.1. PL/pgSQL Server-Side Language

- Allow PL/pgSQL to use `RETURN` with a composite-type expression (Asif Rehman)

  Previously, in a function returning a composite type, `RETURN` could only reference a variable of that type.

- Allow PL/pgSQL to access the number of rows processed by `COPY` (Pavel Stehule)

  The command is `GET DIAGNOSTICS x = ROW_COUNT`.

- Allow greater flexibility in where keywords can be used in PL/pgSQL (Tom Lane)

#### E.1.3.7.2. PL/Python Server-Side Language

- Add PL/Python result object string handler (Peter Eisentraut)

  This allows plpy.debug(rv) to output something reasonable.

- Make PL/Python convert OID values to a proper Python numeric type (Peter Eisentraut)

- Handle SPI errors raised explicitly (with PL/Python's `RAISE`) the same as internal SPI errors (Oskari Saarenmaa and Jan Urbanski)

### E.1.3.8. Client Applications

- Add command-line utility pg_isready to check if the server is ready to accept connections (Phil Sorber)

- Support multiple `--table` arguments for pg_restore, clusterdb, reindexdb, and vacuumdb (Josh Kupershmidt)

  This is similar to the pg_dump `--table` option.

- Add `--dbname` option to pg_dumpall, pg_basebackup, and pg_receivexlog to specify the connection string (Amit Kapila)

- Add libpq function `PQconninfo()` to return connection information (Zoltán Böszörményi, Magnus Hagander)

#### E.1.3.8.1. psql

- Adjust function cost settings so psql tab completion and pattern searching is more efficient (Tom Lane)

- Improve psql tab completion coverage (Jeff Janes, Peter Eisentraut)

- 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.

- Remove psql warning when connecting to an older server (Peter Eisentraut)

  The warning when connecting to a newer server was retained.

##### E.1.3.8.1.1. Backslash Commands

- Add psql `\watch` command to repeatedly execute commands (Will Leinweber)

- Add psql command `\gset` to store query results in psql variables (Pavel Stehule)

- Add SSL information to psql's `\conninfo` command (Alastair Turner)

- Add "Security" label to psql `\df+` output (Jon Erdman)

- Allow psql `\l` to accept a database name pattern (Peter Eisentraut)

- 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.

- Properly reset state if the SQL command executed with psql's "\g file" fails (Tom Lane)

  Previously, the output from subsequent SQL commands would unexpectedly continue to go to the same file.

##### E.1.3.8.1.2. Output

- Add a `latex-longtable` output format to psql (Bruce Momjian)

  This format 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)

#### E.1.3.8.2. pg_dump

- Add pg_dump `--jobs` option to dump tables in parallel (Joachim Wieland)

- Have pg_dump output functions in a more predictable order (Joel Jacobson)

- Fix tar files emitted by pg_dump and pg_basebackup to be POSIX conformant (Brian Weaver, Tom Lane)

- Add `--dbname` option to pg_dump, for consistency with other client commands (Heikki Linnakangas)

  The database name could already be supplied last without a flag.

#### E.1.3.8.3. initdb

- 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.

- Have initdb issue a warning about placing the data directory at the top of a file system mount point (Bruce Momjian)

### E.1.3.9. Source Code

- Add an embedded list interface (Andres Freund)

- Add infrastructure to better support plug-in background worker processes (Álvaro Herrera)

- Create a centralized timeout API (Zoltán Böszörményi)

- Create libpgcommon and move `pg_malloc()` and other functions there (Álvaro Herrera, Andres Freund)

  This allows libpgport to be used solely for porting code.

- Standardize on naming of client-side memory allocation functions (Tom Lane)

- Add compiler designations to indicate some `ereport()` and `elog()` calls do not return (Peter Eisentraut, Andres Freund, Tom Lane, Heikki Linnakangas)

- Allow options to be passed to the regression test output comparison utility via `PG_REGRESS_DIFF_OPTS` (Peter Eisentraut)

- Add isolation tests for `CREATE INDEX CONCURRENTLY` (Abhijit Menon-Sen)

- Remove typedefs for `int2`/`int4` as they are better represented as `int16`/`int32` (Peter Eisentraut)

- Fix install-strip on Mac OS X (Peter Eisentraut)

- Remove configure flag `--disable-shared`, as it is no longer used (Bruce Momjian)

- Rewrite pgindent in Perl (Andrew Dunstan)

- Add emacs macro to match PostgreSQL perltidy formatting (Peter Eisentraut)

- Run tool to check the keyword list when the backend grammar is changed (Tom Lane)

- Centralize flex and bison make rules (Peter Eisentraut)

  This is useful for pgxs authors.

- Support `Assert()` in client-side code (Andrew Dunstan)

- Change many internal backend functions to return `OID`s rather than void (Dimitri Fontaine)

  This is useful for event triggers.

- 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 (Álvaro Herrera)

- Add post-`ALTER`-object server hooks (KaiGai Kohei)

- Implement a generic binary heap and use it for Merge-Append operations (Abhijit Menon-Sen)

- Improve ability to detect official timezone abbreviation changes (Tom Lane)

- Add pkg-config support libpq and ecpg libraries (Peter Eisentraut)

- Remove `src/tool/backend`, now that the content is on the PostgreSQL wiki (Bruce Momjian)

- Split out WAL reading as an independent facility (Heikki Linnakangas, Andres Freund)

- Use a 64-bit integer to represent WAL positions (`XLogRecPtr`) instead of two 32-bit integers (Heikki Linnakangas)

  Generally, tools that need to read the WAL format will need to be adjusted.

- 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)

### E.1.3.10. Additional Modules

- Allow foreign data wrappers to support writes (inserts/updates/deletes) on foreign tables (KaiGai Kohei)

- Add a Postgres foreign data wrapper contrib module (Shigeru Hanada)

  This foreign data wrapper supports writes.

- Add pg_xlogdump contrib program (Andres Freund)

- Add support for indexing of regular-expression searches in pg_trgm (Alexander Korotkov)

- Improve pg_trgm handling of multibyte characters (Tom Lane)

- Add pgstattuple function to report the size of the `GIN` pending index insertion list (Fujii Masao)

- Have oid2name, pgbench, and vacuumlo set `fallback_application_name` (Amit Kapila)

- Improve output of pg_test_timing (Bruce Momjian)

- Improve output of pg_test_fsync (Peter Geoghegan)

- Improve dblink option validator (Tom Lane)

  Details?

#### E.1.3.10.1. pg_upgrade

- 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.

- 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)

- Increase pg_upgrade logging content by showing executed command (Álvaro Herrera)

- Improve pg_upgrade's status display during copy/link (Bruce Momjian)

#### E.1.3.10.2. pgbench

- Add `--foreign-keys` option to pgbench (Jeff Janes)

  This adds foreign key constraints to the standard tables created by pgbench, for use in foreign key performance testing.

- Allow pgbench to aggregate performance statistics and produce output every `--aggregate-interval` seconds (Tomas Vondra)

- Add pgbench `--sampling-rate` option to control the percentage of transactions logged (Tomas Vondra)

- Reduce and improve the status message output of pgbench's initialization mode (Robert Haas, Peter Eisentraut)

- Add pgbench `-q` mode to print one output line every five seconds (Tomas Vondra)

- Output pgbench elapsed and estimated remaining time during initialization (Tomas Vondra)

- Allow pgbench to use a larger scale factor (Greg Smith)

### E.1.3.11. Documentation

- Allow EPUB-format documentation to be created (Peter Eisentraut)

- Update FreeBSD kernel configuration documentation (Brad Davis)

- Improve `WINDOW` function documentation (Bruce Momjian, Tom Lane)

- Add instructions for setting up the documentation tool chain on Mac OS X (Peter Eisentraut)

- Improve `commit_delay` documentation (Peter Geoghegan)

Prev | Home | Next
Release Notes | Up | Release 9.2.4