Make OIDs optional, per discussions in pghackers. WITH OIDS is still the

default, but OIDS are removed from many system catalogs that don't need them.
Some interesting side effects: TOAST pointers are 20 bytes not 32 now;
pg_description has a three-column key instead of one.

Bugs fixed in passing: BINARY cursors work again; pg_class.relhaspkey
has some usefulness; pg_dump dumps comments on indexes, rules, and
triggers in a valid order.

initdb forced.

3 files changed, 147 insertions, 23 deletions

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 69e79c5fbd5..bc5bab0a473 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1,6 +1,6 @@
 <!--
  Documentation of the system catalogs, directed toward PostgreSQL developers
- $Header: /cvsroot/pgsql/doc/src/sgml/catalogs.sgml,v 2.19 2001/07/15 22:48:15 tgl Exp $
+ $Header: /cvsroot/pgsql/doc/src/sgml/catalogs.sgml,v 2.20 2001/08/10 18:57:32 tgl Exp $
  -->
 
 <chapter id="catalogs">
@@ -532,9 +532,9 @@
   <para>
    <structname>pg_class</structname> catalogues tables and mostly
    everything else that has columns or is otherwise similar to a
-   table.  This includes indexes (but see
+   table.  This includes indexes (but see also
    <structname>pg_index</structname>), sequences, views, and some
-   kinds of special relation kinds.  Below, when we mean all of these
+   kinds of special relation.  Below, when we mean all of these
    kinds of objects we speak of <quote>relations</quote>.  Not all
    fields are meaningful for all relation types.
   </para>
@@ -565,8 +565,8 @@
       <entry><type>oid</type></entry>
       <entry>pg_type.oid</entry>
       <entry>
-       The data type that corresponds to this table (not functional,
-       only set for system tables)
+       The OID of the data type that corresponds to this table, if any
+       (zero for indexes, which have no pg_type entry)
       </entry>
      </row>
 
@@ -631,14 +631,19 @@
       <entry>reltoastidxid</entry>
       <entry><type>oid</type></entry>
       <entry>pg_class.oid</entry>
-      <entry>Oid of the index on the TOAST table for this table, 0 if none</entry>
+      <entry>
+       For a TOAST table, the OID of its index.  0 if not a TOAST table.
+      </entry>
      </row>
 
      <row>
       <entry>relhasindex</entry>
       <entry><type>bool</type></entry>
       <entry></entry>
-      <entry>True if this is a table and it has at least one index</entry>
+      <entry>True if this is a table and it has (or recently had) any indexes.
+       This is set by CREATE INDEX, but not cleared immediately by DROP INDEX.
+       VACUUM clears relhasindex if it finds the table has no indexes.
+      </entry>
      </row>
 
      <row>
@@ -664,7 +669,7 @@
       <entry><type>int2</type></entry>
       <entry></entry>
       <entry>
-       Number of columns in the relation, besides system columns.
+       Number of user columns in the relation (system columns not counted).
        There must be this many corresponding entries in
        <structname>pg_attribute</structname>.  See also
        <structname>pg_attribute</structname>.<structfield>attnum</structfield>.
@@ -695,14 +700,30 @@
       <entry>relukeys</entry>
       <entry><type>int2</type></entry>
       <entry></entry>
-      <entry>unused  (<emphasis>Not</emphasis> the number of unique keys or something.)</entry>
+      <entry>unused  (<emphasis>Not</emphasis> the number of unique keys)</entry>
      </row>
 
      <row>
       <entry>relfkeys</entry>
       <entry><type>int2</type></entry>
       <entry></entry>
-      <entry>Number foreign keys on the table</entry>
+      <entry>unused  (<emphasis>Not</emphasis> the number of foreign keys on the table)</entry>
+     </row>
+
+     <row>
+      <entry>relrefs</entry>
+      <entry><type>int2</type></entry>
+      <entry></entry>
+      <entry>unused</entry>
+     </row>
+
+     <row>
+      <entry>relhasoids</entry>
+      <entry><type>bool</type></entry>
+      <entry></entry>
+      <entry>
+       True if we generate an OID for each row of the relation.
+      </entry>
      </row>
 
      <row>
@@ -710,8 +731,7 @@
       <entry><type>bool</type></entry>
       <entry></entry>
       <entry>
-       unused  (No, this does not say whether the table has a primary
-       key.  It's really unused.)
+       True if the table has (or once had) a primary key.
       </entry>
      </row>
 
@@ -726,7 +746,7 @@
       <entry>relhassubclass</entry>
       <entry><type>bool</type></entry>
       <entry></entry>
-      <entry>At least one table inherits this one</entry>
+      <entry>At least one table inherits from this one</entry>
      </row>
 
      <row>
@@ -875,6 +895,23 @@
      </row>
 
      <row>
+      <entry>classoid</entry>
+      <entry><type>oid</type></entry>
+      <entry>pg_class.oid</entry>
+      <entry>The oid of the system catalog this object appears in</entry>
+     </row>
+
+     <row>
+      <entry>objsubid</entry>
+      <entry><type>int4</type></entry>
+      <entry></entry>
+      <entry>For a comment on a table attribute, this is the attribute's
+       column number (the objoid and classoid refer to the table itself).
+       For all other object types, this field is presently zero.
+      </entry>
+     </row>
+
+     <row>
       <entry>description</entry>
       <entry><type>text</type></entry>
       <entry></entry>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index fb5e2b2a550..0e10991c43b 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1,5 +1,5 @@
 <!--
-$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_table.sgml,v 1.42 2001/05/03 17:50:55 tgl Exp $
+$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_table.sgml,v 1.43 2001/08/10 18:57:32 tgl Exp $
 Postgres documentation
 -->
 
@@ -25,8 +25,9 @@ Postgres documentation
   <synopsis>
 CREATE [ TEMPORARY | TEMP ] TABLE <replaceable class="PARAMETER">table_name</replaceable> (
     { <replaceable class="PARAMETER">column_name</replaceable> <replaceable class="PARAMETER">type</replaceable> [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ]
-      | <replaceable>table_constraint</replaceable> }  [, ... ]
-    ) [ INHERITS ( <replaceable>parent_table</replaceable> [, ... ] ) ]
+      | <replaceable>table_constraint</replaceable> }  [, ... ] )
+    [ INHERITS ( <replaceable>parent_table</replaceable> [, ... ] ) ]
+    [ WITH OIDS | WITHOUT OIDS ]
 
 where <replaceable class="PARAMETER">column_constraint</replaceable> can be:
 [ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]
