Unable to view table structure in db clients (e.g. dbeaver or table plus)

[weyert]

What type of bug is this?

Incorrect result

What subsystems are affected?

Table Engine

Minimal reproduce step

1. Start the nginx demo from https://github.com/GreptimeTeam/demo-scene/tree/main/nginx-log-metrics
2. Connect via postgreSQL to the database in DBeaver or Table Plus

What did you expect to see?

I would expect that I can browse the table structure

What did you see instead?

Depending on the user db client I am seeing different behaviour but the end result of no structure table is the same:

1. DBeaver Community: I am getting an error when trying to connect: ERROR: Failed to describe statement
2. Table Plus (macOS): Successfully connects but when navigating to the structure view of a table it is empty

I can execute the query DESCRIBE "public"."ngx_access_log" in the latter db client (Table Plus) and returns the following:

Column	Type	Key	Null	Default	Semantic Type
client	String		YES		FIELD
application_id	String		YES		FIELD
upstream_id	String		YES		FIELD
referer	String		YES		FIELD
method	String		YES		FIELD
endpoint	String		YES		FIELD
trace_id	String		YES		FIELD
protocol	String		YES		FIELD
status	UInt16		YES		FIELD
response_time	Int16		YES		FIELD
response_size	Float64		YES		FIELD
agent	String		YES		FIELD
access_time	TimestampMillisecond	PRI	NO		TIMESTAMP
log_time	TimestampMillisecond		NO		FIELD
ip_address	String		YES		FIELD

What operating system did you use?

Docker (linux/amd64) on Mac OS X 10.5 ARM

What version of GreptimeDB did you use?

0.11.1

Relevant log output and stack trace

TablePlus seems to log the following queries that might be related to fetching the table structure? I can't see any relevant logs inside `greptimedb` itself.


-- 2025-01-02 12:30:55.4400
SELECT * FROM "public"."ngx_access_log" LIMIT 300 OFFSET 0;

-- 2025-01-02 12:30:55.4430
select reltuples::int8 as count from pg_class c JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace where nspname='public'AND relname='ngx_access_log';

[yihong0618]

for dbeaver the error is from this sql: https://github.com/dbeaver/dbeaver/blob/5c0cd0c580d45c423821c99bf0876ab9aa732680/plugins/org.jkiss.dbeaver.ext.postgresql/src/org/jkiss/dbeaver/ext/postgresql/model/PostgreDatabase.java#L187

you can check the log here.

cd ~/Library/DBeaverData/workspace6/.metadata

cat dbeaver-debug.log

Caused by: org.postgresql.util.PSQLException: ERROR: Failed to describe statement
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2725)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2412)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:371)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:502)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:419)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:194)
	at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:137)
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCPreparedStatementImpl.executeQuery(JDBCPreparedStatementImpl.java:233)
	at org.jkiss.dbeaver.ext.postgresql.model.PostgreDatabase.readDatabaseInfo(PostgreDatabase.java:189)
	... 14 more
2025-01-03 17:52:22.454 - Error reading SQL keywords: ERROR: Failed to describe statement
2025-01-03 17:52:22.465 - Error reading system information from the pg_enum table: ERROR: Failed to describe statement
2025-01-03 17:52:22.467 - Error reading system information from the pg_class table: ERROR: Failed to describe statement
2025-01-03 17:52:22.469 - Error initializing datasource
org.jkiss.dbeaver.DBDatabaseException: SQL 错误 [XX000]: ERROR: Failed to describe statement
	at org.jkiss.dbeaver.ext.postgresql.model.PostgreDatabase.cacheDataTypes(PostgreDatabase.java:751)
	at org.jkiss.dbeaver.ext.postgresql.model.PostgreDataSource.initialize(PostgreDataSource.java:468)
	at org.jkiss.dbeaver.registry.DataSourceDescriptor.openDataSource(DataSourceDescriptor.java:1435)
	at org.jkiss.dbeaver.registry.DataSourceDescriptor.connect0(DataSourceDescriptor.java:1278)
	at org.jkiss.dbeaver.registry.DataSourceDescriptor.connect(DataSourceDescriptor.java:1068)
	at org.jkiss.dbeaver.runtime.jobs.ConnectJob.run(ConnectJob.java:78)
	at org.jkiss.dbeaver.runtime.jobs.ConnectionTestJob.run(ConnectionTestJob.java:103)
	at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:119)
	at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: org.postgresql.util.PSQLException: ERROR: Failed to describe statement
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2725)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2412)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:371)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:502)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:419)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:194)
	at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:137)
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCPreparedStatementImpl.executeQuery(JDBCPreparedStatementImpl.java:233)
	at org.jkiss.dbeaver.ext.postgresql.model.PostgreDatabase.cacheDataTypes(PostgreDatabase.java:723)
	... 8 more
