This patch adds a new GUC var, "default_use_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.
Comments are welcome.
(This patch is for the 7.5 queue.)
-Neil
Index: doc/src/sgml/datatype.sgml
===================================================================
RCS file: /var/lib/cvs/pgsql-server/doc/src/sgml/datatype.sgml,v
retrieving revision 1.124
diff -c -r1.124 datatype.sgml
*** doc/src/sgml/datatype.sgml 12 Sep 2003 22:17:22 -0000 1.124
--- doc/src/sgml/datatype.sgml 30 Sep 2003 23:39:42 -0000
***************
*** 2888,2910 ****
<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.
</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.
! </para>
<para>
The <type>oid</> type itself has few operations beyond comparison.
--- 2888,2929 ----
<para>
Object identifiers (OIDs) are used internally by
! <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_use_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.
! </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_use_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.
Index: doc/src/sgml/runtime.sgml
===================================================================
RCS file: /var/lib/cvs/pgsql-server/doc/src/sgml/runtime.sgml,v
retrieving revision 1.209
diff -c -r1.209 runtime.sgml
*** doc/src/sgml/runtime.sgml 20 Sep 2003 20:12:05 -0000 1.209
--- doc/src/sgml/runtime.sgml 30 Sep 2003 21:24:05 -0000
***************
*** 2482,2488 ****
</listitem>
</varlistentry>
! </variablelist>
</sect3>
<sect3 id="runtime-config-compatible-clients">
<title>Platform and Client Compatibility</title>
--- 2482,2515 ----
</listitem>
</varlistentry>
! <varlistentry>
! <term><varname>default_use_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. In <productname>PostgreSQL</productname> &version;
! this defaults to true. This is also the behavior of previous
! versions of <productname>PostgreSQL</productname>. However,
! using OIDs on user tables 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 specifically
! require OIDs on certain tables should 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>
Index: doc/src/sgml/ref/alter_table.sgml
===================================================================
RCS file: /var/lib/cvs/pgsql-server/doc/src/sgml/ref/alter_table.sgml,v
retrieving revision 1.62
diff -c -r1.62 alter_table.sgml
*** doc/src/sgml/ref/alter_table.sgml 22 Sep 2003 00:16:57 -0000 1.62
--- doc/src/sgml/ref/alter_table.sgml 30 Sep 2003 23:35:07 -0000
***************
*** 149,154 ****
--- 149,160 ----
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>
Index: doc/src/sgml/ref/create_table.sgml
===================================================================
RCS file: /var/lib/cvs/pgsql-server/doc/src/sgml/ref/create_table.sgml,v
retrieving revision 1.72
diff -c -r1.72 create_table.sgml
*** doc/src/sgml/ref/create_table.sgml 9 Sep 2003 18:28:52 -0000 1.72
--- doc/src/sgml/ref/create_table.sgml 30 Sep 2003 23:46:48 -0000
***************
*** 243,264 ****
<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</>.)
</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.
</para>
</listitem>
</varlistentry>
--- 243,272 ----
<listitem>
<para>
This optional clause specifies whether rows of the new table
! 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_use_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>
! 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>
Index: doc/src/sgml/ref/pg_dump.sgml
===================================================================
RCS file: /var/lib/cvs/pgsql-server/doc/src/sgml/ref/pg_dump.sgml,v
retrieving revision 1.65
diff -c -r1.65 pg_dump.sgml
*** doc/src/sgml/ref/pg_dump.sgml 23 Sep 2003 22:48:53 -0000 1.65
--- doc/src/sgml/ref/pg_dump.sgml 30 Sep 2003 23:44:42 -0000
***************
*** 611,618 ****
</para>
<para>
! Once restored, it is wise to run <command>ANALYZE</> on each
! restored table so the optimizer has useful statistics.
</para>
</refsect1>
--- 611,621 ----
</para>
<para>
! 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>
Index: src/backend/parser/gram.y
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/backend/parser/gram.y,v
retrieving revision 2.435
diff -c -r2.435 gram.y
*** src/backend/parser/gram.y 26 Sep 2003 15:27:32 -0000 2.435
--- src/backend/parser/gram.y 30 Sep 2003 22:20:28 -0000
***************
*** 63,68 ****
--- 63,69 ----
#include "utils/numeric.h"
#include "utils/datetime.h"
#include "utils/date.h"
+ #include "utils/guc.h"
extern List *parsetree; /* final parse result is delivered here */
***************
*** 1820,1826 ****
OptWithOids:
WITH OIDS { $$ = TRUE; }
| WITHOUT OIDS { $$ = FALSE; }
! | /*EMPTY*/ { $$ = TRUE; }
;
OnCommitOption: ON COMMIT DROP { $$ = ONCOMMIT_DROP; }
--- 1821,1832 ----
OptWithOids:
WITH OIDS { $$ = TRUE; }
| WITHOUT OIDS { $$ = FALSE; }
! /*
! * If the user didn't explicitely specify WITH or WITHOUT
! * OIDS, decide whether to include OIDs based on the
! * "default_use_oids" GUC var
! */
! | /*EMPTY*/ { $$ = default_use_oids; }
;
OnCommitOption: ON COMMIT DROP { $$ = ONCOMMIT_DROP; }
Index: src/backend/utils/misc/guc.c
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/backend/utils/misc/guc.c,v
retrieving revision 1.161
diff -c -r1.161 guc.c
*** src/backend/utils/misc/guc.c 29 Sep 2003 00:05:25 -0000 1.161
--- src/backend/utils/misc/guc.c 30 Sep 2003 21:03:42 -0000
***************
*** 123,128 ****
--- 123,130 ----
bool Password_encryption = true;
+ bool default_use_oids = true;
+
int log_min_error_statement = PANIC;
int log_min_messages = NOTICE;
int client_min_messages = NOTICE;
***************
*** 260,266 ****
/* QUERY_TUNING */
gettext_noop("Query Tuning"),
/* QUERY_TUNING_METHOD */
! gettext_noop("Query Tuning / Planner Method Enabling"),
/* QUERY_TUNING_COST */
gettext_noop("Query Tuning / Planner Cost Constants"),
/* QUERY_TUNING_GEQO */
--- 262,268 ----
/* QUERY_TUNING */
gettext_noop("Query Tuning"),
/* QUERY_TUNING_METHOD */
! gettext_noop("Query Tuning / Planner Method Configuration"),
/* QUERY_TUNING_COST */
gettext_noop("Query Tuning / Planner Cost Constants"),
/* QUERY_TUNING_GEQO */
***************
*** 821,826 ****
--- 823,836 ----
&add_missing_from,
true, NULL, NULL
},
+ {
+ {"default_use_oids", PGC_USERSET, COMPAT_OPTIONS_PREVIOUS,
+ gettext_noop("by default, newly-created tables should have OIDs"),
+ NULL
+ },
+ &default_use_oids,
+ true, NULL, NULL
+ },
/* End-of-list marker */
{
Index: src/backend/utils/misc/postgresql.conf.sample
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/backend/utils/misc/postgresql.conf.sample,v
retrieving revision 1.90
diff -c -r1.90 postgresql.conf.sample
*** src/backend/utils/misc/postgresql.conf.sample 7 Sep 2003 15:26:54 -0000 1.90
--- src/backend/utils/misc/postgresql.conf.sample 30 Sep 2003 21:07:21 -0000
***************
*** 94,100 ****
# QUERY TUNING
#---------------------------------------------------------------------------
! # - Planner Method Enabling -
#enable_hashagg = true
#enable_hashjoin = true
--- 94,100 ----
# QUERY TUNING
#---------------------------------------------------------------------------
! # - Planner Method Configuration -
#enable_hashagg = true
#enable_hashjoin = true
***************
*** 247,252 ****
--- 247,253 ----
#add_missing_from = true
#regex_flavor = advanced # advanced, extended, or basic
#sql_inheritance = true
+ #default_use_oids = true
# - Other Platforms & Clients -
Index: src/bin/pg_dump/pg_dump.c
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.352
diff -c -r1.352 pg_dump.c
*** src/bin/pg_dump/pg_dump.c 27 Sep 2003 22:10:01 -0000 1.352
--- src/bin/pg_dump/pg_dump.c 30 Sep 2003 21:28:18 -0000
***************
*** 5404,5411 ****
appendPQExpBuffer(q, ")");
}
! if (!tbinfo->hasoids)
! appendPQExpBuffer(q, " WITHOUT OIDS");
appendPQExpBuffer(q, ";\n");
--- 5404,5410 ----
appendPQExpBuffer(q, ")");
}
! appendPQExpBuffer(q, tbinfo->hasoids ? " WITH OIDS" : " WITHOUT OIDS");
appendPQExpBuffer(q, ";\n");
Index: src/bin/psql/tab-complete.c
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/bin/psql/tab-complete.c,v
retrieving revision 1.85
diff -c -r1.85 tab-complete.c
*** src/bin/psql/tab-complete.c 7 Sep 2003 15:26:54 -0000 1.85
--- src/bin/psql/tab-complete.c 30 Sep 2003 21:07:58 -0000
***************
*** 516,521 ****
--- 516,522 ----
"default_statistics_target",
"default_transaction_isolation",
"default_transaction_read_only",
+ "default_use_oids",
"dynamic_library_path",
"effective_cache_size",
"enable_hashagg",
Index: src/include/utils/guc.h
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/include/utils/guc.h,v
retrieving revision 1.41
diff -c -r1.41 guc.h
*** src/include/utils/guc.h 1 Sep 2003 04:15:51 -0000 1.41
--- src/include/utils/guc.h 30 Sep 2003 21:02:25 -0000
***************
*** 109,114 ****
--- 109,116 ----
extern bool SQL_inheritance;
extern bool Australian_timezones;
+ extern bool default_use_oids;
+
extern int log_min_error_statement;
extern int log_min_messages;
extern int client_min_messages;
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
