Help Center/ GaussDB/ Centralized_8.x/ SQL Reference/ SQL Syntax/ C/ CLEAN CONNECTION
Updated on 2024-06-03 GMT+08:00
View PDF

CLEAN CONNECTION

Description

Clears database connections. You may use this statement to delete a specific user's connections to a specified database.

Precautions

  • GaussDB does not support specified nodes and supports only TO ALL.
  • This function can be used to clear the normal connections that are being used only in force mode.

Syntax

CLEAN CONNECTION
      TO { COORDINATOR ( nodename [, ... ] ) | NODE ( nodename [, ... ] )| ALL [ CHECK ] [ FORCE ] }
      [ FOR DATABASE dbname ]
      [ TO USER username ];

Parameters

  • CHECK

    This parameter can be specified only when the node list is specified as TO ALL. Setting this parameter will check whether a database is accessed by other sessions before its connections are cleared. If any sessions are detected before DROP DATABASE is executed, an error will be reported and the database will not be deleted.

  • FORCE

    This parameter can be specified only when the node list is specified as TO ALL. Setting this parameter will send SIGTERM signals to all the threads related to the specified dbname and username and forcibly shut them down.

  • COORDINATOR ( nodename [, ... ] ) | NODE ( nodename [, ... ] ) | ALL

    Only TO ALL is supported. This parameter must be specified. All specified connections on the node will be deleted.

  • dbname

    Deletes connections to a specified database. If this parameter is not specified, connections to all databases will be deleted.

    Value range: an existing database name

  • username

    Deletes connections of a specific user. If this parameter is not specified, connections of all users will be deleted.

    Value range: an existing username

Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
 
-- Create the test_clean_connection database.
gaussdb=# CREATE DATABASE test_clean_connection;

-- Create user jack.
gaussdb=# CREATE USER jack PASSWORD '********';

-- Log in to the database as the user jack in another session, and query the connection information in the view.
gaussdb=# SELECT datname,usename,application_name,waiting,state 
          FROM pg_stat_activity 
          WHERE datname = 'test_clean_connection';
        datname        | usename | application_name | waiting | state 
-----------------------+---------+------------------+---------+-------
 test_clean_connection | jack    | gsql             | f       | idle
(1 row)

-- If you directly delete the test_clean_connection database, the following error information is displayed:
gaussdb=# DROP DATABASE test_clean_connection;
ERROR:  Database "test_clean_connection" is being accessed by other users. You can stop all connections by command: "clean connection to all force for database XXXX;" or wait for the sessions to end by querying view: "pg_stat_activity".
DETAIL:  There is 1 other session using the database.

-- Delete the connections to all nodes for logging in to the test_clean_connection database.
-- If the FORCE parameter is not used, connections in other states cannot be deleted.
gaussdb=# CLEAN CONNECTION TO ALL FORCE FOR DATABASE test_clean_connection;

-- Query the connection for logging in to the test_clean_connection database.
gaussdb=# SELECT datname,usename,application_name,waiting,state
          FROM pg_stat_activity
          WHERE datname = 'test_clean_connection';
 datname | usename | application_name | waiting | state 
---------+---------+------------------+---------+-------
(0 rows)


-- Delete the test_clean_connection database.
gaussdb=# DROP DATABASE test_clean_connection;

-- Delete user jack.
gaussdb=# DROP USER jack;
Parent topic: C