Incorrect type lookup for array of off-path types

[MMeent]

Please read https://stackoverflow.com/help/minimal-reproducible-example

Describe the issue
If I define an array of a custom type in a table that is not on the search path / requires schema prefixing, then that array will fail to detect as an array.

The root cause of this issue is that the preparedStatement returned by TypeInfoCache#prepareGetTypeInfoStatement (and used by TypeInfoCache#getSQLType) is run with the type name as exposed to the user, i.e. quoted and with schema qualifications. As the schema qualified sql type cannot be found in the pg_catalog.pg_type catalog under that name, no entry is returned and the type is subsequently considered 'custom'.

Driver Version?
42.2.18

Java Version?
1.8.0_272

OS Version?
Debian Stretch (9.13)

PostgreSQL Version?
11.9

To Reproduce

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

public class TestPostgresIssues {
    public static void main(String []args) throws Exception {
        String url = "jdbc:postgresql://localhost:5432/test";
        Properties props = new Properties();
        props.setProperty("user", "postgres");
        props.setProperty("password", "postgres");
        try (Connection conn = DriverManager.getConnection(url, props)) {
            // Create schema for testing. NOT on search_path.
            try (Statement statement = conn.createStatement()) {
                statement.execute("CREATE SCHEMA test");
            }
            // Create custom type in that schema
            try (Statement statement = conn.createStatement()) {
                statement.execute("CREATE TYPE test.test_enum AS ENUM ('val')");
            }
            // ... and a table that uses that type in an array.
            try (Statement statement = conn.createStatement()) {
                statement.execute("CREATE TABLE test.test_table (val test.test_enum[])");
            }
        }
        try (Connection conn = DriverManager.getConnection(url, props)) {
            ResultSet resultSet = conn.getMetaData().getColumns(null, "test", null, "%");
            resultSet.next();
            System.out.format("Type: [%s], JSQL-type: [%s]", resultSet.getString("TYPE_NAME"), resultSet.getString("DATA_TYPE"));
            // The only result we should be getting is a column of an array type (2003?). The current result is of unknown type (1111)
            if("1111".equals(resultSet.getString("DATA_TYPE"))) {
                throw new AssertionError("array is an unknown type");
            };
        }
    }
}

Expected behaviour
Schema discovery of the test schema will detect attribute 'vars' of table 'testtable' as an array type, which has component type 'testenum'.

[davecramer]

Thought about this a bit and I don't think we want to do this.

What happens if I define a type in schema 1 and a type with the same name in schema 2. Ideally we only want to find the types on the search path.

[MMeent]

I agree with the sentiment, but it currently fails to correctly resolve some types that are not on the path. The schema discovery should (in my humble opinion) not resolve column table(var test.test_enum) to public.test_enum because that test_enum is on the search path. It could very well result in incorrect behaviour.

I believe it is beneficial to instead update the code in getSQLType(oid) to query pg_type on OID directly instead of relying on the (poisoned with FQ names) oidToPgName cache, and then looking up the type by name in pg_type.

Reversing the relation between getSQLType(String) and getSQLType(oid) should be enough to fix this issue.

[davecramer]

So how do you propose to deal with aliased types ?

[MMeent]

I'm not quite sure what you mean by that. DatabaseMetadata#getColumns correctly uses TypeInfoCache#getSQLType(oid), which by definition should not have aliases/cannot shadow other type definitions (as type oids should be unique). The implementation of getSQLType(oid) then incorrectly assumes that getSQLType(name) will result in correct answers, but that function only gives correct results for names that are not fully qualified, and those names are only of the types that are visible on the search path (see TypeInfoCache#getPGType(oid))

[MMeent]

@davecramer PR #1949 basically implements the changes required to fix the type discovery failure.

[MMeent]

@davecramer I see that 42.2.19 has been released, but it does not contain the fix for this issue. Would you know when the fix will be included in a release?

[jie-d]

+1 to the above question. 42.2.22 which was released two weeks ago still doesn't seem to contain this fix. Can we possibly include it in one of the upcoming releases? @davecramer