postgres不推荐修改表字段顺序

SQL server的studio有一个功能，可以随意拖拽表字段，更改其位置并使之重新排序，有同事问起，Postgres是否也可以。Postgres每个字段的顺序是在系统表pg_attribute里面定义，下面实际操作一下看是否支持。

环境：
OS:windows XP
DB:Postgres 9.1.2

数据准备：
postgres=# create table tbl_kenyon(id int,vname varchar(30),remark text);
CREATE TABLE
postgres=# insert into tbl_kenyon select generate_series(1,10),‘Kenyon_good’,‘Nothing is impossible’;
INSERT 0 10
postgres=# select attrelid,attname,attnum from pg_attribute where attrelid = (select relfilenode from pg_class where relname = ‘tbl_kenyon’);
attrelid | attname | attnum
----------±---------±-------
24894 | tableoid | -7
24894 | cmax | -6
24894 | xmax | -5
24894 | cmin | -4
24894 | xmin | -3
24894 | ctid | -1
24894 | id | 1
24894 | vname | 2
24894 | remark | 3
(9 行记录)

postgres=# select ctid,* from tbl_kenyon;
ctid | id | vname | remark
--------±—±------------±----------------------
(0,1) | 1 | Kenyon_good | Nothing is impossible
(0,2) | 2 | Kenyon_good | Nothing is impossible
(0,3) | 3 | Kenyon_good | Nothing is impossible
(0,4) | 4 | Kenyon_good | Nothing is impossible
(0,5) | 5 | Kenyon_good | Nothing is impossible
(0,6) | 6 | Kenyon_good | Nothing is impossible
(0,7) | 7 | Kenyon_good | Nothing is impossible
(0,8) | 8 | Kenyon_good | Nothing is impossible
(0,9) | 9 | Kenyon_good | Nothing is impossible
(0,10) | 10 | Kenyon_good | Nothing is impossible
(10 行记录)

数据调整,校验:
postgres=# update pg_attribute set attnum = 4 where attrelid = 24894 and attname = ‘id’;
UPDATE 1
postgres=# update pg_attribute set attnum = 1 where attrelid = 24894 and attname = ‘vname’;
UPDATE 1
postgres=# update pg_attribute set attnum = 2 where attrelid = 24894 and attname = ‘id’;
UPDATE 1
postgres=# select attrelid,attname,attnum from pg_attribute where attrelid = (select relfilenode from pg_class where relname = ‘tbl_kenyon’);
attrelid | attname | attnum
----------±---------±-------
24894 | tableoid | -7
24894 | cmax | -6
24894 | xmax | -5
24894 | cmin | -4
24894 | xmin | -3
24894 | ctid | -1
24894 | vname | 1
24894 | id | 2
24894 | remark | 3
(9 行记录)
但是查询的时候会直接报错
postgres=# select * from tbl_kenyon;
ERROR: invalid memory alloc request size 1870229097
postgres=# select * from tbl_kenyon limit 1;
ERROR: invalid memory alloc request size 1870229097
postgres=# vacuum full verbose analyze tbl_kenyon;
INFO: vacuuming “public.tbl_kenyon”
ERROR: invalid memory alloc request size 1870229097
才10条数据肯定不可能报这种内存不够的错误，其实是表/数据奔溃。
基于这个表再建其他表，同样是不可访问的。
postgres=# create table tbl_kenyon_new as select *from tbl_kenyon;
SELECT 10
postgres=# select * from tbl_kenyon_new;
ERROR: invalid memory alloc request size 1870229097
我们试着把顺序改回来:
postgres=# update pg_attribute set attnum = 4 where attrelid = 24894 and attname = ‘id’;
UPDATE 1
postgres=# update pg_attribute set attnum = 2 where attrelid = 24894 and attname = ‘vname’;
UPDATE 1
postgres=# update pg_attribute set attnum = 1 where attrelid = 24894 and attname = ‘id’;
UPDATE 1

postgres=# select ctid,* from tbl_kenyon;
ctid | id | vname | remark
--------±—±------------±----------------------
(0,1) | 1 | Kenyon_good | Nothing is impossible
(0,2) | 2 | Kenyon_good | Nothing is impossible
(0,3) | 3 | Kenyon_good | Nothing is impossible
(0,4) | 4 | Kenyon_good | Nothing is impossible
(0,5) | 5 | Kenyon_good | Nothing is impossible
(0,6) | 6 | Kenyon_good | Nothing is impossible
(0,7) | 7 | Kenyon_good | Nothing is impossible
(0,8) | 8 | Kenyon_good | Nothing is impossible
(0,9) | 9 | Kenyon_good | Nothing is impossible
(0,10) | 10 | Kenyon_good | Nothing is impossible
(10 行记录)
OK,它回来了。目前该系统表的表结构有一个组合主键(attrelid,attrnum)，所以不能随便更新attrnum值，极有可能报如下错:
postgres=# update pg_attribute set attnum = 1 where attrelid = 24894 and attname = ‘id’;
ERROR: duplicate key value violates unique constraint “pg_attribute_relid_attnum_index”
描述: Key (attrelid, attnum)=(24894, 1) already exists.