This patch adds a new GUC var, "default_with_oids", which follows the

proposal for eventually deprecating OIDs on user tables that I posted
earlier to pgsql-hackers. pg_dump now always specifies WITH OIDS or
WITHOUT OIDS when dumping a table. The documentation has been updated.

Neil Conway

9 files changed, 180 insertions, 73 deletions

diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index a214f5398c8..8bae619c3d0 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/datatype.sgml,v 1.134 2003/12/01 20:34:53 tgl Exp $
+$PostgreSQL: pgsql/doc/src/sgml/datatype.sgml,v 1.135 2003/12/01 22:07:55 momjian Exp $
 -->
 
  <chapter id="datatype">
@@ -2932,24 +2932,43 @@ SELECT * FROM test;
 
    <para>
     Object identifiers (OIDs) are used internally by
-    <productname>PostgreSQL</productname> as primary keys for various system
-    tables.  Also, an OID system column is added to user-created tables
-    (unless <literal>WITHOUT OIDS</> is specified at table creation time).
-    Type <type>oid</> represents an object identifier.  There are also
-    several alias types for <type>oid</>: <type>regproc</>, <type>regprocedure</>,
-    <type>regoper</>, <type>regoperator</>, <type>regclass</>,
-    and <type>regtype</>. <xref linkend="datatype-oid-table"> shows an overview.
+    <productname>PostgreSQL</productname> as primary keys for various
+    system tables.  An OID system column is also added to user-created
+    tables, unless <literal>WITHOUT OIDS</literal> is specified when
+    the table is created, or the <varname>default_with_oids</varname>
+    configuration variable is set to false.  Type <type>oid</>
+    represents an object identifier.  There are also several alias
+    types for <type>oid</>: <type>regproc</>, <type>regprocedure</>,
+    <type>regoper</>, <type>regoperator</>, <type>regclass</>, and
+    <type>regtype</>. <xref linkend="datatype-oid-table"> shows an
+    overview.
    </para>
 
    <para>
-    The <type>oid</> type is currently implemented as an unsigned four-byte
-    integer.
-    Therefore, it is not large enough to provide database-wide uniqueness
-    in large databases, or even in large individual tables.  So, using a
-    user-created table's OID column as a primary key is discouraged.
-    OIDs are best used only for references to system tables.
+    The <type>oid</> type is currently implemented as an unsigned
+    four-byte integer.  Therefore, it is not large enough to provide
+    database-wide uniqueness in large databases, or even in large
+    individual tables.  So, using a user-created table's OID column as
+    a primary key is discouraged.  OIDs are best used only for
+    references to system tables.
    </para>
 
+   <note>
+    <para>
+     OIDs are included by default in user-created tables in
+     <productname>PostgreSQL</productname> &version;. However, this
+     behavior is likely to change in a future version of
+     <productname>PostgreSQL</productname>. Eventually, user-created
+     tables will not include an OID system column unless <literal>WITH
+     OIDS</literal> is specified when the table is created, or the
+     <varname>default_with_oids</varname> configuration variable is set
+     to true. If your application requires the presence of an OID
+     system column in a table, it should specify <literal>WITH
+     OIDS</literal> when that table is created to ensure compatibility
+     with future releases of <productname>PostgreSQL</productname>.
+    </para>
+   </note>
+
    <para>
     The <type>oid</> type itself has few operations beyond comparison.
     It can be cast to
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 9662d9b02f4..d10be7f2905 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.63 2003/11/29 19:51:38 pgsql Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.64 2003/12/01 22:07:58 momjian Exp $
 PostgreSQL documentation
 -->
 
@@ -149,6 +149,12 @@ ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
       of the OID are kept indefinitely.  This is semantically similar
       to the <literal>DROP COLUMN</literal> process.
      </para>
