Querying System Catalogs

Querying Database Tables

gaussdb=# CREATE TABLE public.search_table_t1(a int);
CREATE TABLE
gaussdb=# CREATE TABLE public.search_table_t2(b int);
CREATE TABLE
gaussdb=# CREATE TABLE public.search_table_t3(c int);
CREATE TABLE
gaussdb=# CREATE TABLE public.search_table_t4(d int);
CREATE TABLE
gaussdb=# CREATE TABLE public.search_table_t5(e int);
CREATE TABLE

1

gaussdb=# SELECT DISTINCT(tablename) FROM pg_tables WHERE SCHEMANAME = 'public' AND TABLENAME LIKE 'search_table%';

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 use PG_USER to view the list of all users in the database, and view the user ID (USESYSID) and permissions.

1

gaussdb=# SELECT * FROM gs_user; 

 usename | usesysid | usecreatedb | usesuper | usecatupd | userepl |  passwd  | valbegin | valuntil |   respool    | parent | spacelimit | useconfig | no
degroup | tempspacelimit | spillspacelimit | usemonitoradmin | useoperatoradmin | usepolicyadmin
---------+----------+-------------+----------+-----------+---------+----------+----------+----------+--------------+--------+------------+-----------+---
--------+----------------+-----------------+-----------------+------------------+----------------
 omm     |       10 | t           | t        | t         | t       | ******** |          |          | default_pool |      0 |            |           |
        |                |                 | t               | t                | t

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.

   1	gaussdb=# SET track_activities = on;

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

2. View the running query statements. View the database names, users performing queries, query status, and the corresponding PID which are connected to the running query statements.

   1	gaussdb=# 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.

   View the query statements that are not in the idle state.

   1	gaussdb=# SELECT datname, usename, state, pid FROM pg_stat_activity WHERE state != 'idle';

3. To cancel queries that have been running for a long time, use the pg_terminate_backend(pid int) function to end sessions based on the thread ID (corresponding to the PID in 2).

   1	gaussdb=# SELECT PG_TERMINATE_BACKEND(140298793514752);

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

   1 2 3 4

   PG_TERMINATE_BACKEND ---------------------- t (1 row)

   If the following information 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

   

   • When the gsql client calls the pg_terminate_backend(pid int) function to terminate the backend thread of the current session, the connection behavior depends on the user identity.
     • Initial user: The client remains connected and is automatically reset (the message " The connection to the server was lost. Attempting reset: Succeeded." is displayed).
     • Non-initial user: The client connection is interrupted and fails to be reset (the message " The connection to the server was lost. Attempting reset: Failed." is displayed).

     Reason: The initial user has the password-free login permission, but common users do not have this permission.

   • If the pg_terminate_backend(pid int) function is used to end inactive backend threads, and the thread pool is enabled, idle sessions do not have thread IDs and cannot be terminated. In non-thread pool mode, terminated sessions are not automatically reconnected.

   1 2 3

   FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command The connection to the server was lost. Attempting reset: Succeeded.