Thread: Type OID search does not support search_path and multiple schemas

Type OID search does not support search_path and multiple schemas

From

Valentine Gogichashvili

Date:

08 March 2011, 16:27:40

[Reposting it now via mail-list directly and not via Google Groups]

When searching for OID of a Type, current search_path is not considered and if type with the same name exists in several schemas, 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 lookup is 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 names without " and , charachters (and version lookup is not really done quite nicely as I am connecting now only to 9.0 version of 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) ) as r 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) ) as r 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 = ?";