After many years of development, PostgreSQL has become feature-complete in
many areas. This release shows a targeted approach to adding
features (e.g., authentication, monitoring, space reuse), and
adds capabilities defined in the later SQL standards. The major
areas of enhancement are:

Previously, these types of queries always removed
duplicate rows by means of Sort/Unique processing (i.e.,
sort then remove adjacent duplicates). Now they can be
implemented by hashing, which will not produce sorted
output. If an application relied on the output being in
sorted order, the recommended fix is to add an ORDER BY clause. As a short-term
workaround, the previous behavior can be restored by
disabling enable_hashagg, but
that is a very performance-expensive fix. SELECT DISTINCT ON never uses hashing,
however, so its behavior is unchanged.

Formerly it was possible to drop such a constraint
from a child table, allowing rows that violate the
constraint to be visible when scanning the parent table.
This was deemed inconsistent, as well as contrary to SQL
standard.

Disallow negative LIMIT or
OFFSET values, rather than
treating them as zero (Simon)

Disallow LOCK TABLE outside a
transaction block (Tom)

Such an operation is useless because the lock would be
released immediately.

In particular, estimates for full-text-search
operators are greatly improved.

Allow SELECT DISTINCT and
UNION/INTERSECT/EXCEPT
to use hashing (Tom)

This means that these types of queries no longer
automatically produce sorted output.

Create explicit concepts of semi-joins and anti-joins
(Tom)

This work formalizes our previous ad-hoc treatment of
IN (SELECT ...) clauses, and
extends it to EXISTS and
NOT EXISTS clauses. It should
result in significantly better planning of EXISTS and NOT
EXISTS queries. In general, logically equivalent
IN and EXISTS clauses should now have similar
performance, whereas previously IN often won.

Improve optimization of sub-selects beneath outer
joins (Tom)

Formerly, a sub-select or view could not be optimized
very well if it appeared within the nullable side of an
outer join and contained non-strict expressions (for
instance, constants) in its result list.

Improve the performance of text_position() and related functions
by using Boyer-Moore-Horspool searching (David
Rowley)

This is particularly helpful for long search
patterns.

Reduce I/O load of writing the statistics collection
file by writing the file only when requested (Martin
Pihlak)

Including the full certificate chain makes the
client able to verify the certificate without having
all intermediate CA certificates present in the local
store, which is often the case for commercial CAs.

This allows identical usernames from different
realms to be authenticated as different database users
using usermaps.

Parse pg_hba.conf fully
when it is loaded, so that errors are reported
immediately (Magnus)

Previously, most errors in the file wouldn't be
detected until clients tried to connect, so an
erroneous file could render the system unusable. With
the new behavior, if an error is detected during reload
then the bad file is rejected and the postmaster
continues to use its old copy.

Show all parsing errors in pg_hba.conf instead of aborting after
the first one (Selena Deckelmann)

Support ident
authentication over Unix-domain sockets on Solaris (Garick Hamlin)

Allow the location of the server's statistics file
to be specified via stats_temp_directory (Magnus)

This allows the statistics file to be placed in a
RAM-resident
directory to reduce I/O requirements. On
startup/shutdown, the file is copied to its traditional
location ($PGDATA/global/) so
it is preserved across restarts.

Free space discovered by VACUUM is now recorded in *_fsm files, rather than in a
fixed-sized shared memory area. The max_fsm_pages and max_fsm_relations settings have been
removed, greatly simplifying administration of free
space management.

Add a visibility map to track pages that do not
require vacuuming (Heikki)

This allows VACUUM to avoid
scanning all of a table when only a portion of the
table needs vacuuming. The visibility map is stored in
per-relation "fork"
files.

Add vacuum_freeze_table_age
parameter to control when VACUUM should ignore the visibility map
and do a full table scan to freeze tuples (Heikki)

Track transaction snapshots more carefully
(Alvaro)

This improves VACUUM's
ability to reclaim space in the presence of
long-running transactions.

Previously these were interpreted as 1 BC. (Note: years 0 and 00 are
still assumed to be the year 2000.)

Include SGT (Singapore
time) in the default list of known time zone
abbreviations (Tom)

Support infinity and
-infinity as values of type
date (Tom)

Make parsing of interval
literals more standard-compliant (Tom, Ron Mayer)

For example, INTERVAL '1'
YEAR now does what it's supposed to.

Allow interval
fractional-seconds precision to be specified after the
second keyword, for
SQL standard
compliance (Tom)

Formerly the precision had to be specified after the
keyword interval. (For backwards
compatibility, this syntax is still supported, though
deprecated.) Data type definitions will now be output
using the standard format.

Improve the handling of casts applied to ARRAY[] constructs, such as ARRAY[...]::integer[] (Brendan Jurd)

