Type OID search does not support search_path and multiple schemas

[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 =
?";

Please consider the environment before printing this email.

E-mail messages may contain viruses, worms, or other malicious code. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective action against such code. Henry Schein is not liable for any loss or damage arising from this message.

The information in this email is confidential and may be legally privileged. It is intended solely for the addressee(s). Access to this e-mail by anyone else is unauthorized.