PostgresSql更改字段位置后，数据库异常

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

数据准备：

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.

总结：
目前并不希望用户去更改postgres表字段的顺序，否则极有可能造成数据奔溃或不可访问。要更改表字段的顺序，一般就通过重建表或者创建新字段以及使用视图等其他手段来实现。

巴特西

PostgresSql更改字段位置后，数据库异常

最新文章

热门文章