Formerly PostgreSQL
attempted to determine a data type for the ARRAY[] construct without reference to
the ensuing cast. This could fail unnecessarily in many
cases, in particular when the ARRAY[] construct was empty or contained
only ambiguous entries such as NULL. Now the cast is consulted to
determine the type that the array elements must be.

Make RETURN QUERY set the
special FOUND and GET DIAGNOSTICSROW_COUNT variables (Pavel Stehule)

Make FETCH and MOVE set the GET
DIAGNOSTICSROW_COUNT
variable (Andrew Gierth)

Make EXIT without a label
always exit the innermost loop (Tom)

Formerly, if there were a BEGIN block more closely nested than any
loop, it would exit that block instead. The new
behavior matches Oracle(TM) and is also what was
previously stated by our own documentation.

Make processing of string literals and nested block
comments match the main SQL parser's processing
(Tom)

In particular, the format string in RAISE now works the same as any other
string literal, including being subject to standard_conforming_strings. This change
also fixes other cases in which valid commands would
fail when standard_conforming_strings is on.

Avoid memory leakage when the same function is
called at varying exception-block nesting depths
(Tom)

Allow \dC to accept a
wildcard pattern, which matches either datatype
involved in the cast (Tom)

Add a function type column to \df's output, and add options to list
only selected types of functions (David Fetter)

Make \df not hide functions
that take or return type cstring
(Tom)

Previously, such functions were hidden because most
of them are datatype I/O functions, which were deemed
uninteresting. The new policy about hiding system
functions by default makes this wart unnecessary.

Add a --no-tablespaces
option to pg_dump/pg_dumpall/pg_restore so that dumps can be
restored to clusters that have non-matching tablespace
layouts (Gavin Roy)

Remove -d and -D options from pg_dump and pg_dumpall (Tom)

These options were too frequently confused with the
option to select a database name in other PostgreSQL client applications.
The functionality is still available, but you must now
spell out the long option name --inserts or --column-inserts.

Remove -i/--ignore-version option from pg_dump and pg_dumpall (Tom)

Use of this option does not throw an error, but it
has no effect. This option was removed because the
version checks are necessary for safety.

Disable statement_timeout
during dump and restore (Joshua Drake)

Add pg_dump/pg_dumpall option --lock-wait-timeout (David Gould)

This allows dumps to fail if unable to acquire a
shared lock within the specified amount of time.

Make PQexecParams()
and related functions return PGRES_EMPTY_QUERY for an empty query
(Tom)

They previously returned PGRES_COMMAND_OK.

Document how to avoid the overhead of WSACleanup() on Windows (Andrew
Chernow)

Do not rely on Kerberos tickets to determine the
default database username (Magnus)

Previously, a Kerberos-capable build of libpq would
use the principal name from any available Kerberos
ticket as default database username, even if the
connection wasn't using Kerberos authentication. This
was deemed inconsistent and confusing. The default
username is now determined the same way with or without
Kerberos. Note however that the database username must
still match the ticket when Kerberos authentication is
used.

libpq now supports
verifying both the certificate and the name of the
server when making SSL connections. If a root
certificate is not available to use for verification,
SSL connections will
fail. The sslmode parameter is
used to enable certificate verification and set the
level of checking. The default is still not to do any
verification, allowing connections to SSL-enabled
servers without requiring a root certificate on the
client.

Support wildcard server certificates (Magnus)

If a certificate CN starts with *, it will be treated as a wildcard when
matching the hostname, allowing the use of the same
certificate for multiple servers.

This has effectively been required for several years,
but now there is no infrastructure claiming to support
other parser tools.

Add pg_config--htmldir option (Peter)

Pass float4 by value inside the
server (Zoltan Boszormenyi)

Add configure option
--disable-float4-byval to use
the old behavior. External C functions that use old-style
(version 0) call convention and pass or return float4 values will be broken by this change,
so you may need the configure option if you have such
functions and don't want to update them.

This adds support for daylight saving time
(DST) calculations
beyond the year 2038.

Deprecate use of platform's time_t data type (Tom)

Some platforms have migrated to 64-bit time_t, some have not, and Windows can't make
up its mind what it's doing. Define pg_time_t to have the same meaning as
time_t, but always be 64 bits
(unless the platform has no 64-bit integer type), and use
that type in all module APIs and on-disk data
formats.

Fix bug in handling of the time zone database when
cross-compiling (Richard Evans)

Link backend object files in one step, rather than in
stages (Peter)

Improve gettext
support to allow better translation of plurals
(Peter)

Rewrite contrib/intagg to
use new functions array_agg() and unnest() (Tom)

Make contrib/pg_standby
recover all available WAL before failover (Fujii Masao,
Simon, Heikki)

To make this work safely, you now need to set the new
recovery_end_command option in
recovery.conf to clean up the
trigger file after failover. pg_standby will no longer remove the
trigger file itself.

contrib/pg_standby's
-l option is now a no-op, because
it is unsafe to use a symlink (Simon)