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: | The Hermit Hacker <scrappy(at)hub(dot)org>, Hiroshi Inoue <Inoue(at)tpf(dot)co(dot)jp>, PostgreSQL Development <pgsql-hackers(at)postgreSQL(dot)org> |
Subject: | Re: ALTER TABLE DROP COLUMN |
Date: | 2000-10-09 17:40:30 |
Message-ID: | 200010091740.NAA11706@candle.pha.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> >> Basically, move the first 100 rows to the end of the table file, then take
> >> 100 and write it to position 0, 101 to position 1, etc ... that way, at
> >> max, you are using ( tuple * 100 ) bytes of disk space, vs 2x the table
> >> size ... either method is going to lock the file for a period of time, but
> >> one is much more friendly as far as disk space is concerned *plus*, if RAM
> >> is available for this, it might even be something that the backend could
> >> use up to -S blocks of RAM to do it off disk? If I set -S to 64meg, and
> >> the table is 24Meg in size, it could do it all in memory?
>
> > Yes, I liked that too.
>
> What happens if you crash partway through?
>
> I don't think it's possible to build a crash-robust rewriting ALTER
> process that doesn't use 2X disk space: you must have all the old tuples
> AND all the new tuples down on disk simultaneously just before you
> commit. The only way around 2X disk space is to adopt some logical
> renumbering approach to the columns, so that you can pretend the dropped
> column isn't there anymore when it really still is.
Yes, I liked the 2X disk space, and making the new tuples visible all at
once at the end.
--
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
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2000-10-09 18:21:03 | Re: [GENERAL] Using BLOBs with PostgreSQL |
Previous Message | Tom Lane | 2000-10-09 17:37:28 | Re: ALTER TABLE DROP COLUMN |