Postgres不推荐更改字段位置

最新推荐文章于 2024-10-11 11:37:45 发布

转载最新推荐文章于 2024-10-11 11:37:45 发布 · 993 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://my.oschina.net/Kenyon/blog/124555

文章标签：

#数据库

本文讨论了Postgres数据库中不建议随意更改字段位置的原因，解释了这种做法可能带来的潜在问题和对数据库性能的影响。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

为什么80%的码农都做不了架构师？>>>

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.

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

参考： http://wiki.postgresql.org/wiki/Alter_column_position

转载于:https://my.oschina.net/Kenyon/blog/124555