Обсуждение: Found a Bug in latest Driver (I THINK) and pg 8.4
Steps to reproduce
1. You need 2 schema with a table of the same name
2. You need a stored proc that has the same name in each schema and takes the table from step 1 as an INPUT parameter.
3. From here I created a custom type in Java essentially an object that extends PGobject and Overrode the getValue() method and sets the Type when constructed.
4. I am using "set search_path TO myschema" when i get a connection but the Driver/PG gets confussed and will not call the right procedure because it tries to call schema2.myproc(schema1.mytable)
Even when I set the type to the schema it doesn't work.
I tailed the query log and the query the the Driver runs to find the OID of the composite type is based on table name and ignores the scehma even when I use set search_path.
My only solution was to keep the table names unique even in different schemas when using Composite Types and IN Params in Stored Procs.
1. You need 2 schema with a table of the same name
2. You need a stored proc that has the same name in each schema and takes the table from step 1 as an INPUT parameter.
3. From here I created a custom type in Java essentially an object that extends PGobject and Overrode the getValue() method and sets the Type when constructed.
4. I am using "set search_path TO myschema" when i get a connection but the Driver/PG gets confussed and will not call the right procedure because it tries to call schema2.myproc(schema1.mytable)
Even when I set the type to the schema it doesn't work.
I tailed the query log and the query the the Driver runs to find the OID of the composite type is based on table name and ignores the scehma even when I use set search_path.
My only solution was to keep the table names unique even in different schemas when using Composite Types and IN Params in Stored Procs.
Jason Tesser <jasontesser@gmail.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
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.
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.Grittner@wicourts.gov> wrote:
Jason Tesser <jasontesser@gmail.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
On Tue, 1 Jun 2010, Jason Tesser wrote: > 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. Right, the JDBC driver's internal knowledge of types has no information about schemas or the search path. It currently assumes the name alone is a unique identifier. Fixing this to respect the search path wouldn't be that tough by using the regtype type, but we want to cache this information to avoid repeated type lookups, so it wouldn't change as the search_path changed. The other approach to fixing things would be to allow the user to specify the schema with the type name, but I don't think people want to do that because the schema may not be known to the application. Kris Jurka
Right in my case my schema changes as each organization has a separate schema.
I would love to be able to set the schema with the typeName. Why couldn't that be a seperate attribute in Java setLookupSchema and if not set function as it does now. Wouldn't this handle both cases.
While I know I am using an outside case I do think it is a valid one.
I would love to be able to set the schema with the typeName. Why couldn't that be a seperate attribute in Java setLookupSchema and if not set function as it does now. Wouldn't this handle both cases.
While I know I am using an outside case I do think it is a valid one.
On Tue, Jun 1, 2010 at 4:34 PM, Kris Jurka <books@ejurka.com> wrote:
Right, the JDBC driver's internal knowledge of types has no information about schemas or the search path. It currently assumes the name alone is a unique identifier. Fixing this to respect the search path wouldn't be that tough by using the regtype type, but we want to cache this information to avoid repeated type lookups, so it wouldn't change as the search_path changed. The other approach to fixing things would be to allow the user to specify the schema with the type name, but I don't think people want to do that because the schema may not be known to the application.
On Tue, 1 Jun 2010, Jason Tesser wrote: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.
Kris Jurka
So Kris you are obviously aware of the issue. Who do i need to talk to or is there someone on here that can confirm if it is possible to get this included in the driver?
On Tue, Jun 1, 2010 at 5:10 PM, Jason Tesser <jasontesser@gmail.com> wrote:
Right in my case my schema changes as each organization has a separate schema.
I would love to be able to set the schema with the typeName. Why couldn't that be a seperate attribute in Java setLookupSchema and if not set function as it does now. Wouldn't this handle both cases.
While I know I am using an outside case I do think it is a valid one.On Tue, Jun 1, 2010 at 4:34 PM, Kris Jurka <books@ejurka.com> wrote:Right, the JDBC driver's internal knowledge of types has no information about schemas or the search path. It currently assumes the name alone is a unique identifier. Fixing this to respect the search path wouldn't be that tough by using the regtype type, but we want to cache this information to avoid repeated type lookups, so it wouldn't change as the search_path changed. The other approach to fixing things would be to allow the user to specify the schema with the type name, but I don't think people want to do that because the schema may not be known to the application.
On Tue, 1 Jun 2010, Jason Tesser wrote: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.
Kris Jurka
On Tue, 1 Jun 2010, Jason Tesser wrote: > Right in my case my schema changes as each organization has a separate > schema. > > I would love to be able to set the schema with the typeName. Why couldn't > that be a seperate attribute in Java setLookupSchema and if not set function > as it does now. Wouldn't this handle both cases. I'm not sure where you are suggesting that this attribute would live. Are you suggesting we should have a PGConnection method setSearchPath(String) that would set the search path on the server side and make the driver aware of the change? Are you suggesting that PGObject should have a getTypeSchema()? Can you clarify your suggestion? Kris Jurka
Well PSQL respects search_path but given your objections before and the fact that this would be a change I was suggesting that on PG_OBJECT an option be given to set/getTypeSchema() This way if not set it functions as it does now. No change to current usaage BUT if it is set in the PGOBECT then it would be respected.
I think this provides a way of handling the case which PSQL does and doesn't break backwards compatibility.
I think this provides a way of handling the case which PSQL does and doesn't break backwards compatibility.
On Wed, Jun 2, 2010 at 6:03 AM, Kris Jurka <books@ejurka.com> wrote:
On Tue, 1 Jun 2010, Jason Tesser wrote:I'm not sure where you are suggesting that this attribute would live. Are you suggesting we should have a PGConnection method setSearchPath(String) that would set the search path on the server side and make the driver aware of the change? Are you suggesting that PGObject should have a getTypeSchema()? Can you clarify your suggestion?Right in my case my schema changes as each organization has a separate
schema.
I would love to be able to set the schema with the typeName. Why couldn't
that be a seperate attribute in Java setLookupSchema and if not set function
as it does now. Wouldn't this handle both cases.
Kris Jurka