Skip site navigation (1) Skip section navigation (2)

Re: Found a Bug in latest Driver (I THINK) and pg 8.4

From: Jason Tesser <jasontesser(at)gmail(dot)com>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: pgsql-jdbc <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: Found a Bug in latest Driver (I THINK) and pg 8.4
Date: 2010-06-01 19:02:23
Message-ID: AANLkTinaVIn6ga3WtlmMkn7tYqZzDeRhJZwrjsVTVPr4@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
No from psql all works.

This is specific to the driver for sure.  I will build a test case. You need
java though.

The issue with teh driver is that when you use a composite type as an INPUT
parameter of a STored Proc teh Driver queries the pg_catalog but from what I
can tell has no way of telling what schema to query for.  The issue arises
because the wrong type is return cause more then one table have the same
name and it gets the wrong table/type to use as the input parameter. So   I
end up with an error because I end up calling schema2.myfunc(schema1.table)
when I should be calling schema2.myfunc(schema2.table)

I wil build an isolated case but I was able to reproduce this again and
again. And again I tailed the query log and saw the driver get the wrong
type to use as the IN param.

Solving this would be hard I think.  You can use the searchpath to try and
fix that query. Or maybe rules like it would do a combination of that and
looking in the schema where the function resides. All of it is weird to be
honest and outside cases.  I believe all works in psql cause I set the
search_path and it uses the proper table/type based on that.  The driver
ignores this.


On Tue, Jun 1, 2010 at 2:15 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov>wrote:

> Jason Tesser <jasontesser(at)gmail(dot)com> wrote:
>
> > Steps to reproduce
>
> Is this actually specific to JDBC or can you create the situation
> with statements in psql?  If so, showing a session that starts from
> an empty database and show the error would help people to understand
> your issue and provide better assistance.  (Even if you need to use
> Java to show the issue, if you can put together the smallest
> possible self-contained case, it helps a lot.)
>
> One thing I wonder about, based on available information, is whether
> you might be expecting the search path at *execution* time to affect
> the parameter types of the function.  Offhand, I would expect those
> to be set at CREATE FUNCTION time.
>
> -Kevin
>

In response to

Responses

pgsql-jdbc by date

Next:From: Manohar BhattaraiDate: 2010-06-01 20:19:11
Subject: Not able to connect to postgresql database
Previous:From: Anderson, Mark S.Date: 2010-06-01 18:48:13
Subject: PSQLException: ERROR: operator does not exist: integer = character varying

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group