Take you to know the database view object. Don’t recognize it as a “table” next time

Time:2021-10-16

Summary:This paper introduces the database view object of gaussdb (DWS). We will discuss the advantages and disadvantages of using database view, and how to use system view to solve some problems.

This article is shared from Huawei cloud community《Gaussdb (DWS) SQL advanced database object view》Author: the wind of dawn.

(1) View overview

When the user is interested in the combination of some fields of 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. These columns are the data columns that users are interested in.

Unlike a table, a view is not a real table physically, but a virtual table. Only the definition of the view is stored in the database, not the data corresponding to the view. 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. In this sense, the view is like a window, through which you can see the data and changes that users are interested in in in the database. Each time a view is viewed or referenced, the query on the view is run.

You can use the select statement to query data from the view. For views that meet certain constraints, you can also use insert, update, delete, merge into and other statements to modify the data in the corresponding basic table of the view. View not only provides convenient operation, but also ensures the security of database data.

(2) Advantages of database view

1. Database view can simplify complex queries

The database view is defined by SQL statements associated with many underlying tables. You can use the database view to hide the complexity of the underlying tables from end users and external applications. Through the database view, you only need to use simple SQL statements and do not need to write complex statements with many connections.

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

If you do not want all users to be able to query sensitive data, you can use the database view 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. Database view provides additional security for database management system. Database view allows you to create a read-only view 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 calculation columns.

The database table should not have calculated columns, but the database view supports calculated columns. Suppose that the order table has columns for the quantity of ordered products and the price of each product, but the order table defines a column to store the total sales of 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 by multiplying the price of the product by the quantity of the ordered product. When querying data from the database view, the data of the calculated column will be calculated dynamically.

5. The database view supports application compatibility

Suppose there is a core database, which is used by many applications. In order to meet the new business requirements, it is possible to redesign the database, delete some tables, create several new tables, and modify the column names of the tables. At this time, we do not want these changes to affect the previous applications. In this case, you can create a database view using the same table structure as the old deleted table. The application can access the view to complete the previous functions, so there is no need to make any changes to the application.

(3) Disadvantages of database view

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

1. The performance may be poor

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

2. View dependency on table structure

Because views are 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, you need to use the create view statement. 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;

In the syntax, create means to create, or replace is used to replace the created view, temp or temporary means to create a temporary view, and view_ Name is the name string to create, column_ Name represents the name of the attribute column, query represents the select query statement or values statement that provides rows and columns for the view, and the with clause can specify an optional parameter for the view. The currently supported parameter is security_ Barrier, this parameter should be used when view attempts to provide row level security.

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

--Create the field spcname as PG_ Default view.
test=# CREATE VIEW myView AS
    SELECT * FROM pg_tablespace WHERE spcname = 'pg_default';

--View the view.
test=# SELECT * FROM myView ;

--Delete view MyView.
test=# DROP VIEW myView;

(5) Updatable view

On gaussdb (DWS), when the enable_view_update parameter 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:

• there can only be one common table in the from statement of view definition, which cannot be system table, appearance 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.
• a view definition cannot contain with, distinct, group by, order by, for update, for share, having, tablesample, limit, offset clauses.
• a view definition cannot contain Union, intersect, or except set operations.
• the selection list of view definitions cannot contain aggregation functions, window functions, and functions that return collections.
• the view cannot have a trigger whose trigger time is instead of.
• a view definition cannot contain child links.
• the view definition cannot contain functions whose attribute is volatile (functions whose value can be changed in one table scan)
• the view definition cannot alias the column where the distribution key of the table is located, or alias the ordinary column as the distribution key column name.
• when the return clause is included in the view update operation, the columns in the view definition can only come from the underlying table.

If an updatable view definition contains a where condition, the condition will restrict the update and delete statements from modifying rows on the underlying table. If the where condition is no longer satisfied after the update statement changes the row, it cannot be queried through the view after the update. Similarly, if the insert command inserts data that does not meet the where condition, it cannot be queried through the view after insertion. Users performing insert, update, or delete on views must have appropriate insert, update, or delete permissions on views and tables.

The following is an example of updating 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 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)

(6) Gaussdb (DWS) common system views

Gaussdb (DWS) also provides many views to show 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)

For more information on GuassDB (DWS), WeChat search is welcome to search for “GaussDB DWS” and pay attention to WeChat official account, and share with you the latest and most complete PB level digital black technology.

Click focus to learn about Huawei cloud’s new technologies for the first time~

Recommended Today

Swift advanced (XV) extension

The extension in swift is somewhat similar to the category in OC Extension can beenumeration、structural morphology、class、agreementAdd new features□ you can add methods, calculation attributes, subscripts, (convenient) initializers, nested types, protocols, etc What extensions can’t do:□ original functions cannot be overwritten□ you cannot add storage attributes or add attribute observers to existing attributes□ cannot add parent […]