RE: [EXT] DBeaver session populating pg_stat_activity.backend_xmin

Hi Christophe,

Thanks for the reply. I am fairly new to Postgres and based on your reply below I am not understanding something. Please see my test case below. I can show where a session is "idle in transaction" but backend_xmin is NOT populated and I show vacuuming a table with 10 dead rows will work fine. I then show a session is "idle in transaction" with backend_xmin populated and vacuuming a table with 10 dead rows will not work because of that session having backend_xmin populated. Note in all cases here these sessions have not executed any DML. Based on your reply below in both of these cases the vacuum should not have been able to cleanup these 10 dead rows. What am I missing here?

Regards
Steve

> -----Original Message-----
> From: Christophe Pettus <xof(at)thebuild(dot)com>
> Sent: Wednesday, January 25, 2023 4:49 PM
> To: Dirschel, Steve <steve(dot)dirschel(at)thomsonreuters(dot)com>
> Cc: Thomas Kellerer <shammat(at)gmx(dot)net>; pgsql-general(at)lists(dot)postgresql(dot)org
> Subject: Re: [EXT] DBeaver session populating pg_stat_activity.backend_xmin

> > On Jan 25, 2023, at 14:21, Dirschel, Steve <steve(dot)dirschel(at)thomsonreuters(dot)com> wrote:
> > From my perspective "idle in transaction" isn't necessarily a problem (although I don't like seeing sessions sitting like that for a long time). The problem is when pg_stat_activity.backend_xmin is populated- that can prevent autovacuum from cleaning up old records.

> I don't think that analysis is quite correct. There's nothing about backend_xmin that blocks tuple cleanup *if the transaction has been committed*. The problem is that the session is sitting in idle in transaction state, and *that* blocks tuple cleanup. The fix is to not leave sessions open in idle in transaction state.

Test case:

create table test1(a numeric, b numeric);

***********
* TEST #1 *
***********

Dbeaver setup:
Connection setting, Initialization, Auto-commit not checked
Connection setting, Metadata, Open separate connection for metadata read is checked.

Connect to the database through DBeaver. Here are the 2 sessions:

select pid, application_name, xact_start, backend_xmin, state, query from pg_stat_activity where usename = 'postgres' and application_name like 'DBeaver%';

dbtest=> select pid, application_name, xact_start, backend_xmin, state, query from pg_stat_activity where usename = 'postgres' and application_name like 'DBeaver%';
pid | application_name | xact_start | backend_xmin | state | query
-------+------------------------------------+------------+--------------+-------+----------------------------------------------------------------------------------------------------------------
30229 | DBeaver 22.0.0 - Main <dbtest> | | | idle | SHOW TRANSACTION ISOLATION LEVEL
30231 | DBeaver 22.0.0 - Metadata <dbtest> | | | idle | SELECT t.oid,t.*,c.relkind,format_type(nullif(t.typbasetype, 0), t.typtypmod) as base_type_name, d.description+
| | | | | FROM pg_catalog.pg_type t +
| | | | | LEFT OUTER JOIN pg_catalog.pg_type et ON et.oid=t.typelem +
| | | | | LEFT OUTER JOIN pg_catalog.pg_class c ON c.oid=t.typrelid +
| | | | | LEFT OUTER JOIN pg_catalog.pg_description d ON t.oid=d.objoid +
| | | | | WHERE t.typname IS NOT NULL +
| | | | | AND (c.relkind IS NULL OR c.relkind = 'c') AND (et.typcategory IS NULL OR et.typcategory <> 'C')

*** Note neither session is in a transaction. State for both is idle.

Leave the 2 DBeaver sessions as is. In psql I run this:

dbtest=> do $$
dbtest$> begin
dbtest$> for cnt in 1..10 loop
dbtest$> insert into test1 values (cnt, cnt);
dbtest$> delete from test1;
dbtest$> end loop;
dbtest$> end; $$
dbtest-> ;
DO
dbtest=>
dbtest=> VACUUM (VERBOSE) test1;
INFO: vacuuming "public.test1"
INFO: table "test1": removed 10 dead item identifiers in 1 pages
INFO: table "test1": found 10 removable, 0 nonremovable row versions in 1 out of 1 pages <------------------- the 10 dead rows are removable and removed. This is expected.
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 19368520
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: table "test1": truncated 1 to 0 pages
DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO: vacuuming "pg_toast.pg_toast_49187"
INFO: table "pg_toast_49187": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 19368521
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM

***********
* TEST #2 *
***********

Leave DBeaver config as is. Now in DBeaver I open the SQL Console and start a transaction by running BEGIN;

Look at sessions now:

