AnsweredAssumed Answered

Map service connection to PostgreSQL DB shown as "Idle in Transaction" - Normal?

Question asked by bogibrynjar on Mar 18, 2016
Latest reply on Apr 18, 2016 by pheede-esristaff

We have a dynamic map service based on an mxd map document containing a Query Layer from a spatially (PostGIS) enabled PostgreSQL table (uses geometry data type for shape column). The service works fine both in ArcGIS clients and JavaScript browser applications through REST, no problems.

 

Our PostgreSQL DBA contacted me however and told me that the service was leaving open and idle queries in the database which he says is not acceptable. He sent me a report as an example, see below.

 

POSTGRES_TXN_IDLE WARNING: DB "postgres" (host:******) longest idle in
  txn: 56384s (15 hours 39 minutes 44 seconds) PID:32278
  database:****** username:********* address:***.****.***
  port:**** query:BEGIN

 

My understanding is that this is normal behavior for ArcGIS server map service connections but I would like to verify if this is the correct understanding or not (we have AGS 10.3.1 and PostgreSQL 9.3.x). So is this normal behavior or not?

 

The only viable solution that I can think of to meet the DBAs demand is to set the minimum number of service instances to zero so that idle services (and their connections to the DB) are closed after x minutes of inactivity. This however would result in a bit of a performance hit for the first user that comes along, and no instances are running, and he has to wait for at least one instance of the service to start up.

 

UPDATE 20160321: Found a post by another user which seems to confirm this behavior on PostgreSQL connections. He had some interesting findings and our DBA seems to agree with him that this is not a good way to make connections to a database. Here is the post, check it out.

 

arcgis server connection pooling

Outcomes