Getting postgres unstuck when it seems to “hang” or be super slow on queries So, I was recently trying to insert 8 million rows into a table in my database (just another day in grad school) and noticed that the query was taking longer than usual. However, if I just selected the 8 million rows, it would execute pretty fast. The table I was inserting into had no triggers or indices, meaning something was blocking the insert. That combination of being able to do fast selects and not being able to do inserts gave me the impression that the table was “locked” by a transaction or something. This had happened to me before, when I had an iPython notebook open and hadn’t closed my connection, and terminated a query midway by crashing the web page. The last time, I knew what was causing the problem, so I simply closed the database connection in my iPython notebook and everything began working smoothly again. This time around, I had no clue what was causing the idle query. There are a gazillion things that connect to my database independently — a bunch of iPython notebooks to test models and ideas, scrapers that are constantly expanding the dataset and a web server through which I serve data for visualization to name a few. I needed a way to find this hanging query in my database, and then close it in my database. And it turns out that this is actually super simple! First, figure out the relation id for the table that is locked. One easy way to do this, is to run your query that seems to hang forever. Then run this query * pg_locks GRANTED; SELECT FROM WHERE NOT Since your query is hanging, it hasn’t been “granted” a lock and should show up on this list. Now copy the relation id of your table. Next, figure out what other locks are present on the same table. * pg_locks relation = &lt;relationID&gt;; SELECT FROM WHERE Now, be sure that you’re not killing the wrong query, or stopping some important process. Because, here’s the coolest part, you can simply kill the offending query with a single SQL statement! Just take the PID from the query above that is running (granted = true) and cancel it. pg_cancel_backend(&lt;pid previous query&gt;); SELECT from Before you do this, if you want to check some details of the query you’re going to kill, you can run this to get some sense of what they query was and when you began executing it. xact_start, query_start, backend_start, state_change, state pg_stat_activity pid (&lt;pid previous query&gt;); SELECT FROM WHERE IN from Mostly wrote this for my reference, but maybe it’ll help someone else too :)

BUNCH

Super

Too Long; Didn't Read

In your car, at home, or at work — Bosch technology shapes many areas of life.

Postgres: Idle queries and pg_locks

Too Long; Didn't Read

Companies Mentioned

Sanjay Guruprasad

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

Postgres: Idle queries and pg_locks

Too Long; Didn't Read

Companies Mentioned

Sanjay Guruprasad

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

RELATED STORIES