Updated May 19, 2023
Definition of PostgreSQL kill query.
PostgreSQL provides a kill query or cancels query facility to the user. Sometimes what happens is we need to cancel or terminate the connection by client or user because we badly access the data or perform irrelevant tasks. So at that time, we need to kill the query or terminate them. For kill a query or session, we need a process id with the help of a process id, we can kill the query or session. PostgreSQL is an open-source tool that can see running queries or sessions, or we can say that it monitors all activity by using some commands.
Syntax:
select pg_cancel_backend[<process id>]Explanation:
We use the select clause with the pg_cancle_backend command in the above syntax. When we need to kill a query, at that time, we require a process id to kill the query or session, so in the above syntax, we use process id with command.
Select pg_terminate_backend[<process id >]Explanation:
We use the terminate command in the above syntax to kill the query. The main difference between pg_cancle_backend and pg_cancle_terminate is that in the first syntax, we just kill the query, but in the second syntax, we kill the query as well as connect with the help of process id we can also call as hard way to kill.
How kill query works in PostgreSQL?
Kill query in PostgreSQL works in different stages. First, we need to identify the all running query using the following statement.
pg_stat_activityExplanation:
In the above statement, we use pg_stat_activity to see all running processes on the Postgres server as well as it also shows all database connections to correspond to the Postgres server.
After that, kill specific query by using process is, so how we can get the process id answer of this question is that when we run pg_stat_activity statement, it shows all details with respect to the query. After getting the process id, we can use any one of the syntaxes to kill the query, as shown in the above syntax.
Examples
Let us discuss examples of the PostgreSQL kill query.
Example #1
First, we need to see all running processes in the PostgreSQL server when we use the following statement as follows.
select * from pg_stat_activity;
Explanation:
In the above statement, we use a select clause with the pg_stat_activity command, when we run this statement, it shows some details as shown in the snapshot. Illustrate the final result of the above statement by using the following snapshot.
The above snapshot shows some details. Let’s see what is the meaning of the above column name as follows.
- datname: The datname means database name such as Postgres and shape as shown above snapshot.
- pid: The pid means process id on the Postgres server, as shown in the above snapshot.
- username: The username means the user name on postgre server.
- client_addr: The client _addr means client address and client address shown on the server by using the client IP address with the help of the IP address, we can make connections between the client and server.
- client_port: The client _port column shows all clients’ port numbers available on the postgre server. The port number means which type of protocol we use to establish the connection.
- xact_start: The xact_start means the start time transaction when we do not need the start time of the query to kill the query.
- backend_start: The backend _start column is used to show the time of the process that started, which means it shows when the client is connected.
- query_start: The query_start shows the time of when query execution is started.
- state: The state column is used to show the current state of the transaction, which means active or idle.
- query: The query column is used to show all executed queries.
Suppose we need to show all non-idle connections or queries on the server at that time we use the following statement as follows.
select * from pg_stat_activity where state !='idle';
select * from pg_stat_activity where state ='active';Explanation:
In the first example, we show all idle query or connection, but in this example, we show all non-idle connection or query by using the above two states in which that we use the select and where clause with pg_stat_acitvity with an idle option in the second statement we use the active command to show all non-idle query or connection. Illustrate the final result of the above statement by using the following snapshot.
Now let’s see how we can kill the query or connection by using the following example as follows.
Example #2
select pg_cancel_backend(1840);Explanation:
Suppose a user needs to kill a particular process on a server, so at that time, we can use the above example in which we use the pg_cancle_backend statement to kill process id (1840). Illustrate the final result of the above statement by using the following snapshot.
Now we can see the result of the above statement by executing the following statement as follows.
select * from pg_stat_activity;Explanation:
The above statement shows all running queries or connections. Illustrate the final result of the above statement by using the following snapshot.
Example #3
Suppose users need to kill the query as well as the connection at that time we can use the following statement as follows.
select pg_terminate_backend(4036);Explanation:
In the above example, we select a clause with pg_terminate_backend command in which we provide process id to kill the query as well as connection from the server. Illustrate the final result of the above statement by using the following snapshot.
After the execution of the above statement, we can see the result as follows by running the pg_stat_activity command. Illustrate the final result of the above statement by using the following snapshot.
Kill more than one Query at a time.
Suppose users need to kill multiple queries or connections at a time so at that time, we can use the following syntax to kill multiple queries or connections as follows.
select pid, pg_cancel_backend(process_id) from pg_stat_activity where pid IN [<process_id1>, <process_id2>];Explanation:
With the above statement’s help, we can kill multiple processes at a time. We also have a function to kill queries from a particular table as follows.
select pid, pg_cancel_backend(pid) from pg_stat_activity where query ilike '%Specific table_Name%';Explanation:
In the above statement, we use a like operator to select a specific table with a process id to kill the query.
Conclusion
We hope you understand the PostgreSQL kill query from this article. The above article taught us the basic syntax of the kill query. We have also learned how we can implement them in PostgreSQL with different examples of each operation. From this article, we have learned how we can handle kill query in PostgreSQL.
Recommended Articles
We hope that this EDUCBA information on “PostgreSQL kill query” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
