From: | Valentine Gogichashvili <valgog(at)gmail(dot)com> |
---|---|
To: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Type OID search does not support search_path and multiple schemas |
Date: | 2011-03-08 17:18:37 |
Message-ID: | AANLkTi=KQhq+y553YJ9W=A1EA5x23MBD2Vd1T2ts3t9g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
[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 =
?";
From | Date | Subject | |
---|---|---|---|
Next Message | Dave Cramer | 2011-03-09 11:11:37 | Re: PreparedStatement batch statement impossible |
Previous Message | Kevin Grittner | 2011-03-08 16:48:57 | Re: Invalid PSQLState used in connection classes |