Change the row
constructor syntax (ROW(...))
so that list elements foo.* will
be expanded to a list of their member fields, rather than
creating a nested row type field as formerly (Tom)

The new behavior is substantially more useful since it
allows, for example, triggers to check for data changes
with IF row(new.*) IS DISTINCT FROM
row(old.*). The old behavior is still available by
omitting .*.

Make row
comparisons follow SQL standard semantics and allow them
to be used in index scans (Tom)

Previously, row = and <> comparisons followed the
standard but < <= > >= did not. A row
comparison can now be used as an index constraint for a
multicolumn index matching the row value.

The former behavior conformed to the standard for simple
cases with IS NULL, but IS NOT NULL would return true if any row
field was non-null, whereas the standard says it should
return true only when all fields are non-null.

In previous releases, SET
CONSTRAINT modified all constraints with a matching
name. In this release, the schema search path is used to
modify only the first matching constraint. A schema
specification is also supported. This more nearly conforms
to the SQL standard.

Remove RULE permission for
tables, for security reasons (Tom)

As of this release, only a table's owner can create or
modify rules for the table. For backwards compatibility,
GRANT/REVOKE
RULE is still accepted, but it does nothing.

This allows applications to use the function without
including the OpenSSL headers.

C-language loadable modules must now include a PG_MODULE_MAGIC macro call for version
compatibility checking (Martijn van Oosterhout)

For security's sake, modules used by a PL/PerlU function
are no longer available to PL/Perl functions (Andrew)

Note: This also implies that data can no
longer be shared between a PL/Perl function and a
PL/PerlU function. Some Perl installations have not
been compiled with the correct flags to allow multiple
interpreters to exist within a single process. In this
situation PL/Perl and PL/PerlU cannot both be used in a
single backend. The solution is to get a Perl
installation which supports multiple interpreters.

In contrib/xml2/, rename
xml_valid() to xml_is_well_formed() (Tom)

xml_valid() will remain
for backward compatibility, but its behavior will change to
do schema checking in a future release.

In previous releases, outer joins would always be
evaluated in the order written in the query. This change
allows the query optimizer to consider reordering outer
joins, in cases where it can determine that the join
order can be changed without altering the meaning of the
query. This can make a considerable performance
difference for queries involving multiple outer joins or
mixed inner and outer joins.

This is valuable for keeping warm standby slave
servers in sync with the master. Transaction log file
switching now also happens automatically during pg_stop_backup(). This ensures that
all transaction log files needed for recovery can be
archived immediately.

Add WAL
informational functions (Simon)

Add functions for interrogating the current
transaction log insertion point and determining
WAL filenames from the
hex WAL locations
displayed by pg_stop_backup() and related
functions.

Improve recovery from a crash during WAL replay (Simon)

The server now does periodic checkpoints during
WAL recovery, so if
there is a crash, future WAL recovery is shortened. This also
eliminates the need for warm standby servers to replay
the entire log since the base backup if they crash.

Improve reliability of long-term WAL replay (Heikki, Simon, Tom)

Formerly, trying to roll forward through more than 2
billion transactions would not work due to XID
wraparound. This meant warm standby servers had to be
reloaded from fresh base backups periodically.

Add archive_timeout to force transaction
log file switches at a given interval (Simon)

Such logging now shows statement names, bind parameter
values, and the text of the query being executed. Also,
the query text is properly included in logged error
messages when enabled by log_min_error_statement.

On platforms where we can determine the actual kernel
stack depth limit (which is most), make sure that the
initial default value of max_stack_depth is safe, and reject
attempts to set it to unsafely large values.

Enable highlighting of error location in query in more
cases (Tom)

The server is now able to report a specific error
location for some semantic errors (such as unrecognized
column name), rather than just for basic syntax errors as
before.

Fix "failed to re-find parent
key" errors in VACUUM
(Tom)

Clean out pg_internal.init
cache files during server restart (Simon)

This avoids a hazard that the cache files might
contain stale data after PITR recovery.

Fix race condition for truncation of a large relation
across a gigabyte boundary by VACUUM (Tom)

Fix bug causing needless deadlock errors on row-level
locks (Tom)

Fix bugs affecting multi-gigabyte hash indexes
(Tom)

Each backend process is now its own process group
leader (Tom)

This allows query cancel to abort subprocesses invoked
from a backend or archive/recovery process.

This allows INSERT to insert
multiple rows of constants, or queries to generate result
sets using constants. For example, INSERT ... VALUES (...), (...), ...., and
SELECT * FROM (VALUES (...), (...),
....) AS alias(f1, ...).

Allow UPDATE and DELETE to
use an alias for the target table (Atsushi Ogawa)

