Newer
Older
<varname>constraint_exclusion</>, since it is not certain that all
rows meet the constraint.
The new <command>ALTER TABLE VALIDATE</> command allows <literal>NOT
VALID</> constraints to be checked for existing rows, after which
they are converted into ordinary constraints.
</para>
</listitem>
<listitem>
<para>
Allow <literal>CHECK</> constraints to be declared <literal>NO
INHERIT</> (Nikhil Sontakke, Alex Hunsaker, Álvaro Herrera)
<para>
This makes them enforceable only on the parent table, not on
child tables.
</para>
</listitem>
<listitem>
<para>
Add the ability to <link linkend="SQL-ALTERTABLE">rename</link>
constraints (Peter Eisentraut)
</para>
</listitem>
</itemizedlist>
</sect4>
<sect4>
<title><command>ALTER</></title>
<itemizedlist>
<listitem>
<para>
Reduce need to rebuild tables and indexes for certain <link
linkend="SQL-ALTERTABLE"><command>ALTER TABLE</command></link>
... <literal>ALTER COLUMN TYPE</> operations (Noah Misch)
Increasing the length limit for a <type>varchar</> or <type>varbit</>
column, or removing the limit altogether, no longer requires a table
rewrite. Similarly, increasing the allowable precision of a
<type>numeric</> column, or changing a column from constrained
<type>numeric</> to unconstrained <type>numeric</>, no longer
requires a table rewrite. Table rewrites are also avoided in similar
cases involving the <type>interval</>, <type>timestamp</>, and
</para>
</listitem>
<listitem>
<para>
Avoid having <link linkend="SQL-ALTERTABLE"><command>ALTER
TABLE</command></link> revalidate foreign key constraints in some
cases where it is not necessary (Noah Misch)
</para>
</listitem>
<listitem>
<para>
Add <literal>IF EXISTS</> options to some <command>ALTER</command>
commands (Pavel Stehule)
</para>
<para>
For example, <command>ALTER FOREIGN TABLE IF EXISTS foo RENAME
TO bar</command>.
</para>
</listitem>
<listitem>
<para>
Add <link linkend="SQL-ALTERFOREIGNDATAWRAPPER"><command>ALTER
FOREIGN DATA WRAPPER</command></link> ... <literal>RENAME</>
and <link linkend="SQL-ALTERSERVER"><command>ALTER
SERVER</command></link> ... <literal>RENAME</> (Peter Eisentraut)
</para>
</listitem>
<listitem>
<para>
Add <link linkend="SQL-ALTERDOMAIN"><command>ALTER
DOMAIN</command></link> ... <literal>RENAME</> (Peter Eisentraut)
<para>
You could already rename domains using <command>ALTER
TYPE</command>.
</para>
</listitem>
<listitem>
<para>
Throw an error for <command>ALTER DOMAIN</command> ... <literal>DROP
CONSTRAINT</> on a nonexistent constraint (Peter Eisentraut)
</para>
An <literal>IF EXISTS</> option has been added to provide the
previous behavior.
</para>
</listitem>
</itemizedlist>
</sect4>
<sect4>
<title><link linkend="SQL-CREATETABLE"><command>CREATE TABLE</></link></title>
<itemizedlist>
<listitem>
<para>
Allow <command>CREATE TABLE (LIKE ...)</command> from foreign
tables, views, and composite types (Peter Eisentraut)
For example, this allows a table to be created whose schema matches a
view.
</para>
</listitem>
<listitem>
<para>
Fix <command>CREATE TABLE (LIKE ...)</command> to avoid index name
conflicts when copying index comments (Tom Lane)
</para>
</listitem>
<listitem>
<para>
Fix <command>CREATE TABLE</command> ... <literal>AS EXECUTE</>
to handle <literal>WITH NO DATA</> and column name specifications
(Tom Lane)
</para>
</listitem>
</itemizedlist>
</sect4>
<sect4>
<title>Object Permissions</title>
<itemizedlist>
<listitem>
<para>
Add a <link
linkend="SQL-CREATEVIEW"><literal>security_barrier</></link>
option for views (KaiGai Kohei, Robert Haas)
This option prevents optimizations that might allow view-protected
data to be exposed to users, for example pushing a clause involving
an insecure function into the <literal>WHERE</> clause of the view.
Such views can be expected to perform more poorly than ordinary
views.
</para>
</listitem>
<listitem>
<para>
Add a new <link
linkend="SQL-CREATEFUNCTION"><literal>LEAKPROOF</></link> function
attribute to mark functions that can safely be pushed down
into <literal>security_barrier</> views (KaiGai Kohei)
</para>
</listitem>
<listitem>
<para>
Add support for privileges on data types (Peter Eisentraut)
<para>
This adds support for the <acronym>SQL</>-conforming
<literal>USAGE</> privilege on types and domains. The intent is
to be able to restrict which users can create dependencies on types,
since such dependencies limit the owner's ability to alter the type.
</para>
</listitem>
<listitem>
<para>
Check for <command>INSERT</command> privileges in <command>SELECT
INTO</command> / <command>CREATE TABLE AS</command> (KaiGai Kohei)
Because the object is being created by <command>SELECT INTO</command>
or <command>CREATE TABLE AS</command>, the creator would ordinarily
have insert permissions; but there are corner cases where this is not
true, such as when <literal>ALTER DEFAULT PRIVILEGES</> has removed
such permissions.
</itemizedlist>
</sect4>
</sect3>
<sect3>
<title>Utility Operations</title>
<itemizedlist>
<listitem>
<para>
Allow <link linkend="SQL-VACUUM"><command>VACUUM</></link> to more
easily skip pages that cannot be locked (Simon Riggs, Robert Haas)
This change should greatly reduce the incidence of <command>VACUUM</>
getting <quote>stuck</> waiting for other sessions.
</para>
</listitem>
<listitem>
<para>
Make <link linkend="SQL-EXPLAIN"><command>EXPLAIN</></link>
<literal>(BUFFERS)</> count blocks dirtied and written (Robert Haas)
</para>
</listitem>
<listitem>
<para>
Make <command>EXPLAIN ANALYZE</command> report the number of rows
rejected by filter steps (Marko Tiikkaja)
</para>
</listitem>
<listitem>
<para>
Allow <command>EXPLAIN ANALYZE</command> to avoid timing overhead when
time values are not wanted (Tomas Vondra)
This is accomplished by setting the new <literal>TIMING</> option to
<literal>FALSE</>.
</para>
</listitem>
</sect3>
<sect3>
<title>Data Types</title>
<itemizedlist>
<listitem>
<para>
Add support for <link linkend="rangetypes">range data types</link>
(Jeff Davis, Tom Lane, Alexander Korotkov)
A range data type stores a lower and upper bound belonging to its
base data type. It supports operations like contains, overlaps, and
intersection.
</para>
</listitem>
<listitem>
<para>
Add a <link linkend="datatype-json"><type>JSON</type></link>
data type (Robert Haas)
This type stores <acronym>JSON</acronym> (JavaScript Object Notation)
data with proper validation.
Add <link
linkend="functions-json"><function>array_to_json()</></link>
and <function>row_to_json()</> (Andrew Dunstan)
</para>
</listitem>
<listitem>
<para>
Add a <link linkend="datatype-serial"><type>SMALLSERIAL</></link>
data type (Mike Pultz)
<para>
This is like <type>SERIAL</>, except it stores the sequence in
a two-byte integer column (<type>int2</>).
</para>
</listitem>
<listitem>
<para>
Allow <link linkend="SQL-CREATEDOMAIN">domains</link> to be
declared <literal>NOT VALID</> (Álvaro Herrera)
This option can be set at domain creation time, or via <command>ALTER
DOMAIN</command> ... <literal>ADD CONSTRAINT</> ... <literal>NOT
VALID</>. <command>ALTER DOMAIN</command> ... <literal>VALIDATE
CONSTRAINT</> fully validates the constraint.
</para>
</listitem>
<listitem>
<para>
Support more locale-specific formatting options for the <link
linkend="datatype-money"><type>money</></link> data type (Tom Lane)
Specifically, honor all the POSIX options for ordering of the value,
sign, and currency symbol in monetary output. Also, make sure that
the thousands separator is only inserted to the left of the decimal
point, as required by POSIX.
</para>
</listitem>
<listitem>
<para>
Add bitwise <quote>and</>, <quote>or</>, and <quote>not</>
operators for the <type>macaddr</> data type (Brendan Jurd)
</para>
</listitem>
<listitem>
<para>
Allow <link
linkend="functions-xml-processing"><function>xpath()</></link> to
return a single-element <acronym>XML</acronym> array when supplied a
scalar value (Florian Pflug)
<para>
Previously, it returned an empty array. This change will also
cause <function>xpath_exists()</> to return true, not false,
for such expressions.
</para>
<listitem>
<para>
Improve <acronym>XML</acronym> error handling to be more robust
(Florian Pflug)
</para>
</listitem>
</sect3>
<sect3>
<title>Functions</title>
<itemizedlist>
<listitem>
<para>
Allow non-superusers to use <link
linkend="functions-admin-signal"><function>pg_cancel_backend()</></link>
linkend="functions-admin-signal"><function>pg_terminate_backend()</></link>
on other sessions belonging to the same user
(Magnus Hagander, Josh Kupershmidt, Dan Farina)
Previously only superusers were allowed to use these functions.
</para>
</listitem>
<listitem>
<para>
Allow importing and exporting of transaction snapshots (Joachim
Wieland, Tom Lane)
This allows multiple transactions to share identical views of the
database state.
Snapshots are exported via <link
linkend="functions-snapshot-synchronization"><function>pg_export_snapshot()</></link>
and imported via <link linkend="SQL-SET-TRANSACTION"><command>SET
TRANSACTION SNAPSHOT</command></link>. Only snapshots from
currently-running transactions can be imported.
</para>
</listitem>
<listitem>
<para>
Support <link
linkend="functions-info-catalog-table"><literal>COLLATION
FOR</></link> on expressions (Peter Eisentraut)
<para>
This returns a string representing the collation of the expression.
</para>
</listitem>
<listitem>
<para>
Add <link
linkend="functions-info-schema-table"><function>pg_opfamily_is_visible()</></link>
(Josh Kupershmidt)
</para>
</listitem>
<listitem>
<para>
Add a <type>numeric</> variant of <link
linkend="functions-admin-dbsize"><function>pg_size_pretty()</></link>
for use with <function>pg_xlog_location_diff()</> (Fujii Masao)
</para>
</listitem>
<listitem>
<para>
Add a <link
linkend="functions-info-session-table"><function>pg_trigger_depth()</></link>
function (Kevin Grittner)
</para>
<para>
This reports the current trigger call depth.
</para>
</listitem>
<listitem>
<para>
Allow <link
linkend="functions-aggregate-table"><function>string_agg()</></link>
to process <type>bytea</> values (Pavel Stehule)
</para>
</listitem>
<listitem>
<para>
Fix regular expressions in which a back-reference occurs within
a larger quantified subexpression (Tom Lane)
For example, <literal>^(\w+)( \1)+$</>. Previous releases did not
check that the back-reference actually matched the first occurrence.
</para>
</listitem>
</itemizedlist>
<sect3>
<title><link linkend="information-schema">Information Schema</link></title>
<itemizedlist>
<listitem>
<para>
Add information schema views
<structname>role_udt_grants</>, <structname>udt_privileges</>,
and <structname>user_defined_types</> (Peter Eisentraut)
</para>
</listitem>
<listitem>
<para>
Add composite-type attributes to the
information schema <structname>element_types</> view
(Peter Eisentraut)
</para>
</listitem>
<listitem>
<para>
Implement <structfield>interval_type</> columns in the information
schema (Peter Eisentraut)
Formerly these columns read as nulls.
Implement collation-related columns in the information schema
<structname>attributes</>, <structname>columns</>,
<structname>domains</>, and <structname>element_types</>
views (Peter Eisentraut)
</para>
</listitem>
<listitem>
<para>
Implement the <structfield>with_hierarchy</> column in the
information schema <structname>table_privileges</> view (Peter
Eisentraut)
</para>
</listitem>
<listitem>
<para>
Add display of sequence <literal>USAGE</> privileges to information
schema (Peter Eisentraut)
</para>
</listitem>
<listitem>
<para>
Make the information schema show default privileges (Peter
Eisentraut)
Previously, non-empty default permissions were not represented in the
views.
</para>
</listitem>
</itemizedlist>
</sect3>
<sect3>
<title>Server-Side Languages</title>
<sect4>
<title><link linkend="plpgsql">PL/pgSQL</link> Server-Side Language</title>
<itemizedlist>
<listitem>
<para>
Allow the PL/pgSQL <command>OPEN</> cursor command to supply
parameters by name (Yeb Havinga)
</para>
</listitem>
<listitem>
<para>
Add a <command>GET STACKED DIAGNOSTICS</command> PL/pgSQL command
to retrieve exception info (Pavel Stehule)
</para>
</listitem>
<listitem>
<para>
Speed up PL/pgSQL array assignment by caching type information
(Pavel Stehule)
</para>
</listitem>
<listitem>
<para>
Improve performance and memory consumption for long chains of
<literal>ELSIF</> clauses (Tom Lane)
</para>
</listitem>
<listitem>
<para>
Output the function signature, not just the name, in PL/pgSQL
error messages (Pavel Stehule)
</para>
</listitem>
</itemizedlist>
</sect4>
<sect4>
<title><link linkend="plpython">PL/Python</link> Server-Side Language</title>
<itemizedlist>
<listitem>
<para>
Add PL/Python <acronym>SPI</acronym> cursor support (Jan
Urbanski)
<para>
This allows PL/Python to read partial result sets.
</para>
</listitem>
<listitem>
<para>
Add result metadata functions to PL/Python (Peter Eisentraut)
Specifically, this adds result object functions
<literal>.colnames</literal>, <literal>.coltypes</literal>, and
<literal>.coltypmods</literal>.
</para>
</listitem>
<listitem>
<para>
Remove support for Python 2.2 (Peter Eisentraut)
</para>
</listitem>
</itemizedlist>
</sect4>
<sect4>
<title><link linkend="xfunc-sql">SQL</link> Server-Side Language</title>
<itemizedlist>
<listitem>
<para>
Allow <acronym>SQL</acronym>-language functions to reference
parameters by name (Matthew Draper)
To use this, simply name the function arguments and then reference
the argument names in the <acronym>SQL</acronym> function body.
</para>
</listitem>
</sect4>
</sect3>
<sect3>
<title>Client Applications</title>
<itemizedlist>
<listitem>
<para>
Add <link linkend="APP-INITDB"><application>initdb</></link>
options <option>--auth-local</> and <option>--auth-host</>
(Peter Eisentraut)
This allows separate control of <literal>local</> and
<literal>host</> <filename>pg_hba.conf</filename> authentication
settings. <option>--auth</> still controls both.
</para>
</listitem>
<listitem>
<para>
Add <option>--replication</>/<option>--no-replication</> flags to
<link linkend="APP-CREATEUSER"><application>createuser</></link>
to control replication permission (Fujii Masao)
</para>
</listitem>
<listitem>
<para>
Add the <option>--if-exists</> option to <link
linkend="APP-DROPDB"><application>dropdb</></link> and <link
linkend="APP-DROPUSER"><application>dropuser</></link> (Josh
Kupershmidt)
</para>
</listitem>
<listitem>
<para>
Give command-line tools the ability to specify the name of the
database to connect to, and fall back to <literal>template1</>
if a <literal>postgres</> database connection fails (Robert Haas)
</para>
</listitem>
</itemizedlist>
<sect4>
<title><link linkend="APP-PSQL"><application>psql</></link></title>
<itemizedlist>
<listitem>
<para>
Add a display mode to auto-expand output based on the
display width (Peter Eisentraut)
<para>
This adds the <literal>auto</> option to the <command>\x</>
command, which switches to the expanded mode when the normal
output would be wider than the screen.
</para>
</listitem>
<listitem>
<para>
Allow inclusion of a script file that is named relative to the
directory of the file from which it was invoked (Gurjeet Singh)
This is done with a new command <command>\ir</>.
</para>
</listitem>
<listitem>
<para>
Add support for non-<acronym>ASCII</acronym> characters in
<application>psql</> variable names (Tom Lane)
</para>
</listitem>
<listitem>
<para>
Add support for major-version-specific <filename>.psqlrc</> files
(Bruce Momjian)
</para>
<application>psql</> already supported minor-version-specific
<filename>.psqlrc</> files.
</para>
</listitem>
<listitem>
<para>
Provide environment variable overrides for <application>psql</>
history and startup file locations (Andrew Dunstan)
</para>
<envar>PSQL_HISTORY</envar> and <envar>PSQLRC</envar> now
determine these file names if set.
</para>
</listitem>
<listitem>
<para>
Add a <command>\setenv</> command to modify
the environment variables passed to child processes (Andrew Dunstan)
</para>
</listitem>
<listitem>
<para>
Name <application>psql</>'s temporary editor files with a
<filename>.sql</> extension (Peter Eisentraut)
</para>
<para>
This allows extension-sensitive editors to select the right mode.
</para>
<listitem>
<para>
Allow <application>psql</> to use zero-byte field and record
separators (Peter Eisentraut)
Various shell tools use zero-byte (NUL) separators,
e.g. <application>find</>.
</para>
</listitem>
<listitem>
<para>
Make the <command>\timing</> option report times for
failed queries (Magnus Hagander)
</para>
Previously times were reported only for successful queries.
</para>
</listitem>
<listitem>
<para>
Unify and tighten <application>psql</>'s treatment of <command>\copy</>
and SQL <command>COPY</> (Noah Misch)
This fix makes failure behavior more predictable and honors
<command>\set ON_ERROR_ROLLBACK</>.
</para>
</listitem>
</itemizedlist>
</sect4>
<sect4>
<title>Informational Commands</title>
<itemizedlist>
<listitem>
<para>
Make <command>\d</> on a sequence show the
table/column name owning it (Magnus Hagander)
</para>
</listitem>
<listitem>
<para>
Show statistics target for columns in <command>\d+</> (Magnus
Hagander)
</para>
</listitem>
<listitem>
<para>
Show role password expiration dates in <command>\du</>
(Fabrízio de Royes Mello)
</para>
</listitem>
<listitem>
<para>
Display comments for casts, conversions, domains, and languages
(Josh Kupershmidt)
These are included in the output of <command>\dC+</>,
<command>\dc+</>, <command>\dD+</>, and <command>\dL</> respectively.
</para>
</listitem>
<listitem>
<para>
Display comments for <acronym>SQL</acronym>/<acronym>MED</acronym>
objects (Josh Kupershmidt)
These are included in the output of <command>\des+</>,
<command>\det+</>, and <command>\dew+</> for foreign servers, foreign
tables, and foreign data wrappers respectively.
</para>
</listitem>
<listitem>
<para>
Change <command>\dd</> to display comments only for object types
without their own backslash command (Josh Kupershmidt)
</para>
</listitem>
</itemizedlist>
</sect4>
<sect4>
<title>Tab Completion</title>
<itemizedlist>
<listitem>
<para>
In <application>psql</> tab completion, complete <acronym>SQL</>
keywords in either upper or lower case according to the new <link
linkend="APP-PSQL-variables"><literal>COMP_KEYWORD_CASE</></link>
setting (Peter Eisentraut)
</para>
</listitem>
<listitem>
<para>
Add tab completion support for
<command>EXECUTE</command> (Andreas Karlsson)
</para>
</listitem>
<listitem>
<para>
Allow tab completion of role references in
<command>GRANT</command>/<command>REVOKE</command> (Peter
Eisentraut)
</para>
</listitem>
<listitem>
<para>
Allow tab completion of file names to supply quotes, when necessary
(Noah Misch)
</para>
</listitem>
<listitem>
<para>
Change tab completion support for
<command>TABLE</command> to also include views (Magnus Hagander)
</para>
</listitem>
</itemizedlist>
</sect4>
<sect4>
<title><link linkend="APP-PGDUMP"><application>pg_dump</></link></title>
<itemizedlist>
<listitem>
<para>
Add an <option>--exclude-table-data</> option to
<application>pg_dump</> (Andrew Dunstan)
</para>
<para>
This allows dumping of a table's definition but not its data,
on a per-table basis.
</para>
</listitem>
<listitem>
<para>
Add a <option>--section</> option to <application>pg_dump</>
and <application>pg_restore</> (Andrew Dunstan)
<para>
Valid values are <literal>pre-data</>, <literal>data</>,
and <literal>post-data</>. The option can be
given more than once to select two or more sections.
</para>
</listitem>
<listitem>
<para>
linkend="APP-PG-DUMPALL"><application>pg_dumpall</></link> dump all
roles first, then all configuration settings on roles (Phil Sorber)
</para>
This allows a role's configuration settings to mention other
roles without generating an error.
</para>
</listitem>
<listitem>
<para>
Allow <application>pg_dumpall</> to avoid errors if the
<literal>postgres</> database is missing in the new cluster
(Robert Haas)
</para>
</listitem>
<listitem>
<para>
Dump foreign server user mappings in user name order (Peter
Eisentraut)
</para>
<para>
This helps produce deterministic dump files.
</para>
</listitem>
<listitem>
<para>
Dump operators in a predictable order (Peter Eisentraut)
</para>
</listitem>
<listitem>
<para>
Tighten rules for when extension configuration tables are dumped
by <application>pg_dump</> (Tom Lane)
</para>
</listitem>
<listitem>
<para>
Make <application>pg_dump</> emit more useful dependency