Thread: ALTER TABLE .. SET WITH / WITHOUT OIDS
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. -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
Attachment
Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches
I will try to apply it within the next 48 hours.
---------------------------------------------------------------------------
Rod Taylor wrote:
-- Start of PGP signed section.
> 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.
>
> --
> Rod Taylor <rbt@rbt.ca>
>
> PGP Key: http://www.rbt.ca/rbtpub.asc
[ Attachment, skipping... ]
-- End of PGP section, PGP failed!
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Rod Taylor <rbt@rbt.ca> writes:
> Works somewhat like adding / dropping a column with the default of 0.
After looking at this patch I'm inclined to reject it. The doc patch
claims
Adds or removes the <literal>OID</literal> column from a table.
When enabling (setting with) OIDs in a table, the currently
existing tuples are not immediately numbered.
but the actual behavior is that the existing tuples are not numbered and
*never can become numbered* --- only freshly-inserted rows will acquire
OIDs. That puts a severe, probably fatal, limit on the usefulness of
SET WITH OIDS.
I can see some value in the SET WITHOUT OIDS half of the patch, as this
gives a way of reclaiming space in a table that was unthinkingly created
with OIDs. But I'm not sure we should expend the code space to provide
just a one-way transformation.
Or we could modify heap_update to assign an OID if it reads a zero OID
from the old tuple in a relation that is marked as having OIDs. Not
sure whether I like that behavior either, but at least it would provide
a mechanism for causing the old rows to acquire OIDs.
Comments?
regards, tom lane
> I can see some value in the SET WITHOUT OIDS half of the patch, as this > gives a way of reclaiming space in a table that was unthinkingly created > with OIDs. But I'm not sure we should expend the code space to provide > just a one-way transformation This is the part I'm after -- for those older tables created before OIDs could be removed. Editing dump files won't work. I can just muck with the system tables, but others may not be able to (for whatever reason). > Or we could modify heap_update to assign an OID if it reads a zero OID > from the old tuple in a relation that is marked as having OIDs. Not > sure whether I like that behavior either, but at least it would provide > a mechanism for causing the old rows to acquire OIDs. Thought about that, but didn't like it as it makes oid more special than it already is. Adding a default to a column works basically the same way. Another, and my original option, was to touch all tuples to assign new OIDS at addition time -- but that requires a lot of resources. -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
Attachment
On Thu, 2003-01-02 at 18:08, Tom Lane wrote: > I can see some value in the SET WITHOUT OIDS half of the patch, as this > gives a way of reclaiming space in a table that was unthinkingly created > with OIDs. But I'm not sure we should expend the code space to provide > just a one-way transformation. IMHO, it's still worth having this part of the patch, even if the transformation is only one-way. Cheers, Neil
I agree. I want to remove OIDs from heaps of our tables when we go to 7.3. I'd rather not have to do it in the dump due to down time. Chris > -----Original Message----- > From: pgsql-patches-owner@postgresql.org > [mailto:pgsql-patches-owner@postgresql.org]On Behalf Of Neil Conway > Sent: Thursday, 9 January 2003 10:58 AM > To: Tom Lane > Cc: Rod Taylor; PostgreSQL Patches > Subject: Re: [PATCHES] ALTER TABLE .. SET WITH / WITHOUT OIDS > > > On Thu, 2003-01-02 at 18:08, Tom Lane wrote: > > I can see some value in the SET WITHOUT OIDS half of the patch, as this > > gives a way of reclaiming space in a table that was unthinkingly created > > with OIDs. But I'm not sure we should expend the code space to provide > > just a one-way transformation. > > IMHO, it's still worth having this part of the patch, even if the > transformation is only one-way. > > Cheers, > > Neil > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
Code for WITHOUT OIDS. On Wed, 2003-01-08 at 21:59, Christopher Kings-Lynne wrote: > I agree. I want to remove OIDs from heaps of our tables when we go to 7.3. > I'd rather not have to do it in the dump due to down time. -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
Attachment
Rod Taylor writes: > Code for WITHOUT OIDS. Why not ALTER TABLE x DROP COLUMN oid? -- Peter Eisentraut peter_e@gmx.net
Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches
I will try to apply it within the next 48 hours.
---------------------------------------------------------------------------
Rod Taylor wrote:
-- Start of PGP signed section.
> Code for WITHOUT OIDS.
>
>
> On Wed, 2003-01-08 at 21:59, Christopher Kings-Lynne wrote:
> > I agree. I want to remove OIDs from heaps of our tables when we go to 7.3.
> > I'd rather not have to do it in the dump due to down time.
> --
> Rod Taylor <rbt@rbt.ca>
>
> PGP Key: http://www.rbt.ca/rbtpub.asc
[ Attachment, skipping... ]
-- End of PGP section, PGP failed!
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Patch applied. Thanks. --------------------------------------------------------------------------- Rod Taylor wrote: -- Start of PGP signed section. > Code for WITHOUT OIDS. > > > On Wed, 2003-01-08 at 21:59, Christopher Kings-Lynne wrote: > > I agree. I want to remove OIDs from heaps of our tables when we go to 7.3. > > I'd rather not have to do it in the dump due to down time. > -- > Rod Taylor <rbt@rbt.ca> > > PGP Key: http://www.rbt.ca/rbtpub.asc [ Attachment, skipping... ] -- End of PGP section, PGP failed! -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
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-- >
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