E.119. Release 8.1

Improve GiST and R-tree index performance (Neil)

Improve the optimizer, including auto-resizing of hash joins (Tom)

Overhaul internal API in several areas

Change WAL record CRCs from 64-bit to 32-bit (Tom)

We determined that the extra cost of computing 64-bit CRCs was significant, and the gain in reliability too marginal to justify it.

Prevent writing large empty gaps in WAL pages (Tom)

Improve spinlock behavior on SMP machines, particularly Opterons (Tom)

Allow nonconsecutive index columns to be used in a multicolumn index (Tom)

For example, this allows an index on columns a,b,c to be used in a query with WHERE a = 4 and c = 10.

Skip WAL logging for CREATE TABLE AS / SELECT INTO (Simon)

Since a crash during CREATE TABLE AS would cause the table to be dropped during recovery, there is no reason to WAL log as the table is loaded. (Logging still happens if WAL archiving is enabled, however.)

Allow concurrent GiST index access (Teodor, Oleg)

Add configuration parameter full_page_writes to control writing full pages to WAL (Bruce)

To prevent partial disk writes from corrupting the database, PostgreSQL™ writes a complete copy of each database disk page to WAL the first time it is modified after a checkpoint. This option turns off that functionality for more speed. This is safe to use with battery-backed disk caches where partial page writes cannot happen.

Use O_DIRECT if available when using O_SYNC for wal_sync_method (Itagaki Takahiro)

O_DIRECT causes disk writes to bypass the kernel cache, and for WAL writes, this improves performance.

Improve COPY FROM performance (Alon Goldshuv)

This was accomplished by reading COPY input in larger chunks, rather than character by character.

Improve the performance of COUNT(), SUM, AVG(), STDDEV(), and VARIANCE() (Neil, Tom)

Prevent problems due to transaction ID (XID) wraparound (Tom)

The server will now warn when the transaction counter approaches the wraparound point. If the counter becomes too close to wraparound, the server will stop accepting queries. This ensures that data is not lost before needed vacuuming is performed.

Fix problems with object IDs (OIDs) conflicting with existing system objects after the OID counter has wrapped around (Tom)

Add warning about the need to increase max_fsm_relations and max_fsm_pages during VACUUM (Ron Mayer)

Add temp_buffers configuration parameter to allow users to determine the size of the local buffer area for temporary table access (Tom)

Add session start time and client IP address to pg_stat_activity (Magnus)

Adjust pg_stat views for bitmap scans (Tom)

The meanings of some of the fields have changed slightly.

Enhance pg_locks view (Tom)

Log queries for client-side PREPARE and EXECUTE (Simon)

Allow Kerberos name and user name case sensitivity to be specified in postgresql.conf (Magnus)

Add configuration parameter krb_server_hostname so that the server host name can be specified as part of service principal (Todd Kover)

If not set, any service principal matching an entry in the keytab can be used. This is new Kerberos matching behavior in this release.

Add log_line_prefix options for millisecond timestamps (%m) and remote host (%h) (Ed L.)

Add WAL logging for GiST indexes (Teodor, Oleg)

GiST indexes are now safe for crash and point-in-time recovery.

Remove old *.backup files when we do pg_stop_backup() (Bruce)

This prevents a large number of *.backup files from existing in pg_xlog/.

Add configuration parameters to control TCP/IP keep-alive times for idle, interval, and count (Oliver Jowett)

These values can be changed to allow more rapid detection of lost client connections.

Add per-user and per-database connection limits (Petr Jelinek)

Using ALTER USER and ALTER DATABASE, limits can now be enforced on the maximum number of sessions that can concurrently connect as a specific user or to a specific database. Setting the limit to zero disables user or database connections.

Allow more than two gigabytes of shared memory and per-backend work memory on 64-bit machines (Koichi Suzuki)

New system catalog pg_pltemplate allows overriding obsolete procedural-language definitions in dump files (Tom)

Add temporary views (Koju Iijima, Neil)

Fix HAVING without any aggregate functions or GROUP BY so that the query returns a single group (Tom)

Previously, such a case would treat the HAVING clause the same as a WHERE clause. This was not per spec.

Add USING clause to allow additional tables to be specified to DELETE (Euler Taveira de Oliveira, Neil)

In prior releases, there was no clear method for specifying additional tables to be used for joins in a DELETE statement. UPDATE already has a FROM clause for this purpose.

Add support for \x hex escapes in backend and ecpg strings (Bruce)

This is just like the standard C \x escape syntax. Octal escapes were already supported.

Add BETWEEN SYMMETRIC query syntax (Pavel Stehule)

This feature allows BETWEEN comparisons without requiring the first value to be less than the second. For example, 2 BETWEEN [ASYMMETRIC] 3 AND 1 returns false, while 2 BETWEEN SYMMETRIC 3 AND 1 returns true. BETWEEN ASYMMETRIC was already supported.

Add NOWAIT option to SELECT ... FOR UPDATE/SHARE (Hans-Juergen Schoenig)

While the statement_timeout configuration parameter allows a query taking more than a certain amount of time to be canceled, the NOWAIT option allows a query to be canceled as soon as a SELECT ... FOR UPDATE/SHARE command cannot immediately acquire a row lock.

Track dependencies of shared objects (Alvaro)

PostgreSQL™ allows global tables (users, databases, tablespaces) to reference information in multiple databases. This addition adds dependency information for global tables, so, for example, user ownership can be tracked across databases, so a user who owns something in any database can no longer be removed. Dependency tracking already existed for database-local objects.

Allow limited ALTER OWNER commands to be performed by the object owner (Stephen Frost)

Prior releases allowed only superusers to change object owners. Now, ownership can be transferred if the user executing the command owns the object and would be able to create it as the new owner (that is, the user is a member of the new owning role and that role has the CREATE permission that would be needed to create the object afresh).

Add ALTER object SET SCHEMA capability for some object types (tables, functions, types) (Bernd Helmle)

This allows objects to be moved to different schemas.

Add ALTER TABLE ENABLE/DISABLE TRIGGER to disable triggers (Satoshi Nagayasu)

Allow TRUNCATE to truncate multiple tables in a single command (Alvaro)

Because of referential integrity checks, it is not allowed to truncate a table that is part of a referential integrity constraint. Using this new functionality, TRUNCATE can be used to truncate such tables, if both tables involved in a referential integrity constraint are truncated in a single TRUNCATE command.

Properly process carriage returns and line feeds in COPY CSV mode (Andrew)

In release 8.0, carriage returns and line feeds in CSV COPY TO were processed in an inconsistent manner. (This was documented on the TODO list.)

Add COPY WITH CSV HEADER to allow a header line as the first line in COPY (Andrew)

This allows handling of the common CSV usage of placing the column names on the first line of the data file. For COPY TO, the first line contains the column names, and for COPY FROM, the first line is ignored.

On Windows, display better sub-second precision in EXPLAIN ANALYZE (Magnus)

Add trigger duration display to EXPLAIN ANALYZE (Tom)

Prior releases included trigger execution time as part of the total execution time, but did not show it separately. It is now possible to see how much time is spent in each trigger.

Add support for \x hex escapes in COPY (Sergey Ten)

Previous releases only supported octal escapes.

Make SHOW ALL include variable descriptions (Matthias Schmidt)

SHOW varname still only displays the variable's value and does not include the description.

Make initdb create a new standard database called postgres, and convert utilities to use postgres rather than template1 for standard lookups (Dave)

In prior releases, template1 was used both as a default connection for utilities like createuser, and as a template for new databases. This caused CREATE DATABASE to sometimes fail, because a new database cannot be created if anyone else is in the template database. With this change, the default connection database is now postgres, meaning it is much less likely someone will be using template1 during CREATE DATABASE.

