![]() ![]() Where n.nspname = v_curr.obj_schema and c.relname = v_curr.obj_name and d.description is not null Join pg_description d on d.objoid = c.oid and d.objsubid = 0 Join pg_namespace n on n.oid = c.relnamespace When c.relkind = 'm' then 'MATERIALIZED VIEW' Select p_view_schema, p_view_name, 'COMMENT ON ' || Insert into ps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run) Select obj_schema, obj_name, obj_type, depth Where (deps.ref_schema != p_schema or deps.ref_name != p_name) Join recursive_deps on deps.ref_schema = recursive_deps.obj_schema and deps.ref_name = recursive_deps.obj_name Join pg_namespace rwr_nsp on rwr_cl.relnamespace = rwr_nsp.oid Join pg_class rwr_cl on rwr.ev_class = rwr_cl.oid Join pg_rewrite rwr on dep.objid = rwr.oid ![]() Join pg_namespace ref_nsp on ref_cl.relnamespace = ref_nsp.oid Join pg_class ref_cl on dep.refobjid = ref_cl.oid Select ref_nsp.nspname ref_schema, ref_cl.relname ref_name, Select dep_schema::varchar, dep_name::varchar, dep_type::varchar, recursive_pth + 1 from Select p_view_schema, p_view_name, null::varchar, 0 With recursive recursive_deps(obj_schema, obj_name, obj_type, depth) as Select obj_schema, obj_name, obj_type from The fix is below to change the parameters of p_view_schema and p_view_name from varchar to name: CREATE OR REPLACE FUNCTION ps_save_and_drop_dependencies( Table that stores DDL: CREATE TABLE ps_saved_ddlĬONSTRAINT deps_saved_ddl_pkey PRIMARY KEY (deps_id) Select ps_restore_dependencies('mdm', 'global_item_master_swap') Rule _RETURN on view toolbox_reporting."Average_setcost" depends onĮRROR: cannot alter type of a column used by a view or ruleĪnd now for the PostgreSQL ninja's magic: select ps_save_and_drop_dependencies('mdm', 'global_item_master_swap') Implementation example: alter table mdm.global_item_master_swapĮRROR: cannot alter type of a column used by a view or rule DETAIL: So, read this article and copy and paste the table and two functions listed: I just tested this on an object that is referenced (on the first level) in 136 separate views, and each of those views is referenced in other views. ![]() Change all the constraint to DEFERRABLE INITIALLY IMMEDIATE so the checking will be performed after each statement.I'm a little late to the party, but years after this question was posted, a brilliant solution was posted via an article referenced below (not mine - I'm simply a thankful beneficiary of his brilliance).Therefore the alter fail which caused the issue that you are facing. You are not allowed to update,insert,alter or any other query that will modify the table without executing all the trigger. In this case, the pending trigger would be the constraint checking. However, because *DEFERRABLE INITIALLY DEFERRED* the constraint will not be checked before the transaction complete which is when it is committed. If the constraint is set as * DEFERRABLE INITIALLY IMMEDIATE*, the constraint will be check after each of the statement execute. A transaction is complete by performing the commit action. Note that the COMMIT statement is at the end of the transaction. In this example transaction we can see that Confluence is trying to perform 2 statement (update & alter). SET content_id = (SELECT attachmentid FROM attachments) įOREIGN KEY key (PREVVER) references CONTENT(CONTENTID) In this case, the issue occurs because there is a single SQL query transaction in Confluence that try to perform two different statement. DEFERRABLE INITIALLY IMMEDIATE - Check the constraint immediately for each statement.DEFERRABLE INITIALLY DEFERRED - Check the constraint be deferred to just before each transaction commit.The difference between the two type of constraint are as follows: Causeīy default, the postgres uses set all the constraint as DEFERRABLE INITIALLY IMMEDIATE. The column "initially_deferred" should be "NO" for all constraint. WHERE lower(tc.constraint_type) in ('foreign key') ON rc.unique_constraint_catalog = ccu.constraint_catalogĪND rc.unique_constraint_schema = ccu.constraint_schemaĪND rc.unique_constraint_name = ccu.constraint_name LEFT JOIN information_nstraint_column_usage ccu ![]() ON tc.constraint_catalog = rc.constraint_catalogĪND tc.constraint_schema = rc.constraint_schemaĪND tc.constraint_name = rc.constraint_name LEFT JOIN information_schema.referential_constraints rc ON tc.constraint_catalog = kcu.constraint_catalogĪND tc.constraint_schema = kcu.constraint_schemaĪND tc.constraint_name = kcu.constraint_name LEFT JOIN information_schema.key_column_usage kcu FROM information_schema.table_constraints tc ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |