When searching for OID of a Type, current search_path is not considered and if type with the same name exists in
severalschemas, not a valid OID is fetched to be cached in TypeInfoCache class.
One of the possible solutions, would be to use select 'schema_name.type_name'::regtype::oid to get the OID. But as the
lookupis now done without using schema name this method is not easy to be used with the current implementation of the
TypeInfoCache...
My patch of the file org/postgresql/jdbc2/TypeInfoCache.java, that would work for search_path's that contain schema
nameswithout " and , charachters (and version lookup is not really done quite nicely as I am connecting now only to 9.0
versionof the DB... Anyway it is better, than a current implementation:
190,203c190,192
< String sql;
<
< if (_conn.haveMinimumServerVersion("8.4")) {
< sql = "select distinct on ( typname ) typinput='array_in'::regproc, typtype\n" +
< " from pg_type as t \n" +
< " join pg_namespace as n on typnamespace = n.oid \n" +
< " left join ( select row_number() over() as rank, \n" +
< " case when n = '\"$user\"' then current_user else btrim(n,' \"') end as
schema_name\n" +
< " from regexp_split_to_table(current_setting('search_path'), E', ?' ) as r(n) )
asr on nspname = schema_name \n" +
< " where typname = ? \n" +
< " order by typname, rank nulls last, oid desc";
< } else {
< // actually this is not correct, but we do not care for that old versions now
< sql = "SELECT typinput='array_in'::regproc, typtype FROM pg_type WHERE typname = ? order by oid
desc";
---
> String sql = "SELECT typinput='array_in'::regproc, typtype FROM ";
> if (_conn.haveMinimumServerVersion("7.3")) {
> sql += "pg_catalog.";
204a194
> sql += "pg_type WHERE typname = ?";
248,256c238,239
< if (_conn.haveMinimumServerVersion("8.4")) {
< sql = "select distinct on ( typname ) t.oid\n" +
< " from pg_type as t \n" +
< " join pg_namespace as n on typnamespace = n.oid \n" +
< " left join ( select row_number() over() as rank, \n" +
< " case when n = '\"$user\"' then current_user else btrim(n, ' \"') end as
schema_name\n" +
< " from regexp_split_to_table(current_setting('search_path'), E', ?' ) as r(n) )
asr on nspname = schema_name \n" +
< " where typname = ? \n" +
< " order by typname, rank nulls last, oid desc";
---
> if (_conn.haveMinimumServerVersion("7.3")) {
> sql = "SELECT oid FROM pg_catalog.pg_type WHERE typname = ?";