On Wed, 2022-09-21 at 16:22 +0000, Dirschel, Steve wrote: > > > > > We are troubleshooting an issue where autovacuum is not cleaning up a table. > > > > > The application using this database runs with autocommit turned off. > > > > > We can see in pg_stat_activity lots of sessions “idle in transaction” > > > > > even though those sessions have not executed any DML- they have > > > > > executed selects but no DML. The database’s isolation level is set to read committed. > > > > > > > > "backend_xmin" is set when the session has an active snapshot. Such a snapshot is held > > > > for the whole duration of a transaction in the REPEATABLE READ isolation level, but > > > > there are cases where you can see that in READ COMMITTED isolation level as well: > > > > > > > > - if there is a long running query > > > > > > > > - if there is a cursor open > > > > > > > > Perhaps you could ask your developers if they have long running read-only transactions with cursors. > > > > > > Thanks for the reply Laurenz. For an application session in this "state" pg_stat_activity > > > shows the state of "idle in transaction" and backend_xmin is populated. The query shows the > > > last select query it ran. It is not currently executing a query. And dev has said they are > > > not using a cursor for the query. So it does not appear they have long running read-only > > > transactions with cursors. That does not follow. You can execute: DECLARE c CURSOR FOR SELECT /* whatever */; FETCH 50 FROM c; SELECT /* something entirely different */ So you have an open cursor (portal), even though the last statement executed does not use a cursor at all. > > > Outside that scenario can you think of any others where a session: > > > 1. Login to the database > > > 2. Set autocommit off > > > 3. Run select query, query completes, session does nothing after that query completes. > > > 4. transaction isolation level is read committed No. > > > That session sitting there idle in transaction has backend_xmin populated. When I run that > > > test backend_xmin does not get populated unless I set my transaction isolation level to > > > repeatable read. We have enabled statement logging so we can see if their sessions are > > > changing that transaction isolation level behind the scenes that they are not aware of > > > but so far we have not seen that type of command logged. > > > > What stack is the application using? Anything like Spring or Hibernate involved? > > Java is the stack. I'm not saying that you shouldn't trust your developers, but they may be using a cursor without being aware of it. If they use "setFetchSize()" to set a fetch size different from 0, they *are* using a cursor. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
