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

It's a 7.4 features

In theory you should only need to change the value in pg_class that
dictates whether OIDS are on or off (I'll let you find it), then:

UPDATE table SET column = column;
VACUUM FULL table;

That said, heavy testing is suggested.

On Tue, 2003-03-25 at 11:31, Fabio Furia Silva wrote:
> 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--
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc