release.sgml

PostgreSQL7.4.6 for Linux

     <listitem>
      <para>
       Inner joins using the explicit <literal>JOIN</literal> syntax
       may behave differently because they are now better
       optimized.
      </para>
     </listitem>

     <listitem>
      <para>
       A number of server configuration parameters have been renamed
       for clarity, primarily those related to
       logging.
      </para>
     </listitem>

     <listitem>
      <para>
       <literal>FETCH 0</literal> or <literal>MOVE 0</literal> now
       does nothing.  In prior releases, <literal>FETCH 0</literal>
       would fetch all remaining rows, and <literal>MOVE 0</literal>
       would move to the end of the cursor.
      </para>
     </listitem>
 
     <listitem>
      <para>
       <command>FETCH</command> and <command>MOVE</command> now return
       the actual number of rows fetched/moved, or zero if at the
       beginning/end of the cursor.  Prior releases would return the
       row count passed to the command, not the number of rows
       actually fetched or moved.
      </para>
     </listitem>

     <listitem>
      <para>
       <command>COPY</command> now can process files that use
       carriage-return or carriage-return/line-feed end-of-line
       sequences. Literal carriage-returns and line-feeds are no
       longer accepted in data values; use <literal>\r</literal> and
       <literal>\n</literal> instead.
      </para>
     </listitem>

     <listitem>
      <para>
       Trailing spaces are now trimmed when converting from type
       <type>char(<replaceable>n</>)</type> to
       <type>varchar(<replaceable>n</>)</type> or <type>text</type>.
       This is what most people always expected to happen anyway.
      </para>
     </listitem>

     <listitem>
      <para>
       The data type <type>float(<replaceable>p</>)</type> now
       measures <replaceable>p</> in binary digits, not decimal
       digits.  The new behavior follows the SQL standard.
      </para>
     </listitem>

     <listitem>
      <para>
       Ambiguous date values now must match the ordering specified by
       the <varname>datestyle</varname> setting.  In prior releases, a
       date specification of <literal>10/20/03</> was interpreted as a
       date in October even if <varname>datestyle</> specified that
       the day should be first.  7.4 will throw an error if a date
       specification is invalid for the current setting of
       <varname>datestyle</>.
      </para>
     </listitem>

     <listitem>
      <para>
       The functions <function>oidrand</function>,
       <function>oidsrand</function>, and
       <function>userfntest</function> have been removed.  These
       functions were determined to be no longer useful.
      </para>
     </listitem>

     <listitem>
      <para>
       String literals specifying time-varying date/time values, such
       as <literal>'now'</literal> or <literal>'today'</literal> will
       no longer work as expected in column default expressions; they
       now cause the time of the table creation to be the default, not
       the time of the insertion.  Functions such as
       <function>now()</>, <function>current_timestamp</>, or
       <function>current_date</function> should be used instead.
      </para>

      <para>
       In previous releases, there was special code so that strings
       such as <literal>'now'</literal> were interpreted at
       <command>INSERT</> time and not at table creation time, but
       this work around didn't cover all cases.  Release 7.4 now
       requires that defaults be defined properly using functions such
       as <function>now()</> or <function>current_timestamp</>. These
       will work in all situations.
      </para>
     </listitem>

     <listitem>
      <para>
       The dollar sign (<literal>$</>) is no longer allowed in
       operator names.  It can instead be a non-first character in
       identifiers.  This was done to improve compatibility with other
       database systems, and to avoid syntax problems when parameter
       placeholders (<literal>$<replaceable>n</></>) are written
       adjacent to operators.
      </para>
     </listitem>
    </itemizedlist>
   </para>
  </sect2>

  <sect2>
   <title>Changes</title>

   <para>
    Below you will find a detailed account of the changes between
    release 7.4 and the previous major release.
   </para>

  <sect3>
   <title>Server Operation Changes</title>

   <itemizedlist>
    <listitem>
     <para>
      Allow IPv6 server connections (Nigel Kukard, Johan Jordaan,
      Bruce, Tom, Kurt Roeckx, Andrew Dunstan)
     </para>
    </listitem>

    <listitem>
     <para>
      Fix SSL to handle errors cleanly (Nathan Mueller)
     </para>
     <para>
      In prior releases, certain SSL API error reports were not
      handled correctly.  This release fixes those problems.
     </para>
    </listitem>

    <listitem>
     <para>
      SSL protocol security and performance improvements (Sean Chittenden)
     </para>
     <para>
      SSL key renegotiation was happening too frequently, causing poor
      SSL performance.  Also, initial key handling was improved.
     </para>
    </listitem>

    <listitem>
     <para>
      Print lock information when a deadlock is detected (Tom)
     </para>
     <para>
      This allows easier debugging of deadlock situations.
     </para>
    </listitem>

    <listitem>
     <para>
      Update <filename>/tmp</filename> socket modification times
      regularly to avoid their removal (Tom)
     </para>
     <para>
      This should help prevent <filename>/tmp</filename> directory
      cleaner administration scripts from removing server socket
      files.
     </para>
    </listitem>

    <listitem><para>Enable PAM for Mac OS X (Aaron Hillegass)</para></listitem>

    <listitem>
     <para>Make B-tree indexes fully WAL-safe (Tom)</para>
     <para>
      In prior releases, under certain rare cases, a server crash
      could cause B-tree indexes to become corrupt. This release
      removes those last few rare cases.
     </para>
    </listitem>

    <listitem><para>Allow B-tree index compaction and empty page reuse (Tom)</para></listitem>

    <listitem>
     <para>
      Fix inconsistent index lookups during split of first root page (Tom)
     </para>
     <para>
      In prior releases, when a single-page index split into two
      pages, there was a brief period when another database session
      could miss seeing an index entry.  This release fixes that rare
      failure case.
     </para>
    </listitem>

    <listitem><para>Improve free space map allocation logic (Tom)</para></listitem>

    <listitem>
     <para>Preserve free space information between server restarts (Tom)</para>
     <para>
      In prior releases, the free space map was not saved when the
      postmaster was stopped, so newly started servers had no free
      space information. This release saves the free space map, and
      reloads it when the server is restarted.
     </para>
    </listitem>

    <listitem><para>Add start time to <literal>pg_stat_activity</literal> (Neil)</para></listitem>
    <listitem><para>New code to detect corrupt disk pages; erase with <varname>zero_damaged_pages</varname> (Tom)</para></listitem>
    <listitem><para>New client/server protocol: faster, no username length limit, allow clean exit from <command>COPY</command> (Tom)</para></listitem>
    <listitem><para>Add transaction status, table ID, column ID to client/server protocol (Tom)</para></listitem>
    <listitem><para>Add binary I/O to client/server protocol (Tom)</para></listitem>
    <listitem><para>Remove autocommit server setting; move to client applications (Tom)</para></listitem>
    <listitem><para>New error message wording, error codes, and three levels of error detail (Tom, Joe, Peter)</para></listitem>
   </itemizedlist>
  </sect3>

  <sect3>
   <title>Performance Improvements</title>

   <itemizedlist>
    <listitem><para>Add hashing for <literal>GROUP BY</literal> aggregates (Tom)</para></listitem>
    <listitem><para>Make nested-loop joins be smarter about multicolumn indexes (Tom)</para></listitem>
    <listitem><para>Allow multikey hash joins (Tom)</para></listitem>
    <listitem><para>Improve constant folding (Tom)</para></listitem>
    <listitem><para>Add ability to inline simple SQL functions (Tom)</para></listitem>

    <listitem>
     <para>Reduce memory usage for queries using complex functions (Tom)</para>
     <para>
      In prior releases, functions returning allocated memory would
      not free it until the query completed. This release allows the
      freeing of function-allocated memory when the function call
      completes, reducing the total memory used by functions.
     </para>
    </listitem>

    <listitem>
     <para>Improve GEQO optimizer performance (Tom)</para>
     <para>
      This release fixes several inefficiencies in the way the GEQO optimizer
      manages potential query paths.
     </para>
    </listitem>

    <listitem>
     <para>
      Allow <literal>IN</>/<literal>NOT IN</> to be handled via hash
      tables (Tom)
     </para>
    </listitem>

    <listitem>
     <para>
      Improve <literal>NOT IN (<replaceable>subquery</>)</literal>
      performance (Tom)
     </para>
    </listitem>

    <listitem>
     <para>
      Allow most <literal>IN</literal> subqueries to be processed as
      joins (Tom)
     </para>
    </listitem>

    <listitem>
     <para>
      Pattern matching operations can use indexes regardless of
      locale (Peter)
     </para>
     <para>
      There is no way for non-ASCII locales to use the standard
      indexes for <literal>LIKE</literal> comparisons. This release
      adds a way to create a special index for
      <literal>LIKE</literal>.
     </para>
    </listitem>

    <listitem>
     <para>Allow the postmaster to preload libraries using <varname>preload_libraries</varname> (Joe)</para>
     <para>
      For shared libraries that require a long time to load, this
      option is available so the library can be preloaded in the
      postmaster and inherited by all database sessions.
     </para>
    </listitem>

    <listitem>
     <para>
      Improve optimizer cost computations, particularly for subqueries (Tom)
     </para>
    </listitem>

    <listitem>
     <para>
      Avoid sort when subquery <literal>ORDER BY</literal> matches upper query (Tom)
     </para>
    </listitem>

    <listitem>
     <para>
      Deduce that <literal>WHERE a.x = b.y AND b.y = 42</literal> also
      means <literal>a.x = 42</literal> (Tom)
     </para>
    </listitem>

    <listitem>
     <para>
      Allow hash/merge joins on complex joins (Tom)
     </para>
    </listitem>

    <listitem>
     <para>
      Allow hash joins for more data types (Tom)
     </para>
    </listitem>

    <listitem>
     <para>
      Allow join optimization of explicit inner joins, disable with
      <varname>join_collapse_limit</varname> (Tom)
     </para>
    </listitem>

    <listitem>
     <para>
      Add parameter <varname>from_collapse_limit</varname> to control
      conversion of subqueries to joins (Tom)
     </para>
    </listitem>

    <listitem>
     <para>
      Use faster and more powerful regular expression code from Tcl
      (Henry Spencer, Tom)
     </para>
    </listitem>

    <listitem>
     <para>
      Use bit-mapped relation sets in the optimizer (Tom)
     </para>
    </listitem>

    <listitem>
     <para>Improve connection startup time (Tom)</para>
     <para>
      The new client/server protocol requires fewer network packets to
      start a database session.
     </para>
    </listitem>

    <listitem>
     <para>
      Improve trigger/constraint performance (Stephan)
     </para>
    </listitem>

    <listitem>
     <para>
      Improve speed of <literal>col IN (const, const, const, ...)</literal> (Tom)
     </para>
    </listitem>

    <listitem>
     <para>
      Fix hash indexes which were broken in rare cases (Tom)
     </para>
    </listitem>

    <listitem><para>Improve hash index concurrency and speed (Tom)</para>
     <para>
      Prior releases suffered from poor hash index performance,
      particularly for high concurrency situations. This release fixes
      that, and the development group is interested in reports
      comparing B-tree and hash index performance.
     </para>
    </listitem>

    <listitem>
     <para>Align shared buffers on 32-byte boundary for copy speed improvement (Manfred Spraul)</para>
     <para>