Help Center/ GaussDB(DWS)/ Standard Data Warehouse (8.1.3.x)/ System Catalogs and System Views/ Overview of System Catalogs and System Views
Updated on 2023-10-31 GMT+08:00
View PDF

Overview of System Catalogs and System Views

System catalogs are used by GaussDB(DWS) to store structure metadata. They are a core component the GaussDB(DWS) database system and provide control information for the database system. These system catalogs contain cluster installation information and information about various queries and processes in GaussDB(DWS). You can collect information about the database by querying the system catalog.

System views provide ways to query system catalogs and internal database status. If some columns in one or more tables in a database are frequently searched for, an administrator can define a view for these columns, and then users can directly access these columns in the view without entering search criteria. A view is different from a basic table. It is only a virtual object rather than a physical one. A database only stores the definition of a view and does not store its data. The data is still stored in the original base table. If data in the base table changes, the data in the view changes accordingly. In this sense, a view is like a window through which users can know their interested data and data changes in the database. A view is triggered every time it is referenced.

In separation of duty, non-administrators have no permission to view system catalogs and views. In other scenarios, system catalogs and views are either visible only to administrators or visible to all users. System catalogs and views that require system administrator permissions can be queried only by system administrators.

Do not add, delete, or modify system catalogs or system views. Manual modification or damage to system catalogs or system views may cause system information inconsistency, system control exceptions, or even cluster unavailability.

Table 1 Common system catalogs

System Catalog

Description

PG_AM

Stores information about index access methods. There is one row for each index access method supported by the system.

PG_ATTRIBUTE

Stores information about table columns.

PG_AUTHID

Stores information about database authorization identifiers (roles). The concept of users is contained in that of roles. A user is actually a role whose rolcanlogin has been set. Any role, whether the rolcanlogin is set or not, can use other roles as members.

For a cluster, only one pg_authid exists which is not available for every database. It is accessible only to users with system administrator rights.

PG_CONSTRAINT

Stores check, primary key, unique, and foreign key constraints on tables.

PG_CLASS

Stores information about database objects and their relationships.

PG_DATABASE

Stores information about the available databases.

PG_DEPEND

Records dependencies among database objects. This information allows DROP commands to find which other objects must be dropped by DROP CASCADE or prevent dropping in the DROP RESTRICT case.

PG_PARTITION

Stores information about all partition tables (partitioned tables), partitions (table partitions), toast tables in partitions, and partition indexes (index partitions) in the database. Partitioned index information is not stored in the PG_PARTITION system catalog.

PG_FOREIGN_TABLE

Stores auxiliary information about foreign tables.

PG_INDEX

Stores part of the information about indexes. The rest is mostly stored in PG_CLASS.

PG_JOBS

Stores detailed information about scheduled tasks created by users. The scheduled task threads periodically poll the pg_jobs system catalog and are automatically executed at the schedule time. This catalog belongs to the Shared Relation category. All job records are visible to all databases.

PG_LARGEOBJECT

Stores data making up large objects. A large object is identified by an OID assigned when it is created. Each large object is broken into segments or "pages" small enough to be conveniently stored as rows in pg_largeobject. The amount of data per page is defined to be LOBLKSIZE. It is accessible only to users with system administrator rights.

PG_NAMESPACE

Stores namespaces, which are schema-related information.

PG_PROC

Stores information about functions or procedures.
Table 2 Common system views

System View

Description

GS_CLUSTER_RESOURCE_INFO

Displays the DN resource summary.

GS_SQL_COUNT

Displays statistics about the five types of statements (SELECT, INSERT, UPDATE, DELETE, and MERGE INTO) executed on the current node of the database, including the number of execution times, response time (the maximum, minimum, average, and total response time of the other four types of statements except the MERGE INTO statement, in microseconds), and the number of execution times of DDL, DML, and DCL statements.

PG_LOCKS

Stores information about locks held by opened transactions.

PG_ROLES

Provides information about database access roles.

PG_RULES

Provides access to query useful information about rewrite rules.

PG_TOTAL_USER_RESOURCE_INFO

Displays resource usage of all users. Only administrators can query this view. This view is valid only when se_workload_manager is set to on.

PG_USER

Provides information about users who access the database.

PG_VIEWS

Provides useful information about access to each view in the database.

PG_STAT_ACTIVITY

Displays information about the current user's queries. If you have the rights of an administrator or the preset role, you can view all information about user queries.

PG_TABLES

Provides useful information about access to each table in the database.

PLAN_TABLE

Displays plan information collected by EXPLAIN PLAN. Plan information is in a session-level life cycle. After the session exits, the data will be deleted. Data is isolated between sessions and between users.