| | |, count(case when r.time_search is null then 1 end) as repos_unsearched_count +| | | | select count(r.id) as repos_count +| relname = 'search_hit' ) Īnd get something like this: pid | state | usename | query | query_start I can even see the queries that have cause those locks if I take a look at the pg_stat_activity table, filtering by those pids: select pid, state, usename, query, query_start from pg_stat_activity where pid in ( select pid from pg_locks l join pg_class t on l. I'm probably doing some of the silly stuff that I mentioned above. These are the things that have created the locks on that table. relname = 'search_hit' Īnd get: pid - 11337 16389 16389 11929 ( 4 rows )Īnd sure enough, I have a few pids, or process ids. So, with a table name like search_hit, I could query: select pid from pg_locks l join pg_class t on l. One of those tables is pg_locks, where we can join with pg_class to be able to search by our table name. Postgres has a ton of great stat information in tables with metadata about how the Postgres system itself is operating. I recently ran the query above, and it just hung there, so I killed it and tried to find out what was causing it. ![]() The first thing that might give you a clue that there's a lock on a table is that you can't do simple things that you usually can, like deleting a row from a table: design_system => delete from search_hit where id = 154193 ^ CCancel request sent ERROR : canceling statement due to user request CONTEXT : while deleting tuple ( 22286, 5 ) in relation "search_hit" You need to be able to find those locks, the processes that caused them, and shut them down. Stuff like this could cause a database table lock in Postgres. Let's say that you do something silly like run a query that hangs and never returns - or open a transaction but never commit it.
0 Comments
Leave a Reply. |