Create new reindexdb command-line utility by moving /contrib/reindexdb into the server (Euler Taveira de Oliveira)

Add MAX() and MIN() aggregates for array types (Koju Iijima)

Fix to_date() and to_timestamp() to behave reasonably when CC and YY fields are both used (Karel Zak)

If the format specification contains CC and a year specification is YYY or longer, ignore the CC. If the year specification is YY or shorter, interpret CC as the previous century.

Add md5(bytea) (Abhijit Menon-Sen)

md5(text) already existed.

Add support for numeric ^ numeric based on power(numeric, numeric)

The function already existed, but there was no operator assigned to it.

Fix NUMERIC modulus by properly truncating the quotient during computation (Bruce)

In previous releases, modulus for large values sometimes returned negative results due to rounding of the quotient.

Add a function lastval() (Dennis Björklund)

lastval() is a simplified version of currval(). It automatically determines the proper sequence name based on the most recent nextval() or setval() call performed by the current session.

Add to_timestamp(DOUBLE PRECISION) (Michael Glaesemann)

Converts Unix seconds since 1970 to a TIMESTAMP WITH TIMEZONE.

Add pg_postmaster_start_time() function (Euler Taveira de Oliveira, Matthias Schmidt)

Allow the full use of time zone names in AT TIME ZONE, not just the short list previously available (Magnus)

Previously, only a predefined list of time zone names were supported by AT TIME ZONE. Now any supported time zone name can be used, e.g.:

SELECT CURRENT_TIMESTAMP AT TIME ZONE 'Europe/London';

In the above query, the time zone used is adjusted based on the daylight saving time rules that were in effect on the supplied date.

Add GREATEST() and LEAST() variadic functions (Pavel Stehule)

These functions take a variable number of arguments and return the greatest or least value among the arguments.

Add pg_column_size() (Mark Kirkwood)

This returns storage size of a column, which might be compressed.

Add regexp_replace() (Atsushi Ogawa)

This allows regular expression replacement, like sed. An optional flag argument allows selection of global (replace all) and case-insensitive modes.

Fix interval division and multiplication (Bruce)

Previous versions sometimes returned unjustified results, like '4 months'::interval / 5 returning '1 mon -6 days'.

Fix roundoff behavior in timestamp, time, and interval output (Tom)

This fixes some cases in which the seconds field would be shown as 60 instead of incrementing the higher-order fields.

Add a separate day field to type interval so a one day interval can be distinguished from a 24 hour interval (Michael Glaesemann)

Days that contain a daylight saving time adjustment are not 24 hours long, but typically 23 or 25 hours. This change creates a conceptual distinction between intervals of « so many days » and intervals of « so many hours ». Adding 1 day to a timestamp now gives the same local time on the next day even if a daylight saving time adjustment occurs between, whereas adding 24 hours will give a different local time when this happens. For example, under US DST rules:

'2005-04-03 00:00:00-05' + '1 day' = '2005-04-04 00:00:00-04'
'2005-04-03 00:00:00-05' + '24 hours' = '2005-04-04 01:00:00-04'

Add justify_days() and justify_hours() (Michael Glaesemann)

These functions, respectively, adjust days to an appropriate number of full months and days, and adjust hours to an appropriate number of full days and hours.

Move /contrib/dbsize into the backend, and rename some of the functions (Dave Page, Andreas Pflug)

pg_total_relation_size() includes indexes and TOAST tables.

Add functions for read-only file access to the cluster directory (Dave Page, Andreas Pflug)

Add pg_reload_conf() to force reloading of the configuration files (Dave Page, Andreas Pflug)

Add pg_rotate_logfile() to force rotation of the server log file (Dave Page, Andreas Pflug)

Change pg_stat_* views to include TOAST tables (Tom)

Rename some encodings to be more consistent and to follow international standards (Bruce)

The original names still work.

Add support for WIN1252 encoding (Roland Volkmann)

Add support for four-byte UTF8 characters (John Hansen)

