网站主域名是什么,l礼品文化网站建设,爱站网为什么不能用了,电商平台有哪些平台其实视图的存在与否在数据库界一直是一个话题。用好视图可以简化程序的很多代码#xff0c;用不好视图不仅会给维护带来很多的不便#xff0c;也会造成很大的性能问题。下面我从维护方面给出案例#xff0c;以及当存在这种问题的时候#xff0c;如何去解决这个问题。
假设…其实视图的存在与否在数据库界一直是一个话题。用好视图可以简化程序的很多代码用不好视图不仅会给维护带来很多的不便也会造成很大的性能问题。下面我从维护方面给出案例以及当存在这种问题的时候如何去解决这个问题。
假设有如下的表和视图 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 [postgreslocalhost 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#
