Postgresql – several idle connections SET bytea_output

postgresql

i have a situation that i don't know if it is normal, i think not.

I have several idle connections with the query SET bytea_output='escape', so i arrive to max_connections and have issues with my applications, why are there so many connections idle with that query?

Best Answer

Many libraries which manage connections for you will execute some housekeeping queries on the new connection before returning it to the caller. SET bytea_output='escape' is just the type of housekeeping query you might expect to see.

So the likely explanation is that someone keeps asking for a new connections, and then immediately forgets about it without either using it or closing it. To figure out who, you should be looking at other fields of "pg_stat_activity", like usename, application_name, and client_addr.

And ultimately if you don't know who is connecting to your database, you should be changing the passwords or other authentication methods.

Related Solutions

Postgresql – Could too many idle connections affect PostgreSQL 9.2 performance

I seriously doubt idle connections are your issue. PostgreSQL is very capable of handling idle sessions since they take almost no resources. Active sessions on the other hand are much more likely the cause of slowness.

See https://wiki.postgresql.org/wiki/Number_Of_Database_Connections for reference on the number of database connections.

Postgresql – How to determine if there are [idle connections with] uncommitted transactions in PostgreSQL

If you want to see how many idle connections you have that have an open transaction, you could use:

select * 
from pg_stat_activity
where (state = 'idle in transaction')
    and xact_start is not null;

This will provide a list of open connections that are in the idle state, that also have an open transaction.

Having said that, I cannot recreate an open connection in the idle state that has an open transaction. Perhaps someone else can provide details on how to do that.

Best Answer

Related Solutions

Postgresql – Could too many idle connections affect PostgreSQL 9.2 performance

Postgresql – How to determine if there are [idle connections with] uncommitted transactions in PostgreSQL

Related Question