Recover droped column from PostgreSQL

digoal

原文地址:
http://blog.163.com/digoal@126/b ... 040201112251058216/

PostgreSQL中使用alter table tbl drop column 删除列的操作，实际上只是标记列为删除状态。并不是里面就回收该列占用的空间。
只有当对原来存在的行发生UPDATE时或新插入行的时候才会影响真实的列记录。

恢复测试一： 对于执行alter table tbl drop column后没有发生UPDATE的操作的表，恢复比较简单如下：
首先当然要记录下原来的表结构。
digoal=> \d tbl_user
            Table "digoal.tbl_user"
  Column   |         Type          | Modifiers
-----------+-----------------------+-----------
id        | integer               | not null
firstname | character varying(32) |
lastname  | character varying(32) |
corp      | character varying(32) |
age       | integer               |
Indexes:
    "tbl_user_pkey" PRIMARY KEY, btree (id)
    "idx_user_age" btree (age)

digoal=> select count(*) from tbl_user;
count
-------
     0
(1 row)
插入测试记录
digoal=> insert into tbl_user select generate_series(1,1000),'zhou','digoal','sky-mobi',27;
INSERT 0 1000
删除列
digoal=> alter table tbl_user drop column corp;
ALTER TABLE
准备修复列
digoal=> select oid from pg_class where relname='tbl_user';
   oid  
---------
2016624
(1 row)
查看得出被删除的列只是把attisdropped ,atttypid ,attname 的值改掉了。
digoal=> select * from pg_attribute where attrelid=2016624;
attrelid |           attname            | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff | atttypmod | attbyva
l | attstorage | attalign | attnotnull | atthasdef | attisdropped | attislocal | attinhcount | attacl |               attoptions   
         
----------+------------------------------+----------+---------------+--------+--------+----------+-------------+-----------+--------
--+------------+----------+------------+-----------+--------------+------------+-------------+--------+-----------------------------
-----------
  2016624 | tableoid                     |       26 |             0 |      4 |     -7 |        0 |          -1 |        -1 | t     
  | p          | i        | t          | f         | f            | t          |           0 |        |
  2016624 | cmax                         |       29 |             0 |      4 |     -6 |        0 |          -1 |        -1 | t     
  | p          | i        | t          | f         | f            | t          |           0 |        |
  2016624 | xmax                         |       28 |             0 |      4 |     -5 |        0 |          -1 |        -1 | t     
  | p          | i        | t          | f         | f            | t          |           0 |        |
  2016624 | cmin                         |       29 |             0 |      4 |     -4 |        0 |          -1 |        -1 | t     
  | p          | i        | t          | f         | f            | t          |           0 |        |
  2016624 | xmin                         |       28 |             0 |      4 |     -3 |        0 |          -1 |        -1 | t     
  | p          | i        | t          | f         | f            | t          |           0 |        |
  2016624 | ctid                         |       27 |             0 |      6 |     -1 |        0 |          -1 |        -1 | f     
  | p          | s        | t          | f         | f            | t          |           0 |        |
  2016624 | id                           |       23 |            -1 |      4 |      1 |        0 |          -1 |        -1 | t     
  | p          | i        | t          | f         | f            | t          |           0 |        |
  2016624 | firstname                    |     1043 |            -1 |     -1 |      2 |        0 |          -1 |        36 | f     
  | x          | i        | f          | f         | f            | t          |           0 |        | {n_distinct=2,n_distinct_inh
erited=10}
  2016624 | lastname                     |     1043 |            -1 |     -1 |      3 |        0 |          -1 |        36 | f     
  | x          | i        | f          | f         | f            | t          |           0 |        |
  2016624 | ........pg.dropped.4........ |        0 |             0 |     -1 |      4 |        0 |          -1 |        36 | f     
  | x          | i        | f          | f         | t            | t          |           0 |        |
  2016624 | age                          |       23 |            -1 |      4 |      5 |        0 |          -1 |        -1 | t     
  | p          | i        | f          | f         | f            | t          |           0 |        |
(11 rows)
digoal=# insert into digoal.tbl_user values (1001,'zhou','digoal',27);
INSERT 0 1
插入一条测试记录，并且执行以下SQL恢复删除列。
digoal=# update pg_attribute set attisdropped='f',atttypid=1043,attname='corp' where attrelid=2016624 and attnum=4;
UPDATE 1
查看列删除后插入的记录，CORP为空。
digoal=# select * from digoal.tbl_user where id=1001 ;
  id  | firstname | lastname | corp | age
------+-----------+----------+------+-----
1001 | zhou      | digoal   |      |  27
(1 row)
查看列删除前插入的记录CORP已经恢复。
digoal=# select * from digoal.tbl_user where id=1000 ;
  id  | firstname | lastname |   corp   | age
------+-----------+----------+----------+-----
1000 | zhou      | digoal   | sky-mobi |  27
(1 row)

恢复测试二：
对于执行 alter table tbl drop column 后发生了UPDATE的表。
digoal=# alter table digoal.tbl_user drop column corp;
ALTER TABLE
digoal=# update digoal.tbl_user set firstname='abc' where id=1000;
UPDATE 1
digoal=# update pg_attribute set attisdropped='f',atttypid=1043,attname='corp' where attrelid=2016624 and attnum=4;
UPDATE 1
digoal=# select * from digoal.tbl_user where id=1000;
  id  | firstname | lastname | corp | age
------+-----------+----------+------+-----
1000 | abc       | digoal   |      |  27
(1 row)
列删除后被UPDATE的行,CORP的记录被抹掉。