Gaussdb (DWS) application: DDL operations on tables referenced by views

Time:2020-9-25

Abstract:Gaussdb (DWS) evolves from Postgres. Like Postgres, if a table is referenced by a view, some DDL operations cannot be performed directly in a specific scenario.

Background note

Gaussdb (DWS) evolved from Postgres. Like Postgres, if a table is referenced by a view, some DDL operations cannot be performed directly in a specific scenario, such as modifying the type of the field referenced by the view, deleting the table, etc., while adding new fields can be operated. The main reason is that the view refers to the table field, and the view needs to be changed if it is modified. Let’s show you a little bit about this part of the content. What will be the performance of DDL operations on tables referenced by views. Then see how to modify the table fields.

Generate experiment content

Create 2 test tables and 3 test views. The SQL statements are as follows. Note that all views use the fields of T1, not T2.

CREATE TABLE t1 (id int,name varchar(20));
		CREATE TABLE t2 (id int,name varchar(20));
		CREATE OR REPLACE VIEW v1 as select * from t1;
		CREATE OR REPLACE VIEW v2 as select a.* from t1 a inner join t2 b on a.id = b.id;
CREATE OR REPLACE VIEW v3 as select a.* from v1 a inner join v2 b on a.id = b.id inner join t1 c on a.id = c.id;

1、 Delete table

DROP TABLE t1;
DROP TABLE t2;

Gaussdb (DWS) application: DDL operations on tables referenced by views

From the execution result prompt, drop table did not execute successfully because of view dependency.You can delete dependent views together by drop… Cascade, but in general, we don’t want to delete views.

2、 Modify field

ALTER TABLE T1 MODIFY NAME VARCHAR(30);
ALTER TABLE T2 MODIFY NAME VARCHAR(30);

Gaussdb (DWS) application: DDL operations on tables referenced by views

From the prompt of execution results, table t1 failed to modify the field type, because view V2 uses this field, andTable t2 is modified successfully because no view uses the field of T2. Although table t2 is used in the view, it is only used for association. The field type of table t2 can be modified successfully because the field of view does not use the field of table t2.

In order to achieve the goal of the later experiment, we modify the view of V2 to get the field of T2

ALTER TABLE T2 MODIFY NAME VARCHAR(20);
CREATE OR REPLACE VIEW v2 as select b.* from t1 a inner join t2 b on a.id = b.id;

3、 New fields

ALTER TABLE t1 ADD COMMENT VARCHAR(30);
ALTER TABLE t2 ADD  COMMENT VARCHAR(30);

Gaussdb (DWS) application: DDL operations on tables referenced by views

There are no restrictions on the new fields, because when the view is created, there is no way to refer to the fields that are not yet available. Let’s look at the definition of a viewCREATE VIEW v1 AS SELECT * FROM t1; Will V1 have new field information at this time? The answer is No. the view needs to be refreshed again to have new fields

select * from v2;
		CREATE OR REPLACE VIEW v2 as select a.* from t1 a inner join t2 b on a.id = b.id;
select * from v2;

Gaussdb (DWS) application: DDL operations on tables referenced by views

How to modify a table definition referenced by a view?

So the question is, how to modify the definition of the table referenced by the view similar to the above modification of fields?

I think it can be divided into the following steps

Backup view definition to text > backup table definition to text > modify table definition in text > backup table XX rename to XX_ Bak) – > Add modified Table > insert data > backup view text refresh view

One of the more difficult to obtain is, which views refer to the table? PG is needed in this_ Rewrite to get the reference relationship, and with recursive.. as loop.

1、 Backup view definition to text

This SQL is a little complicated. Here, we will explain in several steps

Through PG_ Rewrite gets the dependency relationship between the table and the view

select c.nspname as schemaname,b.relname,rel_oid,b.relkind,d.oid as ori_oid,d.relname ori_name
		from (
		select unnest(regexp_ matches(ev_ action::text,':relid (d+)', 'g'))::oid  rel_ oid,ev_ class --rel_ Oid is dependent on the object, ev_ Class view name
		from pg_rewrite 
		union 
		select unnest(regexp_matches(ev_action::text,':resorigtbl (d+)','g'))::oid,ev_class
		from pg_rewrite 
		) deptbl                   --pg_ Write get dependency
		inner join pg_ Class B -- the dependent object gets the table name and other information
		on deptbl.rel_oid = b.oid
		inner join pg_namespace c
		on b.relnamespace = c.oid
		inner join pg_ Class D -- view gets view name and other information, and is used to exclude PG_ Write gets its own object, namely rel_ oid <> ev_ class
		on deptbl.ev_class = d.oid
		and deptbl.rel_oid <> d.oid
