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.
Pleasesee my test case below.  I can show where a session is "idle in transaction" but backend_xmin is NOT populated
andI show vacuuming a table with 10 dead rows will work fine.  I then show a session is "idle in transaction" with
backend_xminpopulated 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
casesthe 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@thebuild.com>
> Sent: Wednesday, January 25, 2023 4:49 PM
> To: Dirschel, Steve <steve.dirschel@thomsonreuters.com>
> Cc: Thomas Kellerer <shammat@gmx.net>; pgsql-general@lists.postgresql.org
> Subject: Re: [EXT] DBeaver session populating pg_stat_activity.backend_xmin



> > On Jan 25, 2023, at 14:21, Dirschel, Steve <steve.dirschel@thomsonreuters.com> wrote:
> > From my perspective "idle in transaction" isn't necessarily a problem (although I don't like seeing sessions
sittinglike that for a long time).  The problem is when pg_stat_activity.backend_xmin is populated-  that can prevent
autovacuumfrom 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
transactionhas been committed*.  The problem is that the session is sitting in idle in transaction state, and *that*
blockstuple 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'
andapplication_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
ONet.oid=t.typelem                                                     + 
       |                                    |            |              |       | LEFT OUTER JOIN pg_catalog.pg_class c
ONc.oid=t.typrelid                                                     + 
       |                                    |            |              |       | LEFT OUTER JOIN
pg_catalog.pg_descriptiond 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
deadrows 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                |
SHOWTRANSACTION ISOLATION LEVEL 
 30231 | DBeaver 22.0.0 - Metadata <dbtest>   |                               |              | idle                |
SELECTt.oid,t.*,c.relkind,format_type(nullif(t.typbasetype, 0), t.typtypmod) as base_type_name, d.description+ 
       |                                      |                               |              |                     |
FROMpg_catalog.pg_type t 
       |                                      |                               |              |                     |
LEFTOUTER JOIN pg_catalog.pg_type et ON et.oid=t.typelem 
       |                                      |                               |              |                     |
LEFTOUTER JOIN pg_catalog.pg_class c ON c.oid=t.typrelid 
       |                                      |                               |              |                     |
LEFTOUTER JOIN pg_catalog.pg_description d ON t.oid=d.objoid 
       |                                      |                               |              |                     |
WHEREt.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 |
SHOWsearch_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,
finds10 dead rows which are removable and it removes them.   I believe you are saying the session that is  idle in
transactionshould 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_typet 
       |                                |                              |              |                     | LEFT
OUTERJOIN pg_catalog.pg_type et ON et.oid=t.typelem 
       |                                |                              |              |                     | LEFT
OUTERJOIN pg_catalog.pg_class c ON c.oid=t.typrelid 
       |                                |                              |              |                     | LEFT
OUTERJOIN pg_catalog.pg_description d ON t.oid=d.objoid 
       |                                |                              |              |                     | WHERE
t.typnameIS NOT NULL 
       |                                |                              |              |                     | AND
(c.relkindIS 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
arenonremovable 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