Help Center > > Developer Guide> Database Quick Start> Viewing a System Catalog

Viewing a System Catalog

Updated at: Mar 13, 2020 GMT+08:00

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

Querying Database Tables

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

1
SELECT distinct(tablename) FROM pg_tables WHERE SCHEMANAME = 'public'; 

Information similar to the following is displayed:

1
2
3
4
5
6
7
8
9
     tablename
-------------------
 err_hr_staffs
 test
 err_hr_staffs_ft3
 web_returns_p1
 mig_seq_table
 films4
(6 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
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
              usename              | usesysid | usecreatedb | usesuper | usecatupd | userepl |  passwd  | valbegin | valuntil |   respool
    | parent | spacelimit | useconfig
-----------------------------------+----------+-------------+----------+-----------+---------+----------+----------+----------+----------
----+--------+------------+-----------
dfc22b86afbd9a745668c3ecd0f15ec18 |    17107 | f           | f        | f         | f       | ******** |          |          | default_p
ool |      0 |            |
guest                             |    17103 | f           | f        | f         | f       | ******** |          |          | default_p
ool |      0 |            |
Ruby                              |       10 | t           | t        | t         | t       | ******** |          |          | default_p
ool |      0 |            |
dbadmin                           |    16404 | f           | f        | f         | f       | ******** |          |          | default_p
ool |      0 |            |
lily                              |    16482 | f           | f        | f         | f       | ******** |          |          | default_p
ool |      0 |            |
jack                              |    16478 | f           | f        | f         | f       | ******** |          |          | default_p
ool |      0 |            |


(6 rows)

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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
SELECT * FROM pg_user WHERE usesysid > 10;    
              usename              | usesysid | usecreatedb | usesuper | usecatupd | userepl |  passwd  | valbegin | valuntil |   respool
    | parent | spacelimit | useconfig
-----------------------------------+----------+-------------+----------+-----------+---------+----------+----------+----------+----------
----+--------+------------+-----------
dfc22b86afbd9a745668c3ecd0f15ec18 |    17107 | f           | f        | f         | f       | ******** |          |          | default_p
ool |      0 |            |
guest                             |    17103 | f           | f        | f         | f       | ******** |          |          | default_p
ool |      0 |            |
dbadmin                           |    16404 | f           | f        | f         | f       | ******** |          |          | default_p
ool |      0 |            |
lily                              |    16482 | f           | f        | f         | f       | ******** |          |          | default_p
ool |      0 |            |
jack                              |    16478 | f           | f        | f         | f       | ******** |          |          | default_p
ool |      0 |            |


(5 rows)

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.

    1
    SET track_activities = 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 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
    ----------+---------+--------+-----------------
     postgres | Ruby    | active | 140298793514752
     postgres | Ruby    | active | 140298718004992
     postgres | Ruby    | idle   | 140298650908416
     postgres | Ruby    | idle   | 140298625742592
     postgres | dbadmin | active | 140298575406848
    (5 rows)
    

    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:

    1
    SELECT datname, usename, state 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.

    1
    SELECT PG_TERMINATE_BACKEND(139834759993104);
    

    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
    

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

    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.
    

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?







Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel