ANBOB™

《VIEW dependencies in Oracle、MySQL、PostGreSQL(数据库比较系列十一)》之前一文记录了postgresql对于table上创建了view以后，修改table列长度时会报错””ERROR: cannot alter type of a column used by a view or rule””,  最近一套就应用需要开启加密功能，大量的表字段需要增加长度，但又有大量的view， 如果view全删修改后再创建确实不少的工作量，基于postgresql的opengauss新开源分支的部分发型版已经支持了该功能，这里是指mogdb,另一个流行商业版vb还存在一些缺陷。这里演示mogdb V5该功能支持

[omm@mogdb2 ~]$ gsql
gsql ((MogDB 5.0.1 build ae6d2ada) compiled at 2023-08-16 09:07:43 commit 0 last mr 1804 )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

MogDB=# create table t(id int,name varchar2(20));
CREATE TABLE
MogDB=# insert into t values(1,'anbob');
INSERT 0 1
MogDB=# create view v_t as select 'aha' flag,id,name from t;
CREATE VIEW
MogDB=# select * from v_t;
 flag | id | name
------+----+-------
 aha  |  1 | anbob
(1 row)

MogDB=# alter table t modify name varchar2(40);
ERROR:  cannot alter type of a column used by a view or rule
DETAIL:  rule _RETURN on view v_t depends on column "name"

Note:
默认和Postgresql一样不支持，在mogdb V5开始支持，但需要调整参数view_independent=on, 默认off。

MogDB=# alter system set view_independent=on;
ALTER SYSTEM SET

MogDB=# alter table t modify name varchar2(40);
ALTER TABLE
MogDB=#
MogDB=#
MogDB=#  \d t
              Table "public.t"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 id     | integer               |
 name   | character varying(40) |

MogDB=# \d v_t
         Invalid View "public.v_t"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 flag   | text                  |
 id     | integer               |
 name   | character varying(20) |

MogDB=# insert into t values(2,lpad('a',30,'x'));
INSERT 0 1
MogDB=# select * from t;
 id |              name
----+--------------------------------
  1 | anbob
  2 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxa
(2 rows)

MogDB=# select * from v_t;
 flag | id |              name
------+----+--------------------------------
 aha  |  1 | anbob
 aha  |  2 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxa
(2 rows)

MogDB=# select id,name,length(name) from v_t;
 id |              name              | length
----+--------------------------------+--------
  1 | anbob                          |      5
  2 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxa |     30
(2 rows)

MogDB=# \d v_t
             View "public.v_t"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 flag   | text                  |
 id     | integer               |
 name   | character varying(40) |

Note：
参数打开后支持直接修改，view会变为失效，但在查询后会自动重新编译为有效，并同步表列长度。

如何查找Invalid View

MogDB=# alter table t modify name varchar2(40);
ALTER TABLE
MogDB=# \d v_t
         Invalid View "public.v_t"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 flag   | text                  |
 id     | integer               |
 name   | character varying(40) |

MogDB=# select * from pg_catalog.pg_rewrite where ev_class=84304;
-[ RECORD 1 ]----------------------------------------------------------  
rulename   | _RETURN
ev_class   | 84304
ev_attr    | -1
ev_type    | 1
ev_enabled | D
is_instead | t
ev_qual    |
...

MogDB=# select * from v_t;
 flag | id |              name
------+----+--------------------------------
 aha  |  1 | anbob
 aha  |  2 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxa
(2 rows)

MogDB=# \d v_t
             View "public.v_t"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 flag   | text                  |
 id     | integer               |
 name   | character varying(40) |


MogDB=# select * from pg_catalog.pg_rewrite where ev_class=84304;
-[ RECORD 1 ]------------------------------------------------------------  
rulename   | _RETURN
ev_class   | 84304
ev_attr    | -1
ev_type    | 1
ev_enabled | O
is_instead | t

Note:
pg_rewrite 的ev_enabled 字段表示view的失效状态: D失效 O（欧）有效

— openGauss V6以后据说也会支持，到时所有分支会支持，you know why。 view的失效状态会在pg_object记录。

打赏

微信扫一扫，打赏作者吧～