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

[DuarteCarreira]

Philip Heede​ thanks for the heads up. Can you elaborate a bit more? I have made a simple test with desktop 10.4 and pgbouncer, opening an mxd with layers from 2 different logins. Connections stay at only 1 per user, which is half of 10.3.

I haven't started on benchmarking this setup yet. But I like what I see so far.

So your cautionary warning got me worried. What kind of circumstances do you mean? Maybe with any luck we do not work in those scenarios.

Thanks.

[pheede-esri]

We don't have a full inventory of cases where state is maintained, getting that is part of the work that is ongoing. Primarily we expect this to be part of more complex types of transactions such as editing of geodatabase functionality like geometric networks, street networks, parcels, etc.

However, that should not be interpreted as the only place where this could occur and thus we cannot officially support setups using pgBouncer at this time. Any such use would be at your own risk and outside of the official scope of support.

[DuarteCarreira]

Philip thanks again for your insights. I'm glad to hear that esri is looking into this.

I had thought about things that could be broken with connection pooling. I tested 2 PCs connecting with the same user, and making large selections - everything worked fine AFAIK. But the doubt was in my head now...

One thing that would be a very big help and may be easy to support is read-only connections.

Some 80% of our connections to the gdb are read-only. At least 40 connections are read-only arcgis server services. These only seat there eating away our server's memory. So just reducing these would make a big difference.

Same can be said of desktop users. Only a few have editing privileges. Big majority only reads data, although ofcourse writing stuff like big selection sets.

What are your thoughts on this? I'm not looking for a certified answer from esri, just for a good discussion on this issue. I understand things move at their own pace.

Thanks again.

[pheede-esri]

Read-only connections should be fairly safe to experiment with, I can't envision any bad things that could happen on the order of e.g. data corruption. Again though: at your own risk, not supported, and so forth. Some types of 'read-only' operations do involve making changes in the geodatabase log tables, as you've mentioned things that involve large selection sets is a good example, so it's not out of the realm of possibility that could encounter issues.