This topic describes the limits on modifying base tables and dependency views, analyzes the impacts of the modifications on dependency views, and describes how to quickly and safely modify a base table or a view in AnalyticDB for PostgreSQL.
Usage notes
You can modify base tables and dependency views only for AnalyticDB for PostgreSQL V7.0 instances.
The views in this topic are all dependency views that rely on base tables.
Query the dependencies between base tables and views
To view dependencies between base tables and views, you must create a function for querying dependencies. The following figure shows an example on how to query dependencies between base tables and views.
Create base tables and dependency views.
CREATE TABLE cities(id INT, name TEXT, info TEXT); CREATE TABLE products(id INT, info TEXT); CREATE TABLE sales(product_id INT, city_id INT, money NUMERIC(10,2)); CREATE VIEW all_info AS SELECT * FROM cities c JOIN sales s ON c.id = s.city_id; CREATE VIEW ps_info AS SELECT * FROM products p JOIN sales s ON p.id = s.product_id; CREATE VIEW psc_info AS SELECT * FROM all_info a JOIN ps_info p ON p.product_id = s.product_id;Create a function for querying the dependencies between the base tables and dependency views.
CREATE OR REPLACE FUNCTION get_table_dependencies(input_schema TEXT, input_table TEXT) RETURNS TABLE( depth INT, dependent_schema TEXT, dependent_view TEXT, dependent_oid OID, source_schema TEXT, source_table TEXT ) AS $$ BEGIN RETURN QUERY WITH RECURSIVE find_views AS ( SELECT DISTINCT n.nspname::TEXT as dependent_schema, -- Query the schema name of the dependency view and convert the schema name into the TEXT type. cl.relname::TEXT as dependent_view, -- Query the name of the dependency view and convert the name into the TEXT type. cl.oid as dependent_oid, -- Query the object identifier (OID) of the dependency view. nb.nspname::TEXT as source_schema, -- Query the schema name of the base table or the dependency view and convert the schema name into the TEXT type. c.relname::TEXT as source_table, -- Query the name of the base table or the dependency view and convert the name into the TEXT type. 1 as depth FROM pg_depend d JOIN pg_rewrite r ON d.objid = r.oid JOIN pg_class cl ON r.ev_class = cl.oid JOIN pg_class c ON d.refobjid = c.oid JOIN pg_namespace n ON cl.relnamespace = n.oid JOIN pg_namespace nb ON c.relnamespace = nb.oid WHERE d.deptype = 'n' AND d.classid='pg_rewrite'::regclass AND cl.relkind IN ('v', 'm') AND nb.nspname = input_schema AND c.relname = input_table UNION ALL SELECT n.nspname::TEXT, cl.relname::TEXT, cl.oid, nb.nspname::TEXT, c.relname::TEXT, fv.depth + 1 FROM pg_depend d JOIN pg_rewrite r ON d.objid = r.oid JOIN pg_class cl ON r.ev_class = cl.oid JOIN pg_class c ON d.refobjid = c.oid JOIN pg_namespace n ON cl.relnamespace = n.oid JOIN pg_namespace nb ON c.relnamespace = nb.oid JOIN find_views fv ON fv.dependent_oid = c.oid WHERE d.deptype = 'n' AND d.classid='pg_rewrite'::regclass AND cl.relkind IN ('v', 'm') AND cl.oid <> c.oid ) SELECT DISTINCT fv.depth, fv.dependent_schema, fv.dependent_view, fv.dependent_oid, fv.source_schema, fv.source_table FROM find_views fv ORDER BY fv.depth; END; $$ LANGUAGE plpgsql;The following table describes the parameters.
Parameter
Description
depth
The dependency level of the dependency view on the intermediate view or the base table. The dependency level of dependency views that directly rely on a base table is 1.
dependent_schema
The schema name of the queried dependency view.
dependent_view
The name of the queried dependency view.
dependent_oid
The OID of the dependency view.
source_schema
The schema name of the base table or the view on which the dependency view relies.
source_table
The name of the base table or the view on which the dependency view relies.
Query the dependencies between the base tables and dependency views.
AS SELECT * FROM get_table_dependencies('public', 'sales'); depth | dependent_schema | dependent_view | dependent_oid | source_schema | source_table -------+------------------+----------------+---------------+---------------+-------------- 1 | public | all_info | 26635 | public | sales 1 | public | ps_info | 26644 | public | sales 2 | public | psc_info | 26648 | public | all_info 2 | public | psc_info | 26648 | public | ps_info (4 ROWS)
Limits on modifying a base table or a view
Limits on modifying a base table
If you change the data types of the columns in a base table that are referenced by a dependency view, the corresponding data types in the view cannot be recognized.
When you create a dependency view, the data type of each column in the view is recorded in the system tables. Therefore, if you change the data types of the columns in a base table that are referenced by the dependency view, the corresponding data types in the view cannot be recognized. Changing the data types of the columns in a base table that are not referenced by a dependency view does not affect the data types in the view.
If you modify the names of the columns in a base table that are referenced by a dependency view, the column names in the view remain unchanged.
When you create a dependency view, the system tables store the sequence number dependencies of the column names in the corresponding base table. Modifying the column names in the base table does not affect dependency judgment. Therefore, after you modify the column names of the base table, the column names of the dependency view remain unchanged. The sequence number relationships of the original column names are recorded in the system tables.
If you remove the columns from a base table that are referenced by a dependency view, the view is deleted.
If you add the CASCADE keyword to remove the columns from a base table, dependency views that reference the columns are deleted.
The following table describes more limits on performing DDL operations on a base table.
DDL statement | Affected | Remarks |
RENAME | No | When you query the definition of a dependency view, the names of the base table and intermediate views are updated. |
RENAME COLUMN | No | When you query the definition of a dependency view, the column names in the SELECT statement are updated, but the column names of the dependency view remain unchanged. |
RENAME CONSTRAINT | No | Renaming a constraint on a base table does not affect the dependency views that reference the base table. |
SET SCHEMA | No | When you query the definition of a dependency view, the schema information of the dependency view is automatically updated. |
ATTACH PARTITION | No | None |
DETACH PARTITION | No | None |
DROP COLUMN statement executed on columns referenced by a dependency view | Yes | If you add the CASCADE keyword to remove the columns from a base table that are referenced by a dependency view, the dependency view is deleted.
|
ALTER COLUMN TYPE | Yes | The following limits at the syntax level apply: You cannot change the data type of a column in a base table that is referenced by a view.
|
ALTER COLUMN SET DEFAULT | No | None |
ALTER COLUMN DROP DEFAULT | No | None |
ALTER COLUMN SET/DROP NOT NULL | No | None |
ALTER COLUMN ADD/SET/DROP GENERATED AS IDENTITY | No | The statements are related to auto-increment column settings of the base table but do not affect the dependency views. |
ALTER COLUMN SET STATISTICS | No | None |
ALTER COLUMN RESET/SET ( attribute_option = value) | No | None |
ALTER COLUMN SET STORAGE | No | None |
ADD table_constraint | No | None |
DROP table_constraint | No | None |
ADD table_constraint_using_index | No | None |
ENABLE TRIGGER | No | None |
ENABLE RULE | No | None |
INHERIT | No | None |
REDACTION POLICY | No | The effect of the redaction policy varies based on your permissions. |
Limits on modifying a view
The statements that can be used to modify a view are different from those that can be used to modify a base table. The ALTER statement can be used only to modify the DEFAULT value of a column of a view. The CREATE OR REPLACE statement is commonly used to re-create a view.
You cannot add columns to or remove columns from a view.
Adding a column to or removing a column from a view is equivalent to updating the definition of the view. The sequence number relationships of the columns in the base table change regardless of whether the added or removed column is referenced by a dependency view. This causes the dependencies recorded in the system tables to become invalid. Therefore, these operations are not allowed.
You can append columns to a view.
You can execute the CREATE OR REPLACE statement to append columns to a view. The appended columns do not affect the order of existing columns. You can still query the corresponding columns in views that rely on the view based on system table information. Therefore, this operation is allowed.
You cannot remove the last column of a view.
Removing the last column of a view is not allowed.
You cannot change the data type of a column in a view.
Changing the data type of a column in a view is not allowed.
The following table describes more limits on performing DDL operations on a view.
Schema change | Affected | Remarks |
Add an intermediate column | Yes | You cannot modify the definition of an intermediate view to add a column if specific views rely on the intermediate view. |
Append a column | No | None |
Remove a column | Yes | You cannot modify the definition of an intermediate view to remove a column if specific views rely on the intermediate view. |
Change the data type of a column that is referenced by dependency views | Yes | Changing the data type of a column that is not referenced by dependency views does not affect dependency views. |
Modify the name of a column | Yes | You cannot modify the names of the columns in an intermediate view. |
Modify a base table or a view
Procedure
Use the following statements to export DDL information related to a view based on your business requirements:
To change the data type of the b column in the public.test base table or intermediate view, execute the SELECT record_dependency_ddl('public','test','b') statement to export the DDL information of the related dependency views.
To remove the b column that is referenced by dependency views from the public.test base table, execute the SELECT record_dependency_ddl ('public','test','b') statement to export the DDL information of the related dependency views.
To insert a column in a position other than the end of an intermediate view, query all dependency views related to the view and execute the SELECT record_dependency_ddl('public','test','') statement to export the DDL information of the related dependency views.
To remove a column from an intermediate view, query all dependency views related to the view and execute the SELECT record_dependency_ddl('public','test','') statement to export the DDL information of the related dependency views.
To modify the name of a column in an intermediate view, query all dependency views related to the view and execute the SELECT record_dependency_ddl('public','test','') statement to export the DDL information of the related dependency views.
To view DROP statements and CREATE statements, query the temp_drop_dependency_ddl temporary table and the temp_create_dependency_ddl temporary table, respectively.
To record DDL statements, invoke the record_dependency_ddl() function. The information in the temporary tables is refreshed each time you invoke the record_dependency_ddl() function. The temporary tables exist only in the current session. If disconnection or database switchover occurs, the temporary tables are lost.
Execute DROP statements.
Execute the SELECT drop_dependency_table() statement to perform the DROP operation. If the DROP operation fails, you can view the temp_drop_dependency_ddl temporary table and manually perform the operation.
Change the data type of a column or add a column to the base table.
Use one of the following methods to execute the CREATE statement based on your business requirements:
To change the data type of the b column in the public.test base table or intermediate view, execute the SELECT create_dependency_table() statement to perform the CREATE operation. If the CREATE operation fails, you can view the temp_create_dependency_ddl temporary table and manually perform the operation.
To remove the b column that is referenced by dependency views from the public.test base table, view the temp_create_dependency_ddl temporary table, modify the DDL statements of dependency views that reference the column, and then manually perform the operation.
To insert a column in a position other than the end of an intermediate view, execute the SELECT create_dependency_table() statement to perform the CREATE operation. If the CREATE operation fails, view the temp_create_dependency_ddl temporary table and manually perform the operation.
To remove a column on which no views rely from an intermediate view, execute the SELECT create_dependency_table() statement to perform the CREATE operation. If the CREATE operation fails, view the temp_create_dependency_ddl temporary table and manually perform the operation.
To remove a column on which specific dependency views rely from an intermediate view, view the temp_create_dependency_ddl temporary table, modify the DDL information of the dependency views that reference the column, and then manually perform the operation.
To modify the name of a column on which no views rely in an intermediate view, execute the SELECT create_dependency_table() statement to perform the CREATE operation. If the CREATE operation fails, view the temp_create_dependency_ddl temporary table and manually perform the operation.
To modify the name of a column on which specific dependency views rely in an intermediate view, view the temp_create_dependency_ddl temporary table, modify the DDL information of the dependency views that reference the column, and then manually perform the operation.
Examples
To safely modify base tables and views, you must create a function for querying dependencies. The following figure shows an example on how to safely modify base tables and views.
Create a base table and three views.
CREATE TABLE test(a INT, b INT, c INT); CREATE VIEW v1 AS SELECT a, b FROM test; CREATE VIEW v2 AS SELECT b, c FROM test; CREATE VIEW v3 AS SELECT test.a, v2.c FROM test JOIN v2 ON test.b = v2.b; List OF relations Schema | Name | Type | Owner | Storage --------+------+-------+------------+--------- public | test | TABLE | adbpgadmin | heap public | v1 | VIEW | adbpgadmin | public | v2 | VIEW | adbpgadmin | public | v3 | VIEW | adbpgadmin | (4 ROWS)Create functions for modifying a base table or a view. These functions can be used to record the DDL statements of the dependency views and delete all dependency views before modifying the base table or the view. After the modification is complete, you can re-create the dependency views.
CREATE OR REPLACE FUNCTION public.record_dependency_ddl(schema_name TEXT, table_name TEXT, column_name TEXT) RETURNS VOID AS $$ DECLARE view_info RECORD; combine_ddl TEXT := ''; drop_ddl TEXT := '---- DROP VIEW ---' || E'\n'; drop_str TEXT; full_name TEXT; ddl_line TEXT; BEGIN CREATE TEMP TABLE IF NOT EXISTS temp_drop_dependency_ddl ( ddl_statement TEXT NOT NULL ); CREATE TEMP TABLE IF NOT EXISTS temp_create_dependency_ddl ( ddl_statement TEXT NOT NULL ); TRUNCATE temp_drop_dependency_ddl; TRUNCATE temp_create_dependency_ddl; IF column_name != '' THEN FOR view_info IN WITH RECURSIVE find_views AS ( SELECT DISTINCT n.nspname AS dependent_schema, cl.relname AS dependent_view, cl.oid AS dependent_oid, cl.relkind AS dependent_type, nb.nspname AS source_schema, c.relname AS source_table, 1 AS depth FROM pg_depend d JOIN pg_rewrite r ON d.objid = r.oid JOIN pg_class cl ON r.ev_class = cl.oid JOIN pg_class c ON d.refobjid = c.oid JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = d.refobjsubid JOIN pg_namespace n ON cl.relnamespace = n.oid JOIN pg_namespace nb ON c.relnamespace = nb.oid WHERE d.deptype = 'n' AND d.classid='pg_rewrite'::regclass AND (cl.relkind = 'v' OR cl.relkind = 'm') -- 'v' means the source object is a view AND nb.nspname = schema_name -- schema of the table AND c.relname = table_name -- name of the table AND a.attname = column_name -- name of the column UNION ALL SELECT n.nspname, cl.relname, cl.oid, cl.relkind, nb.nspname, c.relname, fv.depth + 1 FROM pg_depend d JOIN pg_rewrite r ON d.objid = r.oid JOIN pg_class cl ON r.ev_class = cl.oid JOIN pg_class c ON d.refobjid = c.oid JOIN pg_namespace n ON cl.relnamespace = n.oid JOIN pg_namespace nb ON c.relnamespace = nb.oid JOIN find_views fv ON fv.dependent_oid = c.oid WHERE d.deptype = 'n' AND d.classid='pg_rewrite'::regclass AND (cl.relkind = 'v' OR cl.relkind = 'm') -- 'v' means the source object is a view AND cl.oid <> c.oid ) SELECT DISTINCT depth, dependent_schema, dependent_view, dependent_type FROM find_views ORDER BY depth LOOP full_name := view_info.dependent_schema || '.' || view_info.dependent_view; FOR ddl_line IN SELECT dump_table_ddl(full_name) LOOP IF ddl_line LIKE 'CREATE MATERIALIZED VIEW%' THEN ddl_line := REPLACE(ddl_line, 'VIEW', 'VIEW IF NOT EXISTS'); ELSIF ddl_line LIKE 'CREATE VIEW%' THEN ddl_line := REPLACE(ddl_line, 'CREATE VIEW', 'CREATE OR REPLACE VIEW'); END IF; combine_ddl := combine_ddl || ddl_line || E'\n'; END LOOP; IF view_info.depth = 1 THEN drop_str := 'DROP VIEW '; IF view_info.dependent_type = 'm' THEN drop_str := 'DROP MATERIALIZED VIEW '; END IF; drop_ddl := drop_ddl || drop_str || 'IF EXISTS ' || full_name || ' CASCADE;' || E'\n'; END IF; END LOOP; ELSE FOR view_info IN WITH RECURSIVE find_views AS ( SELECT DISTINCT n.nspname AS dependent_schema, cl.relname AS dependent_view, cl.oid AS dependent_oid, cl.relkind AS dependent_type, nb.nspname AS source_schema, c.relname AS source_table, 1 AS depth FROM pg_depend d JOIN pg_rewrite r ON d.objid = r.oid JOIN pg_class cl ON r.ev_class = cl.oid JOIN pg_class c ON d.refobjid = c.oid JOIN pg_namespace n ON cl.relnamespace = n.oid JOIN pg_namespace nb ON c.relnamespace = nb.oid WHERE d.deptype = 'n' AND d.classid='pg_rewrite'::regclass AND (cl.relkind = 'v' OR cl.relkind = 'm') -- 'v' means the source object is a view AND nb.nspname = schema_name -- schema of the table AND c.relname = table_name -- name of the table UNION ALL SELECT n.nspname, cl.relname, cl.oid, cl.relkind, nb.nspname, c.relname, fv.depth + 1 FROM pg_depend d JOIN pg_rewrite r ON d.objid = r.oid JOIN pg_class cl ON r.ev_class = cl.oid JOIN pg_class c ON d.refobjid = c.oid JOIN pg_namespace n ON cl.relnamespace = n.oid JOIN pg_namespace nb ON c.relnamespace = nb.oid JOIN find_views fv ON fv.dependent_oid = c.oid WHERE d.deptype = 'n' AND d.classid='pg_rewrite'::regclass AND (cl.relkind = 'v' OR cl.relkind = 'm') -- 'v' means the source object is a view AND cl.oid <> c.oid ) SELECT DISTINCT depth, dependent_schema, dependent_view, dependent_type FROM find_views ORDER BY depth LOOP full_name := view_info.dependent_schema || '.' || view_info.dependent_view; FOR ddl_line IN SELECT dump_table_ddl(full_name) LOOP IF ddl_line LIKE 'CREATE MATERIALIZED VIEW%' THEN ddl_line := REPLACE(ddl_line, 'VIEW', 'VIEW IF NOT EXISTS'); ELSIF ddl_line LIKE 'CREATE VIEW%' THEN ddl_line := REPLACE(ddl_line, 'CREATE VIEW', 'CREATE OR REPLACE VIEW'); END IF; combine_ddl := combine_ddl || ddl_line || E'\n'; END LOOP; IF view_info.depth = 1 THEN drop_str := 'DROP VIEW '; IF view_info.dependent_type = 'm' THEN drop_str := 'DROP MATERIALIZED VIEW '; END IF; drop_ddl := drop_ddl || drop_str || 'IF EXISTS ' || full_name || ' CASCADE;' || E'\n'; END IF; END LOOP; END IF; INSERT INTO temp_drop_dependency_ddl VALUES (drop_ddl); INSERT INTO temp_create_dependency_ddl VALUES (combine_ddl); END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION public.drop_dependency_table() RETURNS VOID AS $$ DECLARE ddl_text TEXT; BEGIN FOR ddl_text IN SELECT ddl_statement FROM temp_drop_dependency_ddl LOOP EXECUTE ddl_text; END LOOP; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION public.create_dependency_table() RETURNS VOID AS $$ DECLARE ddl_text TEXT; BEGIN FOR ddl_text IN SELECT ddl_statement FROM temp_create_dependency_ddl LOOP EXECUTE ddl_text; END LOOP; SET search_path TO PUBLIC; END; $$ LANGUAGE plpgsql;NoteYou can use these functions only if the definitions of the dependency views remain unchanged. If you want to modify the dependency views at the same time, you must manually modify and re-create the dependency views based on the exported DDL statements.
Invoke the record_dependency_ddl() function to record the DROP and CREATE statements of dependency views. The DROP statements are stored in the temp_drop_dependency_ddl temporary table and the CREATE statements are stored in the temp_create_dependency_ddl temporary table. The following statements can be used to query views that rely on the b column in the public.test table:
SELECT record_dependency_ddl('public','test','b'); NOTICE: TABLE doesn't have 'DISTRIBUTED BY' clause -- Using column named 'ddl_statement' as the Greenplum Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution OF data. Make sure COLUMN(s) chosen ARE the optimal data distribution key TO minimize skew. NOTICE: TABLE doesn't have 'DISTRIBUTED BY' clause -- Using column named 'ddl_statement' as the Greenplum Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution OF data. Make sure COLUMN(s) chosen ARE the optimal data distribution key TO minimize skew. record_dependency_ddl ----------------------- (1 row) SELECT * FROM temp_drop_dependency_ddl ; ddl_statement ---------------------------------------- ---- DROP VIEW --- + DROP VIEW IF EXISTS public.v1 CASCADE;+ DROP VIEW IF EXISTS public.v2 CASCADE;+ DROP VIEW IF EXISTS public.v3 CASCADE;+ (1 ROW) SELECT * FROM temp_create_dependency_ddl ; ddl_statement ------------------------------------------------------------ -- + -- Greenplum Database database dump + -- + -- Dumped from database version 12.12 + -- Dumped by pg_dump version 12.12 + SET gp_default_storage_options = ''; + SET statement_timeout = 0; + SET lock_timeout = 0; + SET idle_in_transaction_session_timeout = 0; + SET idle_session_timeout = 0; + SET client_encoding = 'UTF8'; + SET standard_conforming_strings = on; + SELECT pg_catalog.set_config('search_path', '', false); + SET check_function_bodies = false; + SET xmloption = content; + SET client_min_messages = warning; + SET row_security = off; + -- + -- Name: v1; Type: VIEW; Schema: public; Owner: adbpgadmin+ -- + CREATE OR REPLACE VIEW public.v1 AS + SELECT test.a, + test.b + FROM public.test; + + ALTER TABLE public.v1 OWNER TO adbpgadmin; + + -- + -- Greenplum Database database dump complete + -- + + -- + -- Greenplum Database database dump + -- + -- Dumped from database version 12.12 + -- Dumped by pg_dump version 12.12 + SET gp_default_storage_options = ''; + SET statement_timeout = 0; + SET lock_timeout = 0; + SET idle_in_transaction_session_timeout = 0; + SET idle_session_timeout = 0; + SET client_encoding = 'UTF8'; + SET standard_conforming_strings = on; + SELECT pg_catalog.set_config('search_path', '', false); + SET check_function_bodies = false; + SET xmloption = content; + SET client_min_messages = warning; + SET row_security = off; + -- + -- Name: v2; Type: VIEW; Schema: public; Owner: adbpgadmin+ -- + CREATE OR REPLACE VIEW public.v2 AS + SELECT test.b, + test.c + FROM public.test; + + ALTER TABLE public.v2 OWNER TO adbpgadmin; + -- + -- Greenplum Database database dump complete + -- + + -- + -- Greenplum Database database dump + -- + -- Dumped from database version 12.12 + -- Dumped by pg_dump version 12.12 + SET gp_default_storage_options = ''; + SET statement_timeout = 0; + SET lock_timeout = 0; + SET idle_in_transaction_session_timeout = 0; + SET idle_session_timeout = 0; + SET client_encoding = 'UTF8'; + SET standard_conforming_strings = on; + SELECT pg_catalog.set_config('search_path', '', FALSE); + SET check_function_bodies = FALSE; + SET xmloption = content; + SET client_min_messages = warning; + SET row_security = off; + -- + -- Name: v3; Type: VIEW; Schema: public; Owner: adbpgadmin+ -- + CREATE OR REPLACE VIEW public.v3 AS + SELECT test.a, + v2.c + FROM (public.test + JOIN public.v2 ON ((test.b = v2.b))); + + ALTER TABLE public.v3 OWNER TO adbpgadmin; + -- + -- Greenplum Database database dump complete + -- + + -- + -- Greenplum Database database dump + -- Dumped from database version 12.12 + -- Dumped by pg_dump version 12.12 + SET gp_default_storage_options = ''; + SET statement_timeout = 0; + SET lock_timeout = 0; + SET idle_in_transaction_session_timeout = 0; + SET idle_session_timeout = 0; + SET client_encoding = 'UTF8'; + SET standard_conforming_strings = on; + SELECT pg_catalog.set_config('search_path', '', false); + SET check_function_bodies = false; + SET xmloption = content; + SET client_min_messages = warning; + SET row_security = off; + -- + -- Name: v3; Type: VIEW; Schema: public; Owner: adbpgadmin+ -- + CREATE OR REPLACE VIEW public.v3 AS + SELECT test.a, + v2.c + FROM (public.test + JOIN public.v2 ON ((test.b = v2.b))); + + ALTER TABLE public.v3 OWNER TO adbpgadmin; + -- + -- Greenplum Database database dump complete + -- + (1 ROW)Invoke the drop_dependency_table() function to delete all dependency views. After you modify the b column in the test base table, invoke the create_dependency_table() function to re-create the dependency views.
SELECT drop_dependency_table(); NOTICE: DROP cascades TO VIEW v3 NOTICE: VIEW "v3" does NOT exist, skipping drop_dependency_table ----------------------- (1 ROW) SELECT create_dependency_table(); create_dependency_table ------------------------- (1 ROW)