Abstract: This article introduces GaussDB(DWS) database view objects. We will discuss the advantages and disadvantages of using database view and how to solve some problems using system view.

This document is shared with “GaussDB(DWS) SQL Advanced Database Object-View” in Huawei cloud community by The wind of Dawn.

(I) View Overview

When a user is interested in some combination of fields from one or more tables in the database, but does not want to type these queries every time, the user can define a view to solve the problem. The columns in the view can come from different columns in the table, which are the data columns that the user is interested in.

A view, unlike a table, is not physically real, but a virtual table. The database stores only the definition of the view, not the corresponding data of the view. The data in the view is stored in the corresponding table. If the data in the table changes, the data queried from the view also changes. In this sense, a view is like a window through which users can see the data and changes in the database that are of interest to them. Each time a view is viewed or referenced, a query is run on the view.

You can use the SELECT statement to query data from a view. For a view that meets certain constraints, you can also use the INSERT, UPDATE, DELETE, and MERGE INTO statements to modify the data in the corresponding basic table. Views not only facilitate operation, but also ensure database data security.

(2) Advantages of database view

1. Database views simplify complex queries

Database views are defined by SQL statements associated with many underlying tables and can be used to hide the complexity of the underlying tables from end users and external applications. With the database view, you only need to use simple SQL statements instead of writing complex statements with many connections.

2. Database views help restrict data access to specific users.

If you don’t want all users to be able to query sensitive data, you can use the database view to expose non-sensitive data only to specific groups of users.

3. Database views provide an additional layer of security.

Security is an important part of any relational database management system, and database views provide additional security for database management systems. Database views allow you to create read-only views to expose read-only data to a specific user, who can only retrieve data in a read-only view, but not update it.

4. The database view can define computed columns.

There should not be computed columns in database tables, but database views support computed columns. Suppose that the order table has columns for the quantity of ordered products and the price of each item, but the order table defines a column to store the total sales for each order. If so, such a database schema is not a good design. In this case, you can create a column called total sales, which is calculated as the price of the product multiplied by the number of products ordered. When the data is queried from the database view, the computed column data is evaluated dynamically.

5. The database view supports application compatibility

Suppose you have a core database that is being used by many applications. To accommodate new business requirements, you might redesign the database, dropping some tables and creating several new tables, changing the column names of the tables, and not wanting those changes to affect the previous application. In this case, the database view can be created using the same table structure as the old deleted table. The application can access the view to perform the previous functions without making any changes to the application.

(3) Disadvantages of database view

In addition to the advantages mentioned above, using a database view has the following disadvantages:

1. Performance may be poor

Querying data from database views can be slow, especially if views are created based on other views.

2. View dependency on table structure

Because the view is created from the underlying tables of the database, you must also change the view whenever you change the structure of those tables associated with the view.

(4) Syntax for creating views

To CREATE a VIEW, use the CREATE VIEW statement, which has the following syntax:

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW view_name [ ( column_name [, ...] ) ]
    [ WITH ( {view_option_name [= view_option_value]} [, ... ] ) ]
    AS query;
Copy the code

In the syntax, CREATE means CREATE, OR REPLACE is used to REPLACE an already created view, TEMPOR TEMPORARY means CREATE a TEMPORARY view, view_name is the name string to be created, column_name is the name of the attribute column, Query represents a SELECT query or VALUES statement that provides rows and columns for the VIEW. The WITH clause can specify an optional parameter for the VIEW, currently supported as security_barrier, which should be used when the VIEW attempts to provide row-level security.

Here is an example of a view’s basic operation:

Create a view with fields SPCname as pg_default. test=# CREATE VIEW myView AS SELECT * FROM pg_tablespace WHERE spcname = 'pg_default'; -- View the view. test=# SELECT * FROM myView ; Delete myView. test=# DROP VIEW myView;Copy the code

(v) Updatable view

