Working with Databases-Querying System Catalogs
Tags: opengauss, database
Published: 2023-03-13 10:26:14

In addition to the created tables, a database contains many system catalogs. These system catalogs contain openGauss installation information and information about various queries and processes in openGauss. You can collect information about the database by querying system catalogs.

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. To query tables that are visible only to the initial user, log in as the user.

openGauss provides the following types of system catalogs and views:

**·** System catalogs and views inherited from PG

These system catalogs and views have the prefix **PG**.

**·** New system catalogs and views of openGauss

These system catalogs and views have the prefix **GS**.

**Querying Database Tables**
For example, you can run the following command to query the PG_TABLES system catalog for all tables in the public schema:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/l8g1160t41dykckyv2on.png)

Information similar to the following is displayed:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6cg5ys8m4csyqeptqiaf.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xhdcrl76uysq959cmwh4.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wllex54ucsdnjws3tqln.png)

**Viewing and Stopping the Running Query Statements**
You can view the running query statements in the PG_STAT_ACTIVITY view. You can use the following methods:

1. Set the parameter **track_activities** to **on**.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/a834drhe2ho9x2fz7foe.png)

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 performing queries, query status, and the corresponding PID which are connected to the running query statements:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/q7o5q01s1o915niivlsw.png)

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:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/e1ggk09ozt8r4vtbbtaa.png)

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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tsuo30z1mwjheiswj8gp.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jt9aavhmgh2ao00f9u3e.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g3t7s1hi3p9qf4fe1v1w.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/s9uwb0nc650rx0xdjwbi.png)