Overview of System Catalogs and System Views

Updated on 2024-11-05 GMT+08:00

View PDF

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.

NOTICE:

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.
System catalogs do not support toast and cannot be stored across pages. If the size of a page in a system catalog is 8 KB, the length of each field must be less than 8 KB.

**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.

Parent topic: GaussDB(DWS) System Catalogs and Views

Previous topic: GaussDB(DWS) System Catalogs and Views

Next topic: System Catalogs

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot