Обсуждение: ALTER Table and CLUSTER does adding a new column rewrite clustered? (8.4.3)

v. 8.4.3

I have a table that has several indexes, one of which the table is clustered on.  If I do an ALTER TABLE Foo ADD COLUMN
barinteger not null default -1; 

It re-writes the whole table.

* Does it adhere to the CLUSTER property of the table and write the new version clustered?
* Does it properly write it with the FILLFACTOR setting?
* Are all the indexes re-created too, or are they bloated and need a REINDEX?

http://www.postgresql.org/docs/8.4/static/sql-altertable.html
  does not seem to answer the above, it mentions the conditions that cause a rewrite but does not say what the state is
afterthe rewrite with respect to CLUSTER, FILLFACTOR, and index bloat. 

Thanks in advance!

Scott Carey wrote:
> v. 8.4.3
>
> I have a table that has several indexes, one of which the table is
> clustered on.  If I do an ALTER TABLE Foo ADD COLUMN bar integer not
> null default -1;
>
> It re-writes the whole table.

All good questions:

> * Does it adhere to the CLUSTER property of the table and write the new
> version clustered?

The new table is the exact same heap ordering as the old table;  it does
not refresh the clustering if the table has become unclustered.

> * Does it properly write it with the FILLFACTOR setting?

Yes, inserts are used to populate the new table, and inserts honor
FILLFACTOR.

> * Are all the indexes re-created too, or are they bloated and need a REINDEX?

They are recreated.

> http://www.postgresql.org/docs/8.4/static/sql-altertable.html
>   does not seem to answer the above, it mentions the conditions that
> cause a rewrite but does not say what the state is after the rewrite
> with respect to CLUSTER, FILLFACTOR, and index bloat.

I have added a documentation patch to mention the indexes are rebuilt;
applied patch attached.

The gory details can be found in src/backend/commands/tablecmds.c.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + None of us is going to be here forever. +
Index: doc/src/sgml/ref/alter_table.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/alter_table.sgml,v
retrieving revision 1.114
diff -c -c -r1.114 alter_table.sgml
*** doc/src/sgml/ref/alter_table.sgml    9 Jun 2010 17:48:10 -0000    1.114
--- doc/src/sgml/ref/alter_table.sgml    24 Jun 2010 14:54:00 -0000
***************
*** 689,696 ****

     <para>
      Adding a column with a non-null default or changing the type of an
!     existing column will require the entire table to be rewritten.  This
!     might take a significant amount of time for a large table; and it will
      temporarily require double the disk space.  Adding or removing a system
      <literal>oid</> column likewise requires rewriting the entire table.
     </para>
--- 689,696 ----

     <para>
      Adding a column with a non-null default or changing the type of an
!     existing column will require the entire table and indexes to be rewritten.
!     This might take a significant amount of time for a large table; and it will
      temporarily require double the disk space.  Adding or removing a system
      <literal>oid</> column likewise requires rewriting the entire table.
     </para>