Take you to know the database view object, don't recognize it as a "table" next time

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

This article is shared from Huawei Cloud Community " GaussDB(DWS) Advanced SQL Database Object-View ", author: Wind of Dawn.

(1) View overview

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

A view is different from a table in that it is not physically real, but a virtual table. Only the definition of the view is stored in the database, and the data corresponding to the view is not stored. The data in the view is stored in its corresponding table. If the data in the table changes, the data queried from the view will also change accordingly. In this sense, the view is like a window through which you can see the data and changes in the database that are of interest to the user. Every time you view a view or reference a view, a query on the view is run.

Users can use the SELECT statement to query data from the view. For views that meet certain constraints, they can also use INSERT, UPDATE, DELETE, MERGE INTO and other statements to modify the data in the underlying table corresponding to the view. While providing convenient operation, the view can also guarantee the security of database data.

(B) the advantages of database views

1. Database views can simplify complex queries

The database view is defined by many SQL statements associated with the underlying tables. You can use the database view to hide the complexity of the underlying tables from end users and external applications. With database views, 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 database views to expose only non-sensitive data to specific user groups.

3. The database view provides an additional layer of security.

Security is an important part of any relational database management system, and database views provide additional security for the database management system. Database views allow the creation of read-only views to expose read-only data to specific users. Users can only retrieve data in the read-only view, but cannot update it.

4. The database view can define calculated columns.

There should be no calculated columns in the database table, but the database views support calculated columns. Suppose there are columns for the quantity of ordered products and the price of each product in the order table, but the order table defines a column to store the total sales of each order. If there is, such a database model is not a good design. In this case, you can create a column called total sales, which is calculated by multiplying the price of the product by the number of products ordered. When querying data from a database view, the data in the calculated column will be dynamically calculated.

5. Database views support application compatibility

Suppose there is a core database, and many applications are using it. In order to adapt to new business needs, it is possible to redesign the database, delete some tables and create several new tables, and modify the column names of the tables. Hope these changes affect previous applications. In this case, you can create a database view using the same table structure as the old table that has been deleted. The application can access the view to complete the previous functions, so there is no need to make any changes to the application.

(Three) the shortcomings of database views

In addition to the above advantages, the use of database views has the following disadvantages:

1. Performance may be poor

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

2. View's dependence on table structure

Since the view is created based on the base table of the database, whenever the structure of those tables associated with the view is changed, the view must also be changed.

(4) Syntax to create a view

To create a view, you need to use the CREATE VIEW statement, and its syntax format is as follows:

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

CREATE in the syntax means create, OR REPLACE is used to replace the view that has been created, TEMP or TEMPORARY means to create a temporary view, view_name is the name string to be created, column_name means the name of the attribute column, query means that the view provides rows and columns SELECT query statement or VALUES statement, the WITH clause can specify an optional parameter for the view. The currently supported parameter is security_barrier. This parameter should be used when the VIEW tries to provide row-level security.

The following is an example of the basic operation of the view:

--创建字段spcname为pg_default组成的视图。
test=# CREATE VIEW myView AS
    SELECT * FROM pg_tablespace WHERE spcname = 'pg_default';

--查看视图。
test=# SELECT * FROM myView ;

--删除视图myView。
test=# DROP VIEW myView;

(5) Updatable view

On GaussDB (DWS), when the view updateable parameter (enable_view_update) is turned on, 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:

• There can be only one ordinary table in the FROM statement of the view definition, not the system table, external table, dfs table, delta table, toast table, or error table.
• The view contains updatable columns, which are simple references to the updatable columns of the underlying table.
• The view definition cannot contain WITH, DISTINCT, GROUP BY, ORDER BY, FOR UPDATE, FOR SHARE, HAVING, TABLESAMPLE, LIMIT, OFFSET clauses.
• The view definition cannot contain UNION, INTERSECT, and EXCEPT set operations.
• The selection list of the view definition cannot contain aggregate functions, window functions, and functions that return sets.
• The view cannot have a trigger with an INSTEAD OF trigger time.
• View definitions cannot contain sub-links.
• View definitions cannot contain functions whose attributes are VOLATILE (functions whose value can be changed within a table scan)
• The view definition cannot alias the column where the distribution key of the table is located, or alias a common column as the name of the distribution key column.
• When the RETURNING clause is included in the view update operation, the columns in the view definition can only come from the underlying table.

If the updatable view definition contains a WHERE condition, the condition will restrict the UPDATE and DELETE statements to modify the rows on the underlying table. If the WHERE condition is no longer satisfied after the UPDATE statement changes the row, the view will not be able to be queried after the update. Similarly, if the INSERT command inserts data that does not meet the WHERE condition, it will not be able to be queried through the view after insertion. Users who perform insert, update, or delete on the view must have the corresponding insert, update, or delete permissions on the view and table.

The following 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

The following is an example of an updatable view that executes the 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)

(6) GaussDB (DWS) common system view

GaussDB (DWS) also provides many views to display the internal state of the database. The following views are often used when locating faults.

• pg_stat_activity: used to query the status of each Session 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 waiting events of each thread 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: used to display the IP 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)

If you want to know more about GuassDB (DWS), welcome to search "GaussDB DWS" on WeChat and follow the WeChat official account, and share with you the latest and most complete PB-level digital warehouse black technology. You can also get a lot of learning materials in the background~

Click to follow, and learn about Huawei Cloud's fresh technology for the first time~