Transcript

Earlier this week, I ran into an issue with a connection to a project's database. The connection should have been closed almost immediately but instead it was hanging around idly. The details of this aren't important. There are all sorts of scenarios where you may end up with an unwanted connection to your database. What we want to focus on is how to track down the connection and then kill it.

Now, there are all sorts of tools and approaches for tracking down and killing processes. What we are about to do is more fun though because it will all happen within a PSQL session.

To get started, let's create a connection to our database by, well, connecting to our database.

$ psql pgcasts

To get a clear picture of what is going on, I am going to start a transaction, create a table with some rows, and then select all rows from that table: 

begin;
create table franks_hats ( slogan varchar primary key );
insert into franks_hats values ('Ninja Expert'), ('Done Deal'), ('Double Cheese'), ('Arcade Champ');

select * from franks_hats;

Now that our idle connection is in place. Let's open up another connection to the database.

-# open new tmux pane
$ psql pgcasts

It is from this connection that we can track down and kill our idle connection. In addition to all of the things we typically think of Postgres doing for us, it also monitors server process activity. Postgres exposes this information through the pg_stat_activity view. One of the processes being monitored is our idle connection. With the right query, we can find that process.

So, what information does this view expose?

\d pg_stat_activity

The things that stand out to me are datname, the name of our database; pid, the process id; and query, the most recent query run on this connection.

Let's see what we find if we grab the pid and query columns for the current database: 

select pid, query from pg_stat_activity where datname = current_database();

We get two results and they are pretty telling. One is our current connection and the other is our idle connection.

With the pid of our idle process in hand, we can now terminate the connection using none other than the pg_terminate_backend(int) function.

select pg_terminate_backend(123);

The single row result of true tells us that the connection termination was successful. However, it doesn't look like anything happened to our other connection. Let's try rerunning our previous query from that connection again.

select * from franks_hats;

The connection was, in fact, terminated. In this case, psql is able to reconnect our session. Because the connection was terminated though, any transactions will have been rolled back and locks released.

We can try this query again and see that our transaction from earlier was indeed rolled back: 

select * from franks_hats;

As you may have noticed, there is a lot more information packed in the pg_stat_activity view, so feel free to explore it further. Also, keep in mind that pg_terminate_backend() and friends are system admin functions and you will need the appropriate permissions to run them successfully. Check out the links in the resources for more details.

Until next time, may your data be consistent and your queries performant.

Resources

• pg_stat_activity view
• pg_terminate_backend(pid int) and other system admin functions