Cannot find JDBC type '2003' in PostgreSQL column type

[1ssqq1lxr]

SELECT d.oid, d.datname AS databasename, d.datacl, pg_get_userbyid(d.datdba) AS databaseowner, d.datcollate, d.datctype, shobj_description(d.oid, 'pg_database') AS description, d.datconnlimit, t.spcname, d.encoding, pg_encoding_to_char(d.encoding) AS encodingname FROM pg_database d LEFT JOIN pg_tablespace t ON d.dattablespace = t.oid ::: DataSources: initds_master
[ERROR] 02:51:14.421 [pool-4-thread-1] o.a.s.s.f.c.CommandExecutorTask - Exception occur:
java.lang.IllegalArgumentException: Cannot find JDBC type '2003' in PostgreSQL column type

[jingshanglu]

@1ssqq1lxr Change sql to "SELECT d.oid, d.datname AS databasename, d.datacl, pg_get_userbyid(d.datdba) AS databaseowner, d.datcollate, d.datctype, shobj_description(d.oid, 'pg_database') AS description, d.datconnlimit, t.spcname, d.encoding, pg_encoding_to_char(d.encoding) AS encodingname FROM pg_database d LEFT JOIN pg_tablespace t ON d.dattablespace = t.oid", is it ok?

[1ssqq1lxr]

PG DB returned normally

…

------------------&nbsp;原始邮件&nbsp;------------------ 发件人: "JingShang Lu"<[email protected]&gt;; 发送时间: 2020年7月23日(星期四) 中午11:30 收件人: "apache/shardingsphere"<[email protected]&gt;; 抄送: "目光所及只有你"<[email protected]&gt;; "Mention"<[email protected]&gt;; 主题: Re: [apache/shardingsphere] SELECT d.oid, d.datname AS databasename, d.datacl, pg_get_userbyid(d.datdba) AS databaseowner, d.datcollate, d.datctype, shobj_description(d.oid, &#39;pg_database&#39;) AS description, d.datconnlimit, t.spcname, d.encoding, pg_encoding_to_char(d.encoding) AS encodingname FROM pg_database d LEFT JOIN pg_tablespace t ON d.dattablespace = t.oid ::: DataSources: initds_master [ERROR] 02:51:14.421 [pool-4-thread-1] o.a.s.s.f.c.CommandExecutorTask - Exception occur: java.lang.IllegalArgumentException: Cannot find JDBC type &#39;2003&#39; in PostgreSQL column type (#6408) @1ssqq1lxr Change sql to "SELECT d.oid, d.datname AS databasename, d.datacl, pg_get_userbyid(d.datdba) AS databaseowner, d.datcollate, d.datctype, shobj_description(d.oid, 'pg_database') AS description, d.datconnlimit, t.spcname, d.encoding, pg_encoding_to_char(d.encoding) AS encodingname FROM pg_database d LEFT JOIN pg_tablespace t ON d.dattablespace = t.oid", is it ok? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.

[jingshanglu]

@1ssqq1lxr Ok, could the origin sql directly run on pg server?

[1ssqq1lxr]

&nbsp;the origin sql directly run on pg server&nbsp; is ok ，The result set can be returned correctly

…

------------------&nbsp;原始邮件&nbsp;------------------ 发件人: "JingShang Lu"<[email protected]&gt;; 发送时间: 2020年7月23日(星期四) 中午12:01 收件人: "apache/shardingsphere"<[email protected]&gt;; 抄送: "目光所及只有你"<[email protected]&gt;; "Mention"<[email protected]&gt;; 主题: Re: [apache/shardingsphere] SELECT d.oid, d.datname AS databasename, d.datacl, pg_get_userbyid(d.datdba) AS databaseowner, d.datcollate, d.datctype, shobj_description(d.oid, &#39;pg_database&#39;) AS description, d.datconnlimit, t.spcname, d.encoding, pg_encoding_to_char(d.encoding) AS encodingname FROM pg_database d LEFT JOIN pg_tablespace t ON d.dattablespace = t.oid ::: DataSources: initds_master [ERROR] 02:51:14.421 [pool-4-thread-1] o.a.s.s.f.c.CommandExecutorTask - Exception occur: java.lang.IllegalArgumentException: Cannot find JDBC type &#39;2003&#39; in PostgreSQL column type (#6408) @1ssqq1lxr Ok, could the origin sql directly run on pg server? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.

[jingshanglu]

@1ssqq1lxr Maybe it is the parser problem. I'll check it.

[jingshanglu]

@1ssqq1lxr What's the meaning of "::: DataSources: initds_master", I didn't find the ':::' operator in PG.

[1ssqq1lxr]

@ 1ssqq1lxr “ ::: DataSources：initds_master”是什么意思，我在PG中找不到':::'运算符。

Complete SQL： SELECT d.oid, d.datname AS databasename, d.datacl, d.datistemplate, d.datallowconn, pg_get_userbyid(d.datdba) AS databaseowner, d.datcollate, d.datctype, shobj_description(d.oid, 'pg_database') AS description, d.datconnlimit, t.spcname, d.encoding, pg_encoding_to_char(d.encoding) AS encodingname FROM pg_database d LEFT JOIN pg_tablespace t ON d.dattablespace = t.oid

Do you mean there is something wrong with my data source configuration? I have switched multiple PostgreSQL versions and still have this problem。
config-master_slave.yaml:
schemaName: 91nami
dataSources:
91ds_master:
url: jdbc:postgresql://192.168.0.6:9527/mycat?serverTimezone=UTC+8&useSSL=false&useUnicode=true&characterEncoding=UTF-8&allowEncodingChanges=true
username: postgres
password: sonic333
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 65
91ds_slave0:
url: jdbc:postgresql://192.168.0.229:9527/mycat?serverTimezone=UTC+8&useSSL=false&useUnicode=true&characterEncoding=UTF-8&allowEncodingChanges=true
username: postgres
password: sonic333
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 65

masterSlaveRule:
name: 91ds_ms
masterDataSourceName: 91ds_master
slaveDataSourceNames:
- 91ds_slave0

server.yaml:
authentication:
users:
postgresuser:
password: sonic333
authorizedSchemas: 91nami
props:
sql.show: true
acceptor.size: 16
proxy.transaction.type: LOCAL
proxy.opentracing.enabled: false
check.table.metadata.enabled: true

[jingshanglu]

@1ssqq1lxr Origin sql is "SELECT d.oid, d.datname AS databasename, d.datacl, pg_get_userbyid(d.datdba) AS databaseowner, d.datcollate, d.datctype, shobj_description(d.oid, 'pg_database') AS description, d.datconnlimit, t.spcname, d.encoding, pg_encoding_to_char(d.encoding) AS encodingname FROM pg_database d LEFT JOIN pg_tablespace t ON d.dattablespace = t.oid ::: DataSources: initds_master" , right?

[1ssqq1lxr]

@1ssqq1lxr Origin sql is "SELECT d.oid, d.datname AS databasename, d.datacl, pg_get_userbyid(d.datdba) AS databaseowner, d.datcollate, d.datctype, shobj_description(d.oid, 'pg_database') AS description, d.datconnlimit, t.spcname, d.encoding, pg_encoding_to_char(d.encoding) AS encodingname FROM pg_database d LEFT JOIN pg_tablespace t ON d.dattablespace = t.oid ::: DataSources: initds_master" , right?

origin sql is "SELECT d.oid, d.datname AS databasename, d.datacl, pg_get_userbyid(d.datdba) AS databaseowner, d.datcollate, d.datctype, shobj_description(d.oid, 'pg_database') AS description, d.datconnlimit, t.spcname, d.encoding, pg_encoding_to_char(d.encoding) AS encodingname FROM pg_database d LEFT JOIN pg_tablespace t ON d.dattablespace = t.oid" "::: DataSources: initds_master" getdatasouce log ?

[jingshanglu]

@1ssqq1lxr Ok, it is not parser problem and is the same to #3954

[kimmking]

duplicated with #6396 #6232 #6132 #3954 #3591 #3104

[kimmking]

And so it's a critical bug for PG users, we should raise priority 0 for this issue now.
@jingshanglu @tuohai666

[1ssqq1lxr]

And so it's a critical bug for PG users, we should raise priority 0 for this issue now.
@jingshanglu @tuohai666

Thank you. Can you solve it quickly

[kimmking]

And so it's a critical bug for PG users, we should raise priority 0 for this issue now.
@jingshanglu @tuohai666

Thank you. Can you solve it quickly

it will be fixed in 5.0.0
and if you need it now, I suggest you can fix it in your own fork.

[sandynz]

Hi, I'd like try to solve it

[sandynz]

I've prepared a demo and reproduced the error result.

After reading some code and debuging, java.sql.Types.ARRAY=2003, many types could be used as array and they have their own unique type oid, e.g. INT4_ARRAY=1007, INT8_ARRAY=1016, so jdbc array columnType(2003) is not enough to get exact array oid. There are 2 possible solutions for now:

1, Get resultSetMetaData from queryResponse.queryResults, use PostgreSQL's implementation PgResultSetMetaData, get field of column index via java reflection, then get type oid. The disadvantage is: a) direct dependency of postgresql jdbc driver, b) once the internal implementation of PostgreSQL jdbc driver changed, then it doesn't work.

2, Get resultSetMetaData from queryResponse.queryResults, use getColumnTypeName(column) method to get database specific type name (e.g. integer array is _int4), then translate it to type oid (e.g. 1007), PostgreSQL has the mapping definition.

I prefer solution 2, though it need more code.

[bobqian1007]

seems meet this problem again on shardingsphere proxy:5.4.1