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]