作者
digoal
日期
2017-05-03
标签
PostgreSQL , 视图 , 表 , alter table , 修改字段属性
背景
在修改数据库表字段的长度时，数据库提供了alter table的语法进行修改。
但是被修改的字段如果有其他引用（例如视图）时，必须先将引用的对象删除，再修改对应的字段。
例子如下
```
测试表
postgres=# create table test_t (id int, info text, crt_time timestamp, c1 varchar(10));
CREATE TABLE
被修改字段上建立索引
postgres=# create index idx_test_t on test_t(c1);
CREATE INDEX
被修改字段上建立视图
postgres=# create view v_test_t as select id,c1 from test_t;
CREATE VIEW
修改字段的长度报错
postgres=# alter table test_t alter column c1 type varchar(32);
ERROR: cannot alter type of a column used by a view or rule
DETAIL: rule _RETURN on view v_test_t depends on column "c1"
```
优雅的修改被视图引用的表字段
PostgreSQL支持将DDL语句封装在事务中处理，所以从删除依赖，到修改字段，再到重建依赖，都可以封装在一个事务中完成。
注意
1. DDL是需要对表加排它锁的，排它锁与所有其他锁冲突，因此建议在事务开始时设置锁超时参数，避免问题。
2. 如果修改字段涉及到rewrite table(例如int改到text)，那么表很大时间会很久。如果需要很久，意味着需要长时间持有排它锁（堵塞也是比较严重的）。
例子
```
begin; -- 开始事务
set local lock_timeout = '1s'; -- 设置锁超时
drop view v_test_t; -- 删除依赖视图
alter table test_t alter column c1 type varchar(32); -- 修改字段长度
create view v_test_t as select id,c1 from test_t; -- 创建视图
end; -- 结束事务
```
hack PostgreSQL pg_attribute元数据的修改方法
PostgreSQL的定义都记录在元数据中，所以某些操作，可以直接修改元数据来实现。比如从numeric低精度修改到高精度，从字符串短长度修改到长长度。
注意
不建议这么做，直接修改元数据存在隐患，甚至可能对数据库造成不可修复的伤害。
例子
首先要查看将要修改的C1字段的pg_attribute元信息
```
视图、索引、表 在pg_attribute中都有对应的元信息，如下
postgres=# select attrelid::regclass,* from pg_attribute where attname='c1';
attrelid | attrelid | attname | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff | atttypmod | attbyval | attstorage | attalign | attnotnull | atthasdef | attidentity | attisdropped | attislocal | attinhcount | attcollation | attacl | attoptions | attfdwoptions
------------+----------+---------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+------------+-----------+-------------+--------------+------------+
test_t | 21988 | c1 | 1043 | -1 | -1 | 4 | 0 | -1 | 36 | f | x | i | f | f | | f | t | 0 | 100 | | |
v_test_t | 21998 | c1 | 1043 | -1 | -1 | 2 | 0 | -1 | 36 | f | x | i | f | f | | f | t | 0 | 100 | | |
idx_test_t | 22002 | c1 | 1043 | -1 | -1 | 1 | 0 | -1 | 36 | f | x | i | f | f | | f | t | 0 | 100 | | |
(6 rows)
```
在修改时，需要将这三个atttypmod一起修改掉。
变长字段的长度为4字节头+实际长度，所以36表示可以存储32个字符。
修改为varchar(64)这样操作
postgres=# update pg_attribute set atttypmod=68 where attname='c1' and attrelid in (21988,21998,22002);
UPDATE 3
更新后，可以看到结构发生了变化.
```
postgres=# \d+ test_t
Table "public.test_t"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
info | text | | | | extended | |
crt_time | timestamp without time zone | | | | plain | |
c1 | character varying(64) | | | | extended | |
Indexes:
"idx_test_t" btree (c1)
postgres=# \d+ v_test_t
View "public.v_test_t"
Column | Type | Collation | Nullable | Default | Storage | Description
--------+-----------------------+-----------+----------+---------+----------+-------------
id | integer | | | | plain |
c1 | character varying(64) | | | | extended |
View definition:
SELECT test_t.id,
test_t.c1
FROM test_t;
postgres=# \d+ idx_test_t
Index "public.idx_test_t"
Column | Type | Definition | Storage
--------+-----------------------+------------+----------
c1 | character varying(64) | c1 | extended
btree, for table "public.test_t"
```
参考
《PostgreSQL 9.0 modify pg_attribute.atttypmod extend variable char length avoid rewrite table》
《PostgreSQL WHY modify numeric scale must rewrite table》
《PostgreSQL How can i decode the NUMERIC precision and scale in pg_attribute.atttypmod》
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