+
+     <para>
+      Note that there is no variant of <command>ALTER TABLE</command>
+      that allows OIDs to be restored to a table once they have been
+      removed.
+     </para>
     </listitem>
    </varlistentry>
 
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 00814fa4431..4af03bccfaa 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/create_table.sgml,v 1.76 2003/11/29 19:51:38 pgsql Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/create_table.sgml,v 1.77 2003/12/01 22:07:58 momjian Exp $
 PostgreSQL documentation
 -->
 
@@ -111,12 +111,12 @@ and <replaceable class="PARAMETER">table_constraint</replaceable> is:
      <para>
       If specified, the table is created as a temporary table.
       Temporary tables are automatically dropped at the end of a
-      session, or optionally at the end of the current transaction 
-      (see ON COMMIT below).  Existing permanent tables with the same 
-      name are not visible to the current session while the temporary 
-      table exists, unless they are referenced with schema-qualified 
-      names. Any indexes created on a temporary table are automatically
-      temporary as well.
+      session, or optionally at the end of the current transaction
+      (see <literal>ON COMMIT</literal> below).  Existing permanent
+      tables with the same name are not visible to the current session
+      while the temporary table exists, unless they are referenced
+      with schema-qualified names. Any indexes created on a temporary
+      table are automatically temporary as well.
      </para>
 
      <para>
@@ -243,22 +243,30 @@ and <replaceable class="PARAMETER">table_constraint</replaceable> is:
     <listitem>
      <para>
       This optional clause specifies whether rows of the new table
-      should have OIDs (object identifiers) assigned to them.  The
-      default is to have OIDs.  (If the new table inherits from any
-      tables that have OIDs, then <literal>WITH OIDS</> is forced even
-      if the command says <literal>WITHOUT OIDS</>.)
+      should have OIDs (object identifiers) assigned to them.  If
+      neither <literal>WITH OIDS</literal> nor <literal>WITHOUT
+      OIDS</literal> is specified, the default value depends upon the
+      <varname>default_with_oids</varname> configuration parameter. (If
+      the new table inherits from any tables that have OIDs, then
+      <literal>WITH OIDS</> is forced even if the command says
+      <literal>WITHOUT OIDS</>.)
      </para>
 
      <para>
-      Specifying <literal>WITHOUT OIDS</> allows the user to suppress
-      generation of OIDs for rows of a table.  This may be worthwhile
-      for large tables, since it will reduce OID consumption and
-      thereby postpone wraparound of the 32-bit OID counter.  Once the
-      counter wraps around, uniqueness of OIDs can no longer be
-      assumed, which considerably reduces their usefulness. Specifying
-      <literal>WITHOUT OIDS</literal> also reduces the space required
-      to store the table on disk by 4 bytes per row of the table,
-      thereby improving performance.
+      If <literal>WITHOUT OIDS</literal> is specified or implied, this
+      means that the generation of OIDs for this table will be
+      supressed. This is generally considered worthwhile, since it
+      will reduce OID consumption and thereby postpone the wraparound
+      of the 32-bit OID counter. Once the counter wraps around, OIDs
+      can no longer be assumed to be unique, which makes them
+      considerably less useful. In addition, excluding OIDs from a
+      table reduces the space required on disk to storage the table by
+      4 bytes per row, leading to increased performance.
+     </para>
+
+     <para>
+      To remove OIDs from a table after it has been created, use <xref
+      linkend="sql-altertable" endterm="sql-altertable-title">.
      </para>
     </listitem>
    </varlistentry>
@@ -572,18 +580,17 @@ and <replaceable class="PARAMETER">table_constraint</replaceable> is:
   <itemizedlist>
    <listitem>
     <para>
-     Whenever an application makes use of OIDs to identify specific
+     Using OIDs in new applications is not recommended: where
+     possible, using a <literal>SERIAL</literal> or other sequence
+     generator as the table's primary key is preferred. However, if
+     your application does make use of OIDs to identify specific rows
      rows of a table, it is recommended to create a unique constraint
      on the <structfield>oid</> column of that table, to ensure that
      OIDs in the table will indeed uniquely identify rows even after
      counter wraparound.  Avoid assuming that OIDs are unique across
      tables; if you need a database-wide unique identifier, use the
      combination of <structfield>tableoid</> and row OID for the
