PostgresSql更改字段位置后,数据库异常
2021/12/3 2:09:39
本文主要是介绍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更改字段位置后,数据库异常的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-12-25初学者必备:订单系统资料详解与实操教程
- 2024-12-24内网穿透资料入门教程
- 2024-12-24微服务资料入门指南
- 2024-12-24微信支付系统资料入门教程
- 2024-12-24微信支付资料详解:新手入门指南
- 2024-12-24Hbase资料:新手入门教程
- 2024-12-24Java部署资料
- 2024-12-24Java订单系统资料:新手入门教程
- 2024-12-24Java分布式资料入门教程
- 2024-12-24Java监控系统资料详解与入门教程