release.sgml

PostgreSQL7.4.6 for Linux

      Implement SQL-compatible options <literal>FIRST</>,
      <literal>LAST</>, <literal>ABSOLUTE <replaceable>n</></>,
      <literal>RELATIVE <replaceable>n</></> for
      <command>FETCH</command> and <command>MOVE</command> (Tom)
     </para>
    </listitem>

    <listitem>
     <para>Allow <command>EXPLAIN</command> on <command>DECLARE CURSOR</command> (Tom)</para>
    </listitem>

    <listitem>
     <para>Allow <command>CLUSTER</command> to use index marked as pre-clustered by default (Alvaro Herrera)</para>
    </listitem>

    <listitem>
     <para>Allow <command>CLUSTER</command> to cluster all tables (Alvaro Herrera)</para>
     <para>
      This allows all previously clustered tables in a database to be
      reclustered with a single command.
     </para>
    </listitem>

    <listitem><para>Prevent <command>CLUSTER</command> on partial indexes (Tom)</para></listitem>

    <listitem><para>Allow DOS and Mac line-endings in <command>COPY</> files (Bruce)</para></listitem>

    <listitem>
     <para>
      Disallow literal carriage return as a data value,
      backslash-carriage-return and <literal>\r</> are still allowed
      (Bruce)
     </para>
    </listitem>

    <listitem>
     <para><command>COPY</> changes (binary, <literal>\.</>) (Tom)</para>
    </listitem>

    <listitem>
     <para>Recover from <command>COPY</command> failure cleanly (Tom)</para>
    </listitem>

    <listitem>
     <para>Prevent possible memory leaks in <command>COPY</command> (Tom)</para>
    </listitem>

    <listitem>
     <para>Make <command>TRUNCATE</command> transaction-safe (Rod)</para>
     <para>
      <command>TRUNCATE</command> can now be used inside a
      transaction. If the transaction aborts, the changes made by the
      <command>TRUNCATE</command> are automatically rolled back.
     </para>
    </listitem>

    <listitem>
     <para>
      Allow prepare/bind of utility commands like
      <command>FETCH</command> and <command>EXPLAIN</command> (Tom)
     </para>
    </listitem>

    <listitem>
     <para>Add <command>EXPLAIN EXECUTE</command> (Neil)</para>
    </listitem>

    <listitem>
     <para>Improve <command>VACUUM</command> performance on indexes by reducing WAL traffic (Tom)</para>
    </listitem>

    <listitem>
     <para>Functional indexes have been generalized into indexes on expressions (Tom)</para>
     <para>
      In prior releases, functional indexes only supported a simple
      function applied to one or more column names.  This release
      allows any type of scalar expression.
     </para>
    </listitem>

    <listitem>
     <para>
      Have <command>SHOW TRANSACTION ISOLATION</command> match input
      to <command>SET TRANSACTION ISOLATION</command>
      (Tom)
     </para>
    </listitem>

    <listitem>
     <para>
       Have <command>COMMENT ON DATABASE</command> on nonlocal
       database generate a warning, rather than an error (Rod)
      </para>

     <para>
      Database comments are stored in database-local tables so
      comments on a database have to be stored in each database.
     </para>
    </listitem>

    <listitem>
     <para>
      Improve reliability of <command>LISTEN</>/<command>NOTIFY</> (Tom)
     </para>
    </listitem>

    <listitem>
     <para>Allow <command>REINDEX</command> to reliably reindex nonshared system catalog indexes (Tom)</para>
     <para>
      This allows system tables to be reindexed without the
      requirement of a standalone session, which was necessary in
      previous releases. The only tables that now require a standalone
      session for reindexing are the global system tables
      <literal>pg_database</>, <literal>pg_shadow</>, and
      <literal>pg_group</>.
     </para>
    </listitem>
   </itemizedlist>
  </sect3>

  <sect3>
   <title>Data Type and Function Changes</title>

   <itemizedlist>
    <listitem>
     <para>
      New server parameter <varname>extra_float_digits</varname> to
      control precision display of floating-point numbers (Pedro
      Ferreira, Tom)
     </para>
     <para>
      This controls output precision which was causing regression
      testing problems.
     </para>
    </listitem>

    <listitem><para>Allow <literal>+1300</literal> as a numeric time-zone specifier, for FJST (Tom)</para></listitem>

    <listitem>
     <para>
      Remove rarely used functions <function>oidrand</>,
      <function>oidsrand</>, and <function>userfntest</> functions
      (Neil)
     </para>
    </listitem>

    <listitem>
     <para>Add <function>md5()</> function to main server, already in <filename>contrib/pgcrypto</filename> (Joe)</para>
     <para>
      An MD5 function was frequently requested. For more complex
      encryption capabilities, use
      <filename>contrib/pgcrypto</filename>.
     </para>
    </listitem>

    <listitem><para>Increase date range of <type>timestamp</type> (John Cochran)</para></listitem>

    <listitem>
     <para>
      Change <literal>EXTRACT(EPOCH FROM timestamp)</literal> so
      <type>timestamp without time zone</type> is assumed to be in
      local time, not GMT (Tom)
     </para>
    </listitem>

    <listitem><para>Trap division by zero in case the operating system doesn't prevent it (Tom)</para></listitem>
    <listitem><para>Change the <type>numeric</type> data type internally to base 10000 (Tom)</para></listitem>
    <listitem><para>New <function>hostmask()</function> function (Greg Wickham)</para></listitem>
    <listitem><para>Fixes for <function>to_char()</function> and <function>to_timestamp()</function> (Karel)</para></listitem>

    <listitem>
     <para>
      Allow functions that can take any argument data type and return
      any data type, using <type>anyelement</type> and
      <type>anyarray</type> (Joe)
     </para>
     <para>
      This allows the creation of functions that can work with any
      data type.
     </para>
    </listitem>

    <listitem>
     <para>
      Arrays may now be specified as <literal>ARRAY[1,2,3]</literal>,
      <literal>ARRAY[['a','b'],['c','d']]</literal>, or
      <literal>ARRAY[ARRAY[ARRAY[2]]]</literal> (Joe)
     </para>
    </listitem>

    <listitem>
     <para>
      Allow proper comparisons for arrays, including <literal>ORDER
      BY</literal> and <literal>DISTINCT</literal> support
      (Joe)
     </para>
    </listitem>

    <listitem><para>Allow indexes on array columns (Joe)</para></listitem>
    <listitem><para>Allow array concatenation with <literal>||</literal> (Joe)</para></listitem>

    <listitem>
     <para>
      Allow <literal>WHERE</literal> qualification
      <literal><replaceable>expr</> <replaceable>op</> ANY/SOME/ALL
      (<replaceable>array_expr</>)</literal> (Joe)
     </para>
     <para>
      This allows arrays to behave like a list of values, for purposes
      like <literal>SELECT * FROM tab WHERE col IN
      (array_val)</literal>.
     </para>
    </listitem>

    <listitem>
     <para>
      New array functions <function>array_append</>,
      <function>array_cat</>, <function>array_lower</>,
      <function>array_prepend</>, <function>array_to_string</>,
      <function>array_upper</>, <function>string_to_array</> (Joe)
     </para>
    </listitem>

    <listitem><para>Allow user defined aggregates to use polymorphic functions (Joe)</para></listitem>
    <listitem><para>Allow assignments to empty arrays (Joe)</para></listitem>

    <listitem>
     <para>
      Allow 60 in seconds fields of <type>time</type>,
      <type>timestamp</type>, and <type>interval</type> input values
      (Tom)
     </para>
     <para>
      Sixty-second values are needed for leap seconds.
     </para>
    </listitem>

    <listitem><para>Allow <type>cidr</type> data type to be cast to <type>text</type> (Tom)</para></listitem>

    <listitem><para>Disallow invalid time zone names in SET TIMEZONE</para></listitem>

    <listitem>
     <para>
      Trim trailing spaces when <type>char</type> is cast to
      <type>varchar</> or <type>text</> (Tom)
     </para>
    </listitem>

    <listitem>
     <para>
      Make <type>float(<replaceable>p</>)</> measure the precision
      <replaceable>p</> in binary digits, not decimal digits
      (Tom)
     </para>
    </listitem>

    <listitem>
     <para>Add IPv6 support to the <type>inet</type> and <type>cidr</type> data types (Michael Graff)</para>
    </listitem>

    <listitem>
     <para>Add <function>family()</function> function to report whether address is IPv4 or IPv6 (Michael Graff)</para>
    </listitem>

    <listitem>
     <para>
      Have <literal>SHOW datestyle</literal> generate output similar
      to that used by <literal>SET datestyle</literal> (Tom)
     </para>
    </listitem>

    <listitem>
     <para>
      Make <literal>EXTRACT(TIMEZONE)</literal> and <literal>SET/SHOW
      TIME ZONE</literal> follow the SQL convention for the sign of
      time zone offsets, i.e., positive is east from UTC (Tom)
     </para>
    </listitem>

    <listitem>
     <para>Fix <literal>date_trunc('quarter', ...)</literal> (B鰆the Zolt醤)</para>
     <para>
      Prior releases returned an incorrect value for this function call.
     </para>
    </listitem>

    <listitem>
     <para>Make <function>initcap()</function> more compatible with Oracle (Mike Nolan)</para>
     <para>
      <function>initcap()</function> now uppercases a letter appearing
      after any non-alphanumeric character, rather than only after
      whitespace.
     </para>
    </listitem>

    <listitem>
     <para>Allow only <varname>datestyle</varname> field order for date values not in ISO-8601 format (Greg)</para>
    </listitem>

    <listitem>
     <para>
      Add new <varname>datestyle</varname> values <literal>MDY</>,
      <literal>DMY</>, and <literal>YMD</> to set input field order;
      honor <literal>US</> and <literal>European</> for backward
      compatibility (Tom)
     </para>
    </listitem>

    <listitem>
     <para>
      String literals like <literal>'now'</literal> or
      <literal>'today'</literal> will no longer work as a column
      default. Use functions such as <function>now()</function>,
      <function>current_timestamp</function> instead.  (change
      required for prepared statements) (Tom)
     </para>
    </listitem>

    <listitem>
     <para>Treat NaN as larger than any other value in <function>min()</>/<function>max()</> (Tom)</para>
     <para>
      NaN was already sorted after ordinary numeric values for most
      purposes, but <function>min()</> and <function>max()</> didn't
      get this right.
     </para>
    </listitem>

    <listitem>
     <para>Prevent interval from suppressing <literal>:00</literal>
     seconds display</para>
    </listitem>

    <listitem>
     <para>
      New functions <function>pg_get_triggerdef(prettyprint)</function>
      and <function>pg_conversion_is_visible()</function> (Christopher)
     </para>
    </listitem>

    <listitem>
     <para>Allow time to be specified as <literal>040506</> or <literal>0405</> (Tom)</para>
    </listitem>

    <listitem>
     <para>
      Input date order must now be <literal>YYYY-MM-DD</literal> (with 4-digit year) or
      match <varname>datestyle</varname>
     </para>
    </listitem>

    <listitem>
     <para>
      Make <function>pg_get_constraintdef</function> support
      unique, primary-key, and check constraints (Christopher)
     </para>
    </listitem>
   </itemizedlist>
  </sect3>

  <sect3>
   <title>Server-Side Language Changes</title>

   <itemizedlist>
    <listitem>
     <para>
      Prevent PL/pgSQL crash when <literal>RETURN NEXT</literal> is
      used on a zero-row record variable (Tom)
     </para>
    </listitem>

    <listitem>
     <para>
      Make PL/Python's <function>spi_execute</function> interface
      handle null values properly (Andrew Bosma)
     </para>
    </listitem>

    <listitem>
     <para>Allow PL/pgSQL to declare variables of composite types without <literal>%ROWTYPE</literal> (Tom)</para>
    </listitem>

    <listitem>
     <para>Fix PL/Python's <function>_quote()</function> function to handle big integers</para>
    </listitem>

    <listitem>
     <para>Make PL/Python an untrusted language, now called <literal>plpythonu</literal> (Kevin Jacobs, Tom)</para>
     <para>
      The Python language no longer supports a restricted execution
      environment, so the trusted version of PL/Python was removed. If
      this situation changes, a version of