pg_stat_activity.backend_xmin

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?

In session #1:

\set AUTOCOMMIT off

Set transaction isolation level repeatable read;

Select * from test1;

In session #2:

Delete from test1;

Insert into test1 values (1);

… do 10 more inserts, delete the rows, do 10 more inserts, delete the rows.

…

vacuum(verbose) test1;

INFO:  vacuuming "public.test1"

INFO:  "test1": found 0 removable, 55 nonremovable row versions in 1 out of 1 pages

DETAIL:  44 dead row versions cannot be removed yet, oldest xmin: 32708199

There were 172 unused item identifiers.

Skipped 0 pages due to buffer pins, 0 frozen pages.

0 pages are entirely empty.

CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.

INFO:  vacuuming "pg_toast.pg_toast_329295"

INFO:  "pg_toast_329295": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages

DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 32708199

There were 0 unused item identifiers.

Skipped 0 pages due to buffer pins, 0 frozen pages.

0 pages are entirely empty.

CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.

VACUUM

The xmin value of 32708199 is the value in pg_stat_activity.backend_xmin for the session who issued the select.  Once I issue a commit in that session and then try autovacuum again:

vacuum(verbose) test1;

INFO:  vacuuming "public.test1"

INFO:  "test1": removed 44 row versions in 1 pages

INFO:  "test1": found 44 removable, 11 nonremovable row versions in 1 out of 1 pages

DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 32708957

There were 172 unused item identifiers.

Skipped 0 pages due to buffer pins, 0 frozen pages.

0 pages are entirely empty.

CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.

INFO:  vacuuming "pg_toast.pg_toast_329295"

INFO:  "pg_toast_329295": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages

DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 32708957

There were 0 unused item identifiers.

Skipped 0 pages due to buffer pins, 0 frozen pages.

0 pages are entirely empty.

CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.

VACUUM

Thanks