Обсуждение: 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
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