This patch adds a "WITH / WITHOUT OIDS" clause to CREATE TABLE
AS. This allows the user to explicitly specify whether OIDs should be
included in the newly-created relation (if form of this clause is
specified, the default_with_oids configuration variable is used). This
is useful because it provides a way for application authors to ensure
their applications are compatible with future versions of PostgreSQL
(in which the relation created by CREATE TABLE AS won't include OIDs
by default).

No equivalent functionality has been added to SELECT INTO: there
isn't a convenient syntax for it that I could see, and in any case
CREATE TABLE AS has always offered a superset of the functionality of
SELECT INTO. Therefore, I don't view this as a problem.

The implementation is a tad messy (it would be nice if CREATE TABLE AS
were a distinct node, to avoid needing to clutter up SelectStmt
further). I also needed to add an additional production to avoid a
shift/reduce conflict in the parser (see the XXX note in the patch
itself).

The patch includes updates to the documentation and regression tests.

Unless anyone objects, I plan to apply this within 48 hours.

-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	6 Jan 2004 19:04:09 -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	6 Jan 2004 19:04:09 -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 clauses 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/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	6 Jan 2004 19:04:09 -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	6 Jan 2004 19:04:09 -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	6 Jan 2004 19:04:09 -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	6 Jan 2004 19:04:09 -0000
***************
*** 1963,1968 ****
--- 1963,1969 ----
  	qry->into = stmt->into;
  	if (stmt->intoColNames)
  		applyColumnNames(qry->targetList, stmt->intoColNames);
+ 	qry->intoHasOids = stmt->intoHasOids;
  
  	/* 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	6 Jan 2004 19:04:09 -0000
***************
*** 232,238 ****
  %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
--- 232,238 ----
  %type <defelt>	createfunc_opt_item
  %type <typnam>	func_arg func_return func_type aggr_argtype
  
! %type <boolean> opt_arg TriggerForType OptTemp OptWithOids WithOidsAs
  %type <oncommit>	OnCommitOption
  
  %type <list>	for_update_clause opt_for_update_clause update_list
***************
*** 1844,1850 ****
   */
  
  CreateAsStmt:
! 			CREATE OptTemp TABLE qualified_name OptCreateAs AS SelectStmt
  				{
  					/*
  					 * When the SelectStmt is a set-operation tree, we must
--- 1844,1850 ----
   */
  
  CreateAsStmt:
! 			CREATE OptTemp TABLE qualified_name OptCreateAs WithOidsAs SelectStmt
  				{
  					/*
  					 * When the SelectStmt is a set-operation tree, we must
***************
*** 1861,1870 ****
--- 1861,1885 ----
  					$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.
+  *
+  * XXX: Is there a cleaner way to do this?
+  */
+ WithOidsAs:
+ 			WITH OIDS AS 							{ $$ = true; }
+ 			| WITHOUT OIDS AS 						{ $$ = false; }
+ 			| AS 									{ $$ = default_with_oids; }
+ 			;
+ 
  OptCreateAs:
  			'(' CreateAsList ')'					{ $$ = $2; }
  			| /*EMPTY*/								{ $$ = NIL; }
***************
*** 4511,4516 ****
--- 4526,4536 ----
  					n->targetList = $3;
  					n->into = $4;
  					n->intoColNames = NIL;
+ 					/*
+ 					 * Whether to include OIDs in the INTO relation is
+ 					 * controlled by the default_with_oids GUC var.
+ 					 */
+ 					n->intoHasOids = default_with_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	6 Jan 2004 19:04:09 -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 */
***************
*** 609,614 ****
--- 610,616 ----
  								 * DISTINCT) */
  	RangeVar   *into;			/* target table (for select into table) */
  	List	   *intoColNames;	/* column names for into table */
+ 	bool		intoHasOids;	/* should target table have OIDs? */
  	List	   *targetList;		/* the target list (of ResTarget) */
  	List	   *fromClause;		/* the FROM clause */
  	Node	   *whereClause;	/* WHERE qualification */
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	6 Jan 2004 19:14:37 -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	6 Jan 2004 19:12:49 -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 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [EMAIL PROTECTED] so that your
      message can get through to the mailing list cleanly

Reply via email to