This release of PostgreSQL
adds features that have been requested for years, such as
easy-to-use replication, a mass permission-changing facility,
and anonymous code blocks. While past major releases have been
conservative in their scope, this release shows a bold new
desire to provide facilities that new and existing users of
PostgreSQL will embrace. This
has all been done with few incompatibilities. Major
enhancements include:

Built-in replication based on log shipping. This advance
consists of two features: Streaming Replication, allowing
continuous archive (WAL)
files to be streamed over a network connection to a standby
server, and Hot Standby, allowing continuous archive
standby servers to execute read-only queries. The net
effect is to support a single master with multiple
read-only slave servers.

Easier database object permissions management. GRANT/REVOKE IN SCHEMA supports mass
permissions changes on existing objects, while ALTER
DEFAULT PRIVILEGES allows control of privileges
for objects created in the future. Large objects (BLOBs)
now support permissions management as well.

Broadly enhanced stored procedure support. The DO statement
supports ad-hoc or "anonymous"
code blocks. Functions can now be called using named
parameters. PL/pgSQL is now
installed by default, and PL/Perl
and PL/Python have been
enhanced in several ways, including support for
Python3.

Exclusion
constraints. These provide a generalized version of
unique constraints, allowing enforcement of complex
conditions.

New and enhanced security features, including RADIUS
authentication, LDAP authentication improvements, and a new
contrib module passwordcheck for testing password
strength.

New high-performance implementation of the LISTEN/NOTIFY
feature. Pending events are now stored in a memory-based
queue rather than a table. Also, a "payload" string can be sent with each
event, rather than transmitting just an event name as
before.

New implementation of VACUUM FULL.
This command now rewrites the entire table and indexes,
rather than moving individual rows to compact space. It is
substantially faster in most cases, and no longer results
in index bloat.

New contrib module pg_upgrade to support in-place upgrades
from 8.3 or 8.4 to 9.0.

Multiple performance enhancements for specific types of
queries, including elimination of unnecessary joins. This
helps optimize some automatically-generated queries, such
as those produced by object-relational mappers (ORMs).

EXPLAIN enhancements. The output is now
available in JSON, XML, or YAML format, and includes buffer
utilization and other data not previously available.

hstore improvements, including new
functions and greater data capacity.

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.0 contains a number of changes that selectively
break backwards compatibility in order to support new features
and code quality improvements. In particular, users who make
extensive use of PL/pgSQL, Point-In-Time Recovery (PITR), or
Warm Standby should test their applications because of slight
user-visible changes in those areas. Observe the following
incompatibilities:

The server parameter bytea_output can be used to select the
traditional output format if needed for
compatibility.

Array input now considers only plain ASCII whitespace
characters to be potentially ignorable; it will never
ignore non-ASCII characters, even if they are whitespace
according to some locales (Tom Lane)

This avoids some corner cases where array values could
be interpreted differently depending on the server's
locale settings.

Improve standards compliance of
SIMILAR TO patterns and SQL-style substring() patterns (Tom Lane)

This includes treating ? and
{...} as pattern metacharacters,
while they were simple literal characters before; that
corresponds to new features added in SQL:2008. Also,
^ and $
are now treated as simple literal characters; formerly
they were treated as metacharacters, as if the pattern
were following POSIX rather than SQL rules. Also, in
SQL-standard substring(),
use of parentheses for nesting no longer interferes with
capturing of a substring. Also, processing of bracket
expressions (character classes) is now more
standards-compliant.

Administrators can still rename such indexes and
columns manually. This change will require an update of
the JDBC driver, and possibly other drivers, so that
unique indexes are correctly recognized after a
rename.

CREATE OR REPLACE FUNCTION
can no longer change the declared names of function
parameters (Pavel Stehule)

In order to avoid creating ambiguity in
named-parameter calls, it is no longer allowed to change
the aliases for input parameters in the declaration of an
existing function (although names can still be assigned
to previously unnamed parameters). You now have to
DROP and recreate the function
to do that.

PL/pgSQL now throws an error if a variable name
conflicts with a column name used in a query (Tom
Lane)

The former behavior was to bind ambiguous names to
PL/pgSQL variables in preference to query columns, which
often resulted in surprising misbehavior. Throwing an
error allows easy detection of ambiguous situations.
Although it's recommended that functions encountering
this type of error be modified to remove the conflict,
the old behavior can be restored if necessary via the
configuration parameter
plpgsql.variable_conflict, or via the
per-function option #variable_conflict.

This is a consequence of aligning the PL/pgSQL parser
to match the core SQL parser more closely. If necessary,
variable names can be double-quoted to avoid this
restriction.

PL/pgSQL now requires columns of composite results to
match the expected type modifier as well as base type
(Pavel Stehule, Tom Lane)

For example, if a column of the result type is
declared as NUMERIC(30,2), it is
no longer acceptable to return a NUMERIC of some other precision in that
column. Previous versions neglected to check the type
modifier and would thus allow result rows that didn't
actually conform to the declared restrictions.

Formerly, a statement like SELECT
... INTO rec.fld FROM ... was treated as a
scalar assignment even if the record field fld was of composite type. Now it
is treated as a record assignment, the same as when the
INTO target is a regular
variable of composite type. So the values to be assigned
to the field's subfields should be written as separate
columns of the SELECT list, not
as a ROW(...) construct as in
previous versions.

If you need to do this in a way that will work in both
9.0 and previous releases, you can write something like
rec.fld := ROW(...) FROM ....

Remove PL/pgSQL's RENAME
declaration (Tom Lane)

Instead of RENAME, use
ALIAS, which can now create an alias for any
variable, not only dollar sign parameter names (such as
$1) as before.

Future versions of PostgreSQL will probably reject this
operator name entirely, in order to support the
SQL-standard notation for named function parameters. For
the moment, it is still allowed, but a warning is emitted
when such an operator is defined.

Remove support for platforms that don't have a working
64-bit integer data type (Tom Lane)

It is believed all still-supported platforms have
working 64-bit integer data types.

PostgreSQL's existing standby-server capability has been
expanded both to support read-only queries on standby
servers and to greatly reduce the lag between master and
standby servers. For many users, this will be a useful and
low-administration form of replication, either for high
availability or for horizontal scalability.

This feature is called Streaming Replication.
Previously WAL data
could be sent to standby servers only in units of
entire WAL files
(normally 16 megabytes each). Streaming Replication
eliminates this inefficiency and allows updates on the
master to be propagated to standby servers with very
little delay. There are new postgresql.conf and recovery.conf settings to control this
feature, as well as extensive documentation.

Add
pg_last_xlog_receive_location() and
pg_last_xlog_replay_location(), which
can be used to monitor standby server WAL activity (Simon Riggs, Fujii
Masao, Heikki Linnakangas)

Outer joins where the inner side is unique and not
referenced above the join are unnecessary and are
therefore now removed. This will accelerate many
automatically generated queries, such as those created
by object-relational mappers (ORMs).

Allow IS NOT NULL
restrictions to use indexes (Tom Lane)

This is particularly useful for finding MAX()/MIN() values in indexes that contain
many null values.

Improve the optimizer's choices about when to use
materialize nodes, and when to use sorting versus
hashing for DISTINCT (Tom
Lane)

Use the same random seed every time GEQO plans a
query (Andres Freund)

While the Genetic Query Optimizer (GEQO) still
selects random plans, it now always selects the same
random plans for identical queries, thus giving more
consistent performance. You can modify geqo_seed to experiment with
alternative plans.

Improve GEQO plan selection (Tom Lane)

This avoids the rare error "failed to make a valid plan", and
should also improve planning speed.

When looking up statistics for greater/less-than
comparisons, if the comparison value is in the first or
last histogram bucket, use an index (if available) to
fetch the current actual column minimum or maximum.
This greatly improves the accuracy of estimates for
comparison values near the ends of the data range,
particularly if the range is constantly changing due to
addition of new data.

This allows users to override the estimated number
or percentage of distinct values for a column. This
statistic is normally computed by ANALYZE, but the estimate can be poor,
especially on tables with very large numbers of
rows.

Previously only per-database and per-role settings
were possible, not combinations. All role and database
settings are now stored in the new pg_db_role_setting system catalog. A
new psql command
\drds shows these settings.
The legacy system views pg_roles, pg_shadow, and pg_user do not show combination
settings, and therefore no longer completely represent
the configuration for a user or database.

Non-superusers can no longer issue ALTER ROLE/DATABASE
SET for parameters that are not currently known to
the server. This allows the server to correctly check
that superuser-only parameters are only set by
superusers. Previously, the SET would be allowed and then ignored at
session start, making superuser-only custom parameters
much less useful than they should be.

Perform SELECT FOR UPDATE/SHARE processing after applying
LIMIT, so the number of rows
returned is always predictable (Tom Lane)

Previously, changes made by concurrent transactions
could cause a SELECT FOR UPDATE
to unexpectedly return fewer rows than specified by its
LIMIT. FOR
UPDATE in combination with ORDER
BY can still produce surprising results, but that
can be corrected by placing FOR
UPDATE in a subquery.

Such operations either produce a new copy of the
table or are rolled back, so WAL archiving can be skipped,
unless running in continuous archiving mode. This
reduces I/O overhead and improves performance.

This allows mass updates, such as UPDATE tab SET col = col + 1, to work
reliably on columns that have unique indexes or are
marked as primary keys. If the constraint is specified
as DEFERRABLE it will be
checked at the end of the statement, rather than after
each row is updated. The constraint check can also be
deferred until the end of the current transaction,
allowing such updates to be spread over multiple SQL
commands.

Exclusion constraints generalize uniqueness
constraints by allowing arbitrary comparison operators,
not just equality. They are created with the
CREATE TABLE CONSTRAINT ... EXCLUDE clause.
The most common use of exclusion constraints is to
specify that column entries must not overlap, rather
than simply not be equal. This is useful for time
periods and other ranges, as well as arrays. This
feature enhances checking of data integrity for many
calendaring, time-management, and scientific
applications.

This greatly simplifies the assignment of object
privileges in a complex database application. Default
privileges can be set for tables, views, sequences, and
functions. Defaults may be assigned on a per-schema
basis, or database-wide.

Add the ability to control large object (BLOB)
permissions with GRANT/REVOKE
(KaiGai Kohei)

Formerly, any database user could read or modify any
large object. Read and write permissions can now be
granted and revoked per large object, and the ownership
of large objects is tracked.

Change VACUUM FULL to
rewrite the entire table and rebuild its indexes,
rather than moving individual rows around to compact
space (Itagaki Takahiro, Tom Lane)

The previous method was usually slower and caused
index bloat. Note that the new method will use more
disk space transiently during VACUUM FULL; potentially as much as
twice the space normally occupied by the table and its
indexes.

The new hex format will be directly compatible with
more applications that use binary data, allowing them to
store and retrieve it without extra conversion. It is
also significantly faster to read and write than the
traditional format.

The previous maximum extra_float_digits setting was 2. There are cases where 3 digits are
needed to dump and restore float4
values exactly. pg_dump
will now use the setting of 3 when dumping from a server
that allows it.

Locale-specific regular expression functionality
includes case-insensitive matching and locale-specific
character classes. Previously, these features worked
correctly for non-ASCII characters only if the database
used a single-byte server encoding (such as LATIN1). They
will still misbehave in multi-byte encodings other than
UTF-8.

The polygon &&
(overlaps) operator formerly just checked to see if the
two polygons' bounding boxes overlapped. It now does a
more correct check. The polygon @> and <@
(contains/contained by) operators formerly checked to see
if one polygon's vertexes were all contained in the
other; this can wrongly report "true" for some non-convex polygons. Now
they check that all line segments of one polygon are
contained in the other.

For example, this is now supported: array_agg(a ORDER BY b). This is useful
with aggregates for which the order of input values is
significant, and eliminates the need to use a
nonstandard subquery to determine the ordering.

Multi-argument aggregate functions can now use
DISTINCT (Andrew Gierth)

Add the
string_agg() aggregate function to combine
values into a single string (Pavel Stehule)

Aggregate functions that are called with DISTINCT are now passed NULL values if
the aggregate transition function is not marked as
STRICT (Andrew Gierth)

For example, agg(DISTINCT
x) might pass a NULL x
value to agg(). This is
more consistent with the behavior in non-DISTINCT cases.

Support execution of anonymous code blocks using the
DO
statement (Petr Jelinek, Joshua Tolley, Hannu
Valtonen)

