System Views

This section is a reference for a set of system views that capture information about resource utilization, inflight queries, active loads, and other operational statistics. You can query these views via ybsql or other SQL query tools.

Note: The information_schema tables and views are provided only for compatibility with Postgres-compliant tools. For database administration, Yellowbrick recommends using the views in the sys schema, as documented in this section. These views provide significant Yellowbrick-specific information that is not available under information_schema.

The system views belong to the sys schema. For example, run this command:

premdb=# set schema 'sys';
SET

Then list the views that belong to the schema:

yellowbrick=# \dvS
                List of relations
 Schema |         Name         | Type |  Owner   
--------+----------------------+------+----------
 sys    | alert                | view | ybdadmin
 sys    | backup               | view | ybdadmin
 sys    | backup_chain         | view | ybdadmin
 sys    | backup_legacy        | view | ybdadmin
 sys    | backup_snapshot      | view | ybdadmin
 sys    | blade                | view | ybdadmin
 sys    | column               | view | ybdadmin
 sys    | database             | view | ybdadmin
...

Alternatively, you can identify the schema in the query itself. For example:

premdb=# select * from sys.log_query;
...

No one can create and modify tables in the sys schema.

premdb=# create table table2(c1 int, c2 int) distribute on(c1) sort on(c2);
ERROR:  permission denied to create "sys.table2"
DETAIL:  System catalog modifications are currently disallowed.

You cannot combine user-defined tables and system tables or views in the same query. One exception to this rule is the sys.const table, which can be queried in conjunction with regular tables.

A log retention policy determines the amount of history that is retained in the following sys.log_* views:

The retention policy is adjustable in the SMC. Go to Configure > Configuration Settings > Retention.

Visibility of Data in System Views

Note that all users can see the list of system views:

premdb=# set schema 'sys';
SET
premdb=# \dS
                List of relations
 Schema |         Name         | Type |  Owner   
--------+----------------------+------+----------
 sys    | alert                | view | ybdadmin
 sys    | backup               | view | ybdadmin
...

However, regular users may not have access to the data they return. Superusers can see all rows for all databases. The visibility of system views for non-superusers depends on the privileges those users have been granted.

To grant access to the data in system views, use the GRANT command. For example, you can grant VIEW QUERY TEXT or TRACE QUERY privileges so that non-superusers can see rows for queries that they did not run themselves:

premdb=# create user tracer1 password '******';
CREATE ROLE
premdb=# grant trace query on system to tracer1;
GRANT

User tracer1 can now see all queries that are running or have been run on the current database but has visibility into all databases on the system. The user would have to connect to different databases to see the queries for each database.

The following table defines the visibility of a number of different system views for regular users (non-superusers):


System View(s)	Regular Users Can See:
`sys.query`, `sys.log_query`, `sys.query_explain`, `sys.query_analyze`	All queries on the current database if they have `TRACE QUERY` privilege either `ON SYSTEM` or on the current database (for example, `ON DATABASE devdb`). Their own queries on the current database if they do not have one of these privileges.
`sys.session`, `sys.log_session`	All sessions on the current database if they have `TRACE QUERY ON SYSTEM`, `TRACE QUERY` on the current database, or `CONTROL ANY SESSION ON SYSTEM` privileges. Their own sessions on the current database if they do not have one of these privileges.
`sys.database`	Databases on which they have one or more of the following privileges: Any privilege on the database (note that `CONNECT` is a default privilege on all databases when users are created) `CREATE DATABASE ON SYSTEM` `ALTER ANY DATABASE ON SYSTEM` `DROP ANY DATABASE ON SYSTEM`
`sys.schema`	Schemas in the current database if they have one or more of the following privileges: `USAGE ON SCHEMA` `ALTER ANY SCHEMA ON DATABASE` `DROP ANY SCHEMA ON DATABASE`
`sys.table`	Their own tables and any tables they have been granted access to in the current database. Users who have `SELECT` privilege on any column of a table will be able to see that table in any view that represents the table's metadata (such as `sys.column` or `sys.table_info`).
`sys.role`, `sys.user`	All roles in the system if they have one or more of the following privileges: `ALTER ANY ROLE ON SYSTEM` `DROP ANY ROLE ON SYSTEM` `CREATE ROLE ON SYSTEM` Specific roles that they have been given any privilege to see via `GRANT...ON ROLE`. Their own roles only if they do not have one of these privileges.