@@ -109,6 +110,18 @@ and <replaceable class="PARAMETER">table_constraint</replaceable> can be:
      </varlistentry>
 
      <varlistentry>
+      <term>WITH OIDS or WITHOUT OIDS</term>
+      <listitem>
+       <para>
+        This optional clause specifies whether rows of the new table should
+	have OIDs (object identifiers) assigned to them.  The default is
+	WITH OIDS.  (If the new table inherits from any tables that have OIDs,
+	then WITH OIDS is forced even if the command says WITHOUT OIDS.)
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
       <term><replaceable class="PARAMETER">constraint_name</replaceable></term>
       <listitem>
        <para>
@@ -303,6 +316,49 @@ INHERITS ( <replaceable class="PARAMETER">parent_table</replaceable> [, ... ] )
   </para>
  </refsect1>
 
+ <refsect1 id="R1-SQL-OIDSCLAUSE-1">
+  <title id="R1-SQL-OIDSCLAUSE-1-TITLE">
+   OIDS Clause
+  </title>
+  <para>
+   <synopsis>
+    WITH OIDS | WITHOUT OIDS
+   </synopsis>
+  </para>
+
+  <para>
+   This clause controls whether an OID (object ID) is generated and assigned
+   to each row inserted into the table.  The default is WITH OIDS.
+   Specifying 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.
+  </para>
+
+  <para>
+   Whenever an application makes use of OIDs to identify specific rows of
+   a table, it is recommended that you create a unique index on OID for
+   that table, to ensure that OIDs in the table will indeed uniquely
+   identify rows even after counter wraparound.  (An index on OID is needed
+   anyway for fast lookup of rows by OID.)  Avoid assuming that OIDs are
+   unique across tables --- if you need a database-wide unique identifier,
+   use the combination of tableoid and row OID for the purpose.  (It is
+   likely that future Postgres releases will use a separate OID counter
+   for each table, so that it will be <emphasis>necessary</> not optional
+   to include tableoid to have a unique identifier database-wide.)
+  </para>
+
+  <tip>
+   <para>
+    WITHOUT OIDS is not recommended for tables with no primary key, since
+    without either an OID or a unique data key, it is difficult to identify
+    specific rows.
+   </para>
+  </tip>
+ </refsect1>
+
  <refsect1 id="R1-SQL-DEFAULTCLAUSE-1">
   <title id="R1-SQL-DEFAULTCLAUSE-1-TITLE">
    DEFAULT Clause
