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.
This e-mail is for the sole use of the intended recipient and contains
information that may be privileged and/or confidential. If you are not an
intended recipient, please notify the sender by return e-mail and delete this
e-mail and any attachments. Certain required legal entity disclosures can be
accessed on our website:
https://www.thomsonreuters.com/en/resources/disclosures.html
