Querying 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. You can collect information about the database by querying system catalogs.
In System Catalogs and System Views, the description about each table specifies 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.
GaussDB provides the following types of system catalogs and views:
- PG-compatible system catalogs and views
- New system catalogs and views of GaussDB
These system catalogs and views have the prefix GS.
- System catalogs and views that are compatible with Oracle Database
These system catalogs and views have the prefix ALL, DBA, USER, or PV.
Querying Database Tables
openGauss=# CREATE TABLE public.search_table_t1(a int) distribute by hash(a); CREATE TABLE openGauss=# CREATE TABLE public.search_table_t2(b int) distribute by hash(b); CREATE TABLE openGauss=# CREATE TABLE public.search_table_t3(c int) distribute by hash(c); CREATE TABLE openGauss=# CREATE TABLE public.search_table_t4(d int) distribute by hash(d); CREATE TABLE openGauss=# CREATE TABLE public.search_table_t5(e int) distribute by hash(e); CREATE TABLE
1
|
openGauss=# SELECT distinct(tablename) FROM pg_tables WHERE SCHEMANAME = 'public' AND TABLENAME LIKE 'search_table%'; |
Information similar to the following is displayed:
1 2 3 4 5 6 7 8 |
tablename ----------------- search_table_t1 search_table_t2 search_table_t3 search_table_t4 search_table_t5 (5 rows) |
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.
1
|
SELECT * FROM pg_user; |
1 2 3 4 5 6 |
usename | usesysid | usecreatedb | usesuper | usecatupd | userepl | passwd | valbegin | valuntil | respool | parent | spacelimit | useconfig | nodegroup | tempspacelimit | spillspacelimit ---------+----------+-------------+----------+-----------+---------+----------+----------+----------+--------------+------ -------+------------+-----------+-----------+----------------+----------------- roach | 10 | t | t | t | t | ******** | | | default_pool | 0 | | | | | (1 row) |
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:
- Set the parameter track_activities to on.
1
SET track_activities = on;
The database collects the running information about active queries only if the parameter is set to on.
- 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:
1
SELECT datname, usename, state,pid FROM pg_stat_activity;
1 2 3 4 5 6 7 8
datname | usename | state | pid ----------+---------+--------+----------------- testdb | Ruby | active | 140298793514752 testdb | Ruby | active | 140298718004992 testdb | Ruby | idle | 140298650908416 testdb | Ruby | idle | 140298625742592 testdb | omm | active | 140298575406848 (5 rows)
If the state column is idle, the connection is idle and requires a user to enter a command.
To identify non-idle query statements, run the following command:
1
SELECT datname, usename, state, pid FROM pg_stat_activity WHERE state != 'idle';
- 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 (corresponding to the PID in Step 2).
1
SELECT PG_TERMINATE_BACKEND(140298793514752);
If information similar to the following is displayed, the session is successfully terminated:
1 2 3 4
PG_TERMINATE_BACKEND ---------------------- t (1 row)
If information similar to the following is displayed, a user has terminated the current session:1 2
FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command
1. If the PG_TERMINATE_BACKEND function is used to terminate the backend threads of the current session and the user is an initial user, the gsql client is reconnected automatically rather than be logged out. The message "The connection to the server was lost. Attempting reset: Succeeded." is returned. Otherwise, the client fails to be reconnected and the error message "The connection to the server was lost. Attempting reset: Failed." is returned because only the initial user can use password-free login.
2. If the PG_TERMINATE_BACKEND function is used to terminate inactive backend threads and the thread pool is opened, idle sessions do not have thread IDs and cannot be ended. In non-thread pool mode, ended sessions are not automatically reconnected.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot