Help Center/ GaussDB(DWS)/ Standard Data Warehouse (8.1.3.x)/ Defining Database Objects/ Viewing a System Catalog
Updated on 2023-10-31 GMT+08:00
View PDF

Viewing a System Catalog

In addition to the created tables, a database contains many system catalogs 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.

In System Catalogs and System Views, the description about each table indicates whether the table is visible to all users or only the initial user. Log in as the initial user to query tables that are visible only to the initial user.

Querying Database Tables

For example, query the PG_TABLES system catalog for all tables in the public schema.

Information similar to the following is displayed:

Viewing Database Users

You can run the PG_USER command to view the list of all users in the database, and view the user ID (USESYSID) and permissions.

GaussDB(DWS) uses Ruby to perform routine management and maintenance. You can add WHERE usesysid > 10 to the SELECT statement to filter queries so that only specified user names are displayed.

Viewing and Stopping the Running Query Statements

You can view the running query statements in the PG_STAT_ACTIVITY view. Do as follows:

  1. Set the parameter track_activities to on.

    The database collects the running information about active queries only if the parameter is set to on.

  2. View the running query statements. Run the following command to view the database names, users, query statuses, and PIDs of the running query statements:

    If the state column is idle, the connection is idle and requires a user to enter a command.

    To identify only active query statements, run the following command:

  3. To cancel queries that have been running for a long time, use the PG_TERMINATE_BACKEND function to end sessions based on the thread ID.

    If information similar to the following is displayed, the session is successfully terminated:

    If information similar to the following is displayed, a user has terminated the current session.

    If the PG_TERMINATE_BACKEND function is used to terminate the backend threads of the current session, the gsql client will be reconnected automatically rather than be logged out. The message "The connection to the server was lost." is returned. Attempting reset: Succeeded."