query from pg_range and pg_type is continuously eating my server's resource - postgresql

Question

Query from postgresql is eating my server's entire resource not allowing any other queries to process.

WITH ranges AS(  SELECT pg_range.rngtypid, pg_type.typname AS rngtypname,         pg_type.typarray AS rngtyparray, pg_range.rngsubtype    
FROM pg_range 
LEFT OUTER JOIN pg_type ON pg_type.oid = pg_range.rngtypid) 
SELECT pg_type.typname, pg_type.typtype, pg_type.oid, pg_type.typarray,       ranges.rngtypname, ranges.rngtypid, ranges.rngtyparray  
FROM pg_type 
LEFT OUTER JOIN ranges ON pg_type.oid = ranges.rngsubtype WHERE (pg_type.typtype IN('b', 'e'));

when seeing status of postgresql status, we see connections from 127.0.0.1 taking all the max-connections limit

 5092 postgres: postgres schools_v6 127.0.0.1(46105) idle
           ├─ 5093 postgres: postgres schools_v6 127.0.0.1(46107) idle
           ├─ 5094 postgres: postgres schools_v6 127.0.0.1(46109) idle
           ├─ 5095 postgres: postgres schools_v6 127.0.0.1(46111) idle
           ├─ 5096 postgres: postgres schools_v6 127.0.0.1(46113) idle
           ├─ 5097 postgres: postgres schools_v6 127.0.0.1(46115) idle
           ├─ 5098 postgres: postgres schools_v6 127.0.0.1(46117) idle
           ├─ 5099 postgres: postgres schools_v6 127.0.0.1(46119) idle
           ├─ 5100 postgres: postgres schools_v6 127.0.0.1(46121) idle
           ├─ 5101 postgres: postgres schools_v6 127.0.0.1(46123) idle
           ├─ 5165 postgres: postgres schools_v6 127.0.0.1(46247) idle
           ├─ 5166 postgres: postgres schools_v6 127.0.0.1(46249) idle
           ├─ 5167 postgres: postgres schools_v6 127.0.0.1(46251) idle
           ├─ 5168 postgres: postgres schools_v6 127.0.0.1(46253) idle
           ├─ 5169 postgres: postgres schools_v6 127.0.0.1(46255) idle
           ├─ 5188 postgres: postgres schools_v6 127.0.0.1(46291) idle
           ├─ 5189 postgres: postgres schools_v6 127.0.0.1(46293) idle
           ├─ 5190 postgres: postgres schools_v6 127.0.0.1(46295) idle
           ├─ 5191 postgres: postgres schools_v6 127.0.0.1(46297) idle
           ├─ 5192 postgres: postgres schools_v6 127.0.0.1(46299) idle
           ├─ 5193 postgres: postgres schools_v6 127.0.0.1(46301) idle
           ├─ 5194 postgres: postgres schools_v6 127.0.0.1(46303) idle
           ├─ 5195 postgres: postgres schools_v6 127.0.0.1(46305) idle
           ├─ 5196 postgres: postgres schools_v6 127.0.0.1(46307) idle
           ├─ 5197 postgres: postgres schools_v6 127.0.0.1(46309) idle

and more like this.

FYI, We use jasperserver fro reporting purposes and connect using jdbc driver. Also we use pg_cron for running 3 jobs each every 5mins.

I am dumbstruck here not knowing from where does this query arise from.

question from:https://stackoverflow.com/questions/65913513/query-from-pg-range-and-pg-type-is-continuously-eating-my-servers-resource-po

Answer 1

Something is opening a connection, running that query (most likely preceded by other queries, that is just the last one run), and then going idle, never closing the connection.

That looks like an introspection query, run by some kind of GUI so that it knows what datatypes exist in your database so it can put them in a drop down list or something.

You might be able to get some clue about what it is by looking in the application_name column of pg_stat_activity. Or maybe use ss -p to track down the process on the other end of the connections.