Tom Lane <[EMAIL PROTECTED]> writes: > One thing that is *not* fine is something that I see had snuck past > me in the previous WITH OIDS patch. It is not okay for gram.y or > scan.l to be looking at GUC variables --- that creates > synchronization issues.
Good point (I remember reading gram.y's warning about this, but it must have slipped my mind...). Attached is a revised patch that corrects this. -Neil
Index: doc/src/sgml/runtime.sgml =================================================================== RCS file: /var/lib/cvs/pgsql-server/doc/src/sgml/runtime.sgml,v retrieving revision 1.227 diff -c -r1.227 runtime.sgml *** doc/src/sgml/runtime.sgml 13 Dec 2003 23:59:06 -0000 1.227 --- doc/src/sgml/runtime.sgml 7 Jan 2004 02:29:15 -0000 *************** *** 2437,2453 **** <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> --- 2437,2453 ---- <term><varname>default_with_oids</varname> (<type>boolean</type>)</term> <listitem> <para> ! This controls whether <command>CREATE TABLE</command> ! and <command>CREATE TABLE AS</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 ! created by <command>SELECT INTO</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> Index: doc/src/sgml/ref/create_table_as.sgml =================================================================== RCS file: /var/lib/cvs/pgsql-server/doc/src/sgml/ref/create_table_as.sgml,v retrieving revision 1.19 diff -c -r1.19 create_table_as.sgml *** doc/src/sgml/ref/create_table_as.sgml 13 Dec 2003 23:59:07 -0000 1.19 --- doc/src/sgml/ref/create_table_as.sgml 7 Jan 2004 03:18:20 -0000 *************** *** 20,26 **** <refsynopsisdiv> <synopsis> ! CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable>table_name</replaceable> [ (<replaceable>column_name</replaceable> [, ...] ) ] AS <replaceable>query</replaceable> </synopsis> </refsynopsisdiv> --- 20,26 ---- <refsynopsisdiv> <synopsis> ! CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable>table_name</replaceable> [ (<replaceable>column_name</replaceable> [, ...] ) ] [ [ WITH | WITHOUT ] OIDS ] AS <replaceable>query</replaceable> </synopsis> </refsynopsisdiv> *************** *** 99,104 **** --- 99,118 ---- </varlistentry> <varlistentry> + <term><literal>WITH OIDS</literal></term> + <term><literal>WITHOUT OIDS</literal></term> + <listitem> + <para> + This optional clause specifies whether the table created by + <command>CREATE TABLE AS</command> should include OIDs. If + neither form of this clause if specified, the value of the + <varname>default_with_oids</varname> configuration parameter is + used. + </para> + </listitem> + </varlistentry> + + <varlistentry> <term><replaceable>query</replaceable></term> <listitem> <para> *************** *** 121,143 **** 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 <productname>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 <productname>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> --- 135,164 ---- 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</> syntax. Furthermore, <command>CREATE ! TABLE AS</command> offers a superset of the functionality offerred ! by <command>SELECT INTO</command>. </para> <para> ! Prior to <productname>PostgreSQL</productname> 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 <productname>PostgresSQL</productname> 7.5, ! the <command>CREATE TABLE AS</command> command allows the user to ! explicitely specify whether OIDs should be included. If the ! presence of OIDs is not explicitely specified, ! the <varname>default_with_oids</varname> configuration variable is ! used. While this variable currently defaults to true, the default ! value may be changed in the future. Therefore, applications that ! require OIDs in the table created by <command>CREATE TABLE ! AS</command> should explicitely specify <literal>WITH ! OIDS</literal> to ensure compatibility with future versions ! of <productname>PostgreSQL</productname>. </para> </refsect1> Index: src/backend/commands/sequence.c =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/backend/commands/sequence.c,v retrieving revision 1.106 diff -c -r1.106 sequence.c *** src/backend/commands/sequence.c 14 Dec 2003 00:34:47 -0000 1.106 --- src/backend/commands/sequence.c 7 Jan 2004 02:51:37 -0000 *************** *** 178,184 **** stmt->relation = seq->sequence; stmt->inhRelations = NIL; stmt->constraints = NIL; ! stmt->hasoids = false; stmt->oncommit = ONCOMMIT_NOOP; seqoid = DefineRelation(stmt, RELKIND_SEQUENCE); --- 178,184 ---- stmt->relation = seq->sequence; stmt->inhRelations = NIL; stmt->constraints = NIL; ! stmt->hasoids = MUST_NOT_HAVE_OIDS; stmt->oncommit = ONCOMMIT_NOOP; seqoid = DefineRelation(stmt, RELKIND_SEQUENCE); Index: src/backend/commands/tablecmds.c =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/backend/commands/tablecmds.c,v retrieving revision 1.94 diff -c -r1.94 tablecmds.c *** src/backend/commands/tablecmds.c 29 Nov 2003 19:51:47 -0000 1.94 --- src/backend/commands/tablecmds.c 7 Jan 2004 03:03:49 -0000 *************** *** 47,52 **** --- 47,53 ---- #include "utils/acl.h" #include "utils/builtins.h" #include "utils/fmgroids.h" + #include "utils/guc.h" #include "utils/inval.h" #include "utils/lsyscache.h" #include "utils/relcache.h" *************** *** 185,191 **** */ descriptor = BuildDescForRelation(schema); ! descriptor->tdhasoid = (stmt->hasoids || parentHasOids); if (old_constraints != NIL) { --- 186,210 ---- */ descriptor = BuildDescForRelation(schema); ! if (parentHasOids) ! descriptor->tdhasoid = true; ! else ! { ! switch (stmt->hasoids) ! { ! case MUST_HAVE_OIDS: ! descriptor->tdhasoid = true; ! break; ! ! case MUST_NOT_HAVE_OIDS: ! descriptor->tdhasoid = false; ! break; ! ! case DEFAULT_OIDS: ! descriptor->tdhasoid = default_with_oids; ! break; ! } ! } if (old_constraints != NIL) { Index: src/backend/commands/typecmds.c =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/backend/commands/typecmds.c,v retrieving revision 1.51 diff -c -r1.51 typecmds.c *** src/backend/commands/typecmds.c 29 Nov 2003 19:51:47 -0000 1.51 --- src/backend/commands/typecmds.c 7 Jan 2004 02:51:13 -0000 *************** *** 1066,1072 **** createStmt->tableElts = coldeflist; createStmt->inhRelations = NIL; createStmt->constraints = NIL; ! createStmt->hasoids = false; createStmt->oncommit = ONCOMMIT_NOOP; /* --- 1066,1072 ---- createStmt->tableElts = coldeflist; createStmt->inhRelations = NIL; createStmt->constraints = NIL; ! createStmt->hasoids = MUST_NOT_HAVE_OIDS; createStmt->oncommit = ONCOMMIT_NOOP; /* Index: src/backend/commands/view.c =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/backend/commands/view.c,v retrieving revision 1.79 diff -c -r1.79 view.c *** src/backend/commands/view.c 29 Nov 2003 19:51:48 -0000 1.79 --- src/backend/commands/view.c 7 Jan 2004 02:50:44 -0000 *************** *** 141,147 **** createStmt->tableElts = attrList; createStmt->inhRelations = NIL; createStmt->constraints = NIL; ! createStmt->hasoids = false; createStmt->oncommit = ONCOMMIT_NOOP; /* --- 141,147 ---- createStmt->tableElts = attrList; createStmt->inhRelations = NIL; createStmt->constraints = NIL; ! createStmt->hasoids = MUST_NOT_HAVE_OIDS; createStmt->oncommit = ONCOMMIT_NOOP; /* Index: src/backend/executor/execMain.c =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/backend/executor/execMain.c,v retrieving revision 1.224 diff -c -r1.224 execMain.c *** src/backend/executor/execMain.c 28 Dec 2003 21:57:36 -0000 1.224 --- src/backend/executor/execMain.c 7 Jan 2004 02:29:15 -0000 *************** *** 593,606 **** if (operation == CMD_SELECT && parseTree->into != NULL) { do_select_into = true; ! ! /* ! * The presence of OIDs in the result set of SELECT INTO is ! * controlled by the default_with_oids GUC parameter. The ! * behavior in versions of PostgreSQL prior to 7.5 is to ! * always include OIDs. ! */ ! estate->es_force_oids = default_with_oids; } /* --- 593,599 ---- if (operation == CMD_SELECT && parseTree->into != NULL) { do_select_into = true; ! estate->es_force_oids = parseTree->intoHasOids; } /* Index: src/backend/nodes/copyfuncs.c =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/backend/nodes/copyfuncs.c,v retrieving revision 1.273 diff -c -r1.273 copyfuncs.c *** src/backend/nodes/copyfuncs.c 5 Jan 2004 05:07:35 -0000 1.273 --- src/backend/nodes/copyfuncs.c 7 Jan 2004 02:29:15 -0000 *************** *** 1538,1543 **** --- 1538,1544 ---- COPY_NODE_FIELD(utilityStmt); COPY_SCALAR_FIELD(resultRelation); COPY_NODE_FIELD(into); + COPY_SCALAR_FIELD(intoHasOids); COPY_SCALAR_FIELD(hasAggs); COPY_SCALAR_FIELD(hasSubLinks); COPY_NODE_FIELD(rtable); *************** *** 1612,1617 **** --- 1613,1619 ---- COPY_NODE_FIELD(distinctClause); COPY_NODE_FIELD(into); COPY_NODE_FIELD(intoColNames); + COPY_SCALAR_FIELD(intoHasOids); COPY_NODE_FIELD(targetList); COPY_NODE_FIELD(fromClause); COPY_NODE_FIELD(whereClause); Index: src/backend/nodes/equalfuncs.c =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/backend/nodes/equalfuncs.c,v retrieving revision 1.212 diff -c -r1.212 equalfuncs.c *** src/backend/nodes/equalfuncs.c 5 Jan 2004 05:07:35 -0000 1.212 --- src/backend/nodes/equalfuncs.c 7 Jan 2004 02:29:15 -0000 *************** *** 604,609 **** --- 604,610 ---- COMPARE_NODE_FIELD(utilityStmt); COMPARE_SCALAR_FIELD(resultRelation); COMPARE_NODE_FIELD(into); + COMPARE_SCALAR_FIELD(intoHasOids); COMPARE_SCALAR_FIELD(hasAggs); COMPARE_SCALAR_FIELD(hasSubLinks); COMPARE_NODE_FIELD(rtable); *************** *** 667,672 **** --- 668,674 ---- COMPARE_NODE_FIELD(distinctClause); COMPARE_NODE_FIELD(into); COMPARE_NODE_FIELD(intoColNames); + COMPARE_SCALAR_FIELD(intoHasOids); COMPARE_NODE_FIELD(targetList); COMPARE_NODE_FIELD(fromClause); COMPARE_NODE_FIELD(whereClause); Index: src/backend/parser/analyze.c =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/backend/parser/analyze.c,v retrieving revision 1.292 diff -c -r1.292 analyze.c *** src/backend/parser/analyze.c 29 Nov 2003 19:51:51 -0000 1.292 --- src/backend/parser/analyze.c 7 Jan 2004 03:02:51 -0000 *************** *** 41,46 **** --- 41,47 ---- #include "utils/acl.h" #include "utils/builtins.h" #include "utils/fmgroids.h" + #include "utils/guc.h" #include "utils/lsyscache.h" #include "utils/relcache.h" #include "utils/syscache.h" *************** *** 1964,1969 **** --- 1965,1985 ---- if (stmt->intoColNames) applyColumnNames(qry->targetList, stmt->intoColNames); + switch (stmt->intoHasOids) + { + case MUST_HAVE_OIDS: + qry->intoHasOids = true; + break; + + case MUST_NOT_HAVE_OIDS: + qry->intoHasOids = false; + break; + + case DEFAULT_OIDS: + qry->intoHasOids = default_with_oids; + break; + } + /* mark column origins */ markTargetListOrigins(pstate, qry->targetList); Index: src/backend/parser/gram.y =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/backend/parser/gram.y,v retrieving revision 2.441 diff -c -r2.441 gram.y *** src/backend/parser/gram.y 1 Dec 2003 22:07:58 -0000 2.441 --- src/backend/parser/gram.y 7 Jan 2004 03:19:16 -0000 *************** *** 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 */ --- 63,68 ---- *************** *** 108,113 **** --- 107,113 ---- JoinType jtype; DropBehavior dbehavior; OnCommitAction oncommit; + ContainsOids withoids; List *list; FastList fastlist; Node *node; *************** *** 232,239 **** %type <defelt> createfunc_opt_item %type <typnam> func_arg func_return func_type aggr_argtype ! %type <boolean> opt_arg TriggerForType OptTemp OptWithOids ! %type <oncommit> OnCommitOption %type <list> for_update_clause opt_for_update_clause update_list %type <boolean> opt_all --- 232,240 ---- %type <defelt> createfunc_opt_item %type <typnam> func_arg func_return func_type aggr_argtype ! %type <boolean> opt_arg TriggerForType OptTemp ! %type <oncommit> OnCommitOption ! %type <withoids> OptWithOids WithOidsAs %type <list> for_update_clause opt_for_update_clause update_list %type <boolean> opt_all *************** *** 1821,1834 **** ; 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_with_oids" GUC var ! */ ! | /*EMPTY*/ { $$ = default_with_oids; } ; OnCommitOption: ON COMMIT DROP { $$ = ONCOMMIT_DROP; } --- 1822,1830 ---- ; OptWithOids: ! WITH OIDS { $$ = MUST_HAVE_OIDS; } ! | WITHOUT OIDS { $$ = MUST_NOT_HAVE_OIDS; } ! | /*EMPTY*/ { $$ = DEFAULT_OIDS; } ; OnCommitOption: ON COMMIT DROP { $$ = ONCOMMIT_DROP; } *************** *** 1844,1850 **** */ CreateAsStmt: ! CREATE OptTemp TABLE qualified_name OptCreateAs AS SelectStmt { /* * When the SelectStmt is a set-operation tree, we must --- 1840,1846 ---- */ CreateAsStmt: ! CREATE OptTemp TABLE qualified_name OptCreateAs WithOidsAs SelectStmt { /* * When the SelectStmt is a set-operation tree, we must *************** *** 1861,1870 **** --- 1857,1879 ---- $4->istemp = $2; n->into = $4; n->intoColNames = $5; + n->intoHasOids = $6; $$ = $7; } ; + /* + * To avoid a shift/reduce conflict in CreateAsStmt, we need to + * include the 'AS' terminal in the parsing of WITH/WITHOUT + * OIDS. Unfortunately that means this production is effectively a + * duplicate of OptWithOids. + */ + WithOidsAs: + WITH OIDS AS { $$ = MUST_HAVE_OIDS; } + | WITHOUT OIDS AS { $$ = MUST_NOT_HAVE_OIDS; } + | AS { $$ = DEFAULT_OIDS; } + ; + OptCreateAs: '(' CreateAsList ')' { $$ = $2; } | /*EMPTY*/ { $$ = NIL; } *************** *** 4511,4516 **** --- 4520,4526 ---- n->targetList = $3; n->into = $4; n->intoColNames = NIL; + n->intoHasOids = DEFAULT_OIDS; n->fromClause = $5; n->whereClause = $6; n->groupClause = $7; Index: src/include/nodes/parsenodes.h =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/include/nodes/parsenodes.h,v retrieving revision 1.250 diff -c -r1.250 parsenodes.h *** src/include/nodes/parsenodes.h 29 Nov 2003 22:41:06 -0000 1.250 --- src/include/nodes/parsenodes.h 7 Jan 2004 02:58:52 -0000 *************** *** 55,60 **** --- 55,61 ---- int resultRelation; /* target relation (index into rtable) */ RangeVar *into; /* target relation for SELECT INTO */ + bool intoHasOids; /* should target relation contain OIDs? */ bool hasAggs; /* has aggregates in tlist or havingQual */ bool hasSubLinks; /* has subquery SubLink */ *************** *** 595,600 **** --- 596,611 ---- SETOP_EXCEPT } SetOperation; + typedef enum ContainsOids + { + MUST_HAVE_OIDS, /* WITH OIDS explicitely specified */ + MUST_NOT_HAVE_OIDS, /* WITHOUT OIDS explicitely specified */ + DEFAULT_OIDS /* neither specified; use the default, + * which is the value of the + * default_with_oids GUC var + */ + } ContainsOids; + typedef struct SelectStmt { NodeTag type; *************** *** 602,614 **** /* * These fields are used only in "leaf" SelectStmts. * ! * into and intoColNames are a kluge; they belong somewhere else... */ List *distinctClause; /* NULL, list of DISTINCT ON exprs, or * lcons(NIL,NIL) for all (SELECT * DISTINCT) */ RangeVar *into; /* target table (for select into table) */ List *intoColNames; /* column names for into table */ List *targetList; /* the target list (of ResTarget) */ List *fromClause; /* the FROM clause */ Node *whereClause; /* WHERE qualification */ --- 613,627 ---- /* * These fields are used only in "leaf" SelectStmts. * ! * into, intoColNames and intoHasOids are a kluge; they belong ! * somewhere else... */ List *distinctClause; /* NULL, list of DISTINCT ON exprs, or * lcons(NIL,NIL) for all (SELECT * DISTINCT) */ RangeVar *into; /* target table (for select into table) */ List *intoColNames; /* column names for into table */ + ContainsOids intoHasOids; /* should target table have OIDs? */ List *targetList; /* the target list (of ResTarget) */ List *fromClause; /* the FROM clause */ Node *whereClause; /* WHERE qualification */ *************** *** 895,901 **** List *inhRelations; /* relations to inherit from (list of * inhRelation) */ List *constraints; /* constraints (list of Constraint nodes) */ ! bool hasoids; /* should it have OIDs? */ OnCommitAction oncommit; /* what do we do at COMMIT? */ } CreateStmt; --- 908,914 ---- List *inhRelations; /* relations to inherit from (list of * inhRelation) */ List *constraints; /* constraints (list of Constraint nodes) */ ! ContainsOids hasoids; /* should it have OIDs? */ OnCommitAction oncommit; /* what do we do at COMMIT? */ } CreateStmt; Index: src/test/regress/expected/without_oid.out =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/test/regress/expected/without_oid.out,v retrieving revision 1.2 diff -c -r1.2 without_oid.out *** src/test/regress/expected/without_oid.out 1 Dec 2003 22:08:02 -0000 1.2 --- src/test/regress/expected/without_oid.out 7 Jan 2004 02:29:15 -0000 *************** *** 36,38 **** --- 36,62 ---- DROP TABLE wi; DROP TABLE wo; + -- + -- WITH / WITHOUT OIDS in CREATE TABLE AS + -- + CREATE TABLE create_table_test ( + a int, + b int + ); + COPY create_table_test FROM stdin; + CREATE TABLE create_table_test2 WITH OIDS AS + SELECT a + b AS c1, a - b AS c2 FROM create_table_test; + CREATE TABLE create_table_test3 WITHOUT OIDS AS + SELECT a + b AS c1, a - b AS c2 FROM create_table_test; + SELECT count(oid) FROM create_table_test2; + count + ------- + 2 + (1 row) + + -- should fail + SELECT count(oid) FROM create_table_test3; + ERROR: column "oid" does not exist + DROP TABLE create_table_test; + DROP TABLE create_table_test2; + DROP TABLE create_table_test3; Index: src/test/regress/sql/without_oid.sql =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/test/regress/sql/without_oid.sql,v retrieving revision 1.2 diff -c -r1.2 without_oid.sql *** src/test/regress/sql/without_oid.sql 1 Dec 2003 22:08:02 -0000 1.2 --- src/test/regress/sql/without_oid.sql 7 Jan 2004 02:29:15 -0000 *************** *** 33,35 **** --- 33,62 ---- DROP TABLE wi; DROP TABLE wo; + + -- + -- WITH / WITHOUT OIDS in CREATE TABLE AS + -- + CREATE TABLE create_table_test ( + a int, + b int + ); + + COPY create_table_test FROM stdin; + 5 10 + 10 15 + \. + + CREATE TABLE create_table_test2 WITH OIDS AS + SELECT a + b AS c1, a - b AS c2 FROM create_table_test; + + CREATE TABLE create_table_test3 WITHOUT OIDS AS + SELECT a + b AS c1, a - b AS c2 FROM create_table_test; + + SELECT count(oid) FROM create_table_test2; + -- should fail + SELECT count(oid) FROM create_table_test3; + + DROP TABLE create_table_test; + DROP TABLE create_table_test2; + DROP TABLE create_table_test3;
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings