Skip site navigation (1) Skip section navigation (2)

Re: ALTER TABLE DROP COLUMN

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Peter Eisentraut <peter_e(at)gmx(dot)net>, PostgreSQL Development <pgsql-hackers(at)postgreSQL(dot)org>
Subject: Re: ALTER TABLE DROP COLUMN
Date: 2000-09-30 02:32:00
Message-ID: 200009300232.WAA02847@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
OK, I am opening this can of worms again.  I personally would like to
see this code activated, even if it does take 2x the disk space to alter
a column.  Hiroshi had other ideas.  Where did we leave this?  We have
one month to decide on a plan.


> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > You can exclusively lock the table, then do a heap_getnext() scan over
> > the entire table, remove the dropped column, do a heap_insert(), then a
> > heap_delete() on the current tuple, making sure to skip over the tuples
> > inserted by the current transaction.  When completed, remove the column
> > from pg_attribute, mark the transaction as committed (if desired), and
> > run vacuum over the table to remove the deleted rows.
> 
> Hmm, that would work --- the new tuples commit at the same instant that
> the schema updates commit, so it should be correct.  You have the 2x
> disk usage problem, but there's no way around that without losing
> rollback ability.
> 
> A potentially tricky bit will be persuading the tuple-reading and tuple-
> writing subroutines to pay attention to different versions of the tuple
> structure for the same table.  I haven't looked to see if this will be
> difficult or not.  If you can pass the TupleDesc explicitly then it
> shouldn't be a problem.
> 
> I'd suggest that the cleanup vacuum *not* be an automatic part of
> the operation; just recommend that people do it ASAP after dropping
> a column.  Consider needing to drop several columns...
> 
> 			regards, tom lane
> 
> ************
> 


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman(at)candle(dot)pha(dot)pa(dot)us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2000-09-30 02:32:29
Subject: Re: grant/revoke bug with delete/update
Previous:From: Bruce MomjianDate: 2000-09-30 02:29:49
Subject: Re: pg_dump possible fix, need testers. (was: Re: pg_dump disaster)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group