|
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. |