-     purpose.  (It is likely that future <productname>PostgreSQL</>
-     releases will use a separate OID counter for each table, so that
-     it will be <emphasis>necessary</>, not optional, to include
-     <structfield>tableoid</> to have a unique identifier
-     database-wide.)
+     purpose.
     </para>
 
     <tip>
diff --git a/doc/src/sgml/ref/create_table_as.sgml b/doc/src/sgml/ref/create_table_as.sgml
index 856846c50b2..a7382abfdc9 100644
--- a/doc/src/sgml/ref/create_table_as.sgml
+++ b/doc/src/sgml/ref/create_table_as.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/create_table_as.sgml,v 1.17 2003/11/29 19:51:38 pgsql Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/create_table_as.sgml,v 1.18 2003/12/01 22:07:58 momjian Exp $
 PostgreSQL documentation
 -->
 
@@ -51,7 +51,20 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable>table_name
 
  <refsect1>
   <title>Parameters</title>
-   
+
+  <variablelist>
+   <varlistentry>
+    <term><literal>GLOBAL</literal> or <literal>LOCAL</literal></term>
+    <listitem>
+     <para>
+      Ignored for compatibility. Refer to <xref
+      linkend="sql-createtable" endterm="sql-createtable-title"> for
+      details.
+     </para>
+    </listitem>
+   </varlistentry>
+  </variablelist>
+
   <variablelist>
    <varlistentry>
     <term><literal>TEMPORARY</> or <literal>TEMP</></term>
@@ -105,10 +118,24 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable>table_name
   <title>Notes</title>
 
   <para>
-   This command is functionally equivalent to <xref
-   linkend="sql-selectinto" endterm="sql-selectinto-title">, but it is preferred since it is less
-   likely to be confused with other uses of the <command>SELECT
-   ... INTO</command> syntax.
+   This command is functionally similar to <xref
+   linkend="sql-selectinto" endterm="sql-selectinto-title">, but it is
+   preferred since it is less likely to be confused with other uses of
+   the <command>SELECT INTO</command> syntax.
+  </para>
+
+  <para>
+   Prior to PostgreSQL 7.5, <command>CREATE TABLE AS</command> always
+   included OIDs in the table it produced. Furthermore, these OIDs
+   were newly generated: they were distinct from the OIDs of any of
+   the rows in the source tables of the <command>SELECT</command> or
+   <command>EXECUTE</command> statement. Therefore, if <command>CREATE
+   TABLE AS</command> was frequently executed, the OID counter would
+   be rapidly incremented. As of PostgreSQL 7.5, the inclusion of OIDs
+   in the table generated by <command>CREATE TABLE AS</command> is
+   controlled by the <varname>default_with_oids</varname> configuration
+   variable. This variable currently defaults to true, but will likely
+   default to false in a future release of <productname>PostgreSQL</>.
   </para>
  </refsect1>
 
@@ -129,7 +156,6 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable>table_name
 
   <simplelist type="inline">
    <member><xref linkend="sql-createtable" endterm="sql-createtable-title"></member>
-   <member><xref linkend="sql-createview" endterm="sql-createview-title"></member>
    <member><xref linkend="sql-execute" endterm="sql-execute-title"></member>
    <member><xref linkend="sql-select" endterm="sql-select-title"></member>
    <member><xref linkend="sql-selectinto" endterm="sql-selectinto-title"></member>
diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index f02c3638a7f..c6041237240 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/pg_dump.sgml,v 1.67 2003/11/29 19:51:39 pgsql Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/pg_dump.sgml,v 1.68 2003/12/01 22:07:58 momjian Exp $
 PostgreSQL documentation
 -->
 
