Getting multiple queries with "show transaction isolation level" in pg_activity

I am using PostgreSQL server for my production use.

When I fire a query like

select * from pg_stat_activity

on my server, I am getting 98% of queries like

SHOW TRANSACTION ISOLATION LEVEL

and my server accepts only 100 connections, so I am not able to proceed further.

Why is this happening? How can I block all these queries?

If they're there; they're there, but they didn't magically pop out of thin air. While I'd love for you to come back and tell us what they were in your case it's usually one of two things,

• Connection pooling

• ORM or SQL abstraction

Sometimes these guys connect and start empty transactions, or prep a transaction. It's always a result of a bad library. It's excusable if the library is new or new to the database. If the library is years old and doing this, I would suspect they don't have too many eyes on it and look at alternatives.

From the comments,

Postgres doesn't create connections by its own. It's your application that creates those connections and runs those queries. Possibly a connection pool or something similar. – a_horse_with_no_name Sep 10 '13 at 9:03

Are you running a Java application? Using HikariCP, or maybe another connection-pooling option? I'm using HikariCP and seeing a similar thing when I log into psql and do select query from pg_stat_activity;... I was also wondering why this is occurring; my guess is this is a mechanism by which HikariCP keeps connections "fresh". – Chris W. Jan 16 '16 at 19:22

While it's possible it's an attempt at keeping a connection "fresh" the kosher way to do that is something like this

result = PQexec(imp_dbh->conn, "/* DBD::Pg ping test v3.6.0 */");

You may want to file a bug report to use that method instead.

StackExchange Database Administrators Q#49585, answer score: 2