This allows execution of server-side code without
the need to create and delete a temporary function
definition. Code can be executed in any language for
which the user has permissions to define a
function.

The language can still be removed from a particular
database if the administrator has security or
performance concerns about making it available.

Improve handling of cases where PL/pgSQL variable
names conflict with identifiers used in queries within
a function (Tom Lane)

The default behavior is now to throw an error when
there is a conflict, so as to avoid surprising
behaviors. This can be modified, via the configuration
parameter
plpgsql.variable_conflict or the per-function
option #variable_conflict, to
allow either the variable or the query-supplied column
to be used. In any case PL/pgSQL will no longer attempt
to substitute variables in places where they would not
be syntactically valid.

Make PL/pgSQL use the main lexer, rather than its
own version (Tom Lane)

This ensures accurate tracking of the main system's
behavior for details such as string escaping. Some
user-visible details, such as the set of keywords
considered reserved in PL/pgSQL, have changed in
consequence.

Avoid throwing an unnecessary error for an invalid
record reference (Tom Lane)

An error is now thrown only if the reference is
actually fetched, rather than whenever the enclosing
expression is reached. For example, many people have
tried to do this in triggers:

Formerly, input parameters were treated as being
declared CONST, so the
function's code could not change their values. This
restriction has been removed to simplify porting of
functions from other DBMSes that do not impose the
equivalent restriction. An input parameter now acts
like a local variable initialized to the passed-in
value.

Improve error location reporting in PL/pgSQL (Tom
Lane)

Add count and
ALL options to MOVE FORWARD/BACKWARD in PL/pgSQL (Pavel Stehule)

Allow PL/pgSQL's WHERE CURRENT
OF to use a cursor variable (Tom Lane)

Allow PL/pgSQL's OPEN cursor FOR EXECUTE to use
parameters (Pavel Stehule, Itagaki Takahiro)

Strings are automatically converted from/to the
server encoding as necessary.

Improve bytea support in
PL/Python (Caleb Welton)

Bytea values passed into
PL/Python are now represented as binary, rather than
the PostgreSQL bytea text format.
Bytea values containing null
bytes are now also output properly from PL/Python.
Passing of boolean, integer, and float values was also
improved.

Support arrays as
parameters and return values in PL/Python (Peter
Eisentraut)

Improve mapping of SQL domains to Python types
(Peter Eisentraut)

Add Python 3
support to PL/Python (Peter Eisentraut)

The new server-side language is called plpython3u. This cannot be used in
the same session with the Python 2 server-side language.

These functions are similar to PQconnectdb() and PQconnectStart() except that they
accept a null-terminated array of connection options,
rather than requiring all options to be provided in a
single string.

Now that /proc/self/oom_adj
allows disabling of the Linux out-of-memory (OOM) killer, it's recommendable to
disable OOM kills for the postmaster. It may then be
desirable to re-enable OOM kills for the postmaster's
child processes. The new compile-time option
LINUX_OOM_ADJ allows the killer to be
reactivated for child processes.

Make backend header files safe to include in
C++ (Kurt Harriman,
Peter Eisentraut)

These changes remove keyword conflicts that
previously made C++
usage difficult in backend code. However, there are
still other complexities when using C++ for backend functions.
extern "C" { } is still
necessary in appropriate places, and memory management
and error handling are still problematic.

Add AggCheckCallContext() for use in
detecting if a C
function is being called as an aggregate (Hitoshi
Harada)

Change calling convention for SearchSysCache() and related
functions to avoid hard-wiring the maximum number of
cache keys (Robert Haas)

Existing calls will still work for the moment, but
can be expected to break in 9.1 or later if not
converted to the new style.

Custom typanalyze functions should no longer rely on
VacAttrStats.attr to determine the type of data
they will be passed (Tom Lane)

This was changed to allow collection of statistics
on index columns for which the storage type is
different from the underlying column data type. There
are new fields that tell the actual datatype being
analyzed.

This avoids the requirement of dumping/reloading the
database when upgrading to a new major release of
PostgreSQL, thus reducing downtime by orders of
magnitude. It supports upgrades to 9.0 from PostgreSQL
8.3 and 8.4.

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.