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
transactionin the REPEATABLE READ isolation level, but there are cases where you can see that in READ COMMITTED
isolationlevel > 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
executinga query. And dev has said they are not using a cursor for the query. So it does not appear they have long
runningread-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
getpopulated unless I set my transaction isolation level to repeatable read. We have enabled statement logging so we
cansee if their sessions are changing that transaction isolation level behind the scenes that they are not aware of but
sofar we have not seen that type of command logged.
Regards
Steve