>> On Sep 21, 2022, at 9:32 AM, Dirschel, Steve >> <steve.dirsc...@thomsonreuters.com> wrote: >> >> On Wed, 2022-09-21 at 14:11 +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. >>> >>> Yours, >>> Laurenz Albe >> >> 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. >> >> 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 >> >> 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. >> >> Regards >> Steve > >What stack is the application using? Anything like Spring or Hibernate >involved?
Java is the stack. Thanks
