Thread: createArrayOf, type resolved outside search_path on arrays of composite

Hi,

I've got a composite type testtype and a function testfunction(testtype[])
of the same name in two schemas.
The function is called with callableStatement and the parameter set with
createArrayOf() of jdbc 4.
The error received when calling the function: ERROR: function
testfunction(testschema1.testtype[]) does not exist
suggests that the unqualified type is resolved to a type from a schema not
in the search_path.

I've tried calling createArrayOf with a schema qualified type which gives
this error:
Unable to find server array type for provided name testschema2.testtype

Here's the code to reproduce:

import java.io.*;
import java.sql.*;

public class Test_search_path {
   class testtype {
      Integer col1;

      /*
      * Override toString() because without this the PostgreSQL JDBC 4
driver method createArrayOf()
      * have the PostgreSQL server throws "malformed record literal" with
arrays of composite
      */
      public String toString() {
         StringBuffer sb = new StringBuffer("(");

         if (col1 != null) {
            org.postgresql.jdbc2.AbstractJdbc2Array.escapeArrayElement(sb,
col1.toString());
         }

         sb = sb.append(")");

         return sb.toString();
      }
   }

   public static void main(String [ ] args)
   throws SQLException  {
      Connection        connection = null;
      CallableStatement cstmt      = null;
      Statement         stmt       = null;

      try {
         connection = DriverManager.getConnection("jdbc:postgresql:main",
"postgres", null);
         stmt = connection.createStatement();
         connection.setAutoCommit(false);

         // set schema 1
         stmt.execute("CREATE SCHEMA testschema1");
         stmt.execute("CREATE TYPE testschema1.testtype AS (col1 integer)");
         stmt.execute("CREATE OR REPLACE FUNCTION
testschema1.testfunction(testschema1.testtype []) RETURNS void AS $$ $$
LANGUAGE 'sql'");

         // set schema 2
         stmt.execute("CREATE SCHEMA testschema2");
         stmt.execute("CREATE TYPE testschema2.testtype AS (col1 integer)");
         stmt.execute("CREATE OR REPLACE FUNCTION
testschema2.testfunction(testschema2.testtype []) RETURNS void AS $$ $$
LANGUAGE 'sql'");

         // call with schema 1, Ok
         stmt.execute("SET SEARCH_PATH = testschema1");
         cstmt = connection.prepareCall("{call testfunction(?) }");
         cstmt.setObject(1, connection.createArrayOf("testtype", new
testtype[0]));
         cstmt.execute();

         // call with schema 2, error indicates "testschema1" which is not
in Search Path
         stmt.execute("SET SEARCH_PATH = testschema2");
         cstmt = connection.prepareCall("{call testfunction(?) }");
         cstmt.setObject(1, connection.createArrayOf("testtype", new
testtype[0]));
         cstmt.execute(); // ERROR: function
testfunction(testschema1.testtype[]) does not exist

         // call with schema 2, error with schema qualified type name
         stmt.execute("SET SEARCH_PATH = testschema2");
         cstmt = connection.prepareCall("{call testfunction(?) }");
         cstmt.setObject(1, connection.createArrayOf("testschema2.testtype",
new testtype[0]));
         cstmt.execute(); // Unable to find server array type for provided
name testschema2.testtype
      }

      finally {
         if (connection != null) {
            connection.rollback(); // always rollback so we can repeat this
test
         }

         if (stmt != null) {
            stmt.close();
         }

         if (cstmt != null) {
            cstmt.close();
         }

         if (connection != null) {
            connection.close();
         }
      }
   }
}

On Tue, 9 Sep 2008, Jean-Pierre Pelletier wrote:

> I've got a composite type testtype and a function
> testfunction(testtype[]) of the same name in two schemas. The function
> is called with callableStatement and the parameter set with
> createArrayOf() of jdbc 4. The error received when calling the function:
> ERROR: function testfunction(testschema1.testtype[]) does not exist
> suggests that the unqualified type is resolved to a type from a schema
> not in the search_path.

The problem is actually that the type cache is not schema aware.  So it
first resolves the name to an OID in testschema1 and when you call it
again with the same name, the driver doesn't bother checking the
search path or the database and simply uses the OID it resolved
previously.  I don't know of any workaround for this other than using type
names that are unique across schemas.

Kris Jurka

Jean-Pierre Pelletier wrote:
>
> Can the type cache be flushed programmatically ?

No.

> Could a workaround be for createArrayOf() to be extended
> to accept schema qualified type name?

I think that would only be part of the fix, you'd still need to make the
type cache schema aware (even if the driver didn't track changes to
search_path).  The JDBC javadoc for createArrayOf states that the
typeName parameter "is the value returned by Array.getBaseTypeName".  So
you'd need to change that to return a schema qualified name and I'm not
sure what else would need to change.

> An unrelated problem is that I had to override toString()
> in testtype to avoid "malformed record literal", is this the
> recommended way to handle composite type?
>

Right now the JDBC driver doesn't know much about composite types, so
that does seem like the correct workaround.

Kris Jurka