title: How can I query the transaction-isolation level of an existing postgres session?
body: <p>I want to be able to query settings in another existing session. In particular the transaction_isolation. We have:</p>
  
  <pre><code>current_setting('transaction_isolation')
  </code></pre>
  
  <p>I want to call something like:</p>
  
  <pre><code>session_setting('transaction_isolation', backend_pid )
  </code></pre>
  
  <p>To find out what transaction isolation level is actually being used by an existing session/connection/back-end.</p>
  
  <p><strong>Background</strong></p>
  
  <p>We have a problem where I believe Auto-vacuum gets stuck. Running vacuum manually leaves certain tables with <em>many</em> (say a million) dead-tuples remaining.
  That, i think, reduces performance a lot. A single row update on such tables can take over a second. Where it normally takes a millisecond.</p>
  
  <p>Looking into pg_stat_activity there are quite a few apps accessing this database.
  Killing off any long open read/write transaction once helped solve the problem.
  (Vacuum ran, and a second later throughput leapt up by perhaps 1000 x)
  In other cases that approach did not work.
  It seems some of the read session may be causing the problem, even when they don't query the suspect tables. This could make sense if we had say sequential-read transaction isolation being used by these other apps' sessions.
  Some of the other apps are using JDBC i think. Some ODBC. And there are a few PgAmdins joining in too.</p>
  
  <p>Its hard to find out quite how the connections/session are being created directly within the bowels of some monitoring/reporting tools.</p>
  
  <p>The default transaction_isolation is the normal read-committed.
  We're running v9.3 postgres.</p>