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. > > In a test database if I login through psql and set autocommit off and issue a > select I can see my session in pg_stat_activity has xact_start populated but > backend_xmin is null. If I run vacuum against the table I selected from > (that has garbage rows that need to be cleaned up) it will clean them up. > But if I do a “set transaction isolation level repeatable read” and then do > the select pg_stat_activity xact_start is populated and backend_xmin is also > populated. In a different session if I delete/insert into the table I > selected from and then run vacuum against the table those rows will not get > cleaned up because the xmin is of the rows that need to get cleaned up are > higher (or is it lower) than the backend_xmin of my select session. > > That scenario is the scenario we are seeing through the application. > > According to the app team they are not aware of their code changing the > isolation level to repeatable read. Are there other scenarios where the > transaction isolation is set to read committed, the session has autocommit > off, and a select will populate backend_xmin in pg_stat_activity for the > session that issued the select? "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 -- Cybertec | https://www.cybertec-postgresql.com