Where b.relname ='t2 '; -- specifies the table name T2

Gaussdb (DWS) application: DDL operations on tables referenced by views

Get all relevant views through the with recursive XX as loop statement

with recursive rec_view as (
		select c.nspname as schemaname,b.relname,rel_oid,b.relkind,d.oid as ori_oid,d.relname ori_name
		, 0 as level -- level
		from (
		select unnest(regexp_ matches(ev_ action::text,':relid (d+)', 'g'))::oid  rel_ oid,ev_ class --rel_ Oid is dependent on the object, ev_ Class view name
		from pg_rewrite 
		union 
		select unnest(regexp_matches(ev_action::text,':resorigtbl (d+)','g'))::oid,ev_class
		from pg_rewrite 
		) deptbl                   --pg_ Write get dependency
		inner join pg_ Class B -- the dependent object gets the table name and other information
		on deptbl.rel_oid = b.oid
		inner join pg_namespace c
		on b.relnamespace = c.oid
		inner join pg_ Class D -- view gets view name and other information, and is used to exclude PG_ Write gets its own object, namely rel_ oid <> ev_ class
		on deptbl.ev_class = d.oid
		and deptbl.rel_oid <> d.oid
		Where b.relname: 'T2' -- specifies the table name T2
		union all
		select c.nspname,b.relname,deptbl.rel_oid,b.relkind,d.oid as ori_oid,d.relname ori_name,level+1
		from (
		select unnest(regexp_matches(ev_action::text,':relid (d+)', 'g'))::oid  rel_oid,ev_class
		from pg_rewrite 
		union 
		select unnest(regexp_matches(ev_action::text,':resorigtbl (d+)','g'))::oid,ev_class
		from pg_rewrite 
		) deptbl 
		inner join pg_class b
		on deptbl.rel_oid = b.oid
		inner join pg_namespace c
		on b.relnamespace = c.oid
		inner join pg_class d
		on deptbl.ev_class = d.oid
		and deptbl.rel_oid <> d.oid
		inner join rec_ View e -- loop statement Association condition
		on deptbl.rel_oid = e.ori_oid
		Where level < = 10 -- level prevents loops
		)
select * from rec_view;

Gaussdb (DWS) application: DDL operations on tables referenced by views

As a result, T2 is related to V2 and v3.

After getting the list of views, we backup V2 and V3 views to the text, and use GS_ Dump mode.

gs_dump mydb1 -s -t v2 -t v3 -c -f view.ddl -p 25308

Gaussdb (DWS) application: DDL operations on tables referenced by views

2Backup table definition to text > modify table definition in text > alter table XX rename to XX_ Bak) – > add the modified table and insert data

Backup table definition to text: use GS_ Dump exports the table structure of T2 to a file

Modify the table definition in the text: change the field type of name from the original varchar (30) to varchar (50)

Alter table XX rename to XX_ Bak): add the alter table rename action to the text

Add modified table and insert data: add insert data SQL in text

gs_dump mydb1 -s -t t2  -f t2.ddl -p 25308

After the above contents are modified, the results are shown in the figure below

Gaussdb (DWS) application: DDL operations on tables referenced by views

Execute the text statement

gsql -d mydb1 -p 25308 -r  -f t2.ddl

3、 Refresh view

V2, V3 view to perform export

gsql -d mydb1 -p 25308 -r  -f view.ddl

Then check to see if the T2 table has changed the definition and see if the view can query

d t2
		select * from v2;
select * from v3;

Gaussdb (DWS) application: DDL operations on tables referenced by views

summary

Because the view will generate dependency when using a table. When modifying the definition of a table that is dependent on a view, it is impossible to modify it under certain circumstances. Here, I think it can be achieved through the following steps:Backup view definition to text > backup table definition to text > modify table definition in text > backup table XX rename to XX_ Bak) – > Add modified Table > insert data > backup view text refresh view

In the step of defining the backup view, you need to know what the relevant views of the table you need to modify. This query process requires the use of PG_ Rewrite table and with recursive XX as recursively get related views. After the relevant views are retrieved and backed up, the remaining steps are relatively simple.

Click follow to learn about Huawei’s new cloud technologies~