CLEAN CONNECTION

Function

Clear 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

1. In non-force mode, this function only clears connections between database cluster nodes (CNs/DNs) and does not affect client connections.
2. This function clears only idle and invalid connections cached on the CN. Normal connections that are being used are not cleared.
3. This function takes effect only on CNs and does not take effect on DNs.
4. You can query the PG_STAT_GET_POOLER_STATUS() function to check the cache connection and verify the clearing effect.
5. You are advised to perform this operation only when the network connection of the database is abnormal.

Syntax

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

Parameter Description

• 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

-- Create user jack.
openGauss=# CREATE USER jack PASSWORD 'xxxxxxxxxx';

-- Delete the idle and invalid connections between the current CN and dn1 and dn2 in the template1 database.
openGauss=# CLEAN CONNECTION TO NODE (dn_6001_6002,dn_6003_6004) FOR DATABASE template1;

-- Delete the idle and invalid connections between the current CN and dn1 that are related to user jack.
openGauss=# CLEAN CONNECTION TO NODE (dn_6001_6002) TO USER jack;

-- Delete the connections between the current CN related to the postgres database and all nodes.
openGauss=# CLEAN CONNECTION TO ALL FORCE FOR DATABASE postgres;

-- Delete user jack.
openGauss=# DROP USER jack;