Обсуждение: DBeaver session populating pg_stat_activity.backend_xmin
Not sure if anyone on this list may have ideas on this or not. I will also try and find a DBeaver e-mail list and send to that also.
We are running into a problem where users will connect to a database through DBeaver. I have found if I leave all DBeaver options as default this issue does not happen. But I have found if I edit my connection under “Connection settings”, “Initialization” and if I uncheck Auto-commit and then under “Connection settings”, “Metadata” if I unselect “Open separate connection for metadata read” I can duplicate the issue.
When I connect to the database through DBeaver with those 2 default settings changed and find that session in pg_stat_activity column xact_start is populated along with backend_xmin. Those get populated just by logging in. I don’t know if changing those 2 default settings is the only way to get the session to get like this but it does mimic the problem. The problem is users will connect using DBeaver and their sessions will sit idle. We have a table with high update activity and I will start seeing queries hitting this table do more and more logical reads because autovacuum is not able to cleanup records newer than the backend_xmin for those session. We have told users not to leave their sessions sitting idle like that and are also considering setting idle_in_transaction_session_timeout to kill sessions that sit like this.
But I am trying to understand what DBeaver is doing to get a session into that state.
I’ve set log_statement to ALL and did a login through DBeaver with the 2 settings changed to see what all it is executing. I then changed the DBeaver settings back to default so I could see what it executes upon connect without having xact_start and backend_xmin populated.
It executes the exact same commands except when the 2 default DBeaver settings are changed to show the issue it is issuing a BEGIN and COMMIT around one block of code and then at another part of code it issues a BEGIN, runs some queries, and never issues a COMMIT. The log info is below with <=================================== next to the BEGIN/COMMIT’s that happen with the non-default settings.
I try and duplicate this in psql and cannot. In psql I do a:
\set AUTOCOMMIT off
I then run the exact same commands as log_statements = ALL shows DBeaver runs without a COMMIT at the end of the last statement (those are shown below). I can find that psql session in pg_stat_activity and xact_start is populated but backend_xmin is NOT populated. Under that scenario it would not cause the problem of autovacuum not being able to cleanup this heavy update table due to a session sitting out there with backend_xmin populated.
Any ideas why I cannot duplicate this in psql if I’m running what appears to be the exact same commands DBeaver is? Is it possible DBeaver is running something else that does not get written to the log file when log_statements is set to all? One slight difference in my psql test was a couple of the queries below were using bind variables when executed through DBeaver. I put the literal values in the query when I executed through psql. Is it possible that somehow using bind variables with autocommit off, using BEGIN causes backend_xmin to get populated?
Thanks
From log file:
SET extra_float_digits = 3
SET application_name = 'PostgreSQL JDBC Driver'
SET application_name = 'DBeaver 22.0.0 - Main <dbtest>'
BEGIN <=========================================================
SELECT current_schema(),session_user
SELECT n.oid,n.*,d.description FROM pg_catalog.pg_namespace n
LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=n.oid AND d.objsubid=0 AND d.classoid='pg_namespace'::regclass
ORDER BY nspname
SELECT n.nspname = ANY(current_schemas(true)), n.nspname, t.typname FROM pg_catalog.pg_type t JOIN pg_catalog.pg_namespace n ON t.typnamespace = n.oid WHERE t.oid = '1034'
SELECT typinput='array_in'::regproc as is_array, typtype, typname FROM pg_catalog.pg_type LEFT JOIN (select ns.oid as nspoid, ns.nspname, r.r from pg_namespace as ns join ( select s.r, (current_schemas(false))[s.r] as nspname from generate_series(1, array_upper(current_schemas(false), 1)) as s(r) ) as r using ( nspname ) ) as sp ON sp.nspoid = typnamespace WHERE typname = '_aclitem' ORDER BY sp.r, pg_type.oid DESC
SHOW search_path
COMMIT <========================================================================
BEGIN <========================================================================
SELECT db.oid,db.* FROM pg_catalog.pg_database db WHERE datname='dbtest'
select * from pg_catalog.pg_settings where name='standard_conforming_strings'
select string_agg(word, ',') from pg_catalog.pg_get_keywords() where word <> ALL ('{a,abs,absolute,action,ada,add,admin,after,all,allocate,alter,always,and,any,are,array,as,asc,asensitive,assertion,assignment,asymmetric,at,atomic,attribute,attributes,authorization,avg,before,begin,bernoulli,between,bigint,binary,blob,boolean,both,breadth,by,c,call,called,cardinality,cascade,cascaded,case,cast,catalog,catalog_name,ceil,ceiling,chain,char,char_length,character,character_length,character_set_catalog,character_set_name,character_set_schema,characteristics,characters,check,checked,class_origin,clob,close,coalesce,cobol,code_units,collate,collation,
collation_catalog,collation_name,collation_schema,collect,column,column_name,command_function,command_function_code,commit,committed,condition,condition_number,connect,connection_name,constraint,constraint_catalog,constraint_name,constraint_schema,constraints,constructors,contains,continue,convert,corr,corresponding,count,covar_pop,covar_samp,create,cross,cube,cume_dist,current,current_collation,current_date,current_default_transform_group,current_path,current_role,current_time,current_timestamp,current_transform_group_for_type,current_user,cursor,cursor_name,cycle,
data,date,datetime_interval_code,datetime_interval_precision,day,deallocate,dec,decimal,declare,default,defaults,deferrable,deferred,defined,definer,degree,delete,dense_rank,depth,deref,derived,desc,describe,descriptor,deterministic,diagnostics,disconnect,dispatch,distinct,domain,double,drop,dynamic,dynamic_function,dynamic_function_code,each,element,else,end,end-exec,equals,escape,every,except,exception,exclude,excluding,exec,execute,exists,exp,external,extract,
false,fetch,filter,final,first,float,floor,following,for,foreign,fortran,found,free,from,full,function,fusion,g,general,get,global,go,goto,grant,granted,group,grouping,having,hierarchy,hold,hour,identity,immediate,implementation,in,including,increment,indicator,initially,inner,inout,input,insensitive,insert,instance,instantiable,int,integer,intersect,intersection,interval,into,invoker,is,isolation,join,k,key,key_member,key_type,language,large,last,lateral,leading,left,length,level,like,ln,local,localtime,localtimestamp,locator,lower,m,map,match,matched,max,maxvalue,
member,merge,message_length,message_octet_length,message_text,method,min,minute,minvalue,mod,modifies,module,month,more,multiset,mumps,name,names,national,natural,nchar,nclob,nesting,new,next,no,none,normalize,normalized,not,"null",nullable,nullif,nulls,number,numeric,object,octet_length,octets,of,old,on,only,open,option,options,or,order,ordering,ordinality,others,out,outer,output,over,overlaps,overlay,overriding,pad,parameter,parameter_mode,parameter_name,parameter_ordinal_position,parameter_specific_catalog,parameter_specific_name,parameter_specific_schema,
partial,partition,pascal,path,percent_rank,percentile_cont,percentile_disc,placing,pli,position,power,preceding,precision,prepare,preserve,primary,prior,privileges,procedure,public,range,rank,read,reads,real,recursive,ref,references,referencing,regr_avgx,regr_avgy,regr_count,regr_intercept,regr_r2,regr_slope,regr_sxx,regr_sxy,regr_syy,relative,release,repeatable,restart,result,return,returned_cardinality,returned_length,returned_octet_length,returned_sqlstate,returns,revoke,right,role,rollback,rollup,routine,routine_catalog,routine_name,routine_schema,row,
row_count,row_number,rows,savepoint,scale,schema,schema_name,scope_catalog,scope_name,scope_schema,scroll,search,second,section,security,select,self,sensitive,sequence,serializable,server_name,session,session_user,set,sets,similar,simple,size,smallint,some,source,space,specific,specific_name,specifictype,sql,sqlexception,sqlstate,sqlwarning,sqrt,start,state,statement,static,stddev_pop,stddev_samp,structure,style,subclass_origin,submultiset,substring,sum,symmetric,system,system_user,table,table_name,tablesample,temporary,then,ties,time,timestamp,timezone_hour,
timezone_minute,to,top_level_count,trailing,transaction,transaction_active,transactions_committed,transactions_rolled_back,transform,transforms,translate,translation,treat,trigger,trigger_catalog,trigger_name,trigger_schema,trim,true,type,uescape,unbounded,uncommitted,under,union,unique,unknown,unnamed,unnest,update,upper,usage,user,
user_defined_type_catalog,user_defined_type_code,user_defined_type_name,user_defined_type_schema,using,value,values,var_pop,
var_samp,varchar,varying,view,when,whenever,where,width_bucket,window,with,within,without,work,write,year,zone}'::text[])
SELECT version()
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 in pg_stat_activity this last query above is what shows up in the DBeaver session so that is the last query the session ran.
Dirschel, Steve schrieb am 25.01.2023 um 20:36: > When I connect to the database through DBeaver with those 2 default > settings changed and find that session in pg_stat_activity column > xact_start is populated along with backend_xmin. Those get > populated just by logging in. As you found out in the log, the driver runs DbEaver run multiple SQL statements during the "log in". And the first query will start a transaction, but as autocommit is disabled, nothing will end that transaction. > The problem is users will connect using DBeaver and their sessions > will sit idle. Idle is not a problem, "idle in transaction" is. > It executes the exact same commands except when the 2 default > DBeaver settings are changed to show the issue it is issuing a BEGIN > and COMMIT around one block of code and then at another part of code > it issues a BEGIN, runs some queries, and never issues a COMMIT. Yes, that's how turning off autocommit works. As soon as a statement is sent through the JDBC driver, the driver will send a BEGIN to start the transaction, but the the application (or the user) is responsible to end it through a COMMIT (or ROLLBACK).
>> When I connect to the database through DBeaver with those 2 default >> settings changed and find that session in pg_stat_activity column >> xact_start is populated along with backend_xmin. Those get populated >> just by logging in. >As you found out in the log, the driver runs DbEaver run multiple SQL statements during the "log in". >And the first query will start a transaction, but as autocommit is disabled, nothing will end that transaction. Yes, I agree with that. And if I set autocommit off in psql I see the same where a select will start a transaction. >> The problem is users will connect using DBeaver and their sessions >> will sit idle. >Idle is not a problem, "idle in transaction" is. From my perspective "idle in transaction" isn't necessarily a problem (although I don't like seeing sessions sitting likethat for a long time). The problem is when pg_stat_activity.backend_xmin is populated- that can prevent autovacuumfrom cleaning up old records. Again, if I login to psql, set auto commit off and run a select I see pg_stat_activity.xact_startpopulated but pg_stat_activity.backend_xmin is NOT populated. So that transaction from psql wouldnot prevent autovacuum from cleaning up. But when this happens through DBeaver not only is pg_stat_activity.xact_startpopulated but pg_stat_activity.backend_xmin is also populated. My main question is what couldDBeaver be doing to get pg_stat_activity.backend_xmin populated? It doesn't happen when running a test in psql. Soat this point I'm unable to duplicate this issue in psql running the same thing I think DBeaver is running. Maybe ifI asked the question a little differently- in psql if I set autocommit off and run a select what else do I need to runto get pg_stat_activity.backend_xmin populated through that session? Is there a certain "type" of select I could runto get it populated? I know if I insert or update or delete a row it will get populated but I also know DBeaver is notexecuting an insert/update/delete. >> It executes the exact same commands except when the 2 default DBeaver >> settings are changed to show the issue it is issuing a BEGIN and >> COMMIT around one block of code and then at another part of code it >> issues a BEGIN, runs some queries, and never issues a COMMIT. >Yes, that's how turning off autocommit works. As soon as a statement is sent through the JDBC driver, the driver will senda BEGIN to start the transaction, but the the application (or the user) is responsible to end it through a COMMIT (orROLLBACK). Thanks
[snip]
The problem is users will connect using DBeaver and their sessions will sit idle.Idle is not a problem, "idle in transaction" is.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).
Well, it is, since you are sitting in an open transaction.
This fixes it: idle_in_transaction_session_timeout
https://www.postgresql.org/docs/9.6/runtime-config-client.html
Born in Arizona, moved to Babylonia.
> 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 sitting likethat 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.
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