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

Querying System Catalogs

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 obtain 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 initial user.

GaussDB provides the following types of system catalogs and views:

  • PostgreSQL-compatible system catalogs and views

    These system catalogs and views have the prefix PG.

  • New system catalogs and views of GaussDB

    These system catalogs and views have the prefix GS.

  • Oracle-compatible system catalogs and views

    These system catalogs and views have the prefix ALL, DBA, USER, or PV.

Querying Database Tables

Create the following tables in the public schema:
gaussdb=#  CREATE TABLE public.search_table_t1(a int) distribute by hash(a);
CREATE TABLE
gaussdb=#  CREATE TABLE public.search_table_t2(b int) distribute by hash(b);
CREATE TABLE
gaussdb=#  CREATE TABLE public.search_table_t3(c int) distribute by hash(c);
CREATE TABLE
gaussdb=#  CREATE TABLE public.search_table_t4(d int) distribute by hash(d);
CREATE TABLE
gaussdb=#  CREATE TABLE public.search_table_t5(e int) distribute by hash(e);
CREATE TABLE
In the PG_TABLES system catalog, view the tables prefixed with search_table in the public schema.
1
gaussdb=#  SELECT distinct(tablename) FROM pg_tables WHERE SCHEMANAME = 'public' AND TABLENAME LIKE 'search_table%';
The result is as follows:
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
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:

  1. Set the parameter track_activities to on.

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

    Identify only active query statements.

    1
    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 function to end sessions based on the thread ID (corresponding to the PID in 2).

    1
    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
    
    1. When an initial user uses the PG_TERMINATE_BACKEND function to terminate the backend threads of active sessions, 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. If non-initial users do this operation, the message "The connection to the server was lost. Attempting reset: Failed." is returned. This is because only initial users can log in to the system in password-free mode.
    2. If the PG_TERMINATE_BACKEND 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.