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

[BogiBjornsson]

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

[AsrujitSengupta]

Enterprise GIS​ Geodatabase​Managing Data​ ArcGIS for Server​

[DanPatterson_Retired]

To add to Asrujit SenGupta​ 's post...

pick a place ... GeoNet Community Structure

GIS is too broad and your question seems targeted. 

options:

• It will probably languish here in the GIS Place, alone, unnoticed, when it could find a home where the question would be warmly welcomed  with people eager to answer it.
• move it

[BogiBjornsson]

Moved, thanks for the tip! Only problem is I can only move it to one place so it's very hard to choose which one!

[DanPatterson_Retired]

but...there is a Share option that appears when you have finished posting.. top right by the thread... now don't share tooo much... pick carefully...

[BogiBjornsson]

Aha ... didn't realize that was what it was for, thought it was only for sharing with individual users. Live and learn

[pheede-esri · Accepted solution]

Bogi Bjornsson​ There were changes made in ArcGIS 10.4 to address the concerns that you raise here. Beginning with ArcGIS 10.4, connections to PostgreSQL databases no longer stay in an open and idle transaction.

Cheers,

Philip

ArcGIS for Server Product Manager

[BogiBjornsson]

Great, thanks for the feedback Philip. My DBA will be happy to hear that

[DuarteCarreira]

Hi all. Yes this is potentially very good news. I am planning to test how now pgbouncer can be used with arcgis 10.4, desktop and server. This has the potential to drastically reduce the number of connections to more sane amounts. On the other hand, it may have significant impact on performance... thus the need to test.

arcgis is very sensible to lan bandwidth. you can easilly see this if you put side-by-side a 100 Mbps and a 1Gbps machines with arcmap opening the same map. Don't even need to time it...

[pheede-esri]

Duarte,

pgBouncer is not explicitly supported even after this change. Under some circumstances we tie specific geodatabase (sde) connection settings to a specific database connection. Using pgBouncer can violate the built-in assumptions that ArcGIS sometimes makes about the connection state.

Philip