Updated on 2025-05-29 GMT+08:00

CLEAN CONNECTION

Description

Clears the network connections between CNs and other specified database nodes (CNs or DNs). In force mode, the client connections to CNs or DNs are forcibly cleared. This function can clear idle or invalid connections of specified databases or users on CNs, and forcibly clear active connections.

Precautions

  • In non-force mode, this function only clears connections between database cluster nodes (CNs/DNs) and does not affect client connections.
  • In non-force mode, this function clears only idle and invalid connections cached on CNs. Normal connections that are being used are not cleared. In force mode, this function forcibly clears normal connections and client connections that are being used.
  • You can query the PG_STAT_GET_POOLER_STATUS() function to check the cache connection and verify the clearing effect.
  • You are advised to perform this operation only when the network connection of the database is abnormal.
  • In force mode, this function clears the connections of specified databases and users on DNs. If residual connections cannot be cleared on distributed DNs, O&M personnel are advised to use the pg_terminate_session function to clear invalid sessions.

Syntax

CLEAN CONNECTION 
    TO { COORDINATOR ( nodename [, ... ] ) | NODE ( nodename [, ... ] )| ALL [ CHECK ] [ FORCE ] }
    { FOR DATABASE dbname | TO USER username | 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 TO ALL. If this parameter is specified, all threads related to the specified dbname and username in the current CN receive the SIGTERM signal, the corresponding session is forcibly closed, the transaction is terminated, and the network connection is cleared. All threads related to the specified dnname or username on the DN receive the SIGTERM signal, and the corresponding sessions are forcibly closed.

  • COORDINATOR ( nodename ,nodename ... } ) | NODE ( nodename , nodename ... ) | ALL

    This command is used to delete the idle or invalid connections between the current CN and a specified node. There are three scenarios:

    • COORDINATOR: deletes the idle or invalid connections from the current CN to a specified CN.
    • NODE: deletes the idle or invalid connections from the current CN to a specified DN.
    • ALL: deletes the idle or invalid connections from the current CN to all nodes, including CNs and DNs.

    Value range: nodename is an existing node name.

  • dbname

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

    Value range: an existing database name

  • username

    Deletes connections to a specified user from the current CN. 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 between the current CN and all nodes of the test_clean_connection database.
-- If the FORCE parameter is not used, connections whose stat is not 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;