Re: ALTER TABLE .. SET WITH / WITHOUT OIDS - Mailing list pgsql-patches
| From | Fabio Furia Silva |
|---|---|
| Subject | Re: ALTER TABLE .. SET WITH / WITHOUT OIDS |
| Date | |
| Msg-id | b5q0ah$2qa2$1@news.hub.org Whole thread Raw |
| In response to | ALTER TABLE .. SET WITH / WITHOUT OIDS (Rod Taylor <rbt@rbt.ca>) |
| Responses |
Re: ALTER TABLE .. SET WITH / WITHOUT OIDS
(Rod Taylor <rbt@rbt.ca>)
|
| 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-- >
pgsql-patches by date: