乘风原创程序

  • PostgreSQL 更新视图脚本的注意事项说明
  • 2021/1/26 12:42:37
  • 项目最早是基于oracle的,移植到postgresql后,本着尽量少修改的原则,创建/更新视图的脚本也沿用了oracle风格的create or replace view形式。但是每当要更新视图定义时,常常报"cannot change name of view column xxx to yyy"的错误,通常是在视图修改某字段名、中间增加字段、删除字段时发生。

    究其原因,是postgresql虽然支持create or replace view语义,却有着容易让人忽略的重要限制(oracle没有该限制),其官方文档这样描述:

    即:更新视图只能在最后增加字段,不能改字段名、不能删除字段、也不能在中间增加字段,这在项目开发阶段是不可忍受的。虽然postgresql提供了alter view的语句,但怎么也不如直接放在create view里那样直观。

    因此,建议脚本放弃oracle风格的create or replace view形式,而改用mysql风格的先drop view再create view的形式。不过,如果view间存在层次引用关系,如视图a建立在视图b之上,则create时必须先建b后建a,drop时必须先删a再删b。当层次引用较多或变化较频繁时,调整顺序又是件麻烦事。

    为降低复杂性,脚本最终只考虑create view时的顺序,而在drop view时,综合使用if exists 和cascade选项,如下所示:

    drop view if exists b cascade;
    create view b as
    ...;
    drop view if exists a cascade;
    create view a as
    ...;
    

    补充:postgresql对视图优化

    我就废话不多说了,大家还是直接看代码吧~

    //关系准备
    create table t01(a int,b char(32));
    create view v_t01 as select * from t01 where a<10;
    //sql准备
    select * from v_t01 where a<7;
    //测试:
    uu=# explain select * from v_t01 where a<7;
                query plan            
    --------------------------------------------------------
     seq scan on t01 (cost=0.00..17.35 rows=163 width=136)
      filter: ((a < 10) and (a < 7))
    (2 rows)
    

    从query plan可以看出,现在是直接访问v_t01视图的基表t01,并且将视图的谓词条件与sql语句的谓词条件组合,但是没有将谓词条件化简,很可惜,这点做的不是太好。

    我曾在mysql做过同样的测试,mysql处理机制一样,并没有化简谓词条件。

    以上为个人经验,希望能给大家一个参考,也希望大家多多支持本教程网。如有错误或未考虑完全的地方,望不吝赐教。