On GaussDB(DWS), after enable_view_UPDATE is enabled, the system allows simple views to be updated using INSERT, UPDATE, DELETE, and MERGE INTO statements. Views that meet all the following conditions can be updated:

  • The FROM statement defined by a view can have only one normal table. It cannot be the system table, appearance table, DFS table, Delta table, toast table, or error table.

  • Views contain updatable columns that are simple references to updatable columns of the underlying table.

  • The view definition cannot contain WITH, DISTINCT, GROUP BY, ORDER BY, FOR UPDATE, FOR SHARE, HAVING, TABLESAMPLE, LIMIT, or OFFSET clauses.

  • View definitions cannot contain UNION, INTERSECT, EXCEPT collection operations.

  • The selection list defined by a view cannot contain aggregate functions, window functions, or functions that return collections.

  • There can be no triggers on the view that fire INSTEAD OF.

  • View definitions cannot contain child links.

  • View definitions cannot contain functions whose property is VOLATILE (functions whose value can change within a table scan)

  • View definitions cannot alias distribution key columns of a table or ordinary columns to distribution key column names.

  • Columns in a view definition can only come from the underlying table when a VIEW update operation includes a RETURNING clause.

If the updatable view definition contains a WHERE condition, that condition restricts the UPDATE and DELETE statements from modifying rows on the underlying table. If the UPDATE statement does not satisfy the WHERE condition after changing the row, the UPDATE cannot be queried through the view. Similarly, if the INSERT command inserts data that does not meet the WHERE criteria, it will not be available through the view after the INSERT. Users who perform inserts, updates, or deletes on views must have corresponding insert, update, or delete permissions on views and tables.

Here is an example of an update operation on an updatable view:

test=# create view v1 as select * from t1;
CREATE VIEW
test=# insert into v1 values(1, 2, 3);
INSERT 0 1
test=# delete from v1 where a = 1;
DELETE 3
test=# update v1 set b = 100 where a = 2;
UPDATE 2
test=# delete from v1 where a = 2;
DELETE 2
Copy the code

The following is an example of an updatable view executing a MERGE INTO statement:

test=# CREATE TABLE products(product_id INTEGER, product_name VARCHAR2(60), category VARCHAR2(60)); NOTICE: The 'DISTRIBUTE BY' clause is not specified. Using 'product_id' as the distribution column by default. HINT: Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column. CREATE TABLE test=# CREATE VIEW products_view AS SELECT * FROM products; CREATE VIEW test=# CREATE TABLE newproducts(product_id INTEGER, product_name VARCHAR2(60), category VARCHAR2(60)); NOTICE: The 'DISTRIBUTE BY' clause is not specified. Using 'product_id' as the distribution column by default. HINT: Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column. CREATE TABLE test=# CREATE VIEW newproducts_view AS SELECT * FROM newproducts; CREATE VIEW test=# INSERT INTO products_view VALUES (1501, 'vivitar 35mm', 'electrncs'); INSERT 0 1 test=# INSERT INTO products_view VALUES (1502, 'olympus is50', 'electrncs'); INSERT 0 1 test=# INSERT INTO products_view VALUES (1600, 'play gym', 'toys'); INSERT 0 1 test=# INSERT INTO products_view VALUES (1601, 'lamaze', 'toys'); INSERT 0 1 test=# INSERT INTO products_view VALUES (1666, 'harry potter', 'dvd'); INSERT 0 1 test=# INSERT INTO newproducts_view VALUES (1502, 'olympus camera', 'electrncs'); INSERT 0 1 test=# INSERT INTO newproducts_view VALUES (1601, 'lamaze', 'toys'); INSERT 0 1 test=# INSERT INTO newproducts_view VALUES (1666, 'harry potter', 'toys'); INSERT 0 1 test=# INSERT INTO newproducts_view VALUES (1700, 'wait interface', 'books'); INSERT 0 1 MERGE INTO products_view p USING newproducts_view np ON (p.product_id = np.product_id) WHEN MATCHED THEN UPDATE SET p.product_name = np.product_name, p.category = np.category WHERE p.product_name ! = 'play gym' WHEN NOT MATCHED THEN INSERT VALUES (np.product_id, np.product_name, np.category) WHERE np.category = 'books'; MERGE 4 test=# SELECT * FROM products_view ORDER BY 1; product_id | product_name | category ------------+----------------+----------- 1501 | vivitar 35mm | electrncs 1502 | olympus camera | electrncs 1600 | play gym | toys 1601 | lamaze | toys 1666 | harry potter | toys 1700 | wait interface | books (6 rows)Copy the code

(6) Common system view of GaussDB(DWS)

GaussDB(DWS) also provides many views to display the internal status of the database. The following views are frequently used to locate faults.

  • Pg_stat_activity: used to query the status of sessions on the current instance

    test=# select datid, pid, application_name, query_id, query from pg_stat_activity; datid | pid | application_name | query_id | query ——-+—————–+——————–+——————–+—————————————————- ————————- 14950 | 139706178189056 | JobScheduler | 0 | 14950 | 139706093266688 | WDRSnapshot | 0 | 14950 | 139706040301312 | workload | 100768041662414941 | WLM fetch collect info from data nodes 14950 | 139705995208448 | CalculateSpaceInfo | 0 | 14950 | 139705978427136 | WorkloadMonitor | 100768041662414940 | WLM monitor update and verify local info 14950 | 139705953277696 | WLMArbiter | 0 | WLM arbiter sync info by CCN and CNs 16390 | 139705917097728 | gsql | 100768041662414942 | select datid, pid, application_name, query_id, query from pg_stat_activity; (7 rows)

  • Pg_thread_wait_status: Used to query the wait events of individual threads on the instance

    test=# select * from pg_thread_wait_status; node_name | db_name | thread_name | query_id | tid | lwtid | ptid | tlevel | smpid | wait_status | wait_event ————–+———-+——————–+——————–+—————–+——–+——+——–+——-+- ————+———— coordinator1 | postgres | JobScheduler | 0 | 139706178189056 | 128830 | | 0 | 0 | none | coordinator1 | postgres | WDRSnapshot | 0 | 139706093266688 | 128834 | | 0 | 0 | none | coordinator1 | postgres | workload | 100768041662415325 | 139706040301312 | 128837 | | 0 | 0 | none | coordinator1 | postgres | CalculateSpaceInfo | 0 | 139705995208448 | 128838 | | 0 | 0 | none | coordinator1 | postgres | WorkloadMonitor | 100768041662415251 | 139705978427136 | 128839 | | 0 | 0 | none | coordinator1 | postgres | WLMArbiter | 0 | 139705953277696 | 128840 | | 0 | 0 | none | coordinator1 | test | gsql | 100768041662415326 | 139705917097728 | 129109 | | 0 | 0 | none | coordinator1 | | Background writer | 0 | 139706242688768 | 128826 | | 0 | 0 | none | coordinator1 | | CheckPointer | 0 | 139706262091520 | 128825 | | 0 | 0 | none | coordinator1 | | Wal Writer | 0 | 139706225907456 | 128827 | | 0 | 0 | none | coordinator1 | | TwoPhase Cleaner | 0 | 139706076485376 | 128835 | | 0 | 0 | none | coordinator1 | | LWLock Monitor | 0 | 139706057082624 | 128836 | | 0 | 0 | none | (12 rows)

  • Pg_locks: used to query the lock status on the current instance

    test=# select locktype, database, relation, pid, mode from pg_locks; locktype | database | relation | pid | mode ————+———-+———-+—————–+—————– relation | 16390 | 11800 | 139705917097728 | AccessShareLock virtualxid | | | 139705917097728 | ExclusiveLock virtualxid | | | 139705953277696 | ExclusiveLock virtualxid | | | 139705978427136 | ExclusiveLock virtualxid | | | 139706040301312 | ExclusiveLock (5 rows)

  • Pgxc_node: displays the IP addresses and port numbers of all instances in the cluster

    test=# select node_name, node_type, node_port, node_host from pgxc_node; node_name | node_type | node_port | node_host ————–+———–+———–+———– coordinator1 | C | 56200 | localhost datanode1 | D | 56201 | localhost datanode2 | D | 56202 | localhost datanode3 | D | 56203 | localhost datanode4 | D | 56204 | localhost (5 rows)

For more information about GuassDB(DWS), please search “GaussDB DWS” on wechat to follow the wechat public account, and share with you the latest and most complete PB series warehouse black technology, the background can also obtain many learning materials oh ~

Click to follow, the first time to learn about Huawei cloud fresh technology ~