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:

premdb=# \dvS
               List of relations
 Schema |         Name         | Type |  Owner  
--------+----------------------+------+---------
 sys    | backup               | view | brumsby
 sys    | blade                | view | brumsby
 sys    | column               | view | brumsby
 sys    | database             | view | brumsby
...

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

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

Note that all users can see the list of system views, but regular users may not have access to the data they return. To grant access to the data in system views, use the GRANT command. For example, you can grant SELECT on all sys schema views or a specific view.

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, sys.table, or sys.table_info). Users who are not superusers must be logged into the database where a table resides to see any data about that table in a system view.

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.

The log retention time period, which determines the history that is retained in the sys.log_* views, is adjustable in the SMC. Go to Configure > Configuration Settings > Retention.