PostgreSQL Learning Notes (iii) connection management

Use the following three SQL statements to cancel a running query and terminate the connection

(1) Isolate the active connection list and its process ID

SELECT * from Pg_stat_activity;

(2) Cancel the active query on the connection

Select Pg_cancel_backend (PROCID);

The operation does not terminate the connection itself

(3) Terminate the connection

Select Pg_terminate_backend (PROCID);

If you do not stop the executing statement on a connection and terminate the connection directly, these statements will also be stopped at this time. After the execution of step 2 above, the client application's suspend state is dismissed, that is, the client can re-execute the statement, some anxious users will be again at this time to execute the statement just terminated, which will cause the system to fall into the previous state. In order to avoid this situation, it is possible to use direct

The way to terminate the connection.

If you want to terminate all connections for a user at once, you can execute the following statements on versions 9.2 and later:

Select Pg_terminate_backend (PID) from pg_stat_activity where username= ' some_role ';

The following statements can be executed on versions prior to version 9.2:

Select Pg_terminate_backend (procpid) from pg_stat_activity where username= ' some_role ';

This article is from the "Corasql" blog, make sure to keep this source http://corasql.blog.51cto.com/5908329/1911927

PostgreSQL Learning Notes (iii) connection management