RE: [EXT] pg_stat_activity.backend_xmin

>> On Sep 21, 2022, at 9:32 AM, Dirschel, Steve <steve(dot)dirschel(at)thomsonreuters(dot)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