Latest news about Bitcoin and all cryptocurrencies. Your daily crypto news habit.
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
SELECT * FROM pg_locks WHERE NOT GRANTED;
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.
SELECT * FROM pg_locks WHERE relation = <relationID>;
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!
SELECT pg_cancel_backend(<relationID>);
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.
SELECT xact_start, query_start, backend_start, state_change, state FROM pg_stat_activity WHERE pid IN (<relationID>);
Mostly wrote this for my reference, but maybe it’ll help someone else too :)
Postgres: Idle queries and pg_locks was originally published in Hacker Noon on Medium, where people are continuing the conversation by highlighting and responding to this story.
Disclaimer
The views and opinions expressed in this article are solely those of the authors and do not reflect the views of Bitcoin Insider. Every investment and trading move involves risk - this is especially true for cryptocurrencies given their volatility. We strongly advise our readers to conduct their own research when making a decision.