The TL;DR version: When you use ArcMap Query Layers to access a PostgreSQL table, ArcMap's database connections are almost always sitting idle in unclosed transactions. Why would this be?

I have PostgreSQL 9.1.x (with PostGIS 2.0.x) with some existing tables and data. I successfully use ArcMap (10.1 SP1) to connect to one of those tables (using a Query Layer) and render dots on my map. The ArcGIS enterprise geodatabase tables are not installed.

One day I brought up pgAdmin's server status window while ArcMap was running (and nothing else was accessing the database). As expected, there are two connections whose application name is "ArcSDE(sv=nnnn;sn={xxx})". But I was surprised to see that both of those connections are listed as "<IDLE> in transaction" in pgAdmin's "Query" column.

That seemed fishy. As a rule of thumb, it's generally bad to leave lingering open transactions. And as a programmer, I've witnessed first-hand the dangers of improper failure handling that leaves unclosed transactions.

So I closed everything down, restarted PostgreSQL, then started ArcMap again. Same result: two connections, both of which are "<IDLE> in transaction".

I cranked up the PostgreSQL statement logging and watched as ArcMap did its magic. Sure enough, the very last statement in the log (once the ArcMap window finishes rendering) is BEGIN, with no corresponding COMMIT or ROLLBACK. And then every time you pan or zoom, the very first thing it does is COMMIT. Then it gets all the data and ends its rendering with an un-closed BEGIN again.

At first glance, it appears benign. Those two connections hold no locks other than the locks on their own virtual transaction IDs. But it sure seems odd.

Any ideas what legitimate reason ArcMap might have for behaving this way?

  • Sounds like time to report an ArcMap bug to me, if you're already on the latest version. Idle sessions are fine, idle transactions are not. – Craig Ringer Jan 7 '14 at 22:57
  • @CraigRinger: I would like to report this as a bug, but I'm not 100% sure it's anything but an annoyance. Though I seem to remember reading somewhere that open transactions prevent VACUUM from cleaning anything newer than that transaction. Is that true? If so, do you know a place in the PostgreSQL 9.1 docs that says so? – csd Jan 9 '14 at 19:48
  • 2
    It's a problem if the transaction has a real txid (done row locking, done writes, certain other things) or it's SERIALIZABLE. I can't find a clear statement and the rules in the docs at the moment - I'll have a better look and propose a patch to add them if they're just absent. It is a bug in their code - whether it's a serious bug depends on what they did before abandoning the idle transactions. – Craig Ringer Jan 10 '14 at 1:33
  • I can confirm that this is still happening in 10.2 (PostgreSQL 9.2) – Jay Cummins Apr 10 '15 at 19:02
  • This is related: Disabling schema locking on a map service. As commented by @JayCummins, it still leaves the connection <IDLE> in transaction, but if the settings are right on your map service it won't hold schema locks that prevent you from DROP-ping or ALTER-ing your table. – csd Apr 10 '15 at 19:08

It's probably an artifact of their data provider object model, and a developer decision to start a transaction as part of setting up the connection (perhaps because they use binary cursors, which require a transaction for even reading? that's something the detailed statement log could tell you). They could probably re-work it to not do that, but you'll have to do some complaining.

  • A look at the SQL in the PostgreSQL logs shows that they are, as you suggest, using binary cursors. But they're declared WITH HOLD, so a transaction wrapper isn't strictly necessary. Regardless you're right: we'd have to do a lot of complaining for something that I can't prove (yet) is the cause of any problems. – csd Jan 9 '14 at 19:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.