Previously only one, two, and three-byte UTF8 characters were supported. This is particularly important for support for some Chinese character sets.

Allow direct conversion between EUC_JP and SJIS to improve performance (Atsushi Ogawa)

Allow the UTF8 encoding to work on Windows (Magnus)

This is done by mapping UTF8 to the Windows-native UTF16 implementation.

Fix ALTER LANGUAGE RENAME (Sergey Yatskevich)

Allow function characteristics, like strictness and volatility, to be modified via ALTER FUNCTION (Neil)

Increase the maximum number of function arguments to 100 (Tom)

Allow SQL and PL/pgSQL functions to use OUT and INOUT parameters (Tom)

OUT is an alternate way for a function to return values. Instead of using RETURN, values can be returned by assigning to parameters declared as OUT or INOUT. This is notationally simpler in some cases, particularly so when multiple values need to be returned. While returning multiple values from a function was possible in previous releases, this greatly simplifies the process. (The feature will be extended to other server-side languages in future releases.)

Move language handler functions into the pg_catalog schema

This makes it easier to drop the public schema if desired.

Add SPI_getnspname() to SPI (Neil)

Overhaul the memory management of PL/pgSQL functions (Neil)

The parsetree of each function is now stored in a separate memory context. This allows this memory to be easily reclaimed when it is no longer needed.

Check function syntax at CREATE FUNCTION time, rather than at runtime (Neil)

Previously, most syntax errors were reported only when the function was executed.

Allow OPEN to open non-SELECT queries like EXPLAIN and SHOW (Tom)

No longer require functions to issue a RETURN statement (Tom)

This is a byproduct of the newly added OUT and INOUT functionality. RETURN can be omitted when it is not needed to provide the function's return value.

Add support for an optional INTO clause to PL/pgSQL's EXECUTE statement (Pavel Stehule, Neil)

Make CREATE TABLE AS set ROW_COUNT (Tom)

Define SQLSTATE and SQLERRM to return the SQLSTATE and error message of the current exception (Pavel Stehule, Neil)

These variables are only defined inside exception blocks.

Allow the parameters to the RAISE statement to be expressions (Pavel Stehule, Neil)

Add a loop CONTINUE statement (Pavel Stehule, Neil)

Allow block and loop labels (Pavel Stehule)

Allow large result sets to be returned efficiently (Abhijit Menon-Sen)

This allows functions to use return_next() to avoid building the entire result set in memory.

Allow one-row-at-a-time retrieval of query results (Abhijit Menon-Sen)

This allows functions to use spi_query() and spi_fetchrow() to avoid accumulating the entire result set in memory.

Force PL/Perl to handle strings as UTF8 if the server encoding is UTF8 (David Kamholz)

Add a validator function for PL/Perl (Andrew)

This allows syntax errors to be reported at definition time, rather than execution time.

Allow PL/Perl to return a Perl array when the function returns an array type (Andrew)

This basically maps PostgreSQL™ arrays to Perl arrays.

Allow Perl nonfatal warnings to generate NOTICE messages (Andrew)

Allow Perl's strict mode to be enabled (Andrew)

Add \set ON_ERROR_ROLLBACK to allow statements in a transaction to error without affecting the rest of the transaction (Greg Sabino Mullane)

This is basically implemented by wrapping every statement in a sub-transaction.

Add support for \x hex strings in psql variables (Bruce)

Octal escapes were already supported.

Add support for troff -ms output format (Roger Leigh)

Allow the history file location to be controlled by HISTFILE (Andreas Seltenreich)

This allows configuration of per-database history storage.

Prevent \x (expanded mode) from affecting the output of \d tablename (Neil)

Add -L option to psql to log sessions (Lorne Sunley)

This option was added because some operating systems do not have simple command-line activity logging functionality.

Make \d show the tablespaces of indexes (Qingqing Zhou)

Allow psql help (\h) to make a best guess on the proper help information (Greg Sabino Mullane)

