Re: ALTER TABLE .. SET WITH / WITHOUT OIDS - Mailing list pgsql-patches

I am using postgresql 7.3.2 but there is no
ALTER TABLE .. SET WITH / WITHOUT OIDS
command:

test=# create table test (a int) with oids;
test=# alter table teste set without oids;
ERROR:  parser: parse error at or near "set" at character 19

Do I have to apply some patch so that I can remove OIDs from a table without
having to drop it and recreate it?

Thanks,

Fabio.

"Rod Taylor" <rbt@rbt.ca> wrote in message
news:1039359174.79804.100.camel@jester...
> --=-39x4Ba5NCnmHYLSHQVel
> Content-Type: multipart/mixed; boundary="=-kOit5t7LMlG8gYDF7SuD"
>
> --=-kOit5t7LMlG8gYDF7SuD
> Content-Type: text/plain
> Content-Transfer-Encoding: quoted-printable
>
> Opted for the syntax in the subject as I didn't think that ADD / DROP
> OIDS would work (shift/reduce conflicts), and it matches the CREATE
> TABLE oids syntax.
>
> Added and removed the oids column from several inherited tables many
> times, while adding and playing with data.  I could not come up with any
> issues with insert, select, update, delete statements.  So I'll assume
> that the various places reading OIDS are doing the right thing.
>
> Works somewhat like adding / dropping a column with the default of 0.
>
> --=20
> Rod Taylor <rbt@rbt.ca>
>
> PGP Key: http://www.rbt.ca/rbtpub.asc
>
> --=-kOit5t7LMlG8gYDF7SuD
> Content-Disposition: attachment; filename=altoid.patch
> Content-Type: text/plain; name=altoid.patch; charset=ISO-8859-1
> Content-Transfer-Encoding: quoted-printable
>
> ? src/backend/commands/.tablecmds.c.swp
> Index: doc/src/sgml/ref/alter_table.sgml
>
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
>
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> RCS file:
/projects/cvsroot/pgsql-server/doc/src/sgml/ref/alter_table.sgml,v
> retrieving revision 1.52
> diff -c -r1.52 alter_table.sgml
> *** doc/src/sgml/ref/alter_table.sgml 2002/10/19 23:09:20 1.52
> --- doc/src/sgml/ref/alter_table.sgml 2002/12/08 14:34:48
> ***************
> *** 34,39 ****
> --- 34,41 ----
>   ALTER TABLE [ ONLY ] <replaceable
class=3D"PARAMETER">table</replaceable>=
>  [ * ]
>       ALTER [ COLUMN ] <replaceable
class=3D"PARAMETER">column</replaceable=
> > SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
>   ALTER TABLE [ ONLY ] <replaceable
class=3D"PARAMETER">table</replaceable>=
>  [ * ]
> +     SET { WITH | WITHOUT } OIDS
> + ALTER TABLE [ ONLY ] <replaceable
class=3D"PARAMETER">table</replaceable>=
>  [ * ]
>       RENAME [ COLUMN ] <replaceable
class=3D"PARAMETER">column</replaceabl=
> e> TO <replaceable
>       class=3D"PARAMETER">new_column</replaceable>
>   ALTER TABLE <replaceable class=3D"PARAMETER">table</replaceable>
> ***************
> *** 287,296 ****
>      </varlistentry>
> =20=20
>      <varlistentry>
>       <term>RENAME</term>
>       <listitem>
>        <para>
> !       The <literal>RENAME</literal> forms change the name of  a table
>         (or an index, sequence, or view) or the name of an individual
colum=
> n in
>         a table. There is no effect on the stored data.
>        </para>
> --- 289,317 ----
>      </varlistentry>
> =20=20
>      <varlistentry>
> +     <term>SET WITH/WITHOUT OIDS</term>
> +     <listitem>
> +      <para>
> +       Adds or removes the <literal>OID</literal> column from the the
tabl=
> e.  When enabling
> +       (setting with) oids in a table, the currently existing tuples are
n=
> ot immediately
> +       numbered.  Rather, it is treated as if a new column has been added
=
> to the table.
> +       Since <literal>OID</literal>s cannot be <literal>NULL</literal>, 0
=
> is used instead.
> +      </para>
> +      <para>
> +       Removing (setting without) oids from a table also do not occur
imme=
> diately.  The
> +       space an <literal>OID</literal> uses will be reclaimed when the
tup=
> le is updated.=20
> +       Without updating the tuple, both the space and the value of the
<li=
> teral>OID</literal>
> +       are maintained indefinitely.  This is semantically similar to the=
> =20
> +       <literal>DROP COLUMN</literal> process.
> +      </para>
> +     </listitem>
> +    </varlistentry>
> +=20
> +    <varlistentry>
>       <term>RENAME</term>
>       <listitem>
>        <para>
> !       The <literal>RENAME</literal> forms change the name of a table
>         (or an index, sequence, or view) or the name of an individual
colum=
> n in
>         a table. There is no effect on the stored data.
>        </para>
> Index: src/backend/catalog/heap.c
>
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
>
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> RCS file: /projects/cvsroot/pgsql-server/src/backend/catalog/heap.c,v
> retrieving revision 1.235
> diff -c -r1.235 heap.c
> *** src/backend/catalog/heap.c 2002/11/15 02:50:05 1.235
> --- src/backend/catalog/heap.c 2002/12/08 14:35:30
> ***************
> *** 528,533 ****
> --- 528,585 ----
>   heap_close(rel, RowExclusiveLock);
>   }
> =20=20
> + /*
> +  * AddNewOidAttributeTuple
> +  *
> +  * Create a pg_attribute entry for OIDs
> +  */
> + void
> + AddNewOidAttributeTuple(Oid new_rel_oid)
> + {
> + Form_pg_attribute attStruct;
> + HeapTuple tup;
> + Relation rel;
> + CatalogIndexState indstate;
> +=20
> + /*
> + * open pg_attribute and its indexes.
> + */
> + rel =3D heap_openr(AttributeRelationName, RowExclusiveLock);
> +=20
> + indstate =3D CatalogOpenIndexes(rel);
> +=20
> + /* Add the OID attribute */
> + tup =3D heap_addheader(Natts_pg_attribute,
> + false,
> + ATTRIBUTE_TUPLE_SIZE,
> + &a2);
> +=20
> + /* Fill in the correct relation OID in the copied tuple */
> + attStruct =3D (Form_pg_attribute) GETSTRUCT(tup);
> + attStruct->attrelid =3D new_rel_oid;
> +=20
> + /*
> + * Unneeded since they should be OK in the constant data
> + * anyway
> + */
> + /* attStruct->attstattarget =3D 0; */
> + /* attStruct->attcacheoff =3D -1; */
> +=20
> + simple_heap_insert(rel, tup);
> +=20
> + CatalogIndexInsert(indstate, tup);
> +=20
> + heap_freetuple(tup);
> +=20
> + /*
> + * clean up
> + */
> + CatalogCloseIndexes(indstate);
> +=20
> + heap_close(rel, RowExclusiveLock);
> + }
> +=20
> +=20
>   /* --------------------------------
>    * AddNewRelationTuple
>    *
> Index: src/backend/commands/tablecmds.c
>
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
>
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> RCS file:
/projects/cvsroot/pgsql-server/src/backend/commands/tablecmds.c,v
> retrieving revision 1.57
> diff -c -r1.57 tablecmds.c
> *** src/backend/commands/tablecmds.c 2002/11/23 18:26:45 1.57
> --- src/backend/commands/tablecmds.c 2002/12/08 14:36:18
> ***************
> *** 2140,2146 ****
>   heap_close(rel, NoLock);
>   }
> =20=20
> -=20
>   /*
>    * ALTER TABLE ALTER COLUMN SET/DROP DEFAULT
>    */
> --- 2140,2145 ----
> ***************
> *** 2390,2395 ****
> --- 2389,2511 ----
>   heap_close(rel, NoLock); /* close rel, but keep lock! */
>   }
> =20=20
> + /*
> +  * ALTER TABLE SET {WITH | WITHOUT} OIDS
> +  */
> + void
> + AlterTableAlterOids(Oid myrelid, bool recurse, bool setOid)
> + {
> + Relation rel;
> + Relation class_rel;
> + HeapTuple tuple;
> + Form_pg_class tuple_class;
> +=20
> + rel =3D heap_open(myrelid, AccessExclusiveLock);
> +=20
> + if (rel->rd_rel->relkind !=3D RELKIND_RELATION)
> + elog(ERROR, "ALTER TABLE: relation \"%s\" is not a table",
> + RelationGetRelationName(rel));
> +=20
> + if (!allowSystemTableMods
> + && IsSystemRelation(rel))
> + elog(ERROR, "ALTER TABLE: relation \"%s\" is a system catalog",
> + RelationGetRelationName(rel));
> +=20
> + if (!pg_class_ownercheck(myrelid, GetUserId()))
> + aclcheck_error(ACLCHECK_NOT_OWNER, RelationGetRelationName(rel));
> +=20
> +=20
> + /* Get its pg_class tuple, too */
> + class_rel =3D heap_openr(RelationRelationName, RowExclusiveLock);
> +=20
> + tuple =3D SearchSysCacheCopy(RELOID,
> +    ObjectIdGetDatum(myrelid),
> +    0, 0, 0);
> + if (!HeapTupleIsValid(tuple))
> + elog(ERROR, "ALTER TABLE: relation %u not found", myrelid);
> + tuple_class =3D (Form_pg_class) GETSTRUCT(tuple);
> +=20
> + /* Can we change the ownership of this tuple? */
> + CheckTupleType(tuple_class);
> +=20
> + /*
> + * Okay, this is a valid tuple: check it's hasoids flag
> + * to see if we actually need to change anything
> + */
> + if (tuple_class->relhasoids =3D=3D setOid)
> + elog(ERROR, "ALTER TABLE: Table is already %s",
> + setOid ? "WITH OIDS" : "WITHOUT OIDS");
> +=20
> + /*
> + * Propagate to children if desired
> + */
> + if (recurse)
> + {
> + List    *child,
> +    *children;
> +=20
> + /* this routine is actually in the planner */
> + children =3D find_all_inheritors(myrelid);
> +=20
> + /*
> + * find_all_inheritors does the recursive search of the
> + * inheritance hierarchy, so all we have to do is process all of
> + * the relids in the list that it returns.
> + */
> + foreach(child, children)
> + {
> + Oid childrelid =3D lfirsti(child);
> +=20
> + if (childrelid =3D=3D myrelid)
> + continue;
> +=20
> + AlterTableAlterOids(childrelid, false, setOid);
> + }
> + }
> +=20
> +=20
> + tuple_class->relhasoids =3D setOid;
> + simple_heap_update(class_rel, &tuple->t_self, tuple);
> +=20
> + /* Keep the catalog indexes up to date */
> + CatalogUpdateIndexes(class_rel, tuple);
> +=20
> +=20
> +=20
> + if (setOid)
> + /*
> + * Generate the now required OID pg_attribute entry
> + */
> + AddNewOidAttributeTuple(myrelid);
> + else
> + {
> + HeapTuple atttup;
> + Relation attrel;
> +=20
> + /* Add / Remove the oid record from pg_attribute */
> + attrel =3D heap_open(RelOid_pg_attribute, RowExclusiveLock);
> +=20
> + /*
> + * Oids are being removed from the relation, so we need
> + * to remove the oid pg_attribute record relating.
> + */
> + atttup =3D SearchSysCache(ATTNUM,
> + ObjectIdGetDatum(myrelid),
> + ObjectIdAttributeNumber, 0, 0);
> + if (!HeapTupleIsValid(atttup))
> + elog(ERROR, "ALTER TABLE: relation %u doesn't have an Oid column to re=
> move", myrelid);
> +=20
> + simple_heap_delete(attrel, &atttup->t_self);
> +=20
> + ReleaseSysCache(atttup);
> +=20
> + heap_close(attrel, NoLock); /* close rel, but keep lock! */
> + }
> +=20
> +=20
> + heap_close(rel, NoLock); /* close rel, but keep lock! */
> + heap_close(class_rel, NoLock); /* close rel, but keep lock! */
> + }
> =20=20
>   /*
>    * ALTER TABLE DROP COLUMN
> Index: src/backend/parser/gram.y
>
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
>
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> RCS file: /projects/cvsroot/pgsql-server/src/backend/parser/gram.y,v
> retrieving revision 2.383
> diff -c -r2.383 gram.y
> *** src/backend/parser/gram.y 2002/11/25 21:29:40 2.383
> --- src/backend/parser/gram.y 2002/12/08 14:38:01
> ***************
> *** 313,318 ****
> --- 313,319 ----
> =20=20
>   %type <list> constraints_set_list
>   %type <boolean> constraints_set_mode
> + %type <chr> alter_with_without
> =20=20
> =20=20
>   /*
> ***************
> *** 1142,1148 ****
>   | ALTER TABLE relation_expr ALTER opt_column ColId SET NOT NULL_P
>   {
>   AlterTableStmt *n =3D makeNode(AlterTableStmt);
> ! n->subtype =3D 'O';
>   n->relation =3D $3;
>   n->name =3D $6;
>   $$ =3D (Node *)n;
> --- 1143,1149 ----
>   | ALTER TABLE relation_expr ALTER opt_column ColId SET NOT NULL_P
>   {
>   AlterTableStmt *n =3D makeNode(AlterTableStmt);
> ! n->subtype =3D 'n';
>   n->relation =3D $3;
>   n->name =3D $6;
>   $$ =3D (Node *)n;
> ***************
> *** 1197,1202 ****
> --- 1198,1211 ----
>   n->behavior =3D $7;
>   $$ =3D (Node *)n;
>   }
> + /* ALTER TABLE <relation> SET {WITH|WITHOUT} OIDS  */
> + | ALTER TABLE relation_expr SET alter_with_without OIDS
> + {
> + AlterTableStmt *n =3D makeNode(AlterTableStmt);
> + n->relation =3D $3;
> + n->subtype =3D $5;
> + $$ =3D (Node *)n;
> + }
>   /* ALTER TABLE <name> CREATE TOAST TABLE */
>   | ALTER TABLE qualified_name CREATE TOAST TABLE
>   {
> ***************
> *** 1217,1222 ****
> --- 1226,1236 ----
>   $$ =3D (Node *)n;
>   }
>   ;
> +=20
> + alter_with_without:
> + WITH { $$ =3D 'O'; }
> + | WITHOUT { $$ =3D 'o'; }
> + ;
> =20=20
>   alter_column_default:
>   SET DEFAULT a_expr
> Index: src/backend/tcop/utility.c
>
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
>
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> RCS file: /projects/cvsroot/pgsql-server/src/backend/tcop/utility.c,v
> retrieving revision 1.182
> diff -c -r1.182 utility.c
> *** src/backend/tcop/utility.c 2002/11/15 03:09:38 1.182
> --- src/backend/tcop/utility.c 2002/12/08 14:38:11
> ***************
> *** 491,497 ****
>     interpretInhOption(stmt->relation->inhOpt),
>   stmt->name);
>   break;
> ! case 'O': /* ALTER COLUMN SET NOT NULL */
>   AlterTableAlterColumnSetNotNull(relid,
>     interpretInhOption(stmt->relation->inhOpt),
>   stmt->name);
> --- 491,497 ----
>     interpretInhOption(stmt->relation->inhOpt),
>   stmt->name);
>   break;
> ! case 'n': /* ALTER COLUMN SET NOT NULL */
>   AlterTableAlterColumnSetNotNull(relid,
>     interpretInhOption(stmt->relation->inhOpt),
>   stmt->name);
> ***************
> *** 552,557 ****
> --- 552,567 ----
>   /* get_usesysid raises an error if no such user */
>   AlterTableOwner(relid,
>   get_usesysid(stmt->name));
> + break;
> + case 'O': /* ADD OIDS */
> + AlterTableAlterOids(relid,
> + interpretInhOption(stmt->relation->inhOpt),
> + TRUE);
> + break;
> + case 'o': /* DROP OIDS */
> + AlterTableAlterOids(relid,
> + interpretInhOption(stmt->relation->inhOpt),
> + FALSE);
>   break;
>   default: /* oops */
>   elog(ERROR, "T_AlterTableStmt: unknown subtype");
> Index: src/include/catalog/heap.h
>
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
>
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> RCS file: /projects/cvsroot/pgsql-server/src/include/catalog/heap.h,v
> retrieving revision 1.59
> diff -c -r1.59 heap.h
> *** src/include/catalog/heap.h 2002/11/11 22:19:23 1.59
> --- src/include/catalog/heap.h 2002/12/08 14:38:27
> ***************
> *** 52,57 ****
> --- 52,59 ----
>     List *rawColDefaults,
>     List *rawConstraints);
> =20=20
> + extern void AddNewOidAttributeTuple(Oid new_rel_oid);
> +=20
>   extern Node *cookDefault(ParseState *pstate,
>   Node *raw_default,
>   Oid atttypid,
> Index: src/include/commands/tablecmds.h
>
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
>
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> RCS file:
/projects/cvsroot/pgsql-server/src/include/commands/tablecmds.h,v
> retrieving revision 1.10
> diff -c -r1.10 tablecmds.h
> *** src/include/commands/tablecmds.h 2002/11/11 22:19:24 1.10
> --- src/include/commands/tablecmds.h 2002/12/08 14:38:34
> ***************
> *** 47,52 ****
> --- 47,54 ----
> =20=20
>   extern void AlterTableOwner(Oid relationOid, int32 newOwnerSysId);
> =20=20
> + extern void AlterTableAlterOids(Oid myrelid, bool recurse, bool setOid);
> +=20
>   extern Oid DefineRelation(CreateStmt *stmt, char relkind);
> =20=20
>   extern void RemoveRelation(const RangeVar *relation, DropBehavior
behavio=
> r);
> Index: src/include/nodes/parsenodes.h
>
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
>
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> RCS file: /projects/cvsroot/pgsql-server/src/include/nodes/parsenodes.h,v
> retrieving revision 1.218
> diff -c -r1.218 parsenodes.h
> *** src/include/nodes/parsenodes.h 2002/11/25 03:36:50 1.218
> --- src/include/nodes/parsenodes.h 2002/12/08 14:38:50
> ***************
> *** 808,814 ****
>   * A =3D add column
>   * T =3D alter column default
>   * N =3D alter column drop not null
> ! * O =3D alter column set not null
>   * S =3D alter column statistics
>   * M =3D alter column storage
>   * D =3D drop column
> --- 808,814 ----
>   * A =3D add column
>   * T =3D alter column default
>   * N =3D alter column drop not null
> ! * n =3D alter column set not null
>   * S =3D alter column statistics
>   * M =3D alter column storage
>   * D =3D drop column
> ***************
> *** 818,823 ****
> --- 818,825 ----
>   * X =3D drop constraint
>   * E =3D create toast table
>   * U =3D change owner
> + *  O =3D ADD OIDS
> + *  o =3D DROP OIDS
>   *------------
>   */
>   RangeVar   *relation; /* table to work on */
> Index: src/test/regress/expected/alter_table.out
>
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
>
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> RCS file:
/projects/cvsroot/pgsql-server/src/test/regress/expected/alter_ta=
> ble.out,v
> retrieving revision 1.53
> diff -c -r1.53 alter_table.out
> *** src/test/regress/expected/alter_table.out 2002/10/19 01:35:43 1.53
> --- src/test/regress/expected/alter_table.out 2002/12/08 14:39:22
> ***************
> *** 1166,1168 ****
> --- 1166,1260 ----
>   drop table p1, p2 cascade;
>   NOTICE:  Drop cascades to table c1
>   NOTICE:  Drop cascades to table gc1
> + --
> + -- Test the ALTER TABLE {WITH|WITHOUT} OIDS commands
> + --
> + create table altstartwith (col integer) with oids;
> + insert into altstartwith values (1);
> + select oid > 0, * from altstartwith;
> +  ?column? | col=20
> + ----------+-----
> +  t        |   1
> + (1 row)
> +=20
> + alter table altstartwith set with oids; -- fails
> + ERROR:  ALTER TABLE: Table is already WITH OIDS
> + alter table altstartwith set without oids;
> + select oid > 0, * from altstartwith; -- fails
> + ERROR:  Attribute "oid" not found
> + select * from altstartwith;
> +  col=20
> + -----
> +    1
> + (1 row)
> +=20
> + insert into altstartwith values (2);
> + alter table altstartwith set without oids; -- fails
> + ERROR:  ALTER TABLE: Table is already WITHOUT OIDS
> + alter table altstartwith set with oids;
> + insert into altstartwith values (3);
> + select oid > 0, * from altstartwith;
> +  ?column? | col=20
> + ----------+-----
> +  t        |   1
> +  f        |   2
> +  t        |   3
> + (3 rows)
> +=20
> + -- Run inheritance tests
> + create table altwithoid (col integer) with oids;
> + -- Inherits parents oid column
> + create table altinhoid () inherits (altwithoid) without oids;
> + insert into altinhoid values (1);
> + select oid > 0, * from altwithoid;
> +  ?column? | col=20
> + ----------+-----
> +  t        |   1
> + (1 row)
> +=20
> + select oid > 0, * from altinhoid;
> +  ?column? | col=20
> + ----------+-----
> +  t        |   1
> + (1 row)
> +=20
> + alter table altwithoid set without oids;
> + alter table altinhoid set without oids; -- fails
> + ERROR:  ALTER TABLE: Table is already WITHOUT OIDS
> + select oid > 0, * from altwithoid; -- fails
> + ERROR:  Attribute "oid" not found
> + select oid > 0, * from altinhoid; -- fails
> + ERROR:  Attribute "oid" not found
> + select * from altwithoid;
> +  col=20
> + -----
> +    1
> + (1 row)
> +=20
> + select * from altinhoid;
> +  col=20
> + -----
> +    1
> + (1 row)
> +=20
> + alter table only altwithoid set with oids;
> + insert into altwithoid values (2);
> + insert into altinhoid values (3);
> + select oid > 0, * from altwithoid;
> +  ?column? | col=20
> + ----------+-----
> +  t        |   2
> +  t        |   1
> +  f        |   3
> + (3 rows)
> +=20
> + select oid > 0, * from altinhoid; -- fails
> + ERROR:  Attribute "oid" not found
> + select * from altinhoid; -- fails
> +  col=20
> + -----
> +    1
> +    3
> + (2 rows)
> +=20
> + drop table altstartwith cascade;
> Index: src/test/regress/sql/alter_table.sql
>
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
>
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> RCS file:
/projects/cvsroot/pgsql-server/src/test/regress/sql/alter_table.s=
> ql,v
> retrieving revision 1.34
> diff -c -r1.34 alter_table.sql
> *** src/test/regress/sql/alter_table.sql 2002/10/19 01:35:43 1.34
> --- src/test/regress/sql/alter_table.sql 2002/12/08 14:39:38
> ***************
> *** 845,847 ****
> --- 845,900 ----
>   order by relname, attnum;
> =20=20
>   drop table p1, p2 cascade;
> +=20
> + --
> + -- Test the ALTER TABLE {WITH|WITHOUT} OIDS commands
> + --
> + create table altstartwith (col integer) with oids;
> +=20
> + insert into altstartwith values (1);
> +=20
> + select oid > 0, * from altstartwith;
> +=20
> + alter table altstartwith set with oids; -- fails
> + alter table altstartwith set without oids;
> +=20
> + select oid > 0, * from altstartwith; -- fails
> + select * from altstartwith;
> +=20
> + insert into altstartwith values (2);
> +=20
> + alter table altstartwith set without oids; -- fails
> + alter table altstartwith set with oids;
> +=20
> + insert into altstartwith values (3);
> + select oid > 0, * from altstartwith;
> +=20
> + -- Run inheritance tests
> + create table altwithoid (col integer) with oids;
> +=20
> + -- Inherits parents oid column
> + create table altinhoid () inherits (altwithoid) without oids;
> +=20
> + insert into altinhoid values (1);
> +=20
> + select oid > 0, * from altwithoid;
> + select oid > 0, * from altinhoid;
> +=20
> + alter table altwithoid set without oids;
> + alter table altinhoid set without oids; -- fails
> +=20
> + select oid > 0, * from altwithoid; -- fails
> + select oid > 0, * from altinhoid; -- fails
> + select * from altwithoid;
> + select * from altinhoid;
> +=20
> + alter table only altwithoid set with oids;
> +=20
> + insert into altwithoid values (2);
> + insert into altinhoid values (3);
> +=20
> + select oid > 0, * from altwithoid;
> + select oid > 0, * from altinhoid; -- fails
> + select * from altinhoid; -- fails
> +=20
> + drop table altstartwith cascade;
>
> --=-kOit5t7LMlG8gYDF7SuD--
>
> --=-39x4Ba5NCnmHYLSHQVel
> Content-Type: application/pgp-signature; name=signature.asc
> Content-Description: This is a digitally signed message part
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.2.1 (FreeBSD)
>
> iD8DBQA981zG6DETLow6vwwRAjDbAJ4i8md9fPkCwonRgACWcMdwjc4OEACfQyyD
> oIG7jckmkAoko9952/B2ZjU=
> =fzuK
> -----END PGP SIGNATURE-----
>
> --=-39x4Ba5NCnmHYLSHQVel--
>