Newer
Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
<!-- doc/src/sgml/release-9.2.sgml -->
<!-- See header comment in release.sgml about typical markup -->
<sect1 id="release-9-2">
<title>Release 9.2</title>
<note>
<title>Release Date</title>
<simpara>2012-??-??</simpara>
<simpara>CURRENT AS OF 2012-05-09</simpara>
</note>
<sect2>
<title>Overview</title>
<para>
NARRATIVE HERE. Major enhancements include:
</para>
<itemizedlist>
<listitem><para>MAJOR LIST HERE</para></listitem>
<!-- This list duplicates items below, but without authors or details-->
</itemizedlist>
<para>
The above items are explained in more detail in the sections below.
</para>
</sect2>
<sect2>
<title>Migration to Version 9.2</title>
<para>
A dump/restore using <application>pg_dump</application>, or use of
<application>pg_upgrade</application>, is required for those wishing
to migrate data from any previous release.
</para>
<para>
Version 9.2 contains a number of changes that may affect compatibility
with previous releases. Observe the following incompatibilities:
</para>
<sect3>
<title>Functions</title>
<itemizedlist>
<listitem>
<para>
Ensure that <link
linkend="functions-xml-processing"><function>xpath()</></link>
escapes special characters in string values (Florian Pflug)
<para>
Without this it is possible to output illegal
<acronym>XML</acronym>.
</para>
</listitem>
<listitem>
<para>
Remove <link linkend="hstore">hstore</link>'s <literal>=></>
operator (Robert Haas)
<para>
Users should now use hstore(text, text). Since
<productname>PostgreSQL</productname> 9.0, a warning message is
emitted when an operator named => is created because
the <acronym>SQL</acronym> standard reserves that token for
another use.
</para>
</listitem>
<listitem>
<para>
Have <link
linkend="functions-admin-dbobject"><function>pg_relation_size()</></link>
and friends return NULL if the object does not exist (Phil Sorber)
</para>
<para>
This prevents queries that call these functions from returning
errors during concurrent drop operations.
</para>
</listitem>
<listitem>
<para>
Remove the <link
linkend="catalog-pg-tablespace"><structname>spclocation</></link>
field from <structname>pg_tablespace</> (Magnus Hagander)
<para>
This field was duplicative of the symbolic links already
present in the data directory. This allows tablespace
directories to be moved while the server is down. Also add
<function>pg_tablespace_location()</> to allow querying of the
symbolic links.
</para>
</listitem>
<listitem>
<para>
Move <type>tsvector</> most-common-element statistics to new
<link linkend="view-pg-stats"><structname>pg_stats</></link> columns
(Alexander Korotkov)
</para>
<para>
Consult <structname>most_common_elems</>
and <structname>most_common_elem_freqs</> for the data formerly
available in <structname>most_common_vals</>
and <structname>most_common_freqs</>.
</para>
</listitem>
</itemizedlist>
<sect4>
<title>Temporal Functions</title>
<itemizedlist>
<listitem>
<para>
Have <link
linkend="functions-datetime-extract"><function>EXTRACT</></link> of
a non-timezone-aware value measure the epoch from local midnight,
not <acronym>UTC</acronym> midnight (Tom Lane)
</para>
<para>
Having these computations depend on <acronym>UTC</acronym>
was inconsistent. The old behavior is available by casting the
value to timestamp with time zone.
</para>
</listitem>
<listitem>
<para>
Properly parse time strings with trailing <literal>yesterday</>,
<literal>today</>, and <literal>tomorrow</> (Dean Rasheed)
</para>
<para>
Previously, <command>SELECT '04:00:00
yesterday'::timestamp</command> returned yesterday's date at
midnight.
</para>
</listitem>
<listitem>
<para>
Fix <link
linkend="functions-formatting"><function>to_date()</></link> and
<function>to_timestamp()</> to wrap incomplete dates toward 2020
(Bruce Momjian)
</para>
Previously, supplied years and year masks of less than four digits
wrapped inconsistently.
</para>
</listitem>
</itemizedlist>
</sect4>
<sect3>
<title>Object Modification</title>
<itemizedlist>
<listitem>
<para>
Prevent <link linkend="SQL-ALTERDOMAIN"><command>ALTER
DOMAIN</command></link> from working on non-domain types (Peter
Eisentraut)
</para>
<para>
Owner and schema changes were previously possible on non-domain
types.
</para>
</listitem>
<listitem>
<para>
No longer auto-lowercase procedural language names (Robert Haas)
</para>
While unquoted language identifiers are still lowercased, strings
and quoted identifiers are no longer forcibly down-cased.
Thus for example <literal>CREATE FUNCTION ... LANGUAGE 'C'</>
will no longer work; it must be spelled <literal>'c'</>, or better
omit the quotes.
</para>
</listitem>
</itemizedlist>
</sect3>
<sect3>
<title>Command-Line Tools</title>
<itemizedlist>
<listitem>
<para>
Provide consistent backquote, variable
expansion, and quoted substring behavior in <link
linkend="APP-PSQL"><application>psql</></link> meta-command
arguments (Tom Lane)
</para>
<para>
Previously, such usage was only properly expanded when isolated by
whitespace, e.g. <literal>'FOO'BAR</> was output as <literal>FOO
BAR</>.
</para>
</listitem>
<listitem>
<para>
No longer treat <link
linkend="APP-CLUSTERDB"><application>clusterdb</></link>
table names as double-quoted; no longer treat <link
linkend="APP-REINDEXDB"><application>reindexdb</></link> table
and index names as double-quoted (Bruce Momjian)
</para>
<para>
Users must supply double-quotes in the command arguments to
accomplish double-quoting behavior.
</para>
</listitem>
<listitem>
<para>
Disable prompting for the user name in <link
linkend="APP-DROPDB"><application>dropdb</></link> unless
<option>--interactive</> is specified (Peter Eisentraut)
</para>
</listitem>
<listitem>
<para>
No longer have <link
linkend="APP-CREATEUSER"><application>createuser</></link> prompt
for settings (Peter Eisentraut)
<para>
Previously <application>createuser</> prompted regarding various
user settings.
</para>
</listitem>
<listitem>
<para>
Remove <link linkend="APP-INITDB"><application>initdb</></link>
support for the crypt authentication method (Peter Eisentraut)
</para>
<para>
This was removed from the backend years ago.
</para>
</listitem>
</itemizedlist>
</sect3>
<sect3>
<title>Server Settings</title>
<itemizedlist>
<listitem>
<para>
Remove the <filename>postgresql.conf</filename> setting
<varname>silent_mode</> (Heikki Linnakangas)
</para>
<para>
This behavior could already be accomplished with <command>pg_ctl
-l postmaster.log</>.
</para>
</listitem>
<listitem>
<para>
Remove <acronym>GUC</acronym> <varname>wal_sender_delay</>,
as it is no longer needed (Tom Lane)
</para>
<para>
The new "latch" capability made this setting unnecessary.
</para>
</listitem>
<listitem>
<para>
Remove the <varname>custom_variable_classes</>
<filename>postgresql.conf</filename> parameter (Tom Lane)
</para>
<para>
The checking provided by this setting was dubious. Now any
setting can be prefixed by any class name.
</para>
</listitem>
Add GUC <link linkend="guc-ssl-key-file">parameters</link> for
specifying the locations of server-side <acronym>SSL</acronym>
files (Peter Eisentraut)
</para>
<para>
This allows changing the location of the files that were previously
hard-coded to <filename>server.crt</>, <filename>server.key</>,
<filename>root.crt</>, <filename>root.crl</>. <acronym>CA</>
(crt) and <acronym>CRL</acronym> files no longer have default
names, and if specified, these files must exist.
</para>
</listitem>
</itemizedlist>
</sect3>
<sect3>
<title>Monitoring</title>
<itemizedlist>
<listitem>
<para>
Rename <link
linkend="monitoring-stats-views-table"><structname>pg_stat_activity</></link><structname>.procpid</>
to <structname>pid</>, to match other system tables (Magnus Hagander)
</para>
</listitem>
<listitem>
<para>
Create a separate <structname>pg_stat_activity</> column to
report state information, e.g. idle (Scott Mead, Magnus Hagander)
The last query values are preserved, allowing for enhanced analysis.
</para>
</listitem>
<listitem>
<para>
Rename <structname>pg_stat_activity.current_query</> to
<structname>query</> because it is not cleared when the query
completes (Magnus Hagander)
</para>
</listitem>
<listitem>
<para>
Change all <acronym>SQL</acronym>-level statistics timing values
to float8-stored milliseconds (Tom Lane)
</para>
<para>
The columns affected are:
<structname>pg_stat_user_functions.total_time</>,
<structname>pg_stat_user_functions.self_time</>,
<structname>pg_stat_xact_user_functions.total_time</>,
<structname>pg_stat_xact_user_functions.self_time</>.
</para>
</listitem>
<listitem>
<para>
Change <application>pg_stat_statements</>'
<structname>total_time</> column to be measured in milliseconds
(Tom Lane)
</para>
</listitem>
</itemizedlist>
</sect3>
</sect2>
<sect2>
<title>Changes</title>
<para>
Below you will find a detailed account of the changes between
<productname>PostgreSQL</productname> 9.2 and the previous major
release.
</para>
<sect3>
<title>Server</title>
<sect4>
<title>Performance</title>
<itemizedlist>
<listitem>
<para>
Allow queries to retrieve data only from indexes, avoiding heap
access (Robert Haas, Ibrar Ahmed, Heikki Linnakangas, Tom Lane)
This is often called "index-only scans", a feature which now enables
the use of indexes with additional columns, or "covering indexes".
This is possible for heap pages with exclusively all-visible
tuples, as reported by the visibility map. The visibility map was
made crash-safe as a necessary part of implementing this feature.
</para>
</listitem>
<listitem>
<para>
Allow frequently uncontended locks to be recorded using a new
</para>
</listitem>
<listitem>
<para>
Move the frequently accessed members of the <structname>PGPROC</>
shared memory array to a separate section, for performance (Pavan
Deolasee, Heikki Linnakangas, Robert Haas)
</para>
</listitem>
<listitem>
<para>
Reduce overhead of creating virtual transaction id locks (Robert
</para>
</listitem>
<listitem>
<para>
Reduce overhead for shared invalidation cache messages (Robert
</para>
</listitem>
<listitem>
<para>
Improve <command>COPY</command> performance by adding tuples to
the heap in batches (Heikki Linnakangas)
</para>
</listitem>
<listitem>
<para>
Improve GiST geometric type index performance by producing better
trees with less memory allocation overhead (Alexander Korotkov)
</para>
</listitem>
<listitem>
<para>
Allow hint bits to be set sooner for temporary and unlogged tables
(Robert Haas)
</para>
</listitem>
<listitem>
<para>
Allow sorting to be performed by inlined and faster,
non-<acronym>SQL</acronym>-callable comparison functions (Peter
Geoghegan, Robert Haas, Tom Lane)
</para>
</listitem>
<listitem>
<para>
Add the <link linkend="SPGiST">SP-GiST</link> (Space-Partitioned
GiST) index access method (Teodor Sigaev, Oleg Bartunov, Tom
<para>
SP-GiST is comparable to GiST in flexibility, but supports
non-balanced partitioned search structures rather than balanced
trees. CLARIFY?
</para>
</listitem>
<listitem>
<para>
Improve GiST index build times (Alexander Korotkov, Heikki
Linnakangas)
</para>
</listitem>
<listitem>
<para>
Take fewer <acronym>MVCC</acronym> snapshots, for performance
(Robert Haas)
</para>
</listitem>
<listitem>
<para>
Have the number of clog buffers scale based on <link
linkend="guc-shared-buffers"><varname>shared_buffers</></link>
(Robert Haas, Simon Riggs, Tom Lane)
</para>
</listitem>
<listitem>
<para>
Reduce the overhead of serializable isolation level locks (Dan
</para>
</listitem>
<listitem>
<para>
Allow group commit to work effectively under heavy load (Peter
Geoghegan, Simon Riggs, Heikki Linnakangas)
</para>
<para>
Previously, batching of commits became ineffective as the write
workload increased because of internal lock contention.
<listitem>
<para>
Improve PowerPC and Itanium spinlock performance (Manabu Ori,
Robert Haas, Tom Lane)
</para>
</listitem>
</itemizedlist>
</sect4>
<sect4>
<title>Process Management</title>
<itemizedlist>
<listitem>
<para>
Create a dedicated worker process to perform checkpoints (Simon
<para>
Formerly the background writer did dirty page writing and
checkpointing.
</para>
</listitem>
<listitem>
<para>
Improve asynchronous commit behavior by waking up sooner (Simon
Riggs)
</para>
<para>
Previously, only <link
linkend="guc-wal-writer-delay"><varname>wal_writer_delay</></link>
triggered <acronym>WAL</acronym> flushing to disk; now filling a
<acronym>WAL</acronym> buffer also triggers <acronym>WAL</acronym>
writes.
</para>
</listitem>
<listitem>
<para>
Allow backends to detect postmaster death via a pipe read failure,
rather than polling (Peter Geoghegan, Heikki Linnakangas, Tom Lane)
The wait events are internally called "latches".
</para>
</listitem>
<listitem>
<para>
Allow the <link linkend="pg-stat-bgwriter-view">bgwriter</link>,
<link linkend="guc-wal-writer-delay">walwriter</link>, <link
linkend="monitoring-stats">statistics collector</link>, archiver,
and checkpointer to sleep more efficiently during periods of
inactivity (Peter Geoghegan, Tom Lane)
This reduces <acronym>CPU</acronym> wake-ups, which dramatically
reduces power consumption on idle servers.
</para>
</listitem>
<listitem>
<para>
Pass the safe number of file descriptors to Windows processes
(Heikki Linnakangas)
</para>
<para>
This allows Windows sessions to cache more open file descriptors.
</para>
</listitem>
</itemizedlist>
</sect4>
<sect4>
<title>Optimizer</title>
<itemizedlist>
<listitem>
<para>
Improve the ability of the planner to choose parameterized plans
(Tom Lane)
</para>
<para>
A prepared statement is now parsed, analyzed, and rewritten,
but not necessarily planned. When the prepared plan is executed
with parameters, the planner might replan it for every constant,
or it might execute a generic plan if its cost is close to that
of a constant-specific plan. CLARIFY
</para>
</listitem>
<listitem>
<para>
Allow the optimizer to use <literal>CHECK</> constraints defined
on tables referenced in subqueries in planning decisions (Tom Lane)
</para>
<para>
This is only enabled when <link
linkend="guc-constraint-exclusion"><literal>constraint_exclusion
= on</></link>.
</para>
</listitem>
<listitem>
<para>
Allow <literal>indexedcol op ANY(ARRAY[...])</> conditions to be
used in plain indexscans (Tom Lane)
</para>
</listitem>
<listitem>
<para>
Improve heuristics for determining the type of unknown values by
considering other data types involved in the operation (Tom Lane)
</para>
</listitem>
<listitem>
<para>
Allow better matching of index columns with non-default operator
qualifications (Tom Lane)
</para>
</listitem>
<listitem>
<para>
Improve the planner's ability to use nested loops with inner
index scans (Tom Lane)
</para>
</listitem>
<listitem>
<para>
Support <function>MIN</>/<function>MAX</> index optimizations on
boolean columns (Marti Raudsepp)
</para>
</listitem>
</itemizedlist>
<sect5>
<title>Optimizer Statistics</title>
<itemizedlist>
<listitem>
<para>
Improve the ability to use statistics on columns referenced in
subqueries (Tom Lane)
</para>
</listitem>
<listitem>
<para>
Improve optimizer estimation for subqueries using
<literal>DISTINCT</> (Tom Lane)
</para>
</listitem>
<listitem>
<para>
Collect and use element-frequency statistics for arrays (Alexander
Korotkov, Tom Lane)
<para>
This patch improves selectivity estimation for the array <@,
&&, and @> (containment and overlaps) array operators.
</para>
</listitem>
<listitem>
<para>
Improve costing of partial indexes (Tom Lane)
</para>
</listitem>
<listitem>
<para>
Allow analyze statistics to be collected for foreign tables
(Etsuro Fujita)
</para>
</listitem>
</itemizedlist>
</sect5>
</sect4>
<sect4>
<title>Authentication</title>
<itemizedlist>
<listitem>
<para>
Do not assume roles and <literal>samerole</> specified in <link
linkend="auth-pg-hba-conf"><filename>pg_hba.conf</filename></link>
include superusers (Andrew Dunstan)
<para>
This makes it easier to use reject lines with group roles.
</para>
</listitem>
<listitem>
<para>
Make superuser imply replication privilege (Noah Misch)
</para>
<para>
This avoids the need to explicitly assign such privileges.
</para>
</listitem>
<listitem>
<para>
Adjust <filename>pg_hba.conf</filename> processing to more
consistently handle token parsing (Brendan Jurd, Álvaro
</para>
</listitem>
<listitem>
<para>
Disallow empty <filename>pg_hba.conf</filename> files (Tom Lane)
</para>
<para>
This was done to more quickly report misconfiguration.
</para>
</listitem>
</itemizedlist>
</sect4>
<sect4>
<title>Monitoring</title>
<itemizedlist>
<listitem>
<para>
Attempt to log the current query string before a backend crash
(Marti Raudsepp)
</para>
</listitem>
<listitem>
<para>
Make logging of autovacuum I/O activity more verbose (Greg
Smith, Noah Misch)
<para>
This logging is triggered by <link
linkend="guc-log-autovacuum-min-duration"><varname>log_autovacuum_min_duration</></link>.
</para>
</listitem>
<listitem>
<para>
Have <acronym>WAL</acronym> replay report failures sooner
(Fujii Masao)
</para>
<para>
There were some cases where failures were only reported once the
server went into master mode.
</para>
</listitem>
<listitem>
<para>
Add <link
linkend="functions-admin-backup"><function>pg_xlog_location_diff()</></link>
to simplify xlog comparisons (Euler Taveira de Oliveira)
<para>
This is useful for computing replication lag.
</para>
</listitem>
<listitem>
<para>
Support configurable eventlog application names on Windows
(MauMau, Magnus Hagander)
<para>
This allows different instances to use the eventlog
with different identifiers, by setting the <link
linkend="guc-event-source"><varname>event_source</></link>
<acronym>GUC</acronym>, which is similar to how <link
linkend="guc-syslog-ident"><varname>syslog_ident</></link> works.
</para>
</listitem>
<listitem>
<para>
Change "unexpected EOF" message to <literal>DEBUG1</> level,
except when there is an open transaction (Magnus Hagander)
</para>
</listitem>
</itemizedlist>
</sect4>
<sect4>
<title>Statistical Views</title>
<itemizedlist>
<listitem>
<para>
Track temporary file sizes and file counts in the <link
linkend="monitoring-stats-views"><structname>pg_stat_database</></link>
system view (Tomas Vondra)
</para>
</listitem>
<listitem>
<para>
Add a deadlock counter to the <structname>pg_stat_database</>
system view (Magnus Hagander)
</para>
</listitem>
<listitem>
<para>
Add a <acronym>GUC</acronym> setting <link
linkend="guc-track-io-timing"><varname>track_io_timing</></link>
to track I/O timings (Ants Aasma, Robert Haas)
</para>
</listitem>
<listitem>
<para>
Report checkpoint timing information in <link
linkend="pg-stat-bgwriter-view"><structname>pg_stat_bgwriter</></link>
</para>
</listitem>
</itemizedlist>
</sect4>
<sect4>
<title>Server Settings</title>
<itemizedlist>
<listitem>
<para>
Allow <link
linkend="guc-deadlock-timeout"><varname>deadlock_timeout</></link>
to be set per-session by superusers, not just per-cluster (Noah
<para>
This allows <varname>deadlock_timeout</> to be reduced for
transactions that are likely to be involved in a deadlock,
thus detecting it more quickly. It can also be used to reduce
the chances of a session being chosen for cancellation due to
a deadlock.
</para>
</listitem>
<listitem>
<para>
Add <acronym>GUC</acronym> parameter <link
linkend="guc-temp-file-limit"><varname>temp_file_limit</></link>
to constrain temporary file space usage per session (Mark Kirkwood)
</para>
</listitem>
<listitem>
<para>
Add <filename>postgresql.conf</filename> category "Replication /
Sending Servers" (Fujii Masao)
</para>
</listitem>
<listitem>
<para>
Allow a superuser to <command>SET</command> an extension's
superuser-only custom variable before loading the associated
extension (Tom Lane)
</para>
<para>
The system now remembers whether a <command>SET</command> was
performed by a superuser.
</para>
</listitem>
<listitem>
<para>
Add <link linkend="app-postmaster">postmaster</link> <option>-C</>
option to query configuration parameters (Bruce Momjian)
</para>
<para>
This allows <application>pg_ctl</> to better handle
configuration-only directory installations.
</para>
</listitem>
<listitem>
<para>
Force the locale to be recorded more specifically, rather than
using the empty string to mean the default (Tom Lane)
</para>
</listitem>
</itemizedlist>
<sect5>
<title><filename>postgresql.conf</filename></title>
<itemizedlist>
<listitem>
<para>
Allow multiple errors in <filename>postgresql.conf</filename>
to be reported, rather than just the first one (Alexey Klyukin,
Tom Lane)
</para>
</listitem>
<listitem>
<para>
Allow a reload of <filename>postgresql.conf</filename> to be