2025-01-03 17:52:22.482 - Execution context closed (jdbc:postgresql://localhost:4003/public, 20)
2025-01-03 17:52:22.521 - ERROR: Failed to describe statement
org.postgresql.util.PSQLException: ERROR: Failed to describe statement
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2725)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2412)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:371)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:502)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:419)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:194)
	at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:137)
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCPreparedStatementImpl.executeQuery(JDBCPreparedStatementImpl.java:233)
	at org.jkiss.dbeaver.ext.postgresql.model.PostgreDatabase.cacheDataTypes(PostgreDatabase.java:723)
	at org.jkiss.dbeaver.ext.postgresql.model.PostgreDataSource.initialize(PostgreDataSource.java:468)
	at org.jkiss.dbeaver.registry.DataSourceDescriptor.openDataSource(DataSourceDescriptor.java:1435)
	at org.jkiss.dbeaver.registry.DataSourceDescriptor.connect0(DataSourceDescriptor.java:1278)
	at org.jkiss.dbeaver.registry.DataSourceDescriptor.connect(DataSourceDescriptor.java:1068)
	at org.jkiss.dbeaver.runtime.jobs.ConnectJob.run(ConnectJob.java:78)
	at org.jkiss.dbeaver.runtime.jobs.ConnectionTestJob.run(ConnectionTestJob.java:103)
	at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:119)
	at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)

[killme2008]

Reply from @sunng87 in slack:

We haven't implemented all information schema queries of postgres (which will a huge amount of work).

I'll create an tracking issue for it.

[sunng87]

There are a few statements and built-in functions we need to support. I will record them here if anyone is interested.

• 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 = $1
• SELECT typinput='pg_catalog.array_in'::regproc as is_array, typtype, typname, pg_type.oid 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 pg_type.oid = $1 ORDER BY sp.r, pg_type.oid DESC
• SHOW search_path
• SELECT db.oid,db.* FROM pg_catalog.pg_database db WHERE datname=$1
• select * from pg_catalog.pg_settings where name=$1
• 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 * FROM pg_catalog.pg_enum WHERE 1<>1 LIMIT 1
• SELECT reltype FROM pg_catalog.pg_class WHERE 1<>1 LIMIT 1
• 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')
• SELECT * FROM pg_catalog.pg_enum
• SELECT c.oid,c.*,d.description,pg_catalog.pg_get_expr(c.relpartbound, c.oid) as partition_expr, pg_catalog.pg_get_partkeydef(c.oid) as partition_key FROM pg_catalog.pg_class c LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=c.oid AND d.objsubid=0 AND d.classoid='pg_class'::regclass WHERE c.relnamespace=$1 AND c.relkind not in ('i','I','c')
• select c.oid,pg_catalog.pg_total_relation_size(c.oid) as total_rel_size,pg_catalog.pg_relation_size(c.oid) as rel_size FROM pg_class c WHERE c.relnamespace=$1

[yihong0618]

I would like to take a look maybe can handle some of these

[killme2008]

I would like to take a look maybe can handle some of these

Cool! I would love to handle some of them, too

[yihong0618]

@sunng87 first line in https://github.com/GreptimeTeam/greptimedb/pull/5313 will try to support pg_database next

[yihong0618]

for SELECT db.oid,db.* FROM pg_catalog.pg_database db WHERE datname=$1 part

error found same as https://github.com/xtdb/xtdb/issues/3781

SELECT db.oid,db.* FROM pg_catalog.pg_database db WHERE datname=$1;                      ERROR:  Failed to plan SQL: Error during planning: Projections require unique expression names but the expression "db.oid" at position 0 and "db.oid" at position 1 have the same name. Consider aliasing ("AS") one of them.

after some search seems its better to wait https://github.com/apache/datafusion/pull/13489 done

[J0HN50N133]

@yihong0618 FYI I've supported pg_enum related query and extended pg_catalog to support the related query. Please avoid doing duplicated work. I could make a pr this weekend.

[yihong0618]

@yihong0618 FYI I've supported pg_enum related query and extended pg_catalog to support the related query. Please avoid doing duplicated work. I could make a pr this weekend.

maybe I would take pg_setting this weekend

[sunng87]

I have implemented a portable pg_catalog on datafusion, and with https://github.com/datafusion-contrib/datafusion-postgres/pull/140 it has good compatibility with dbeaver (at least for startup)

Hopefully we can port it into greptimedb.

[yihong0618]

amazing!

[sunng87]

First batch coming in #6918

[sunng87]

dbeaver database navigator works after #7025

I'm going to close this with #7025 . Of course there are more database viewer or BI tools to support but for now we should already have capable with dbeaver, psql, pgcli or even more. Other tools will be tracked and supported from the datafusion-postgres library.

[weyert]

Awesome. I will give it a try with Table Plus in the upcoming release