GaussDB (DWS) View Decoupling and Automatic Rebuilding

Scenario

GaussDB(DWS) uses object identifiers (OIDs) to store reference relationships between objects. When a view is defined, the OID of the database object on which the view depends is bound to it. No matter how the view name changes, the dependency does not change. If you modify some columns in the base table, an error will be reported because the columns are strongly bound some objects. If you want to delete a table column or the entire table, you need to use the cascade keyword to delete the associated views. After the table column is deleted or the table is re-created, you need to re-create the views of different levels one by one. This increases the workload and deteriorates the usability.

To solve this problem, GaussDB(DWS) 8.1.0 decouples views from their dependent base tables or other database objects (views, synonyms, functions, and table columns), so that these objects can be deleted independently. After the base table is rebuilt, you can run the ALTER VIEW view_name REBUILD command to rebuild the dependency. In 8.1.1, automatic rebuilding is implemented. Dependency relationships can be automatically rebuilt without being perceived. After automatic rebuilding is enabled, lock conflicts may occur. Therefore, you are not advised to enable automatic rebuilding.

Usage

1. Create a cluster on the management console. For details, see section Creating a Cluster.
2. Enable the GUC parameter view_independent.

   The GUC parameter view_independent controls whether to decouple a view from its objects. This parameter is disabled by default. You need to manually enable the parameter. To enable the view_independent parameter, log in to the management console and click the cluster name. On the displayed Cluster Details page, click the Parameters tab, search for view_independent, modify the parameter, and save the modification.

   

3. Use DAS to connect to a cluster. Locate the required cluster in the cluster list and click Log In in the Operation column. On the DAS page that is displayed, enter the username, database name, and password, and test the connection. If the connection is successful, log in to the cluster. For details, see Using DAS to Connect to a Cluster.

   

4. Create a sample table t1 and insert data into the table.

   1 2 3

   SET current_schema='public'; CREATE TABLE t1 (a int, b int, c char(10)) DISTRIBUTE BY HASH (a); INSERT INTO t1 VALUES(1,1,'a'),(2,2,'b');

5. Create view v1 that depends on table t1, and create view v11 that depends on view v1. Query view v11.

   1 2 3 4 5 6 7 8 9

   CREATE VIEW v1 AS SELECT a, b FROM t1; CREATE VIEW v11 AS SELECT a FROM v1; SELECT * FROM v11; a --- 1 2 (2 rows)

6. After table t1 is deleted, an error is reported when you query the view v11. However, the views still exist.
   GaussDB(DWS) provides the GS_VIEW_INVALID view to query all invalid views visible to the user. If the base table, function, or synonym that the view depends on is abnormal, the validtype column of the view is displayed as "invalid".

   1 2 3 4 5 6 7 8 9 10 11

   DROP TABLE t1; SELECT * FROM v11; ERROR: relation "public.t1" does not exist SELECT * FROM gs_view_invalid; oid | schemaname | viewname | viewowner | definition | validtype --------+------------+----------+-----------+-----------------------------+----------- 213563 | public | v1 | dbadmin | SELECT a, b FROM public.t1; | invalid 213567 | public | v11 | dbadmin | SELECT a FROM public.v1; | invalid (2 rows)

7. In a cluster of a version earlier than recreates table t1, the view is automatically recreated. The views are automatically refreshed only when they are used.

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

   CREATE TABLE t1 (a int, b int, c char(10)) DISTRIBUTE BY HASH (a); INSERT INTO t1 VALUES(1,1,'a'),(2,2,'b'); SELECT * from v1; a | b ---+--- 1 | 1 2 | 2 (2 rows) SELECT * FROM gs_view_invalid; oid | schemaname | viewname | viewowner | definition | validtype --------+------------+----------+-----------+--------------------------+----------- 213567 | public | v11 | dbadmin | SELECT a FROM public.v1; | invalid (1 row) SELECT * from v11; a --- 1 2 (2 rows) SELECT * FROM gs_view_invalid; oid | schemaname | viewname | viewowner | definition | validtype -----+------------+----------+-----------+------------+----------- (0 rows)