如何优雅的修改被视图引用的表字段

2 minute read

背景

在修改数据库表字段的长度时,数据库提供了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表的定义》

《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》

Flag Counter

digoal’s 大量PostgreSQL文章入口