This allows the user to just add \h to the front of the syntax error query and get help on the supported syntax. Previously any additional query text beyond the command name had to be removed to use \h.

Add \pset numericlocale to allow numbers to be output in a locale-aware format (Eugen Nedelcu)

For example, using C locale 100000 would be output as 100,000.0 while a European locale might output this value as 100.000,0.

Make startup banner show both server version number and psql's version number, when they are different (Bruce)

Also, a warning will be shown if the server and psql are from different major releases.

Add -n / --schema switch to pg_restore (Richard van den Berg)

This allows just the objects in a specified schema to be restored.

Allow pg_dump to dump large objects even in text mode (Tom)

With this change, large objects are now always dumped; the former -b switch is a no-op.

Allow pg_dump to dump a consistent snapshot of large objects (Tom)

Dump comments for large objects (Tom)

Add --encoding to pg_dump (Magnus Hagander)

This allows a database to be dumped in an encoding that is different from the server's encoding. This is valuable when transferring the dump to a machine with a different encoding.

Rely on pg_pltemplate for procedural languages (Tom)

If the call handler for a procedural language is in the pg_catalog schema, pg_dump does not dump the handler. Instead, it dumps the language using just CREATE LANGUAGE name, relying on the pg_pltemplate catalog to provide the language's creation parameters at load time.

Add a PGPASSFILE environment variable to specify the password file's filename (Andrew)

Add lo_create(), that is similar to lo_creat() but allows the OID of the large object to be specified (Tom)

Make libpq consistently return an error to the client application on malloc() failure (Neil)

Fix pgxs to support building against a relocated installation

Add spinlock support for the Itanium processor using Intel compiler (Vikram Kalsi)

Add Kerberos 5 support for Windows (Magnus)

Add Chinese FAQ (laser@pgsqldb.com)

Rename Rendezvous to Bonjour to match OS/X feature renaming (Bruce)

Add support for fsync_writethrough on Darwin (Chris Campbell)

Streamline the passing of information within the server, the optimizer, and the lock system (Tom)

Allow pg_config to be compiled using MSVC (Andrew)

This is required to build DBD::Pg using MSVC.

Remove support for Kerberos V4 (Magnus)

Kerberos 4 had security vulnerabilities and is no longer maintained.

Code cleanups (Coverity static analysis performed by EnterpriseDB)

Modify postgresql.conf to use documentation defaults on/off rather than true/false (Bruce)

Enhance pg_config to be able to report more build-time values (Tom)

Allow libpq to be built thread-safe on Windows (Dave Page)

Allow IPv6 connections to be used on Windows (Andrew)

Add Server Administration documentation about I/O subsystem reliability (Bruce)

Move private declarations from gist.h to gist_private.h (Neil)

In previous releases, gist.h contained both the public GiST API (intended for use by authors of GiST index implementations) as well as some private declarations used by the implementation of GiST itself. The latter have been moved to a separate file, gist_private.h. Most GiST index implementations should be unaffected.

Overhaul GiST memory management (Neil)

GiST methods are now always invoked in a short-lived memory context. Therefore, memory allocated via palloc() will be reclaimed automatically, so GiST index implementations do not need to manually release allocated memory via pfree().

Add /contrib/pg_buffercache contrib module (Mark Kirkwood)

This displays the contents of the buffer cache, for debugging and performance tuning purposes.

Remove /contrib/array because it is obsolete (Tom)

Clean up the /contrib/lo module (Tom)

Move /contrib/findoidjoins to /src/tools (Tom)

Remove the <<, >>, &<, and &> operators from /contrib/cube

These operators were not useful.

Improve /contrib/btree_gist (Janko Richter)

Improve /contrib/pgbench (Tomoaki Sato, Tatsuo)

There is now a facility for testing with SQL command scripts given by the user, instead of only a hard-wired command sequence.

Improve /contrib/pgcrypto (Marko Kreen)