dbtest=> select pid, application_name, xact_start, backend_xmin, state, query from pg_stat_activity where usename = 'postgres' and application_name like 'DBeaver%';
pid | application_name | xact_start | backend_xmin | state | query
-------+--------------------------------------+-------------------------------+--------------+---------------------+--------------------------------------------------------------------------------------------
30229 | DBeaver 22.0.0 - Main <dbtest> | | | idle | SHOW TRANSACTION ISOLATION LEVEL
30231 | DBeaver 22.0.0 - Metadata <dbtest> | | | idle | SELECT t.oid,t.*,c.relkind,format_type(nullif(t.typbasetype, 0), t.typtypmod) as base_type_name, d.description+
| | | | | FROM pg_catalog.pg_type t
| | | | | LEFT OUTER JOIN pg_catalog.pg_type et ON et.oid=t.typelem
| | | | | LEFT OUTER JOIN pg_catalog.pg_class c ON c.oid=t.typrelid
| | | | | LEFT OUTER JOIN pg_catalog.pg_description d ON t.oid=d.objoid
| | | | | WHERE t.typname IS NOT NULL
| | | | | AND (c.relkind IS NULL OR c.relkind = 'c') AND (et.typcategory IS NULL OR et.typcategory <> 'C')
31163 | DBeaver 22.0.0 - SQLEditor <Console> | 2023-01-31 10:05:16.125645-06 | | idle in transaction | SHOW search_path
(3 rows)

*** Now we see the SQL Editor session with xact_start populated, state is idle in transaction, but backend_xmin is NOT populated.

Run the test again with sessions in that state:

dbtest=> do $$
dbtest$> begin
dbtest$> for cnt in 1..10 loop
dbtest$> insert into test1 values (cnt, cnt);
dbtest$> delete from test1;
dbtest$> end loop;
dbtest$> end; $$
dbtest-> ;
DO
dbtest=>
dbtest=> VACUUM (VERBOSE) test1;
INFO: vacuuming "public.test1"
INFO: table "test1": removed 10 dead item identifiers in 1 pages
INFO: table "test1": found 10 removable, 0 nonremovable row versions in 1 out of 1 pages <------------------- again, finds 10 dead rows which are removable and it removes them. I believe you are saying the session that is idle in transaction should have blocked this from cleaning those up.
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 19368826
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: table "test1": truncated 1 to 0 pages
DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO: vacuuming "pg_toast.pg_toast_49187"
INFO: table "pg_toast_49187": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 19368827
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM

***********
* TEST #3 *
***********
Close my DBeaver connections.

Dbeaver setup:
Connection setting, Initialization, Auto-commit not checked
Connection setting, Metadata, Open separate connection for metadata read is NOT checked.

Connect to the database through DBeaver. Here are the 2 sessions:

dbtest=> select pid, application_name, xact_start, backend_xmin, state, query from pg_stat_activity where usename = 'postgres' and application_name like 'DBeaver%';
pid | application_name | xact_start | backend_xmin | state | query

-------+--------------------------------+------------------------------+--------------+---------------------+---------------------------------------------------------------------------------------------------
-------------
31421 | DBeaver 22.0.0 - Main <dbtest> | 2023-01-31 10:09:08.35667-06 | 19369010 | idle in transaction | SELECT t.oid,t.*,c.relkind,format_type(nullif(t.typbasetype, 0), t.typtypmod) as base_type_name, d.description+
| | | | | FROM pg_catalog.pg_type t
| | | | | LEFT OUTER JOIN pg_catalog.pg_type et ON et.oid=t.typelem
| | | | | LEFT OUTER JOIN pg_catalog.pg_class c ON c.oid=t.typrelid
| | | | | LEFT OUTER JOIN pg_catalog.pg_description d ON t.oid=d.objoid
| | | | | WHERE t.typname IS NOT NULL
| | | | | AND (c.relkind IS NULL OR c.relkind = 'c') AND (et.typcategory IS NULL OR et.typcategory <> 'C')
(1 row)

*** Now I only see 1 DBeaver session. xact_start is populated, state is idle in transaction. But now backend_xmin IS populated. That is the key (at least in my opinion) to show the problem where backend_xmin is populated.

Now run the test again:

dbtest=> do $$
dbtest$> begin
dbtest$> for cnt in 1..10 loop
dbtest$> insert into test1 values (cnt, cnt);
dbtest$> delete from test1;
dbtest$> end loop;
dbtest$> end; $$
dbtest-> ;
DO
dbtest=>
dbtest=> VACUUM (VERBOSE) test1;
INFO: vacuuming "public.test1"
INFO: table "test1": found 0 removable, 10 nonremovable row versions in 1 out of 1 pages
DETAIL: 10 dead row versions cannot be removed yet, oldest xmin: 19369010 <------------------- now these 10 rows are nonremovable due to the oldest xmin from the session above.
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: vacuuming "pg_toast.pg_toast_49187"
INFO: table "pg_toast_49187": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 19369010
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM