PG删除列后,只是做了一个标记,并没有真正回收空间
举例:
hank=> select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 9.5.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-16), 64-bit
hank=>\c hank hank
hank=> \d t1
Table "hank.t1"
Column | Type | Modifiers
--------+---------------+-----------
a | integer |
b | character(20) |
hank=> select * from t1;
a | b
----+----------------------
2 | wang
3 | li
20 | hank
20 | hank
20 | hank
hank=> select oid from pg_class where relname='t1';
oid
-------
74219
hank=> select * from pg_attribute where attrelid=74219 and attname='b';
attrelid | attname | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff | atttypmod | attbyval | attstorage | attalign | attnotnull
| atthasdef | attisdropped | attislocal | attinhcount | attcollation | attacl | attoptions | attfdwoptions
----------+---------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+------------
+-----------+--------------+------------+-------------+--------------+--------+------------+---------------
74219 | b | 1042 | -1 | -1 | 2 | 0 | -1 | 24 | f | x | i | f
| f | f | t | 0 | 100 | | |
hank=> alter table t1 drop column b;
hank=> select * from pg_attribute where attrelid=74219;
attrelid | attname | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff | atttypmod | attbyval | attstorage | a
ttalign | attnotnull | atthasdef | attisdropped | attislocal | attinhcount | attcollation | attacl | attoptions | attfdwoptions
----------+------------------------------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+--
--------+------------+-----------+--------------+------------+-------------+--------------+--------+------------+---------------
74219 | tableoid | 26 | 0 | 4 | -7 | 0 | -1 | -1 | t | p | i
| t | f | f | t | 0 | 0 | | |
74219 | cmax | 29 | 0 | 4 | -6 | 0 | -1 | -1 | t | p | i
| t | f | f | t | 0 | 0 | | |
74219 | xmax | 28 | 0 | 4 | -5 | 0 | -1 | -1 | t | p | i
| t | f | f | t | 0 | 0 | | |
74219 | cmin | 29 | 0 | 4 | -4 | 0 | -1 | -1 | t | p | i
| t | f | f | t | 0 | 0 | | |
74219 | xmin | 28 | 0 | 4 | -3 | 0 | -1 | -1 | t | p | i
| t | f | f | t | 0 | 0 | | |
74219 | ctid | 27 | 0 | 6 | -1 | 0 | -1 | -1 | f | p | s
| t | f | f | t | 0 | 0 | | |
74219 | a | 23 | -1 | 4 | 1 | 0 | -1 | -1 | t | p | i
| f | f | f | t | 0 | 0 | | |
74219 | ........pg.dropped.2........ | 0 | 0 | -1 | 2 | 0 | -1 | 24 | f | x | i
| f | f | t | t | 0 | 100 | | |
只要更新一下字典表即可恢复
hank=> \c hank postgres
You are now connected to database "hank" as user "postgres".
hank=# update pg_attribute set attisdropped='f',attname='b',atttypid=1042 where attrelid=74219 and attnum = 2;
UPDATE 1
hank=# \d hank.t1
Table "hank.t1"
Column | Type | Modifiers
--------+---------------+-----------
a | integer |
b | character(20) |
hank=# select * from hank.t1;
a | b
----+----------------------
2 | wang
3 | li
20 | hank
20 | hank
20 | hank
(5 rows)