Re: adding a nullable col could rewrite the whole table?

Hi all,

in the docs I read:

"Adding a column with a non-null default [...] will require the entire table to be rewritten"

I guess that that can't be read as "you can always assume that adding a nullable column to a table won't rewrite the whole table".

I'm asking because since the page layout includes a null bitmap that
"If it is present it begins just after the fixed header and occupies enough bytes to have one bit per data column", I guess that when all the bits in the bitmap are used when I add a column (even a nullable one) the table gets rewritten (to add a new byte to the nullable bitmap); is this true?

On Fri, Aug 14, 2009 at 12:37:14PM +0000, Scara Maccai wrote:
> I'm asking because since the page layout includes a null bitmap that
> "If it is present it begins just after the fixed header and occupies
> enough bytes to have one bit per data column", I guess that when all
> the bits in the bitmap are used when I add a column (even a nullable
> one) the table gets rewritten (to add a new byte to the nullable
> bitmap); is this true?

Sort of; there is indeed a null bitmap saying which columns are null.
But there's also a number saying how many columns there are and hence
each row knows if a new column is going to be off the end and means it
doesn't need to rewrite the table when adding a new NULLable column.

I made a suggestion about how to generalize this to non-NULLable columns
with a default value, but haven't written any code to make it do this
yet.

--
Sam http://samason.me.uk/

> are null.
> But there's also a number saying how many columns there are

Uh, thank you.

Looking for t_natts I think I've found the discussion:

http://archives.free.net.ph/message/20090521.144512.3ffe4801.en.html

Thank you again.