今天又遇到一个需求,要把PG中的字段类型修改一下。本来以为是个很简单的事情,毕竟Oracle就是一条指令就行了。但是在PG中改字段真的真的太难了。
当你修改表字段的时候,会报ERROR: cannot alter type of a column used by a view or rule.
这主要是因为这个表上存在视图或者是rule,rule这里代表是触发器。所以在PG中它不能像Oracle那样修改字段。一般做法就是:
BEGIN; DROP VIEW view_name ALTER TABLE users ALTER COLUMN column_name TYPE character varying(500); CREATE VIEW view_name AS SELECT * FROM table_name; COMMIT; |
这样干也没什么问题,但是一旦上百个视图依赖于一张表,或者视图有多个嵌套,这问题就麻烦起来了,特别是有的视图定义动辄上百上千行的,修改字段再创建视图,一套弄下来就特别累。那么就没有什么完美的解决办法吗?
通过研究,发现这个问题有两种解决办法,针对两种不同的情况。
情况一:只修改长度
修改长度,是在日常维护中经常发生的。比如以前一个字段是20个长度,运行一段时间之后,发现长度不够要扩成30。这个时候一般就会通知dba进行操作。我们可以通过修改pg_attribute基表的方式来绕开这个限制。
create table a(id int ,name varchar(20)); create view a_view as select id,name from a; alter table a alter name type varchar(30); ERROR: cannot alter type of a column used by a view or rule DETAIL: rule _RETURN on view a_view depends on column "name" SELECT atttypmod FROM pg_attribute WHERE attrelid = 'a'::regclass AND attname = 'name'; atttypmod ----------- 24 (1 row) update pg_attribute set atttypmod =34 WHERE attrelid ='a'::regclass AND attname = 'name'; UPDATE 1 SELECT atttypmod FROM pg_attribute WHERE attrelid = 'a'::regclass AND attname = 'name'; atttypmod ----------- 34 |
这里需要注意的一点是我设置的是varchar(20),查出来的是varchar(24),这是因为历史原因,添加了4。我如果要改成30,这里就需要修改为34。
改完之后我们再来查询我们的表和视图,发现都是ok的。
postgres=# \d a Table "public.a" Column | Type | Collation | Nullable | Default --------+-----------------------+-----------+----------+--------- id | integer | | | name | character varying(30) | | | postgres=# insert into a values(1,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaa'); INSERT 0 1 postgres=# select lengthb(name) from a; lengthb --------- 29 postgres=# select * from a_view; id | name ----+------------------------------- 1 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
虽然这样修改能解决问题,但是确实有一定发生错误的风险,所以需要谨慎使用,最好要经过详细的评审和测试之后再操作。
情况二:修改字段类型
修改字段类型这种情况多见于执行SQL缓慢,通过执行计划发现是字段类型不匹配产生了隐式在转换,而无法使用上索引。
这种情况就得通过我们之前的方法来实现,把删除视图、修改字段、创建视图放到一个事务下执行,但是如果嵌套的视图比较多就很麻烦。为了克服这个麻烦,就有一个大神级人物写了两个函数来轻松实现了这个问题。由于太多人受到这个“烦恼”问题的困扰,作者得到了极高的赞扬。
BEGIN; select deps_save_and_drop_dependencies('public', 'a'); alter table a alter name type varchar(30); select deps_restore_dependencies('public', 'a'); COMMIT |
以下是我在自己环境中进行的测试,非常简单就搞定了。
函数可以在github上下载:
https://gist.github.com/mateuszwenus/11187288(PG12之前版本)
https://gist.github.com/briandignan/03ef42e78434658cf27f052e2f0798e8(PG12之后的版本)
如果让我推荐,我还是推荐使用第二种方法,毕竟这个方法比较稳妥一点。也基本上达到了比较完美的地步。就算遇到上百个视图或者像俄罗斯套娃一样的视图你也不用担心了。
参考文档:
Problemwith Postgres ALTER TABLE
https://stackoverflow.com/questions/3243863/problem-with-postgres-alter-table/49000321