view system table
In addition to the created tables, the database also contains many system tables. These system tables contain openGauss installation information and information about various queries and processes running on openGauss. Information about the database can be gathered by querying the system tables.
Viewing System Tables and System Views The instructions for each table indicate whether the table is visible to all users or only to the initializing user. You must be logged in as the initializing user to query tables that are visible only to the initializing user.
openGauss provides the following types of system tables and views:
System tables and views inherited from PG
Such system tables and views have the PG prefix.
OpenGaussl new system tables and views
Such system tables and views have the GS prefix.
View the tables contained in the database
For example, view all tables contained in the public schema in the PG_TABLES system table.
SELECT distinct(tablename) FROM pg_tables WHERE SCHEMANAME = 'public';
The result looks like this:
tablename
err_hr_staffs
test
err_hr_staffs_ft3
web_returns_p1
mig_seq_table
films4
(6 rows)
View database users
Through PG_USER, you can view the list of all users in the database, and you can also view the user ID (USESYSID) and user permissions.
SELECT * FROM pg_user;
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 | |
omm | 10 | t | t | t | t | ******** | | | default_p
ool | 0 | |
omm | 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)
View and stop running queries
You can view the running query statement through the view PG_STAT_ACTIVITY. Methods as below:
Set the parameter track_activities to on.
SET track_activities = on;
When this parameter is on, the database system will collect the running information of the current active query.
Check out the running query. Take the database name connected to the running query statement, the user who executes the query, the query status, and the PID corresponding to the query as an example:
SELECT datname, usename, state,pid FROM pg_stat_activity;
datname | usename | state | pid
----------+---------+--------+-----------------
postgres | Ruby | active | 140298793514752
postgres | Ruby | active | 140298718004992
postgres | Ruby | idle | 140298650908416
postgres | Ruby | idle | 140298625742592
postgres | omm | active | 140298575406848
(5 rows)
If the state field is displayed as idle, it indicates that the connection is idle, waiting for the user to input commands.
If you only need to view non-idle query statements, use the following command to view:
SELECT datname, usename, state FROM pg_stat_activity WHERE state != 'idle';
If you need to cancel the query that runs for too long, use the PG_TERMINATE_BACKEND function to end the session according to the thread ID.
SELECT PG_TERMINATE_BACKEND(139834759993104);
If information similar to the following is displayed, the session ends successfully.
PG_TERMINATE_BACKEND
t
(1 row)
If information similar to the following is displayed, it means that the user has performed an operation to end the current session.
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
Note:
When the gsql client uses the PG_TERMINATE_BACKEND function to end the background thread of the current session, the client will not exit but automatically reconnect. That is, it will also return "The connection to the server was lost. Attempting reset: Succeeded."
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
The connection to the server was lost. Attempting reset: Succeeded.
Top comments (0)