@@ -2098,6 +2154,18 @@ ALTER DOMAIN cities
      supported by <productname>Postgres</productname>.
     </para>
    </refsect3>
+
+   <refsect3 id="R3-SQL-INHERITANCE-1">
+    <title>
+     Object IDs
+    </title>
+    <para>
+     The <productname>Postgres</productname> concept of OIDs is not
+     standard.  SQL99 (but not SQL92) has a notion of object ID, but
+     the syntax and semantics are different --- SQL99 associates OIDs
+     with individual values, not with rows.
+    </para>
+   </refsect3>
   </refsect2>
  </refsect1>
 </refentry>
diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml
index 300851235ca..fd340b0c940 100644
--- a/doc/src/sgml/syntax.sgml
+++ b/doc/src/sgml/syntax.sgml
@@ -1,5 +1,5 @@
 <!--
-$Header: /cvsroot/pgsql/doc/src/sgml/syntax.sgml,v 1.43 2001/06/19 22:39:08 tgl Exp $
+$Header: /cvsroot/pgsql/doc/src/sgml/syntax.sgml,v 1.44 2001/08/10 18:57:32 tgl Exp $
 -->
 
 <chapter id="sql-syntax">
@@ -641,8 +641,9 @@ CAST ( '<replaceable>string</replaceable>' AS <replaceable>type</replaceable> )
 	  <primary>OID</primary>
 	 </indexterm>
 	 The unique identifier (object ID) of a row.  This is a serial number
-	 that is added by Postgres to all rows automatically. OIDs are not
-	 reused and are 32-bit quantities.
+	 that is automatically added by Postgres to all table rows (unless
+	 the table was created WITHOUT OIDS, in which case this column is
+	 not present).
 	</para>
        </listitem>
       </varlistentry>
@@ -686,8 +687,10 @@ CAST ( '<replaceable>string</replaceable>' AS <replaceable>type</replaceable> )
        <listitem>
 	<para>
 	 The identity (transaction ID) of the deleting transaction,
-	 or zero for an undeleted tuple.  In practice, this is never nonzero
-	 for a visible tuple.
+	 or zero for an undeleted tuple.  It is possible for this field
+	 to be nonzero in a visible tuple: that indicates that the
+	 deleting transaction hasn't committed yet, or that an attempted
+	 deletion was rolled back.
 	</para>
        </listitem>
       </varlistentry>
@@ -697,7 +700,6 @@ CAST ( '<replaceable>string</replaceable>' AS <replaceable>type</replaceable> )
        <listitem>
 	<para>
 	 The command identifier within the deleting transaction, or zero.
-	 Again, this is never nonzero for a visible tuple.
 	</para>
        </listitem>
       </varlistentry>
@@ -721,9 +723,26 @@ CAST ( '<replaceable>string</replaceable>' AS <replaceable>type</replaceable> )
     </para>
 
     <para>
+     OIDs are 32-bit quantities and are assigned from a single cluster-wide
+     counter.  In a large or long-lived database, it is possible for the
+     counter to wrap around.  Hence, it is bad practice to assume that OIDs
+     are unique, unless you take steps to ensure that they are unique.
+     Recommended practice when using OIDs for row identification is to create
+     a unique index on the OID column of each table for which the OID will be
+     used.  Never assume that OIDs are unique across tables; use the
+     combination of tableoid and row OID if you need a database-wide
+     identifier.  (Future releases of Postgres are likely to use a separate
+     OID counter for each table, so that tableoid <emphasis>must</> be
+     included to arrive at a globally unique identifier.)
+    </para>
+
+    <para>
+     Transaction and command identifiers are 32-bit quantities.
+    </para>
+
+    <para>
      For further information on the system attributes consult
      <xref linkend="STON87a" endterm="STON87a">.
-     Transaction and command identifiers are 32-bit quantities.
     </para>
 
   </sect1>