release-9.2.sgml

<!-- 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,
        Radoslaw Smogura)
       </para>
       
       <para>
        Without this it is possible to output illegal
        <acronym>XML</acronym>.
       </para>
      </listitem>
      
      <listitem>
       <para>
        Remove <link linkend="hstore">hstore</link>'s <literal>=&gt;</>
        operator (Robert Haas, David Wheeler)
       </para>
       
       <para>
        Users should now use hstore(text, text).  Since
        <productname>PostgreSQL</productname> 9.0, we have emitted a
        warning message when an operator named =&gt; 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,
        Tom Lane)
       </para>
       
       <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>
      
    </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>
       
       <para>
        Previously supplied years and year masks of less than four digits
        wrapped inconsistently.
       </para>
      </listitem>

     </itemizedlist>
    </sect4>

    </sect3>

   <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>
       
       <para>
        While unquoted language identifiers are still lowercased, double
        and single-quoted identifiers are no longer down-cased.
       </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, Josh
        Kupershmidt)
       </para>
      </listitem>
      
      <listitem>
       <para>
        No longer have <link
        linkend="APP-CREATEUSER"><application>createuser</></link> prompt
        for settings (Peter Eisentraut, Josh Kupershmidt)
       </para>
       
       <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>
      
      <listitem>
       <para>
        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,
        Greg Smith)
       </para>
       
       <para>
        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, Tom Lane)
       </para>
       
       <para>
        This is often called "index-only scans" or "covering indexes".
        This is possible for heap pages with exclusively all-visible
        tuples, as reported by the visibility map.
       </para>
      </listitem>
      
      <listitem>
       <para>
        Allow frequently uncontended locks to be recorded using a new
        lightweight lock mechanism (Robert Haas, Jeff Davis)
       </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
        Haas, Jeff Davis)
       </para>
      </listitem>
      
      <listitem>
       <para>
        Reduce overhead for shared invalidation cache messages (Robert
        Haas, Noah Misch)
       </para>
      </listitem>
      
      <listitem>
       <para>
        Improve performance of transactions using many savepoints (Simon
        Riggs, Robert Haas)  PARTIALLY REVERTED.  KEEP?
       </para>
      </listitem>
      
      <listitem>
       <para>
        Improve <command>COPY</command> performance by adding tuples to
        the heap in batches (Heikki Linnakangas)
       </para>
      </listitem>
      
      <listitem>
       <para>
        Make the visibility map crash-safe (Robert Haas, Noah Misch)
       </para>
       
       <para>
        This helps vacuum be more efficient, and is necessary for
        index-only scans.
       </para>
      </listitem>
      
      <listitem>
       <para>
        Improve GiST box and point index performance by producing better
        trees with less memory allocation overhead (Alexander Korotkov,
        Heikki Linnakangas, Kevin Grittner)
       </para>
      </listitem>
      
      <listitem>
       <para>
        Allow hint bits to be set sooner for temporary and unlogged tables
        (Robert Haas)
       </para>
      </listitem>
      
      <listitem>
       <para>
        Reduce checkpoints and <acronym>WAL</acronym> traffic on low
        activity servers (Simon Riggs)
       </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 Lane,
        Robert Haas)
       </para>
       
       <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)
       </para>
      </listitem>
      
      <listitem>
       <para>
        Take fewer <acronym>MVCC</acronym> snapshots, for performance
        (Robert Haas, Dimitri Fontaine)
       </para>
      </listitem>
      
      <listitem>
       <para>
        Have the number of clog buffer count scale based on <link
        linkend="guc-shared-buffers"><varname>shared_buffers</></link>
        (Robert Haas)
       </para>
      </listitem>
      
      <listitem>
       <para>
        Reduce the overhead of serializable isolation level locks (Dan
        Ports, Heikki Linnakangas)
       </para>
      </listitem>
      
      <listitem>
       <para>
        Improve performance of <acronym>WAL</acronym> writes when multiple
        transactions commit at the same time (Peter Geoghegan, Simon Riggs,
        Heikki Linnakangas)
       </para>
      </listitem>
      
      <listitem>
       <para>
        Improve PowerPC and Itanium spinlock performance (Manabu Ori,
        Tom Lane, Robert Haas)
       </para>
      </listitem>
      
     </itemizedlist>

    </sect4>

    <sect4>
     <title>Process Management</title>

     <itemizedlist>

      <listitem>
       <para>
        Create a dedicated worker process to perform checkpoints (Simon
        Riggs, Dickson Guedes)
       </para>
       
       <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)
       </para>
       
       <para>
        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>, and <link
        linkend="monitoring-stats">statistics collector</link> to sleep
        more efficiently during periods of inactivity (Peter Geoghegan,
        Heikki Linnakangas, Tom Lane)
       </para>
       
       <para>
        This reduces <acronym>CPU</acronym> wake-ups.
       </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, Abhijit Menon-Sen)
       </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, Noah Misch, Nathan Boley, Tom Lane, Daniele Varrazzo)
       </para>
       
       <para>
        This patch improves selectivity estimation for the array &lt;@,
        &amp;&amp;, and @&gt; (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, Shigeru Hanada, Tom Lane)
       </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, Robert Haas)
       </para>
       
       <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, &Aacute;lvaro
        Herrera, Pavel Stehule)
       </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, <acronym>PDXPUG</>, Tom Lane)
       </para>
      </listitem>
      
      <listitem>
       <para>
        Make logging of autovacuum I/O activity more verbose (Greg Smith,
        Noah Misch)
       </para>
       
       <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, Fujii
        Masao, Magnus Hagander)
       </para>
       
       <para>
        This is useful for computing replication lag.
       </para>
      </listitem>
      
      <listitem>
       <para>
        Support configurable eventlog application names on Windows (MauMau,
        Magnus Hagander)
       </para>
       
       <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
        (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, Jaime Casanova)
       </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, Greg Smith, Robert Haas)
       </para>
      </listitem>
      
      <listitem>
       <para>
        Report checkpoint timing information in <link
        linkend="pg-stat-bgwriter-view"><structname>pg_stat_bgwriter</></link>
        (Greg Smith, Peter Geoghegan, Robert Haas)
       </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
        Misch, Shigeru Hanada)
       </para>
       
       <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,
        C&eacute;dric Villemain, Tatsuo Ishii)
       </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,