Re: pg_stat_activity.backend_xmin

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