Re: [EXT] pg_stat_activity.backend_xmin

On Wed, 2022-09-21 at 16:22 +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.
> > >
> > > 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.

That does not follow. You can execute:

DECLARE c CURSOR FOR SELECT /* whatever */;
FETCH 50 FROM c;
SELECT /* something entirely different */

So you have an open cursor (portal), even though the last statement executed does
not use a cursor at all.

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

No.

> > > 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.
> >
> > What stack is the application using?  Anything like Spring or Hibernate involved?
>
> Java is the stack.

I'm not saying that you shouldn't trust your developers, but they may be using a cursor
without being aware of it. If they use "setFetchSize()" to set a fetch size different from 0,
they *are* using a cursor.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com