PostgreSQL 9.2beta4 Documentation
Prev | Up | Appendix E. Release Notes | Next

# E.1. Release 9.2

> Release Date: 2012-??-??

> CURRENT AS OF 2012-05-09

## E.1.1. Overview

NARRATIVE HERE. Major enhancements include:

- MAJOR LIST HERE

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

## E.1.2. Migration to Version 9.2

A dump/restore using pg_dump, or use of pg_upgrade, is required for those wishing to migrate data from any previous release.

Version 9.2 contains a number of changes that may affect compatibility with previous releases. Observe the following incompatibilities:

### E.1.2.1. Functions

- Ensure that `xpath()` escapes special characters in string values (Florian Pflug)

  Without this it is possible to output illegal XML.

- Remove hstore's `=>` operator (Robert Haas)

  Users should now use hstore(text, text). Since PostgreSQL 9.0, a warning message is emitted when an operator named => is created because the SQL standard reserves that token for another use.

- Have `pg_relation_size()` and friends return NULL if the object does not exist (Phil Sorber)

  This prevents queries that call these functions from returning errors during concurrent drop operations.

- Remove the `spclocation` field from `pg_tablespace` (Magnus Hagander)

  This field was duplicative of the symbolic links already present in the data directory. This allows tablespace directories to be moved while the server is down. Also add `pg_tablespace_location()` to allow querying of the symbolic links.

- Move `tsvector` most-common-element statistics to new `pg_stats` columns (Alexander Korotkov)

  Consult `most_common_elems` and `most_common_elem_freqs` for the data formerly available in `most_common_vals` and `most_common_freqs`.

#### E.1.2.1.1. Temporal Functions

- Have `EXTRACT` of a non-timezone-aware value measure the epoch from local midnight, not UTC midnight (Tom Lane)

  Having these computations depend on UTC was inconsistent. The old behavior is available by casting the value to timestamp with time zone.

- Properly parse time strings with trailing `yesterday`, `today`, and `tomorrow` (Dean Rasheed)

  Previously, `SELECT '04:00:00 yesterday'::timestamp` returned yesterday's date at midnight.

- Fix `to_date()` and `to_timestamp()` to wrap incomplete dates toward 2020 (Bruce Momjian)

  Previously, supplied years and year masks of less than four digits wrapped inconsistently.

### E.1.2.2. Object Modification

- Prevent `ALTER DOMAIN` from working on non-domain types (Peter Eisentraut)

  Owner and schema changes were previously possible on non-domain types.

- No longer auto-lowercase procedural language names (Robert Haas)

  While unquoted language identifiers are still lowercased, strings and quoted identifiers are no longer forcibly down-cased. Thus for example `CREATE FUNCTION ... LANGUAGE 'C'` will no longer work; it must be spelled `'c'`, or better omit the quotes.

### E.1.2.3. Command-Line Tools

- Provide consistent backquote, variable expansion, and quoted substring behavior in psql meta-command arguments (Tom Lane)

  Previously, such usage was only properly expanded when isolated by whitespace, e.g. `'FOO'BAR` was output as `FOO BAR`.

- No longer treat clusterdb table names as double-quoted; no longer treat reindexdb table and index names as double-quoted (Bruce Momjian)

  Users must supply double-quotes in the command arguments to accomplish double-quoting behavior.

- Disable prompting for the user name in dropdb unless `--interactive` is specified (Peter Eisentraut)

- No longer have createuser prompt for settings (Peter Eisentraut)

  Previously createuser prompted regarding various user settings.

- Remove initdb support for the crypt authentication method (Peter Eisentraut)

  This was removed from the backend years ago.

### E.1.2.4. Server Settings

- Remove the `postgresql.conf` setting `silent_mode` (Heikki Linnakangas)

  This behavior could already be accomplished with `pg_ctl -l postmaster.log`.

- Remove GUC `wal_sender_delay`, as it is no longer needed (Tom Lane)

  The new "latch" capability made this setting unnecessary.

- Remove the `custom_variable_classes` `postgresql.conf` parameter (Tom Lane)

  The checking provided by this setting was dubious. Now any setting can be prefixed by any class name.

- Add GUC parameters for specifying the locations of server-side SSL files (Peter Eisentraut)

  This allows changing the location of the files that were previously hard-coded to `server.crt`, `server.key`, `root.crt`, `root.crl`. CA (crt) and CRL files no longer have default names, and if specified, these files must exist.

### E.1.2.5. Monitoring

