Release date: 2013-09-09
Major enhancements in PostgreSQL 9.3 include:
Make simple views auto-updatable
Add many features for the
JSON data type, including operators and
functions to extract elements from
LATERAL option for
FROM-clause subqueries and function
Allow foreign data wrappers to support writes (inserts/updates/deletes) on foreign tables
Add a Postgres foreign data wrapper to allow access to other Postgres servers
Add support for event triggers
Add optional ability to checksum data pages and report corruption
Prevent non-key-field row updates from blocking foreign key checks
Greatly reduce System V shared memory requirements
The above items are explained in more detail in the sections below.
Version 9.3 contains a number of changes that may affect compatibility with previous releases. Observe the following incompatibilities:
This setting controls the WAL sender timeout.
Require superuser privileges to set
commit_delay because it can now
potentially delay other sessions (Simon Riggs)
Allow in-memory sorts to use their full memory allocation (Jeff Janes)
Users who have set
work_mem based on the previous behavior may
need to revisit that setting.
Throw an error if a tuple to be updated or deleted
has already been updated or deleted by a
BEFORE trigger (Kevin Grittner)
Formerly, the originally-intended update was
silently skipped, resulting in logical inconsistency
since the trigger might have propagated data to other
places based on the intended update. Now an error is
thrown to prevent the inconsistent results from being
committed. If this change affects your application, the
best solution is usually to move the data-propagation
actions to an
This error will also be thrown if a query invokes a volatile function that modifies rows that are later modified by the query itself. Such cases likewise previously resulted in silently skipping updates.
ON UPDATE SET
NULL/SET DEFAULT foreign key actions to
affect all columns of the constraint, not just those
changed in the
Previously, we would set only those referencing
columns that correspond to referenced columns that were
changed by the
This was what was required by SQL-92, but more recent
editions of the SQL standard specify the new
Force cached plans to be replanned if the
search_path changes (Tom Lane)
Previously, cached plans already generated in the
current session were not redone if the query was
re-executed with a new
search_path setting, resulting in
to_number() to properly handle a period used
as a thousands separator (Tom Lane)
Previously, a period was considered to be a decimal
point even when the locale says it isn't and the
D format code is used to
specify use of the locale-specific decimal point. This
resulted in wrong answers if
FM format was also used.
non-set-returning functions that have set-returning
functions in their arguments to properly return null
rows (Tom Lane)
A null value passed to the strict function should result in a null output, but instead, that output row was suppressed entirely.
Store WAL in a continuous stream, rather than skipping the last 16MB segment every 4GB (Heikki Linnakangas)
files with names ending in
FF were not used because of this
skipping. If you have WAL backup or restore scripts that
took this behavior into account, they will need to be
store the default foreign key match type
s for “simple” (Tom
Previously this case was represented by
u for “unspecified”.
Below you will find a detailed account of the changes between PostgreSQL 9.3 and the previous major release.
Prevent non-key-field row updates from blocking foreign key checks (Álvaro Herrera, Noah Misch, Andres Freund, Alexander Shulgin, Marti Raudsepp, Alexander Shulgin)
This change improves concurrency and reduces the
probability of deadlocks when updating tables
involved in a foreign-key constraint.
UPDATEs that do not change any
columns referenced in a foreign key now take the new
NO KEY UPDATE lock mode
on the row, while foreign key checks use the new
KEY SHARE lock mode,
which does not conflict with
KEY UPDATE. So there is no blocking unless a
foreign-key column is changed.
Add configuration variable
lock_timeout to allow limiting how long a
session will wait to acquire any one lock (Zoltán
Add SP-GiST support for range data types (Alexander Korotkov)
Allow GiST indexes to be unlogged (Jeevan Chalke)
Improve performance of GiST index insertion by randomizing the choice of which page to descend to when there are multiple equally good alternatives (Heikki Linnakangas)
Improve concurrency of hash index operations (Robert Haas)
Collect and use histograms of upper and lower bounds, as well as range lengths, for range types (Alexander Korotkov)
Improve optimizer's cost estimation for index access (Tom Lane)
Improve optimizer's hash table size estimate for
DISTINCT via hash
aggregation (Tom Lane)
Suppress no-op Result and Limit plan nodes (Kyotaro Horiguchi, Amit Kapila, Tom Lane)
Reduce optimizer overhead by not keeping plans on the basis of cheap startup cost when the optimizer only cares about total cost overall (Tom Lane)
option to avoid the overhead of marking tuples as
frozen later (Simon Riggs, Jeff Davis)
Improve performance of
NUMERIC calculations (Kyotaro
Improve synchronization of sessions waiting for
commit_delay (Peter Geoghegan)
This greatly improves the usefulness of
Improve performance of the
TABLE ... ON COMMIT DELETE ROWS option by
not truncating such temporary tables in transactions
that haven't touched any temporary tables (Heikki
Make vacuum recheck visibility after it has removed expired tuples (Pavan Deolasee)
This increases the chance of a page being marked as all-visible.
Add per-resource-owner lock caches (Jeff Janes)
This speeds up lock bookkeeping at statement completion in multi-statement transactions that hold many locks; it is particularly useful for pg_dump.
Avoid scanning the entire relation cache at commit of a transaction that creates a new relation (Jeff Janes)
This speeds up sessions that create many tables in successive small transactions, such as a pg_restore run.
Improve performance of transactions that drop many relations (Tomas Vondra)
Add optional ability to checksum data pages and report corruption (Simon Riggs, Jeff Davis, Greg Smith, Ants Aasma)
The checksum option can be set during initdb.
Split the statistics collector's data file into separate global and per-database files (Tomas Vondra)
This reduces the I/O required for statistics tracking.
Fix the statistics collector to operate properly in cases where the system clock goes backwards (Tom Lane)
Previously, statistics collection would stop until the time again reached the latest time previously recorded.
Emit an informative message to postmaster standard error when we are about to stop logging there (Tom Lane)
This should help reduce user confusion about where to look for log output in common configurations that log to standard error only during postmaster startup.
When an authentication failure occurs, log the
pg_hba.conf line, to ease
debugging of unintended failures (Magnus
Improve LDAP error reporting and documentation (Peter Eisentraut)
Add support for specifying LDAP authentication parameters in URL format, per RFC 4516 (Peter Eisentraut)
ssl_ciphers parameter to start with
DEFAULT, rather than
ALL, then remove
insecure ciphers (Magnus Hagander)
This should yield a more appropriate SSL cipher set.
Parse and load
pg_ident.conf once, not during
each connection (Amit Kapila)
This is similar to how
pg_hba.conf is processed.
Greatly reduce System V shared memory requirements (Robert Haas)
On Unix-like systems,
mmap() is now used for most of
memory. For most users, this will eliminate any need
to adjust kernel parameters for shared memory.
Allow the postmaster to listen on multiple Unix-domain sockets (Honza Horák)
The configuration parameter
unix_socket_directory is replaced by
accepts a list of directories.
Allow a directory of configuration files to be processed (Magnus Hagander, Greg Smith, Selena Deckelmann)
Such a directory is specified with
include_dir in the server configuration
This is the maximum value that initdb will attempt
to set in
the previous maximum was 32MB.
Remove the external PID file, if any, on postmaster exit (Peter Eisentraut)
Allow a streaming replication standby to follow a timeline switch (Heikki Linnakangas)
This allows streaming standby servers to receive WAL data from a slave newly promoted to master status. Previously, other standbys would require a resync to begin following the new master.
These functions report the status of base backups.
Improve performance of streaming log shipping with
synchronous_commit disabled (Andres
Allow much faster promotion of a streaming standby to primary (Simon Riggs, Kyotaro Horiguchi)
Add the last checkpoint's redo location to pg_controldata's output (Fujii Masao)
This information is useful for determining which WAL files are needed for restore.
Allow tools like pg_receivexlog to run on computers with different architectures (Heikki Linnakangas)
WAL files can still only be replayed on servers with the same architecture as the primary; but they can now be transmitted to and stored on machines of any architecture, since the streaming replication protocol is now machine-independent.
--write-recovery-conf output a minimal
(Zoltán Böszörményi, Magnus Hagander)
This simplifies setting up a standby server.
parameter to control the WAL receiver's timeout (Amit
This allows more rapid detection of connection failure.
Change the WAL record format to allow splitting the record header across pages (Heikki Linnakangas)
The new format is slightly more compact, and is more efficient to write.
LATERAL option for
FROM-clause subqueries and function
calls (Tom Lane)
This feature allows subqueries and functions in
FROM to reference columns
from other tables in the
FROM clause. The
LATERAL keyword is optional for
Allow a multirow
VALUES clause in a rule to
NEW (Tom Lane)
Add support for event triggers (Dimitri Fontaine, Robert Haas, Álvaro Herrera)
This allows server-side functions written in event-enabled languages to be called when DDL commands are run.
Allow foreign data wrappers to support writes (inserts/updates/deletes) on foreign tables (KaiGai Kohei)
SCHEMA ... IF NOT EXISTS clause (Fabrízio de
OWNED also change ownership of shared
objects (Álvaro Herrera)
AGGREGATE complain if the given initial
value string is not valid input for the transition
datatype (Tom Lane)
TABLE's messages about implicit index and
sequence creation (Robert Haas)
These messages now appear at
DEBUG1 verbosity, so that they will
not be shown by default.
DROP TABLE IF
EXISTS to succeed when a non-existent schema
is specified in the table name (Bruce Momjian)
Previously, it threw an error if the schema did not exist.
Provide clients with constraint violation details as separate fields (Pavel Stehule)
This allows clients to retrieve table, column, data type, or constraint name error details. Previously such information had to be extracted from error strings. Client library support is required to access these fields.
IF NOT EXISTS
... ADD VALUE (Andrew Dunstan)
This is useful for conditionally adding values to enumerated types.
ALL SET to establish settings for all
users (Peter Eisentraut)
This allows settings to apply to all users in all
SET already allowed addition of settings
for all users in a single database.
postgresql.conf has a similar
Add support for
ALTER RULE ... RENAME (Ali
Add materialized views (Kevin Grittner)
Unlike ordinary views, where the base tables are read on every access, materialized views create physical tables at creation or refresh time. Access to the materialized view then reads from its physical table. There is not yet any facility for incrementally refreshing materialized views or auto-accessing them via base table access.
Make simple views auto-updatable (Dean Rasheed)
RECURSIVE VIEW syntax (Peter
Internally this is translated into
CREATE VIEW ... WITH RECURSIVE
Improve view/rule printing code to handle cases where referenced tables are renamed, or columns are renamed, added, or dropped (Tom Lane)
Table and column renamings can produce cases where, if we merely substitute the new name into the original text of a rule or view, the result is ambiguous. This change fixes the rule-dumping code to insert manufactured table and column aliases when needed to preserve the original semantics.
Increase the maximum size of large objects from 2GB to 4TB (Nozomi Anzai, Yugo Nagata)
This change includes adding 64-bit-capable large object access functions, both in the server and in libpq.
Allow text timezone designations,
e.g. “America/Chicago”, in the
“T” field of ISO-format
timestamptz input (Bruce Momjian)
format() to provide field width
and left/right alignment options (Pavel Stehule)
Previously the behavior was either wrong or inconsistent with positive/AD handling, e.g. with the format mask “IYYY-IW-DY”.
start a new line by default after each
SELECT target list entry and
FROM entry (Marko
This reduces line length in view printing, for instance in pg_dump output.
map_sql_value_to_xml_value() to print
values of domain types the same way their base type
would be printed (Pavel Stehule)
There are special formatting rules for certain
built-in types such as
boolean; these rules now also apply to
domains over these types.
Allow PL/pgSQL to use
RETURN with a composite-type
expression (Asif Rehman)
Previously, in a function returning a composite
RETURN could only
reference a variable of that type.
Allow PL/pgSQL to access constraint violation details as separate fields (Pavel Stehule)
Allow PL/pgSQL to access the number of rows
COPY executed in a
PL/pgSQL function now updates the value retrieved by
GET DIAGNOSTICS x = ROW_COUNT.
Allow unreserved keywords to be used as identifiers everywhere in PL/pgSQL (Tom Lane)
In certain places in the PL/pgSQL grammar, keywords had to be quoted to be used as identifiers, even if they were nominally unreserved.
Add PL/Python result object string handler (Peter Eisentraut)
plpy.debug(rv) to output something
Make PL/Python convert OID values to a proper Python numeric type (Peter Eisentraut)
SPI errors raised explicitly (with
same as internal SPI errors (Oskari Saarenmaa and
Prevent leakage of SPI tuple tables during subtransaction abort (Tom Lane)
At the end of any failed subtransaction, the core
SPI code now releases any SPI tuple tables that were
created during that subtransaction. This avoids the
need for SPI-using code to keep track of such tuple
tables and release them manually in error-recovery
code. Failure to do so caused a number of
transaction-lifespan memory leakage issues in PL/pgSQL
and perhaps other SPI clients.
SPI_freetuptable() now protects
itself against multiple freeing requests, so any
existing code that did take care to clean up shouldn't
be broken by this change.
functions to access the number of rows processed by
Add command-line utility pg_isready to check if the server is ready to accept connections (Phil Sorber)
This is similar to the way pg_dump's
--table option works.
Add libpq function
PQconninfo() to return connection
information (Zoltán Böszörményi, Magnus Hagander)
Adjust function cost settings so psql tab completion and pattern searching are more efficient (Tom Lane)
Improve psql's tab completion coverage (Jeff Janes, Dean Rasheed, Peter Eisentraut, Magnus Hagander)
Allow the psql
to work when reading from standard input (Fabien
Coelho, Robert Haas)
Previously this option only worked when reading from a file.
Remove psql warning when connecting to an older server (Peter Eisentraut)
A warning is still issued when connecting to a server of a newer major version than psql's.
repeatedly execute a SQL command (Will
\gset to store
query results in psql variables (Pavel
information to psql's
\conninfo command (Alastair
Add “Security” column to
\df+ output (Jon
\l to accept a
database name pattern (Peter Eisentraut)
In psql, do not
\connect to use
defaults if there is no active connection (Bruce
This might be the case if the server had crashed.
Properly reset state after failure of a SQL
command executed with psql's
file (Tom Lane)
Previously, the output from subsequent SQL commands would unexpectedly continue to go to the same file.
latex-longtable output format to
This format allows tables to span multiple pages.
output mode to the psql
latex format (Bruce Momjian)
In psql's tuples-only and expanded output modes, no longer emit “(No rows)” for zero rows (Peter Eisentraut)
In psql's unaligned, expanded output mode, no longer print an empty line for zero rows (Peter Eisentraut)
--jobs option to dump
tables in parallel (Joachim Wieland)
Make pg_dump output functions in a more predictable order (Joel Jacobson)
Fix tar files emitted by pg_dump to be POSIX conformant (Brian Weaver, Tom Lane)
--dbname option to
consistency with other client commands (Heikki
The database name could already be supplied last without a flag.
Make initdb fsync the newly created data directory (Jeff Davis)
This insures data integrity in event of a system
crash shortly after initdb. This can be disabled by
option to sync the data directory to durable storage
This is used by pg_upgrade.
Make initdb issue a warning about placing the data directory at the top of a file system mount point (Bruce Momjian)
Add infrastructure to allow plug-in background worker processes (Álvaro Herrera)
Create a centralized timeout API (Zoltán Böszörményi)
Create libpgcommon and move
pg_malloc() and other functions there
(Álvaro Herrera, Andres Freund)
This allows libpgport to be used solely for portability-related code.
Add support for list links embedded in larger structs (Andres Freund)
SA_RESTART for all
Ensure that the correct text domain is used when
messages (Heikki Linnakangas)
Standardize naming of client-side memory allocation functions (Tom Lane)
Provide support for “static assertions” that will fail at compile time if some compile-time-constant condition is not met (Andres Freund, Tom Lane)
client-side code (Andrew Dunstan)
Add decoration to inform the C compiler that some
elog() calls do not
return (Peter Eisentraut, Andres Freund, Tom Lane,
Allow options to be passed to the regression test
output comparison utility via
Add isolation tests for
CONCURRENTLY (Abhijit Menon-Sen)
Remove typedefs for
they are better represented as
Fix install-strip on Mac OS X (Peter Eisentraut)
Remove configure flag
--disable-shared, as it is no longer
supported (Bruce Momjian)
Rewrite pgindent in Perl (Andrew Dunstan)
Provide Emacs macro to set Perl formatting to match PostgreSQL's perltidy settings (Peter Eisentraut)
Run tool to check the keyword list whenever the backend grammar is changed (Tom Lane)
Change the way
is lexed, to significantly reduce the size of the lexer
tables (Heikki Linnakangas)
Centralize flex and bison make rules (Peter Eisentraut)
This is useful for pgxs authors.
Change many internal backend functions to return
OIDs rather than void
This is useful for event triggers.
Invent pre-commit/pre-prepare/pre-subcommit events for transaction callbacks (Tom Lane)
Loadable modules that use transaction callbacks might need modification to handle these new event types.
to produce a machine-readable description of a database
object (Álvaro Herrera)
server hooks (KaiGai Kohei)
Implement a generic binary heap and use it for Merge-Append operations (Abhijit Menon-Sen)
Provide a tool to help detect timezone abbreviation
changes when updating the
src/timezone/data files (Tom
Add pkg-config support for libpq and ecpg libraries (Peter Eisentraut)
src/tools/backend, now that the
content is on the PostgreSQL wiki (Bruce
Split out WAL reading as an independent facility (Heikki Linnakangas, Andres Freund)
Use a 64-bit integer to represent
XLogRecPtr) instead of
two 32-bit integers (Heikki Linnakangas)
Generally, tools that need to read the WAL format will need to be adjusted.
Allow PL/Python to support platform-specific include directories (Peter Eisentraut)
Allow PL/Python on OS X to build against custom versions of Python (Peter Eisentraut)
Add a Postgres foreign data wrapper contrib module to allow access to other Postgres servers (Shigeru Hanada)
This foreign data wrapper supports writes.
Add pg_xlogdump contrib program (Andres Freund)
Add support for indexing of regular-expression searches in pg_trgm (Alexander Korotkov)
Improve pg_trgm's handling of multibyte characters (Tom Lane)
On a platform that does not have the wcstombs() or
towlower() library functions, this could result in an
incompatible change in the contents of pg_trgm indexes for non-ASCII
data. In such cases,
REINDEX those indexes to ensure
correct search results.
Add a pgstattuple function to report the size of the pending-insertions list of a GIN index (Fujii Masao)
Improve output of pg_test_timing (Bruce Momjian)
Improve output of pg_test_fsync (Peter Geoghegan)
Create a dedicated foreign data wrapper, with its own option validator function, for dblink (Shigeru Hanada)
When using this FDW to define the target of a dblink connection, instead of using a hard-wired list of connection options, the underlying libpq library is consulted to see what connection options it supports.
Allow pg_upgrade to do dumps and restores in parallel (Bruce Momjian, Andrew Dunstan)
This allows parallel schema dump/restore of
databases, as well as parallel copy/link of data
files per tablespace. Use the
--jobs option to specify the level of
Make pg_upgrade create Unix-domain sockets in the current directory (Bruce Momjian, Tom Lane)
This reduces the possibility that someone will accidentally connect during the upgrade.
--check mode properly
detect the location of non-default socket directories
(Bruce Momjian, Tom Lane)
Improve performance of pg_upgrade for databases with many tables (Bruce Momjian)
Improve pg_upgrade's logs by showing executed commands (Álvaro Herrera)
Improve pg_upgrade's status display during copy/link (Bruce Momjian)
option to pgbench
This adds foreign key constraints to the standard tables created by pgbench, for use in foreign key performance testing.
Allow pgbench to
aggregate performance statistics and produce output
--aggregate-interval seconds (Tomas
--sampling-rate option to
control the percentage of transactions logged (Tomas
Reduce and improve the status message output of pgbench's initialization mode (Robert Haas, Peter Eisentraut)
-q mode to print one
output line every five seconds (Tomas Vondra)
Output pgbench elapsed and estimated remaining time during initialization (Tomas Vondra)
Allow pgbench to
use much larger scale factors, by changing relevant
bigint when the requested
scale factor exceeds 20000 (Greg Smith)
Allow EPUB-format documentation to be created (Peter Eisentraut)
Update FreeBSD kernel configuration documentation (Brad Davis)
WINDOW function documentation
(Bruce Momjian, Florian Pflug)
Add instructions for setting up the documentation tool chain on macOS (Peter Eisentraut)
commit_delay documentation (Peter
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.