Thread: pg_stat_activity.backend_xmin

This e-mail is for the sole use of the intended recipient and contains information that may be privileged and/or confidential. If you are not an intended recipient, please notify the sender by return e-mail and delete this e-mail and any attachments. Certain required legal entity disclosures can be accessed on our website: https://www.thomsonreuters.com/en/resources/disclosures.html

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.
>  
> In a test database if I login through psql and set autocommit off and issue a
> select I can see my session in pg_stat_activity has xact_start populated but
> backend_xmin is null.  If I run vacuum against the table I selected from
> (that has garbage rows that need to be cleaned up) it will clean them up.
> But if I do a “set transaction isolation level repeatable read” and then do
> the select pg_stat_activity xact_start is populated and backend_xmin is also
> populated.  In a different session if I delete/insert into the table I
> selected from and then run vacuum against the table those rows will not get
> cleaned up because the xmin is of the rows that need to get cleaned up are
> higher (or is it lower) than the backend_xmin of my select session.
>  
> That scenario is the scenario we are seeing through the application.
>  
> According to the app team they are not aware of their code changing the
> isolation level to repeatable read.  Are there other scenarios where the
> transaction isolation is set to read committed, the session has autocommit
> off, and a select will populate backend_xmin in pg_stat_activity for the
> session that issued the select?

"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
-- 
Cybertec | https://www.cybertec-postgresql.com

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

> On Sep 21, 2022, at 9:32 AM, Dirschel, Steve <steve.dirschel@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
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
intransaction" 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
notget populated unless I set my transaction isolation level to repeatable read.  We have enabled statement logging so
wecan see if their sessions are changing that transaction isolation level behind the scenes that they are not aware of
butso far we have not seen that type of command logged. 
>
> Regards
> Steve

What stack is the application using?  Anything like Spring or Hibernate involved?

>> On Sep 21, 2022, at 9:32 AM, Dirschel, Steve <steve.dirschel@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
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
intransaction" 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
notget populated unless I set my transaction isolation level to repeatable read.  We have enabled statement logging so
wecan see if their sessions are changing that transaction isolation level behind the scenes that they are not aware of
butso 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

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