- Rename `pg_stat_activity` `.procpid` to `pid`, to match other system tables (Magnus Hagander)

- Create a separate `pg_stat_activity` column to report state information, e.g. idle (Scott Mead, Magnus Hagander)

  The last query values are preserved, allowing for enhanced analysis.

- Rename `pg_stat_activity.current_query` to `query` because it is not cleared when the query completes (Magnus Hagander)

- Change all SQL-level statistics timing values to float8-stored milliseconds (Tom Lane)

  The columns affected are: `pg_stat_user_functions.total_time`, `pg_stat_user_functions.self_time`, `pg_stat_xact_user_functions.total_time`, `pg_stat_xact_user_functions.self_time`.

- Change pg_stat_statements' `total_time` column to be measured in milliseconds (Tom Lane)

## E.1.3. Changes

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

### E.1.3.1. Server

#### E.1.3.1.1. Performance

- Allow queries to retrieve data only from indexes, avoiding heap access (Robert Haas, Ibrar Ahmed, Heikki Linnakangas, Tom Lane)

  This is often called "index-only scans", a feature which now enables the use of indexes with additional columns, or "covering indexes". This is possible for heap pages with exclusively all-visible tuples, as reported by the visibility map. The visibility map was made crash-safe as a necessary part of implementing this feature.

- Allow frequently uncontended locks to be recorded using a new fast-path lock mechanism (Robert Haas)

- Move the frequently accessed members of the `PGPROC` shared memory array to a separate section, for performance (Pavan Deolasee, Heikki Linnakangas, Robert Haas)

- Reduce overhead of creating virtual transaction id locks (Robert Haas)

- Reduce overhead for shared invalidation cache messages (Robert Haas)

- Improve `COPY` performance by adding tuples to the heap in batches (Heikki Linnakangas)

- Improve GiST geometric type index performance by producing better trees with less memory allocation overhead (Alexander Korotkov)

- Allow hint bits to be set sooner for temporary and unlogged tables (Robert Haas)

- Allow sorting to be performed by inlined and faster, non-SQL-callable comparison functions (Peter Geoghegan, Robert Haas, Tom Lane)

- Add the SP-GiST (Space-Partitioned GiST) index access method (Teodor Sigaev, Oleg Bartunov, Tom Lane)

  SP-GiST is comparable to GiST in flexibility, but supports non-balanced partitioned search structures rather than balanced trees. CLARIFY?

- Improve GiST index build times (Alexander Korotkov, Heikki Linnakangas)

- Take fewer MVCC snapshots, for performance (Robert Haas)

- Have the number of clog buffers scale based on `shared_buffers` (Robert Haas, Simon Riggs, Tom Lane)

- Reduce the overhead of serializable isolation level locks (Dan Ports)

- Allow group commit to work effectively under heavy load (Peter Geoghegan, Simon Riggs, Heikki Linnakangas)

  Previously, batching of commits became ineffective as the write workload increased because of internal lock contention.

- Improve PowerPC and Itanium spinlock performance (Manabu Ori, Robert Haas, Tom Lane)

#### E.1.3.1.2. Process Management

- Create a dedicated worker process to perform checkpoints (Simon Riggs)

  Formerly the background writer did dirty page writing and checkpointing.

- Improve asynchronous commit behavior by waking up sooner (Simon Riggs)

  Previously, only `wal_writer_delay` triggered WAL flushing to disk; now filling a WAL buffer also triggers WAL writes.

- Allow backends to detect postmaster death via a pipe read failure, rather than polling (Peter Geoghegan, Heikki Linnakangas, Tom Lane)

  The wait events are internally called "latches".

- Allow the bgwriter, walwriter, statistics collector, archiver, and checkpointer to sleep more efficiently during periods of inactivity (Peter Geoghegan, Tom Lane)

  This reduces CPU wake-ups, which dramatically reduces power consumption on idle servers.

- Pass the safe number of file descriptors to Windows processes (Heikki Linnakangas)

  This allows Windows sessions to cache more open file descriptors.

#### E.1.3.1.3. Optimizer

- Improve the ability of the planner to choose parameterized plans (Tom Lane)

  A prepared statement is now parsed, analyzed, and rewritten, but not necessarily planned. When the prepared plan is executed with parameters, the planner might replan it for every constant, or it might execute a generic plan if its cost is close to that of a constant-specific plan. CLARIFY

- Allow the optimizer to use `CHECK` constraints defined on tables referenced in subqueries in planning decisions (Tom Lane)

  This is only enabled when `constraint_exclusion = on`.

- Allow `indexedcol op ANY(ARRAY[...])` conditions to be used in plain indexscans (Tom Lane)

- Improve heuristics for determining the type of unknown values by considering other data types involved in the operation (Tom Lane)

- Allow better matching of index columns with non-default operator qualifications (Tom Lane)

- Improve the planner's ability to use nested loops with inner index scans (Tom Lane)

- Support `MIN`/`MAX` index optimizations on boolean columns (Marti Raudsepp)

##### E.1.3.1.3.1. Optimizer Statistics

- Improve the ability to use statistics on columns referenced in subqueries (Tom Lane)

- Improve optimizer estimation for subqueries using `DISTINCT` (Tom Lane)

- Collect and use element-frequency statistics for arrays (Alexander Korotkov, Tom Lane)

  This patch improves selectivity estimation for the array <@, &&, and @> (containment and overlaps) array operators.

- Improve costing of partial indexes (Tom Lane)

- Allow analyze statistics to be collected for foreign tables (Etsuro Fujita)

#### E.1.3.1.4. Authentication

- Do not assume roles and `samerole` specified in `pg_hba.conf` include superusers (Andrew Dunstan)

  This makes it easier to use reject lines with group roles.

- Make superuser imply replication privilege (Noah Misch)

  This avoids the need to explicitly assign such privileges.

- Adjust `pg_hba.conf` processing to more consistently handle token parsing (Brendan Jurd, Álvaro Herrera)

- Disallow empty `pg_hba.conf` files (Tom Lane)

  This was done to more quickly report misconfiguration.

#### E.1.3.1.5. Monitoring

- Attempt to log the current query string before a backend crash (Marti Raudsepp)

- Make logging of autovacuum I/O activity more verbose (Greg Smith, Noah Misch)

  This logging is triggered by `log_autovacuum_min_duration`.

- Have WAL replay report failures sooner (Fujii Masao)

  There were some cases where failures were only reported once the server went into master mode.

- Add `pg_xlog_location_diff()` to simplify xlog comparisons (Euler Taveira de Oliveira)

  This is useful for computing replication lag.

- Support configurable eventlog application names on Windows (MauMau, Magnus Hagander)

  This allows different instances to use the eventlog with different identifiers, by setting the `event_source` GUC, which is similar to how `syslog_ident` works.

- Change "unexpected EOF" message to `DEBUG1` level, except when there is an open transaction (Magnus Hagander)

#### E.1.3.1.6. Statistical Views

- Track temporary file sizes and file counts in the `pg_stat_database` system view (Tomas Vondra)

- Add a deadlock counter to the `pg_stat_database` system view (Magnus Hagander)

- Add a GUC setting `track_io_timing` to track I/O timings (Ants Aasma, Robert Haas)

- Report checkpoint timing information in `pg_stat_bgwriter` (Greg Smith, Peter Geoghegan)

#### E.1.3.1.7. Server Settings

- Allow `deadlock_timeout` to be set per-session by superusers, not just per-cluster (Noah Misch)

  This allows `deadlock_timeout` to be reduced for transactions that are likely to be involved in a deadlock, thus detecting it more quickly. It can also be used to reduce the chances of a session being chosen for cancellation due to a deadlock.

- Add GUC parameter `temp_file_limit` to constrain temporary file space usage per session (Mark Kirkwood)

- Add `postgresql.conf` category "Replication / Sending Servers" (Fujii Masao)

- Allow a superuser to `SET` an extension's superuser-only custom variable before loading the associated extension (Tom Lane)

  The system now remembers whether a `SET` was performed by a superuser.

- Add postmaster `-C` option to query configuration parameters (Bruce Momjian)

  This allows pg_ctl to better handle configuration-only directory installations.

- Force the locale to be recorded more specifically, rather than using the empty string to mean the default (Tom Lane)

##### E.1.3.1.7.1. `postgresql.conf`

- Allow multiple errors in `postgresql.conf` to be reported, rather than just the first one (Alexey Klyukin, Tom Lane)

- Allow a reload of `postgresql.conf` to be processed by all backends even if there are some settings that are invalid for that session (Alexey Klyukin)

  Previously, such not-valid-for-session errors would cause all setting changes to be ignored by that backend.

- Add an `include_if_exists` facility for configuration files (Greg Smith)

  This works the same as `include`, except that an error is not thrown if the file is missing.

- Check the server time zone during initdb, and set `postgresql.conf` values `timezone` and `log_timezone` accordingly (Tom Lane)

  This avoids expensive time zone probes during server start.

- Allow Windows to report `postgresql.conf` line numbers in `pg_settings` (Tom Lane)

### E.1.3.2. Replication and Recovery

#### E.1.3.2.1. Streaming Replication

- Allow streaming replications slaves to serve other slaves (Fujii Masao)

  Previously, only the master server could supply streaming replication log files to standby servers. Slaves can also now do continuous archiving. (?)

- Add new `synchronous_commit` replication mode `remote_write` (Fujii Masao, Simon Riggs)

  This waits for the remote server to acknowledge that it has received the data, but does not wait for it to be written to the remote disk.

- Add a pg_receivexlog tool to archive xlog file changes as they are written, rather than waiting for completed xlog files (Magnus Hagander)

- Send keepalive messages to standby servers (Simon Riggs)

  This allows administrators to accurately calculate streaming replication transfer and application latency.

##### E.1.3.2.1.1. pg_basebackup

- Allow streaming of xlog files while pg_basebackup is performing a backup (Magnus Hagander)

  This allows passing of xlog files to the standby before they are discarded on the primary.

- Allow pg_basebackup to pull from standby servers (Jun Ishizuka, Fujii Masao)

- Exclude the postmaster.opts file from pg_basebackup backups (Magnus Hagander)

### E.1.3.3. Queries

- Improve columns labels produced by subqueries (Marti Raudsepp)

  Previously, the generic label `?column?` was used.

- Preserve column names in row expressions (Andrew Dunstan, Tom Lane)

- Cancel queries if clients get disconnected (Florian Pflug)

- Silently ignore nonexistent schemas specified in `search_path` (Tom Lane)

- Warn about casts to and from domain types (Robert Haas)

  Such casts have no effect.

- When a row fails a `CHECK` or `NOT NULL` constraint, show the row's contents in errdetail (Jan Kundrát)

  This should make it easier to identify which row is problematic when an insert or update is processing many rows.

### E.1.3.4. Object Manipulation

- Provide more reliable operation during concurrent DDL (Robert Haas, Noah Misch)

  This eliminates "cache lookup failed" errors in many scenarios. Also, it is no longer possible to add relations to a schema which is being concurrently dropped, a scenario that formerly led to inconsistent system catalog contents.

- Add `CONCURRENTLY` option to `DROP INDEX CONCURRENTLY` (Simon Riggs)

  This allows index removal without blocking other sessions.

#### E.1.3.4.1. Constraints

- Allow `CHECK` constraints to be declared `NOT VALID` (Álvaro Herrera)

  A `NOT VALID` `CHECK` constraint can be added to a table without the table's data being verified against the constraint. New rows are verified.

  Such constraints are ignored by the planner when considering `constraint_exclusion`. `ALTER TABLE` / `VALIDATE` allows `NOT VALID` `CHECK` check constraints to be fully validated and considered for `constraint_exclusion`, including re-planning of cached plans.

- Allow `CHECK` constraints to be declared `NO INHERIT` (Nikhil Sontakke, Alex Hunsaker, Álvaro Herrera)

  This makes them enforceable only on the parent table, not on child tables.

- Add the ability to rename constraints (Peter Eisentraut)

#### E.1.3.4.2. `ALTER`

- Reduce need to rebuild tables and indexes for various `ALTER TABLE` operations (Noah Misch)

  Increasing the length constraint on a `varchar` or `varbit` column, or removing it altogether, no longer requires a table rewrite. Increasing the allowable precision of a `numeric` constraint, or changing a column from constrained `numeric` to unconstrained numeric, no longer requires a table rewrite. Table rewrites are also avoided in similar cases involving the `interval`, `timestamp`, and `timestamptz` types.

- Avoid having `ALTER TABLE` revalidate foreign key constraints in some cases where it is not necessary (Noah Misch)

- Add `IF EXISTS` clause to `ALTER` commands (Pavel Stehule)

  For example, `ALTER FOREIGN TABLE IF EXISTS foo RENAME TO bar`.

- Add `ALTER FOREIGN DATA WRAPPER` / `RENAME` and `ALTER SERVER` / `RENAME` (Peter Eisentraut)

- Add `ALTER DOMAIN` / `RENAME` (Peter Eisentraut)

  You could already rename domains using `ALTER TYPE`.

- Throw an error for `ALTER DOMAIN` / `DROP CONSTRAINT` on a nonexistent constraint (Peter Eisentraut)

  An `IF EXISTS` option has been added to enable the previous behavior.

#### E.1.3.4.3. `CREATE/ALTER TABLE`

- Fix `CREATE TABLE` ... `AS EXECUTE` to handle `WITH NO DATA` and column name specification (Tom Lane)

- Support `CREATE TABLE (LIKE ...)` from foreign tables and views (Peter Eisentraut)

- Allow `CREATE TABLE (LIKE ...)` from composite type (Peter Eisentraut)

  This also allows a table to be created whose schema matches a view.

#### E.1.3.4.4. Object Permissions

- Add a `security_barrier` option for views (KaiGai Kohei, Robert Haas)

  This prevents `security_barrier` views from being moved into other scopes, preventing possible leakage of view-prohibited data. Such views might perform more poorly.

- Add a new `LEAKPROOF` function specification to mark functions that can be safely pushed down into `security_barrier` views (KaiGai Kohei)

- Add support for privileges on date types (Peter Eisentraut)

  This adds support for the SQL-conforming `USAGE` privilege on types and domains. The intent is to be able restrict which users can create dependencies on types, which restricts the way in which owners can alter types.

- Check for `INSERT` privileges in `SELECT INTO` / `CREATE TABLE AS` (KaiGai Kohei)

  Because the object is being created by `SELECT INTO` or `CREATE TABLE AS`, it will have default insert permissions, except if `ALTER DEFAULT PRIVILEGES` has removed such permissions, hence the need for a permission check.

### E.1.3.5. Utility Operations

- Allow vacuum to more easily skip pages that cannot be locked (Simon Riggs, Robert Haas)

  This change should greatly reduce the incidence of vacuum getting "stuck".

#### E.1.3.5.1. `EXPLAIN`

- Make `EXPLAIN (BUFFERS)` track blocks dirtied and written (Robert Haas)

- Have `EXPLAIN ANALYZE` report the number of rows rejected by filter steps (Marko Tiikkaja)

- Allow `EXPLAIN` to avoid timing overhead when time values are unwanted (Tomas Vondra)

  This is accomplished by setting `TIMING` to `FALSE`.

### E.1.3.6. Data Types

- Add a `JSON` data type (Robert Haas)

  This stores JSON data as text after proper validation.

- Add `array_to_json()` and `row_to_json()` (Andrew Dunstan)

- Add support for range data types (Jeff Davis, Tom Lane, Alexander Korotkov)

  The range data type records a lower and upper bound, and supports operations like contains, overlaps, and intersection.

- Add `SMALLSERIAL` data type (Mike Pultz)

  This is like `SERIAL`, except it stores the sequence in a two-byte integer column (int2).

- Allow domains to be declared `NOT VALID` (Álvaro Herrera)

  This can be set at domain creation time, or via `ALTER DOMAIN` / `ADD CONSTRAINT / NOT VALID`. `ALTER DOMAIN` / `VALIDATE CONSTRAINT` fully validates the domain.

- Support more locale-specific formatting options for the MONEY data type (Tom Lane)

  Specifically, display the plus/minus sign, currency symbol, and separators in a locale-specific order. CORRECT?

- Add bitwise "and", "or", and "not" operators for the macaddr data type (Brendan Jurd)

#### E.1.3.6.1. XML

- Allow `xpath()` to return a single-element XML array when supplied a scalar value (Florian Pflug)

  Previously, it returned an empty array. This change will also cause `xpath_exists()` to return true, not false, for such expressions.

- Improve XML error handling to be more robust (Florian Pflug)

### E.1.3.7. Functions

- Allow non-superusers to use `pg_cancel_backend()` and `pg_terminate_backend()()` on other sessions for the same user (Magnus Hagander, Josh Kupershmidt, Dan Farina)

  Previously only the superuser could use these functions.

- Allow `string_agg()` to process bytea values (Pavel Stehule)

- Allow regular expressions to use back-references (Tom Lane)

  For example, '^(\w+)( \1)+$'.

- Allow importing and exporting of transaction snapshots (Joachim Wieland, Tom Lane)

  Snapshots are exported via `pg_export_snapshot()`, and imported via `SET TRANSACTION SNAPSHOT`. Only snapshots of currently-running transactions can be imported.

- Support `COLLATION FOR` on expressions (Peter Eisentraut)

  This returns a string representing the collation of the expression.

- Add a `NUMERIC` variant of `pg_size_pretty()` for use with `pg_xlog_location_diff()` (Fujii Masao)

#### E.1.3.7.1. Function and Trigger Creation

- Change default names of triggers to fire "action" triggers before "check" triggers (Tom Lane)

  This allows default-named check triggers to check post-action rows.

- Add a `pg_trigger_depth()` function (Kevin Grittner)

  This reports the current trigger call depth.

#### E.1.3.7.2. Object Information Functions

- Add `pg_opfamily_is_visible()` (Josh Kupershmidt)

- Allow per-column foreign data wrapper options and `force_not_null` (Shigeru Hanada)

  This allows SQL specification of per-column information useful to foreign data sources. Includes psql option display support.

- Improve pretty printing of view definitions (Andrew Dunstan)

#### E.1.3.7.3. Information Schema

- Add `information_schema` views `role_udt_grants`, `udt_privileges`, and `user_defined_types` (Peter Eisentraut)

- Add composite-type attributes to the `information_schema`.`element_types` view (Peter Eisentraut)

- Implement information schema `interval_type` columns (Peter Eisentraut)

- Add collation columns to various information schema views (Peter Eisentraut)

  Specifically, provide collation columns for views referencing attributes, columns, domains, and `element_types`.

- Add display of sequence `USAGE` privileges to information schema (Peter Eisentraut)

- Add a `with_hierarchy` column to the information schema privileges display (Peter Eisentraut)

- Have the information schema show default privileges (Peter Eisentraut)

  Previously, default permissions generated `NULL` fields. (WAS IT NULL?)

### E.1.3.8. Server-Side Languages

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

- Allow the PL/pgSQL `OPEN` cursor command to supply parameters by name (Yeb Havinga)

- Add a `GET STACKED DIAGNOSTICS` PL/pgSQL command to retrieve exception info (Pavel Stehule)

- Speed up PL/pgSQL array assignment by caching type information (Pavel Stehule)

- Improve performance and memory consumption of the PL/pgSQL `ELSIF` clauses (Tom Lane)

- Output the function signature, not just the name, in PL/pgSQL error messages (Pavel Stehule)

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

- Add PL/Python SPI cursor support (Jan Urbanski)

  This allows PL/Python to read partial result sets.

- Add result metadata functions to PL/Python (Peter Eisentraut)

  Specifically, this adds result object functions .colnames, .coltypes, and .coltypmods.

#### E.1.3.8.3. SQL Server-Side Language

- Allow SQL-language functions to reference parameters by name (Matthew Draper)

  To use this, simply name the function arguments and reference the argument names in the SQL function body.

### E.1.3.9. Client Applications

- Add initdb options `--auth-local` and `--auth-host` (Peter Eisentraut)

  This allows individual control of `local` and `host` `pg_hba.conf` authentication settings. `--auth` still controls both.

- Add `--replication`/`--no-replication` flags to createuser to control replication permission (Fujii Masao)

- Add the `--if-exists` option to dropdb and dropuser (Josh Kupershmidt)

- Give command-line tools the ability to specify the name of the database to connect to, and fall back to `template1` if a `postgres` database connection fails (Robert Haas)

#### E.1.3.9.1. psql

- Add psql mode to auto-expand output based on the display width (Peter Eisentraut)

  This adds the `auto` option to the `\x` command, which switches to the expanded mode when the normal output would be wider than the screen.

- Allow inclusion of psql files relative to the directory of the input file from which it was invoked (Gurjeet Singh)

  The psql command is `\ir`.

- Add support for non-ASCII characters in psql variable names (Tom Lane)

- Add support for major-version-specific `.psqlrc` files (Bruce Momjian)

  psql already supported minor-version-specific `.psqlrc` files.

- Provide environment variable overrides for psql history and startup file locations (Andrew Dunstan)

  Specifically, `PSQL_HISTORY` and `PSQLRC`.

- Add a psql `\setenv` command to modify the environment variables used by child processes (Andrew Dunstan)

  This is useful in `.psqlrc` files.

- Have psql temporary editor files use a `.sql` extension (Peter Eisentraut)

- Allows psql to use zero-byte field and record separators (Peter Eisentraut)

  Various shell tools use zero/null-byte separators, e.g. find.

- Have psql `\timing` report times for failed queries (Magnus Hagander)

  Previously only successful query times were reported.

- Unify and tighten psql treatment of `\copy` and plain `COPY` (Noah Misch)

  This makes failure behavior more predictable and honors `\set ON_ERROR_ROLLBACK`.

