Re: [JDBC] TypeInfoCache.getPGType(pgTypeName) drops schema name when caching - Mailing list pgsql-jdbc

> On 2017-07-16, at 01:53, Vladimir Sitnikov <sitnikov.vladimir@gmail.com> wrote:
>
> Michael>_pgNameToOid.put(schema + "." + name, oid)
> Michael>I think it makes sense to follow this behavior in getPGType(pgTypeName).
>
> The idea of type cache is to avoid pg_catalog SQLs on the hot path.
> Consider a user issuing `createArrayOf`. Do you mean pgjdbc should query the catalog on each invocation of
createArrayOf?I'm not sure it would be the right thing, as it is basically the only way to create arrays. 

Sorry if I wasn't clear. I understand the purpose of the cache and agree that make sense to cache type information.
WhatI'm trying to get at is the cached name values are different depending on whether getPGType(pgTypeName) or
getPGPGType(oid)are used resulting in inconsistent behavior. 

Here's an example that illustrates what I'm seeing.

> cat GetPGTypeTest.java
/**
   export CLASSPATH="postgresql-42.1.3.jar:."

   javac GetPGTypeTest.java && java -Djdbc.drivers=org.postgresql.Driver GetPGTypeTest

   export PGPORT=5496
   creatdb get_pg_type_test;
   psql -d get_pg_type_test -c "CREATE SCHEMA cars;"
   psql -d get_pg_type_test -c "CREATE TYPE cars.color AS ENUM ('black')"

   Any customer can have a car painted any color that he wants so long as it is black.
   -- Henry Ford
*/

import java.sql.*;
import org.postgresql.util.*;

public class GetPGTypeTest
{
    static String url = "jdbc:postgresql://localhost:5496/get_pg_type_test";

    static String sql = "SELECT CAST(? AS cars.color)";
    static String color = "black";
    static String colorType = "cars.color";

    static String getColumnTypeName(PreparedStatement st) throws SQLException {
        ResultSet rs = st.executeQuery();
        ResultSetMetaData md = rs.getMetaData();
        rs.close();
        return md.getColumnTypeName(1);
    }

    static String getStringTypeName(Connection conn) throws SQLException {
        PreparedStatement st = conn.prepareStatement(sql);
        st.setString(1, color);
        return getColumnTypeName(st);
    }

    static String getObjectTypeName(Connection conn) throws SQLException {
        PreparedStatement st = conn.prepareStatement(sql);
        PGobject colorObject = new PGobject();
        colorObject.setType(colorType);
        colorObject.setValue(color);
        st.setObject(1, colorObject);
        return getColumnTypeName(st);
    }

    public static void main(String[] args) {

        Connection conn = null;

        try {
            System.out.println("## setString first -- caching via getPGType(oid)");
            conn = DriverManager.getConnection(url);
            System.out.format("string: %s%n", getStringTypeName(conn));
            System.out.format("object: %s%n", getObjectTypeName(conn));

            System.out.println("## setObject first -- caching via getPGType(pgTypeName)");
            conn = DriverManager.getConnection(url);
            System.out.format("object: %s%n", getObjectTypeName(conn));
            System.out.format("string: %s%n", getStringTypeName(conn));
        }
        catch (SQLException e) {
            System.err.println(e.getMessage());
            System.exit(1);
        }

    }
}

> export CLASSPATH="postgresql-42.1.3.jar:."
> javac GetPGTypeTest.java
> java -Djdbc.drivers=org.postgresql.Driver GetPGTypeTest
## setString first -- caching via getPGType(oid)
string: "cars"."color"
object: "cars"."color"
## setObject first -- caching via getPGType(pgTypeName)
object: color
string: color

The caching to the cars.colors type name is different depending on whether getPGType(pgTypeName) and getPGType(oid) is
doingthe caching. getPGType(pgTypeName) doesn't include the schema name when the type name is cached. This is clear
fromthe differences in code used to generate the type names: 


In getPGType(pgTypeName):

https://github.com/pgjdbc/pgjdbc/blob/cb3995b5a0311a2f5f7737fdfe83457680305efb/pgjdbc/src/main/java/org/postgresql/jdbc/TypeInfoCache.java#L376-L377

String internalName = rs.getString(2); // typname, from getOidStatement
_oidToPgName.put(oid, internalName);


In getPGType(oid):

https://github.com/pgjdbc/pgjdbc/blob/cb3995b5a0311a2f5f7737fdfe83457680305efb/pgjdbc/src/main/java/org/postgresql/jdbc/TypeInfoCache.java#L412-L432

pgTypeName = "\"" + schema + "\".\"" + name + "\"";
// ...
_oidToPgName.put(oid, pgTypeName);


The behavior is inconsistent and arguably wrong in the case of getPGType(pgTypeName): if I have types with the same
pg_type.typnamebut in different schemas, ResultSetMetaData.getColumnTypeName doesn't provide a way to distinguish
betweenthem if getPGType(pgTypeName) is doing the caching. 

I hope this is a clearer explanation of the behavior. If it isn't, please let me know.

Best,

Michael Glaesemann
grzm seespotcode net