Improving Development Efficiency by Leveraging GaussDB(DWS)'s View Decoupling and Rebuilding Function

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 REBUILD command to rebuild the dependency. As a development, the version 8.1.1 supports automatic rebuilding. Dependencies can be automatically rebuilt without user awareness. After automatic rebuilding is enabled, lock conflicts may occur. Therefore, you are advised not to enable automatic rebuilding.

Usage

1. Create a cluster on the management console. For details, see Creating a DWS 2.0 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 the gsql client to connect to a GaussDB(DWS) database (using the password you defined when creating the cluster).

   1	gsql -d gaussdb -p 8000 -h 192.168.0.86 -U dbadmin -W password -r

   If the following information is displayed, the connection succeeded:

   1

   gaussdb=>

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. After the table t1 is recreated in a cluster of a version earlier than 8.3.0, 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)

8. After the table t1 is recreated for a cluster of version 8.3.0 or later, the view is not automatically recreated. The view can be automatically refreshed only after the ALTER VIEW REBUILD operation is performed.

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

   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 --------+------------+----------+-----------+-----------------------------+----------- 213563 | public | v1 | dbadmin | SELECT a, b FROM public.t1; | invalid 213567 | public | v11 | dbadmin | SELECT a FROM public.v1; | invalid (1 row) ALTER VIEW ONLY v1 REBUILD; SELECT * FROM gs_view_invalid; oid | schemaname | viewname | viewowner | definition | validtype --------+------------+----------+-----------+--------------------------+----------- 213567 | public | v11 | dbadmin | SELECT a FROM public.v1; | invalid (1 rows)