#### E.1.3.9.2. Informational Commands

- Make psql `\d` on a sequence show the table/column name owning it (Magnus Hagander)

- Show statistics target for columns in psql `\d+` (Magnus Hagander)

- Show role password expiration dates in psql `\du` (Fabrízio de Royes Mello)

##### E.1.3.9.2.1. Comments

- Allow psql to display comments for languages and casts (Josh Kupershmidt)

  These are output via `\dL` and `\dC`.

- Allow psql to display comments for conversions and domains (Josh Kupershmidt)

  This is accomplished with the addition of `+` to `\dc` and `\dD` commands.

- Allow psql to display the comments on SQL/MED objects in verbose mode (Josh Kupershmidt)

- Change psql's `\dd` command to display only objects without their own backslash command (Josh Kupershmidt)

#### E.1.3.9.3. Tab Completion

- In psql tab completion, complete SQL keywords based on `COMP_KEYWORD_CASE` setting and the perhaps case of the partially-supplied word (Peter Eisentraut)

- Add psql tab completion support for `EXECUTE` (Andreas Karlsson)

- Allow tab completion of role references in `GRANT`/`REVOKE` (Peter Eisentraut)

- Allow tab completion of file names to supply quotes, when necessary (Noah Misch)

- Change psql tab completion support for `TABLE` to also display views (Magnus Hagander)

#### E.1.3.9.4. pg_dump

- Add an `--exclude-table-data` option to pg_dump (Andrew Dunstan)

  This allows dumping of a table's definition but not its data, on a per-table basis.

- Add a `--section` option to pg_dump and pg_restore (Andrew Dunstan)

  Valid values are `pre-data`, `data`, and `post-data`. The option can be given more than once. `--schema-only` is equivalent to `--section=pre-data --section=post-data`. `--data-only` is equivalent to `--section=data`.

- Have pg_dumpall dump all roles first, then all configuration settings on roles (Phil Sorber)

  This allows a role's configuration settings to rely on other roles without generating an error.

- Allow pg_dumpall to avoid errors if the `postgres` database is missing in the new cluster (Robert Haas)

- Dump foreign server user mappings in user name order (Peter Eisentraut)

  This helps produce deterministic dump files.

- Dump operators in a predictable order (Peter Eisentraut)

- Tighten rules for when extension configuration tables are dumped by pg_dump (Tom Lane)

### E.1.3.10. libpq

- Add URIs support to libpq connection functions (Alexander Shulgin)

  The syntax begins with `postgres://`.

- Add a libpq connection option to disable SSL compression (Laurenz Albe)

  This can be used to remove the overhead of SSL compression on fast networks.

- Add a single-row processing mode to libpq for better handling of large result sets (Kyotaro Horiguchi, Marko Kreen)

  Previously, libpq always collected the entire query result in memory before passing it back to the application.

- Enhance `const` qualifiers in the libpq functions PQconnectdbParams, PQconnectStartParams, and PQpingParams (Lionel Elie Mamane)

- Allow libpq's `.pgpass` file to process escaped characters in the password field (Robert Haas)

- Have library functions use `abort()` instead of `exit()` to terminate the executable (Peter Eisentraut)

  This does not interfere with the normal exit codes used by the program, and generates a signal that can be caught by the caller.

- Fix a libpq memory leak by freeing client encoding connection parameters (Peter Eisentraut)

### E.1.3.11. Source Code

- Add support for building with MS Visual Studio 2010 (Brar Piening)

- Enable compiling with the MinGW-w64 32-bit compiler (Lars Kanis)

- Install `plpgsql.h` into `include/server` during installation (Heikki Linnakangas)

- Use C flexible array members, where supported (Peter Eisentraut)

- Improve the concurrent transaction regression tests (isolationtester) (Noah Misch)

- Modify thread_test to create its test files in the current directory, rather than /tmp (Bruce Momjian)

- Improve flex and bison warning and error reporting (Tom Lane)

- Add memory barrier support (Robert Haas)

  This is currently unused.

- Modify pgindent to use a typedef file (Bruce Momjian)

- Add a hook for processing messages due to be sent to the server log (Martin Pihlak)

- Add object access hooks for `DROP` commands (KaiGai Kohei)

- Centralize `DROP` handling for some object types (KaiGai Kohei)

- Add a pg_upgrade test suite (Peter Eisentraut)

- Sync regular expression code with TCL 8.5.11 and improve internal processing (Tom Lane)

