We are using PG 10.6. We have one cron job that queries pg_stat_activity table to find out how many queries are running longer than X minutes and generate metrics.
Query look like this : SELECT * FROM pg_stat_activity WHERE state='active'
After some days, this query get stuck and doesn't finish. We tried to run this query manually and same result.
We looked into pg_locks table and there this query is not blocked on any lock : https://justpaste.it/48rpe
Now we don't know where to look next. Our expectation was query on pg_stat_activity shouldn't get blocked on anything and pg_locks also showing same result.
Google search also not returning anything useful that is why reaching out to this email list.
We have separated that DB and issue is still produceable on that DB, in case if more information is not needed.