The SQL standard does not permit an alias in these
commands, but many database systems allow one anyway for
notational convenience.

Allow UPDATE to set multiple columns with a
list of values (Susanne Ebrecht)

This is basically a short-hand for assigning the
columns and values in pairs. The syntax is UPDATE tab SET (column, ...) = (val, ...).

Make row comparisons work per standard (Tom)

The forms <, <=, >, >= now compare rows
lexicographically, that is, compare the first elements,
if equal compare the second elements, and so on. Formerly
they expanded to an AND condition across all the
elements, which was neither standard nor very useful.

This allows backslash escaping in strings to be
disabled, making PostgreSQL more standards-compliant.
The default is off for backwards
compatibility, but future releases will default this to
on.

Do not flatten subqueries that contain volatile functions in their target lists
(Jaime Casanova)

This prevents surprising behavior due to multiple
evaluation of a volatile
function (such as random()
or nextval()). It might
cause performance degradation in the presence of
functions that are unnecessarily marked as volatile.

A shell type declaration creates a type name, without
specifying any of the details of the type. Making a shell
type is useful because it allows cleaner declaration of
the type's input/output functions, which must exist
before the type can be defined "for
real". The syntax is CREATE
TYPE typename.

This was added for setting sequence-specific
permissions. GRANT ON TABLE for
sequences is still supported for backward
compatibility.

Add USAGE permission for sequences that
allows only currval() and
nextval(), not setval() (Bruce)

USAGE permission allows more
fine-grained control over sequence access. Granting
USAGE allows users to increment
a sequence, but prevents them from setting the sequence
to an arbitrary value using setval().

Previously, comments attached to databases were stored
in individual databases, making them ineffective, and
there was no provision at all for comments on roles or
tablespaces. This change adds a new shared catalog
pg_shdescription and stores
comments on databases, roles, and tablespaces
therein.

The new functions are var_pop(), var_samp(), stddev_pop(), and stddev_samp(). var_samp() and stddev_samp() are merely renamings of
the existing aggregates variance() and stddev(). The latter names remain
available for backward compatibility.

For example, the result of a user-defined function
that is declared to return a domain type is now checked
against the domain's constraints. This closes a
significant hole in the domain implementation.

The fix is to dump a SERIAL
column by explicitly specifying its DEFAULT and sequence elements, and
reconstructing the SERIAL column on
reload using a new ALTER
SEQUENCE OWNED BY command. This also allows
dropping a SERIAL column
specification.

Previously, it was lexical, which caused unexpected
sharing violations.

Run PL/Perl and PL/PerlU in separate interpreters, for
security reasons (Andrew)

In consequence, they can no longer share data nor
loaded modules. Also, if Perl has not been compiled with
the requisite flags to allow multiple interpreters, only
one of these languages can be used in any given backend
process.

Allow complex selection of objects to be included or
excluded by pg_dump
(Greg Sabino Mullane)

pg_dump now supports
multiple -n (schema) and
-t (table) options, and adds
-N and -T options to exclude objects. Also, the
arguments of these switches can now be wild-card
expressions rather than single object names, for example
-t 'foo*', and a schema can be
part of a -t or -T switch, for example -t schema1.table1.

Add PG_VERSION_NUM for use by
third-party applications wanting to test the backend
version in C using > and < comparisons (Bruce)

Add XLOG_BLCKSZ as
independent from BLCKSZ (Mark
Wong)

Add LWLOCK_STATS define to
report locking activity (Tom)

Emit warnings for unknown configure options (Martijn van
Oosterhout)

Add server support for "plugin" libraries that can be used for
add-on tasks such as debugging and performance
measurement (Korry Douglas)

This consists of two features: a table of "rendezvous variables" that allows
separately-loaded shared libraries to communicate, and a
new configuration parameter
local_preload_libraries that allows libraries to
be loaded into specific sessions without explicit
cooperation from the client application. This allows
external add-ons to implement features such as a PL/pgSQL
debugger.

New functions_PG_init() and _PG_fini() are called if the library
defines such symbols. Hence we no longer need to specify
an initialization function in shared_preload_libraries; we can assume
that the library used the _PG_init() convention instead.

New functions are cube(float[]), cube(float[], float[]), and
cube_subset(cube,
int4[]).

Add async query capability to dblink (Kai Londenberg,
Joe Conway)

New operators for array-subset comparisons (@>, <@,
&&) (Tom)

Various contrib packages already had these operators
for their datatypes, but the naming wasn't consistent. We
have now added consistently named array-subset comparison
operators to the core code and all the contrib packages
that have such functionality. (The old names remain
available, but are deprecated.)

Submit correction

If you see anything in the documentation that is not correct, does not match
your experience with the particular feature or requires further clarification,
please use
this form
to report a documentation issue.