Thread: COLUMN MODIFY

Hey guys,

I was just thinking about altering column type.  Now, I'm not actually going
to implement it any time soon, but I'm just thinking about it!!!

One proposal was to introduce a new pg_attribute column called 'attlognum'
so changing a column would involve adding a new column, dropping the old one
and nudging the attlognum so that the columns are still select *'d in the
same order.

That involves catalog changes, etc.

My idea is why not do what cluster does?  Can we just simply write an entire
new relation with the new type, update relfilenode and drop the old
relation?

ISTM that that would prevent catalog changes and would occupy identical disk
space (2 x table size) during the ALTER, but would automatically 'free'
itself back down to 1 x table size.  Otherwise, the user has to do a vacuum
full.

Actually, if the type is binary compatible with the old type, all you need
to update is the catalog.

The existing DROP COLUMN implementation could even be changed to work like
that, so long as we just leave the attisdropped column always false.

What do you think?

Chris

Sorry to be commenting so late.

We could do that CLUSTER way of making a new heap file, but we rejected
that for DROP COLUMN, so I am not sure why we would use that for ALTER
COLUMN.  Can anyone think of a good reason?

Clearly if the new data type is binary compatible and it is just a
catalog change, we can do that in place.

---------------------------------------------------------------------------

Christopher Kings-Lynne wrote:
> Hey guys,
> 
> I was just thinking about altering column type.  Now, I'm not actually going
> to implement it any time soon, but I'm just thinking about it!!!
> 
> One proposal was to introduce a new pg_attribute column called 'attlognum'
> so changing a column would involve adding a new column, dropping the old one
> and nudging the attlognum so that the columns are still select *'d in the
> same order.
> 
> That involves catalog changes, etc.
> 
> My idea is why not do what cluster does?  Can we just simply write an entire
> new relation with the new type, update relfilenode and drop the old
> relation?
> 
> ISTM that that would prevent catalog changes and would occupy identical disk
> space (2 x table size) during the ALTER, but would automatically 'free'
> itself back down to 1 x table size.  Otherwise, the user has to do a vacuum
> full.
> 
> Actually, if the type is binary compatible with the old type, all you need
> to update is the catalog.
> 
> The existing DROP COLUMN implementation could even be changed to work like
> that, so long as we just leave the attisdropped column always false.
> 
> What do you think?
> 
> Chris
> 
> 
> ---------------------------(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,
Pennsylvania19073
 

Bruce Momjian wrote:
> 
> Sorry to be commenting so late.
> 
> We could do that CLUSTER way of making a new heap file, but we rejected
> that for DROP COLUMN, so I am not sure why we would use that for ALTER
> COLUMN.  Can anyone think of a good reason?
> 
> Clearly if the new data type is binary compatible and it is just a
> catalog change, we can do that in place.

...with the caveat that any indexes that refer to the column must be
rebuilt, since the value interpretation could be quite different.

Guess that's obvious enough, though...

                      Kevin, a.k.a. "Mr. Obvious"  :-)


-- 
Kevin Brown                          kevin@sysexperts.com