[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    RE: [EXT] Re: pg_stat_activity.backend_xmin
From:       "Dirschel, Steve" <steve.dirschel () thomsonreuters ! com>
Date:       2022-09-21 15:32:47
Message-ID: DM6PR03MB4332F78D8B98D802279600C5FA4F9 () DM6PR03MB4332 ! namprd03 ! prod ! outlook ! com
[Download RAW message or body]

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


[prev in list] [next in list] [prev in thread] [next in thread]