postgresql表中间加列_PostgreSQL修改列与重写表

postgres=# \d tab_kenyon

Table "public.tab_kenyon"

Column | Type | Modifiers

--------+-----------------------+-----------

id | integer |

name | character varying(35) |

remark | text | 验证过程如下：

postgres=# vacuum full tab_kenyon;

VACUUM

postgres=# select ctid,* from tab_kenyon ;

ctid | id | name | remark

-------+----+--------------+--------

(0,1) | 1 | kenyon |

(0,2) | 2 | china |

(0,3) | 5 | oopoo |

(0,4) | 45 | xxooxx |

(0,5) | 4 | i love china |

(5 rows)

postgres=# update tab_kenyon set name = 'kenyon_test' where id = 5;

UPDATE 1

postgres=# select ctid,* from tab_kenyon ;

ctid | id | name | remark

-------+----+--------------+--------

(0,1) | 1 | kenyon |

(0,2) | 2 | china |

(0,4) | 45 | xxooxx |

(0,5) | 4 | i love china |

(0,6) | 5 | kenyon_test |

(5 rows) 可以看到ctid已经没有了(0,3)，而是新增了(0,6)，这是新老版本混在一起的缘故，老版本的数据不可见，并将被清理。

postgres=# alter table tab_kenyon add tt varchar(2);

ALTER TABLE

postgres=# select ctid,* from tab_kenyon ;

ctid | id | name | remark | tt

-------+----+--------------+--------+----

(0,1) | 1 | kenyon | |

(0,2) | 2 | china | |

(0,4) | 45 | xxooxx | |

(0,5) | 4 | i love china | |

(0,6) | 5 | kenyon_test | |

(5 rows) 可以看到ctid并没有更改，也就是说没有重写表(rewrite table)。

换一个场景,新增带default值的列

postgres=# alter table tab_kenyon add yy varchar(2) default 'Y';

ALTER TABLE

postgres=# select ctid,* from tab_kenyon ;

ctid | id | name | remark | tt | yy

-------+----+--------------+--------+----+----

(0,1) | 1 | kenyon | | | Y

(0,2) | 2 | china | | | Y

(0,3) | 45 | xxooxx | | | Y

(0,4) | 4 | i love china | | | Y

(0,5) | 5 | kenyon_test | | | Y

(5 rows) 此时可以看到ctid的值有了变化了。

总结：

1.新增NULL列时只是更新字典信息表，而不会对基础表重写，系统只会保持非常短时间的锁

2.新增非NULL列时会更新基础表

3.修改表结构时是会阻塞表的读写的，可以用begin...end来模拟

4.PG的9.2版本更加减少了alter table的表重写，如下是场景

varchar(x) --> varchar(y) 当 y>=x. 或varchar(x) --> varchar or text (no size limitation)

numeric(x,z) --> numeric(y,z) 当 y>=x, or to numeric without specifier

varbit(x) --> varbit(y) when y>=x, or to varbit without specifier

timestamp(x) --> timestamp(y) when y>=x or timestamp without specifier

timestamptz(x) --> timestamptz(y) when y>=x or timestamptz without specifier

interval(x) --> interval(y) when y>=x or interval without specifier

参考：

http://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.2