ALTER TABLE rewrites

alter一个表的列的时候会出现rewrites表：

比如添加一个有默认值的新列会rewrites重写表（即先vacuum full此表）： http://my.oschina.net/Kenyon/blog/99757

ctid来查看有默认值的新列：

digoal=# select ctid,* from foo;                  
 ctid  | fooid | foosubid | fooname | col1 | col2 
-------+-------+----------+---------+------+------
 (0,1) |     1 |        2 | three   |      |   90
 (0,2) |     4 |        5 | six     |      |   90
 (0,3) |     5 |        5 | two     |      |   90
 (0,4) |     6 |        6 | rrr     |      |   90
 (0,5) |     7 |        7 | rrr     |      |   90
(5 rows)
digoal=# update foo set fooname='vvvvv' where fooid=6;
UPDATE 1
digoal=# select ctid,* from foo;                      
 ctid  | fooid | foosubid | fooname | col1 | col2 
-------+-------+----------+---------+------+------
 (0,1) |     1 |        2 | three   |      |   90
 (0,2) |     4 |        5 | six     |      |   90
 (0,3) |     5 |        5 | two     |      |   90
 (0,5) |     7 |        7 | rrr     |      |   90
 (0,6) |     6 |        6 | vvvvv   |      |   90
(5 rows)

digoal=#  alter table foo add col3 int default 22;     
ALTER TABLE
digoal=# select ctid,* from foo;                  
 ctid  | fooid | foosubid | fooname | col1 | col2 | col3 
-------+-------+----------+---------+------+------+------
 (0,1) |     1 |        2 | three   |      |   90 |   22
 (0,2) |     4 |        5 | six     |      |   90 |   22
 (0,3) |     5 |        5 | two     |      |   90 |   22
 (0,4) |     7 |        7 | rrr     |      |   90 |   22
 (0,5) |     6 |        6 | vvvvv   |      |   90 |   22
(5 rows)

ctid来查看无默认值的新列：

digoal=# update foo set fooname='vvvvv' where fooid=6;
UPDATE 1
digoal=#  select ctid,* from foo;
 ctid  | fooid | foosubid | fooname | col1 | col2 | col3 | col4 
-------+-------+----------+---------+------+------+------+------
 (0,1) |     1 |        2 | three   |      |   90 |   22 |  202
 (0,2) |     4 |        5 | six     |      |   90 |   22 |  202
 (0,3) |     5 |        5 | two     |      |   90 |   22 |  202
 (0,4) |     7 |        7 | rrr     |      |   90 |   22 |  202
 (0,6) |     6 |        6 | vvvvv   |      |   90 |   22 |  202
(5 rows)

digoal=# alter table foo add col5 int;
ALTER TABLE
digoal=#  select ctid,* from foo;     
 ctid  | fooid | foosubid | fooname | col1 | col2 | col3 | col4 | col5 
-------+-------+----------+---------+------+------+------+------+------
 (0,1) |     1 |        2 | three   |      |   90 |   22 |  202 |     
 (0,2) |     4 |        5 | six     |      |   90 |   22 |  202 |     
 (0,3) |     5 |        5 | two     |      |   90 |   22 |  202 |     
 (0,4) |     7 |        7 | rrr     |      |   90 |   22 |  202 |     
 (0,6) |     6 |        6 | vvvvv   |      |   90 |   22 |  202 |     
(5 rows)

避免rewrites 重写的原则如下： http://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.2

A table won't get rewritten anymore during an ALTER TABLE when changing the type of a column in the following cases:

• varchar(x) to varchar(y) when y>=x. It works too if going from varchar(x) to varchar or text (no size limitation)
• numeric(x,z) to numeric(y,z) when y>=x, or to numeric without specifier
• varbit(x) to varbit(y) when y>=x, or to varbit without specifier
• timestamp(x) to timestamp(y) when y>=x or timestamp without specifier
• timestamptz(x) to timestamptz(y) when y>=x or timestamptz without specifier
• interval(x) to interval(y) when y>=x or interval without specifier