Thread: ALTER TABLE .. < ADD | DROP > OIDS
I wish to create an alter command which will allow a table to have OIDs added or removed. The tricky part appears to be changing the tuples themselves. I believe if I pull the same trick that cluster does (create new file, copy tuples, etc) it can be done fairly easily. First, set up pg_class appropriately (oid flag). Second, copy out tuples from oldfile to newfile, running a heap_deformtuple() -> heap_formtuple() process on each. Since heap_deformtuple only deals with positive numbered attributes (non-system attributes) this should be safe to do on a mis-configured relation. heap_formtuple completes the dirty work of setting up the OID column appropriately. -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
Attachment
OK, patch applied and tested. --------------------------------------------------------------------------- Rod Taylor wrote: -- Start of PGP signed section. > I wish to create an alter command which will allow a table to have OIDs > added or removed. > > > The tricky part appears to be changing the tuples themselves. I believe > if I pull the same trick that cluster does (create new file, copy > tuples, etc) it can be done fairly easily. > > First, set up pg_class appropriately (oid flag). > > Second, copy out tuples from oldfile to newfile, running a > heap_deformtuple() -> heap_formtuple() process on each. Since > heap_deformtuple only deals with positive numbered attributes > (non-system attributes) this should be safe to do on a mis-configured > relation. heap_formtuple completes the dirty work of setting up the OID > column appropriately. > > -- > Rod Taylor <rbt@rbt.ca> > > PGP Key: http://www.rbt.ca/rbtpub.asc -- 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
Bruce Momjian wrote: > > OK, patch applied and tested. Sorry, wrong email. I meant to say that his previous ALTER DOMAIN patch had been applied with the new file now supplied. > > --------------------------------------------------------------------------- > > Rod Taylor wrote: > -- Start of PGP signed section. > > I wish to create an alter command which will allow a table to have OIDs > > added or removed. > > > > > > The tricky part appears to be changing the tuples themselves. I believe > > if I pull the same trick that cluster does (create new file, copy > > tuples, etc) it can be done fairly easily. > > > > First, set up pg_class appropriately (oid flag). > > > > Second, copy out tuples from oldfile to newfile, running a > > heap_deformtuple() -> heap_formtuple() process on each. Since > > heap_deformtuple only deals with positive numbered attributes > > (non-system attributes) this should be safe to do on a mis-configured > > relation. heap_formtuple completes the dirty work of setting up the OID > > column appropriately. > > > > -- > > Rod Taylor <rbt@rbt.ca> > > > > PGP Key: http://www.rbt.ca/rbtpub.asc > -- 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- 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:
> I wish to create an alter command which will allow a table to have OIDs
> added or removed.
> The tricky part appears to be changing the tuples themselves.
Are you sure you need to? Methinks the lazy approach of letting them
auto-adjust on next UPDATE should work as well for OIDs as for user
columns.
There might be a few places that look at the pg_class.relhasoids
field where they should be examining the tuple header has-oid bit,
but I don't think there are many.
regards, tom lane
On Fri, 2002-12-06 at 15:19, Tom Lane wrote: > Rod Taylor <rbt@rbt.ca> writes: > > I wish to create an alter command which will allow a table to have OIDs > > added or removed. > > > The tricky part appears to be changing the tuples themselves. > There might be a few places that look at the pg_class.relhasoids > field where they should be examining the tuple header has-oid bit, > but I don't think there are many. Ok.. If you think thats safe, I'll give it a try. I was afraid that the system would confuse itself if the table had mix and matched tuples in it. New tuples without oids, old tuples with. That helps DROP OID. How about ADD OID? -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
Attachment
Rod Taylor <rbt@rbt.ca> writes:
> Ok.. If you think thats safe, I'll give it a try. I was afraid that the
> system would confuse itself if the table had mix and matched tuples in
> it. New tuples without oids, old tuples with.
Manfred's original implementation would have failed (since it didn't
have a tuple-header hasoid bit). I think I got all the places that
should consult the header bit, but there may be some left; you'll need
to test.
> That helps DROP OID. How about ADD OID?
What about it? I think it'll work just like adding a column, except
that OID will probably read as 0 not NULL if the row hasn't been updated
yet. (You could probably make it read as NULL if you wanted though.)
regards, tom lane
> > That helps DROP OID. How about ADD OID? > > What about it? I think it'll work just like adding a column, except > that OID will probably read as 0 not NULL if the row hasn't been updated > yet. (You could probably make it read as NULL if you wanted though.) Good point. I forgot new columns were empty by default. -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc