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
