getting multiple queries with “show transaction isolation level” in pg_activity

Question

i am using postgres db server for my production use.

when i fire a query select * from pg_stat_activity on my postgresql server, so i getting 98% of queries like "SHOW TRANSACTION ISOLATION LEVEL" and my postgresql server accepts only 100 connections. and my server get stucked. so i am not able to proceed further.

any one have idea why this happened, is there any idea to block this all queries. or why this query create this many no of connections ?.

Answer 1

SHOW TRANSACTION ISOLATION LEVEL is likely to be called when your connection pool open up a connection

Have you tried reduce your connection pool size, try set it to 1 and see, you might get a better idea on what's going on.

I just had a similar problem today which is how I found this post, I found max connection reached only happens in my test environment, the reason is that my test framework (ScalaTest) seems to instantiate the database object in ORM for every test case.

For example my postgres have

• max_connections = 100
• connection pool = 100

it supposed to be fine, if there's only 1 app connect to the database, however in test, the connection pool is instantiate as many as my test cases, thus it reach the maximum easily.