- Move CRC tables to libpgport, and provide them in a separate include file (Daniel Farina)

- Add options to git_changelog for use in major release note creation (Bruce Momjian)

- Remove dead ports (Peter Eisentraut)

  The removed ports are: dgux, nextstep, sunos4, svr4, ultrix4, univel, bsdi.

### E.1.3.12. Modules

- Improve efficiency of dblink by using libpq's new single-row processing mode (Kyotaro Horiguchi, Marko Kreen)

  This improvement does not apply to `dblink_send_query()`/`dblink_get_result()`.

- Add tcn (triggered change notification) to generate `NOTIFY` events on table changes (Kevin Grittner)

- Implement dry-run mode for pg_archivecleanup (Gabriele Bartolini)

  This only outputs the names of files to be deleted.

- Add new pgbench switches `--unlogged-tables`, `--tablespace`, and `--index-tablespace` (Robert Haas)

- Have vacuumlo remove large objects in batches, to reduce locking (Tim Lewis, Tom Lane)

  The new `-l` option allows changing the number of large objects removed per transaction.

- Change pg_test_fsync to test for a fixed amount of time, rather than a fixed number of cycles (Bruce Momjian)

  The `-o`/cycles option was removed, and `-s`/seconds added.

- Add a pg_test_timing utility to measure clock monotonicity and timing overhead (Ants Aasma, Greg Smith)

#### E.1.3.12.1. pg_upgrade

- Allow pg_upgrade to upgrade an old cluster that does not have a `postgres` database (Bruce Momjian)

- Allow pg_upgrade to handle cases where some old or new databases are missing, as long as they are empty (Bruce Momjian)

- Allow pg_upgrade to handle configuration-only directory installations (Bruce Momjian)

- In pg_upgrade, add `-o`/`-O` options to pass parameters to the servers (Bruce Momjian)

  This is useful for configuration-only directory installs.

- Change pg_upgrade to use port 50432 by default (Bruce Momjian)

  This helps avoid unintended client connections during the upgrade.

- Overhaul pg_upgrade logging and failure reporting (Bruce Momjian)

  Create four append-only log files, and delete them on success. Add `-r`/`--retain` option to unconditionally retain these files. Also remove pg_upgrade options `-g`/`-G`/`-l` options as unnecessary, and tighten log file permissions.

- Adjust pg_upgrade environment variables (Bruce Momjian)

  Rename data, bin, and port environment variables to begin with `PG`, and support `PGPORTOLD`/`PGPORTNEW`, to replace `PGPORT`. INCOMPATIBILITY?

- Have pg_upgrade create a script to incrementally generate more accurate optimizer statistics (Bruce Momjian)

  This reduces the time needed to generate minimal cluster statistics after an upgrade.

- Reduce cluster locking in pg_upgrade (Bruce Momjian)

  Specifically, only lock the old cluster if link mode is used, and do it right after the schema is restored.

#### E.1.3.12.2. pg_stat_statements

- Allow pg_stat_statements to aggregate similar queries via SQL text normalisation (Peter Geoghegan, Tom Lane)

  Users with applications that use non-parameterized SQL will now be able to monitor query performance without using log file analysis.

- Add dirtied and written block counts and read/write times to pg_stat_statements (Robert Haas, Ants Aasma)

- Prevent pg_stat_statements from double-counting `PREPARE` and `EXECUTE` commands (Tom Lane)

#### E.1.3.12.3. sepgsql

- Support `SECURITY LABEL` on global objects (KaiGai Kohei, Robert Haas)

  Specifically, add `SECURITY LABEL`s to databases, tablespaces, and roles.

- Allow sepgsql to honor database labels (KaiGai Kohei)

- Perform sepgsql permission checks during the creation of various objects (KaiGai Kohei)

- Add `sepgsql_setcon()` and related functions to control the sepgsql security domain (KaiGai Kohei)

- Add a userspace access cache to sepgsql to improve performance (KaiGai Kohei)

### E.1.3.13. Documentation

- Add a rule to optionally build docs with the stylesheet from the website (Magnus Hagander)

  Use `make STYLE=website draft`.

- Improve `EXPLAIN` documentation (Tom Lane)

- Document that user/database names are preserved with double-quoting by command-line tools like vacuumdb (Bruce Momjian)

- Improve the foreign data wrapper API and documentation (Hanada Shigeru)

- Document the actual string returned by the client for MD5 authentication (Cyan Ogilvie)

Prev | Home | Next
Release Notes | Up | Release 9.1.5