@@ -611,8 +611,11 @@ CREATE DATABASE foo WITH TEMPLATE template0;
   </para>
 
   <para>
-   Once restored, it is wise to run <command>ANALYZE</> on each
-   restored table so the optimizer has useful statistics.
+   The dump file produced by <application>pg_dump</application> does
+   not contain the statistics used by the optimizer to make query
+   planning decisions.  Therefore, it is wise to run
+   <command>ANALYZE</command> after restoring from a dump file to
+   ensure good performance.
   </para>
 
  </refsect1>
diff --git a/doc/src/sgml/ref/prepare.sgml b/doc/src/sgml/ref/prepare.sgml
index 82e36fe89fc..684b4bb8ba0 100644
--- a/doc/src/sgml/ref/prepare.sgml
+++ b/doc/src/sgml/ref/prepare.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/prepare.sgml,v 1.8 2003/11/29 19:51:39 pgsql Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/prepare.sgml,v 1.9 2003/12/01 22:07:58 momjian Exp $
 PostgreSQL documentation
 -->
 
@@ -52,13 +52,12 @@ PREPARE <replaceable class="PARAMETER">plan_name</replaceable> [ (<replaceable c
   </para>
 
   <para>
-   Prepared statements are only stored in and for the duration of
-   the current database session. When
-   the session ends, the prepared statement is forgotten, and so it must be
-   recreated before being used again. This also means that a single
-   prepared statement cannot be used by multiple simultaneous database
-   clients; however, each client can create their own prepared statement
-   to use.
+   Prepared statements are only for the duration of the current
+   database session. When the session ends, the prepared statement is
+   forgotten, so it must be recreated before being used again. This
+   also means that a single prepared statement cannot be used by
+   multiple simultaneous database clients; however, each client can
+   create their own prepared statement to use.
   </para>
 
   <para>
diff --git a/doc/src/sgml/ref/select_into.sgml b/doc/src/sgml/ref/select_into.sgml
index cb321f0cfa6..1261f3cd4b5 100644
--- a/doc/src/sgml/ref/select_into.sgml
+++ b/doc/src/sgml/ref/select_into.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/select_into.sgml,v 1.25 2003/11/29 19:51:39 pgsql Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/select_into.sgml,v 1.26 2003/12/01 22:07:58 momjian Exp $
 PostgreSQL documentation
 -->
 
@@ -82,13 +82,29 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="PARAMETER">expression</replac
   <title>Notes</title>
 
   <para>
-   <xref linkend="sql-createtableas" endterm="sql-createtableas-title">
-   is functionally equivalent to <command>SELECT INTO</command>.
-   <command>CREATE TABLE AS</command> is the recommended syntax, since
-   this form of <command>SELECT INTO</command> is not available in
-   <application>ECPG</application> or
-   <application>PL/pgSQL</application>, because they interpret the
-   <literal>INTO</literal> clause differently.
+   <xref linkend="sql-createtableas"
+   endterm="sql-createtableas-title"> is functionally similar to
+   <command>SELECT INTO</command>.  <command>CREATE TABLE AS</command>
+   is the recommended syntax, since this form of <command>SELECT
+   INTO</command> is not available in <application>ECPG</application>
+   or <application>PL/pgSQL</application>, because they interpret the
+   <literal>INTO</literal> clause differently. Furthermore,
+   <command>CREATE TABLE AS</command> offers a superset of the
+   functionality provided by <command>SELECT INTO</command>.
+  </para>
+
+  <para>
+   Prior to PostgreSQL 7.5, the table created by <command>SELECT
+   INTO</command> always included OIDs. Furthermore, these OIDs were
+   newly generated: they were distinct from the OIDs of any of the
+   rows in the source tables of the <command>SELECT INTO</command>
+   statement. Therefore, if <command>SELECT INTO</command> was
+   frequently executed, the OID counter would be rapidly
+   incremented. As of PostgreSQL 7.5, the inclusion of OIDs in the
+   table created by <command>SELECT INTO</command> is controlled by
+   the <varname>default_with_oids</varname> configuration
+   variable. This variable currently defaults to true, but will likely
+   default to false in a future release of <productname>PostgreSQL</>.
   </para>
  </refsect1>
 
@@ -96,7 +112,7 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="PARAMETER">expression</replac
   <title>Compatibility</title>
 
   <para>
-   The SQL standard uses <command>SELECT ... INTO</command> to
+   The SQL standard uses <command>SELECT INTO</command> to
    represent selecting values into scalar variables of a host program,
    rather than creating a new table.  This indeed is the usage found
    in <application>ECPG</application> (see <xref linkend="ecpg">) and
diff --git a/doc/src/sgml/runtime.sgml b/doc/src/sgml/runtime.sgml
index 2ee3ca9667b..49bf3aa7af4 100644
--- a/doc/src/sgml/runtime.sgml
+++ b/doc/src/sgml/runtime.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/runtime.sgml,v 1.223 2003/11/29 19:51:37 pgsql Exp $
+$PostgreSQL: pgsql/doc/src/sgml/runtime.sgml,v 1.224 2003/12/01 22:07:56 momjian Exp $
 -->
 
 <Chapter Id="runtime">
@@ -2433,7 +2433,38 @@ dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir'
       </listitem>
      </varlistentry>
 
-          </variablelist>
+     <varlistentry>
+      <term><varname>default_with_oids</varname> (<type>boolean</type>)</term>
+      <listitem>
+       <para>
+        This controls whether <command>CREATE TABLE</command> will
+        include OIDs in newly-created tables, if neither <literal>WITH
+        OIDS</literal> or <literal>WITHOUT OIDS</literal> have been
+        specified. It also determines whether OIDs will be included in
+        the table generated by <command>SELECT INTO</command> and
+        <command>CREATE TABLE AS</command>. In
+        <productname>PostgreSQL</productname> &version;
+        <varname>default_with_oids</varname> defaults to true. This is
+        also the behavior of previous versions of
+        <productname>PostgreSQL</productname>. However, assuming that
+        tables will contain OIDs by default is not
+        encouraged. Therefore, this option will default to false in a
+        future release of <productname>PostgreSQL</productname>.
+       </para>
+
+       <para>
+        To ease compatibility with applications that make use of OIDs,
+        this option should left enabled. To ease compatibility with
+        future versions of <productname>PostgreSQL</productname>, this
+        option should be disabled, and applications that require OIDs
+        on certain tables should explictely specify <literal>WITH
+        OIDS</literal> when issuing the <command>CREATE
+        TABLE</command> statements for the tables in question.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     </variablelist>
     </sect3>
     <sect3 id="runtime-config-compatible-clients">
      <title>Platform and Client Compatibility</title>
diff --git a/doc/src/sgml/spi.sgml b/doc/src/sgml/spi.sgml
index 36b6d8be2f3..aab5b0dae16 100644
--- a/doc/src/sgml/spi.sgml
+++ b/doc/src/sgml/spi.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/spi.sgml,v 1.29 2003/11/29 19:51:37 pgsql Exp $
+$PostgreSQL: pgsql/doc/src/sgml/spi.sgml,v 1.30 2003/12/01 22:07:57 momjian Exp $
 -->
 
 <chapter id="spi">
@@ -320,7 +320,7 @@ typedef struct
      <listitem>
       <para>
        if a <command>SELECT</command> (but not <command>SELECT
-       ... INTO</>) was executed
+       INTO</>) was executed
       </para>
      </listitem>
     </varlistentry>
@@ -329,7 +329,7 @@ typedef struct
      <term><symbol>SPI_OK_SELINTO</symbol></term>
      <listitem>
       <para>
-       if a <command>SELECT ... INTO</command> was executed
+       if a <command>SELECT INTO</command> was executed
       </para>
      </listitem>
     </varlistentry>