Help Center/ GaussDB(DWS)/ Getting Started/ Database Quick Start/ Querying System Catalogs
Updated on 2023-08-23 GMT+08:00
View PDF
Share

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 of GaussDB(DWS). You can collect information about a database by querying system catalogs.

The description of each table in System Catalogs and System Views specifies whether the table is visible to all users or only to the initial user. To query tables that are visible only to the initial user, log in as the initial user.

Querying Database Tables

For example, you can run the following statement to 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)

Querying Database Users

You can run PG_USER to query all users in the database. User IDs (USESYSID) and permissions can also be queried.

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)

GaussDB(DWS) uses Ruby to perform routine management and O&M. You can add WHERE usesysid > 10 to the SELECT statement so that only specified usernames 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)

Querying User Attributes

PG_AUTHID can be used to view the attribute list of all users in the database.

1
 
SELECT * FROM pg_authid;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
 rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolauditadmin | rolsystemadmin | rolconnlimit | rolpassword  | rolvalidbegin | rolvaliduntil |  rolrespool  | roluseft | rolparentid | roltabspace | rolkind | rolnodegroup | roltempspace | rolspillspace | rolexcpdata | rolmonitoradmin | roloperatoradmin | rolpolicyadmin
---------+----------+------------+---------------+-------------+--------------+-------------+----------------+---------------+----------------+--------------+------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------
--------+---------------+--------------+----------+-------------+-------------+---------+--------------+--------------+---------------+-------------+-----------------+------------------+----------------
 dbadmin | f        | t          | f             | f           | f            | t           | f              | f             | t              |           -1 | sha256ce0ea617e7b7c0f2d38b00a12261b5e98ce18c218
89a30ebf410631c52f882d376141f31b47b67b0ceec9d10c931358140d276009bd8d19ac6a5647558c8b70d009986e774c2ba9563e42f4331629379d40720e4a3e0997c2b592833db778908md5eb0c1ffc5c76ef6272debb03f585b0b9ecdfecefade |
        |               | default_pool | f        |           0 |             | n       |              |              |               |             | f               | f                | f
 Ruby    | t        | t          | t             | t           | t            | t           | t              | t             | t              |           -1 | sha256d2058470eb2cead16d1d85a2b69207bc33e020bd4
530b67102c9c237dd2cb5d1ebae9d98c88ebf8f51950a333a4bb436488df40e645eb3d346af6c401c8fe5f83b2087349dcccf38fd1eb8ec828b27f28af4e5066549ba0bb6d249f82c664151md5417898ceaa6a205cb03d1b8df8fb92f7ecdfecefade |
        |               | default_pool | t        |           0 |             | n       |            0 |              |               |             | t               | t                | t
(2 rows)

Query the permissions of user joe.

1
 
SELECT * FROM pg_authid where rolname = 'joe';

Querying and Stopping the Running Query Statements

You can view the running query statements in the PG_STAT_ACTIVITY view.

  1. Set track_activities to on. 

    1
    		 
    SET track_activities = on;

    The database collects the running information about active queries only when this parameter is set to on.

  2. Query the information about running query statements, such as the user who runs the statements and the connected database, query status, and PID of the 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 state is idle, the connection is idle and requires a user to enter a command.

    To identify queries that are not idle, 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(pid);

    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 terminates 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. Information The connection to the server was lost. Attempting reset: Succeeded. is returned.

    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.

Parent topic: Database Quick Start