Menu
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.
272
To set the transaction isolation level, use an ISOLATION LEVEL level clause. It is not permitted to specify multiple ISOLATION LEVEL clauses in the same SET TRANSACTION statement. The default isolation level is REPEATABLE READ . Other permitted values are READ COMMITTED , READ UNCOMMITTED , and SERIALIZABLE .
Read Committed is the default isolation level in PostgreSQL. When a transaction uses this isolation level, a SELECT query (without a FOR UPDATE/SHARE clause) sees only data committed before the query began; it never sees either uncommitted data or changes committed during query execution by concurrent transactions.
If you want to see how many idle connections you have that have an open transaction, you could use: select * from pg_stat_activity where (state = 'idle in transaction') and xact_start is not null; This will provide a list of open connections that are in the idle state, that also have an open transaction.
To determine the global and session transaction isolation levels at runtime, check the value of the tx_isolation system variable: SELECT @@GLOBAL. tx_isolation, @@tx_isolation; InnoDB supports each of the translation isolation levels described here using different locking strategies.
You can show all the information by running SHOW all.
If you want to show the isolation level run this:
SHOW default_transaction_isolation;
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.
45
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
