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