Help Center/ GaussDB/ Developer Guide(Distributed_8.x)/ SQL Reference/ SQL Syntax/ C/ CLEAN CONNECTION
Updated on 2024-08-20 GMT+08:00
View PDF
Share

CLEAN CONNECTION

Description

Clears idle or invalid network connections between the current CN and other specified CNs or DNs. This statement is used to clear the specified database and idle or invalid connections of a specified user cached in the current CN on a specified CN.

Precautions

  • In non-force mode, this function only clears connections between database cluster nodes (CNs/DNs) and does not affect client connections.
  • This function clears only idle and invalid connections cached on the CN. Normal connections that are being used are not cleared.
  • This function takes effect only on CNs and does not take effect on DNs.
  • 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.
  • 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.

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

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

    • COORDINATOR: Delete the idle or invalid connections from the current CN to a specified CN.
    • NODE: Delete the idle or invalid connections from the current CN to a specified DN.
    • ALL: Delete 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;
Parent topic: C