I want to be able to query settings in another existing session. In particular the transaction_isolation. We have:
current_setting('transaction_isolation')
I want to call something like:
session_setting('transaction_isolation', backend_pid )
To find out what transaction isolation level is actually being used by an existing session/connection/back-end.
Background
We have a problem where I believe Auto-vacuum gets stuck. Running vacuum manually leaves certain tables with many (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.
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.
Its hard to find out quite how the connections/session are being created directly within the bowels of some monitoring/reporting tools.
The default transaction_isolation is the normal read-committed. We're running v9.3 postgres.
I don't think there's any way to look into one session from within another session. (I could be wrong.)
The only alternative I can think of is to expand your logging. In postgresql.conf, set
logging_collector = on
log_statement = 'all'
Restart the server.
This will log all SQL statements, including those that set the transaction isolation level. There are a lot of settings; log_connections might be a useful one.
So you'd assume that the isolation level is "read committed" unless you log a different isolation level.