> 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?
