当前位置: 首页 > news >正文

深圳网站公司湖南免费网站建设

深圳网站公司,湖南免费网站建设,编程的基础知识,网页设计秀丽南宁其实视图的存在与否在数据库界一直是一个话题。用好视图可以简化程序的很多代码,用不好视图不仅会给维护带来很多的不便,也会造成很大的性能问题。下面我从维护方面给出案例,以及当存在这种问题的时候,如何去解决这个问题。 假设…

其实视图的存在与否在数据库界一直是一个话题。用好视图可以简化程序的很多代码,用不好视图不仅会给维护带来很多的不便,也会造成很大的性能问题。下面我从维护方面给出案例,以及当存在这种问题的时候,如何去解决这个问题。

假设有如下的表和视图
CREATE TABLE view_test (id int,cname varchar(10),des text);
CREATE VIEW vw_view_testa AS SELECT * FROM view_test WHERE id > 2;
CREATE VIEW vw_view_testb AS SELECT * FROM view_test WHERE id < 3;
INSERT INTO view_test(id,cname,des) VALUES (1,'a','value a'),(2,'b','value b'),(3,'c','value c'),(4,'d','value d'),(5,'e','value e');

现在我想修改表的字段 cname 从 varchar(10) 到 varchar(50)
ALTER TABLE view_test ALTER COLUMN cname TYPE VARCHAR(50);

ERROR:  cannot alter type of a column used by a view or rule
DETAIL:  rule _RETURN on view vw_view_testa depends on column "cname"
说明:这个时候会提示存在视图依赖无法修改字段的长度。当视图依赖成为一种嵌套的时候,这种问题在系统升级的时候会严重的影响升级的速度。那么有没有更好的办法去兼容呢。好的架构师需要从根本上解决,一般的业务公司只能是考虑做兼容。

下面是解决视图依赖的解决方案和步骤以及操作案例

CREATE TABLE public.deps_saved_ddl (
  deps_id serial,
  deps_view_schema name,
  deps_view_name name,
  deps_ddl_to_run text,
  PRIMARY KEY (deps_id));
ALTER TABLE public.deps_saved_ddl OWNER TO postgres;

-- 将所有与表依赖的视图进行转储
CREATE OR REPLACE FUNCTION public.deps_save_and_drop_dependencies(p_view_schema name, p_view_name name)
  RETURNS pg_catalog.void AS $BODY$
  DECLARE v_curr record;
  BEGIN FOR v_curr IN (
            SELECT obj_schema,obj_name,obj_type
            FROM ( WITH recursive recursive_deps(obj_schema, obj_name, obj_type, depth) AS
                ( SELECT p_view_schema, p_view_name, null::char,0
                  UNION
                  SELECT dep_schema::name, dep_name::name, dep_type::char, recursive_deps.depth + 1
                  FROM ( SELECT ref_nsp.nspname ref_schema, ref_cl.relname ref_name, rwr_cl.relkind dep_type, rwr_nsp.nspname dep_schema, rwr_cl.relname dep_name
                 FROM pg_depend dep
                 INNER JOIN pg_class ref_cl ON dep.refobjid = ref_cl.oid
                 INNER JOIN pg_namespace ref_nsp ON ref_cl.relnamespace = ref_nsp.oid
                 INNER JOIN pg_rewrite rwr ON dep.objid = rwr.oid
                 INNER JOIN pg_class rwr_cl ON rwr.ev_class = rwr_cl.oid
                 INNER JOIN pg_namespace rwr_nsp ON rwr_cl.relnamespace = rwr_nsp.oid
                 WHERE dep.deptype = 'n' AND dep.classid = 'pg_rewrite'::regclass ) deps
          INNER JOIN recursive_deps ON deps.ref_schema = recursive_deps.obj_schema AND deps.ref_name = recursive_deps.obj_name
    WHERE (deps.ref_schema != deps.dep_schema or deps.ref_name != deps.dep_name) )  
    SELECT obj_schema, obj_name, obj_type, depth
        FROM recursive_deps
        WHERE depth > 0 ) t
    GROUP BY obj_schema, obj_name, obj_type
    ORDER BY max(depth) DESC ) LOOP
         
    INSERT INTO deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
    SELECT DISTINCT p_view_schema, p_view_name, indexdef
    FROM pg_indexes
    WHERE schemaname = v_curr.obj_schema AND tablename = v_curr.obj_name;

    INSERT INTO deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
    SELECT DISTINCT tablename, rulename, definition
    FROM pg_rules
    WHERE schemaname = v_curr.obj_schema AND tablename = v_curr.obj_name;

    INSERT INTO deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
    SELECT p_view_schema,
               p_view_name,
                     'COMMENT ON ' || CASE WHEN c.relkind = 'v' THEN 'VIEW' WHEN c.relkind = 'm' THEN 'MATERIALIZED VIEW' ELSE '' END || ' ' || n.nspname || '.' || c.relname || ' IS ''' || replace(d.description, '''', '''''') || ''';'
    FROM pg_class c
    INNER JOIN pg_namespace n ON n.oid = c.relnamespace
    INNER JOIN pg_description d ON d.objoid = c.oid AND d.objsubid = 0
    WHERE n.nspname = v_curr.obj_schema AND c.relname = v_curr.obj_name AND d.description is not null;

  INSERT INTO deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
  SELECT p_view_schema,
           p_view_name,
                 'COMMENT ON COLUMN ' || n.nspname || '.' || c.relname || '.' || a.attname || ' IS ''' || replace(d.description, '''', '''''') || ''';'
  FROM pg_class c
  INNER JOIN pg_attribute a ON c.oid = a.attrelid
  INNER JOIN pg_namespace n ON n.oid = c.relnamespace
  INNER JOIN pg_description d ON d.objoid = c.oid AND d.objsubid = a.attnum
  WHERE n.nspname = v_curr.obj_schema AND c.relname = v_curr.obj_name AND d.description is not null;
 
  INSERT INTO deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
  SELECT p_view_schema,
           p_view_name,
                 'GRANT ' || privilege_type || ' ON ' || table_schema || '.' || quote_ident(table_name) || ' TO ' || grantee
  FROM information_schema.role_table_grants
  WHERE table_schema = v_curr.obj_schema AND table_name = v_curr.obj_name;
 
  IF v_curr.obj_type = 'v' THEN
    INSERT INTO deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
    SELECT p_view_schema,
               p_view_name,
                     'CREATE VIEW ' || v_curr.obj_schema || '.' || quote_ident(v_curr.obj_name) || ' AS ' || view_definition
    FROM information_schema.views
    WHERE table_schema = v_curr.obj_schema AND table_name = v_curr.obj_name;
  elsif v_curr.obj_type = 'm' THEN
    INSERT INTO deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
    SELECT p_view_schema,
               p_view_name,
                     'CREATE MATERIALIZED VIEW ' || v_curr.obj_schema || '.' || quote_ident(v_curr.obj_name) || ' AS ' || definition
    FROM pg_matviews
    WHERE schemaname = v_curr.obj_schema AND matviewname = v_curr.obj_name;
  END IF;
 
  EXECUTE 'DROP ' || CASE WHEN v_curr.obj_type = 'v' THEN 'VIEW' WHEN v_curr.obj_type = 'm' THEN 'MATERIALIZED VIEW' END || ' ' || v_curr.obj_schema || '.' || quote_ident(v_curr.obj_name);
 
END loop;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

ALTER FUNCTION public.deps_save_and_drop_dependencies(p_view_schema name, p_view_name name) OWNER TO postgres;

-- 对所有之前创建的视图进行重建
CREATE OR REPLACE FUNCTION "public"."deps_restore_dependencies"("p_view_schema" name, "p_view_name" name)
  RETURNS "pg_catalog"."void" AS $BODY$
  DECLARE   v_curr record;
  BEGIN
  FOR v_curr IN ( SELECT deps_ddl_to_run
                        FROM deps_saved_ddl
                        WHERE deps_view_schema = p_view_schema AND deps_view_name = p_view_name ORDER BY deps_id DESC )
        loop
        EXECUTE v_curr.deps_ddl_to_run;
      END loop;
        DELETE FROM deps_saved_ddl WHERE deps_view_schema = p_view_schema AND deps_view_name = p_view_name;
  END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
 
ALTER FUNCTION public.deps_restore_dependencies(p_view_schema name, p_view_name name) OWNER TO postgres;

操作案例
BEGIN;
SELECT deps_save_and_drop_dependencies('public', 'view_test');
ALTER TABLE view_test ALTER COLUMN cname TYPE VARCHAR(50);
SELECT deps_restore_dependencies('public', 'view_test');
COMMIT;

查看表的结构字段 cname 的长度是否是 50
[postgres@localhost data]$ psql
psql (12.8)
Type "help" for help.

postgres=# \c cloud_test
You are now connected to database "cloud_test" as user "postgres".
cloud_test=# \d view_test
                    Table "public.view_test"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 id     | integer               |           |          |
 cname  | character varying(50) |           |          |
 des    | text                  |           |          |

cloud_test=#

http://www.yayakq.cn/news/684499/

相关文章:

  • 高青网站建设网站后台验证码错误
  • 做设计挣钱的网站主页网页设计
  • 文章管理系统网站模板端 传媒网站模板
  • 网站导航条内容阐述网络营销策略的内容
  • 网站建设的学习遵化建设招标网站
  • 网站结构优化建议wordpress 简单企业主题下载
  • 网页设计网站模板网站建设网页模板下载深圳快速网站制作服务
  • 青岛手机建站模板wordpress 基本模版
  • 做国外单的网站叫什么成都网站建设s1emens
  • html格式的网站地图建设工程有限公司网站
  • 网站建设有哪些软件有哪些自建站怎么接入支付
  • 智能模板网站建设工具广州网站建设=388元
  • 鹤山区网站建设便宜做网站的公司
  • cmsapp模板网站网站流量导入是什么意思
  • 北京赛车网站开发河南继续浏览此网站(不推荐)
  • 微软雅黑做网站网站服务器问题
  • 网页视频怎么下载到ios四川做网站优化价格
  • 蛋糕电子商务网站建设方案seo推广计划类型可以分为什么
  • 翰诺网站建设报价单模板
  • 股权分配系统建设网站华大基因 网站建设
  • 医院网站建设方案详细linux上搭建网站
  • 网站建设 选择题吉林建设网站
  • 聊城专业网站制作公司o2o平台网站开发
  • 二级网站的建设方案电子商务网站建设有哪些知识点
  • 阿盟住房与建设局门户网站网络营销环境分析主要包括
  • 网站网址怎么找哪里有营销型网站最新报价
  • 个人网站设计说明wordpress u盘
  • 吉林网站建设司物流网站建设策划书怎么写
  • 网站建设找星火龙房产信息查询平台
  • 营销网站制作都选ls15227wordpress如何添加文章来源