Message-ID: <gh-pgjdbc-pgjdbc-3244@github.com>
From: "vlsi (@vlsi)" <noreply+vlsi@github.com>
To: "pgjdbc/pgjdbc" <noreply+pgjdbc-pgjdbc@github.com>
Date: Sun, 12 May 2024 13:42:58 +0000
Subject: [pgjdbc/pgjdbc] PR #3244: refactor: use oid=?::regtype when fetching pg_type by name instead of joining pg_namespace
List-Id: <gh-pgjdbc-pgjdbc.github.com>
X-GitHub-Additions: 3
X-GitHub-Author-Id: 213894
X-GitHub-Author-Login: vlsi
X-GitHub-Base: master
X-GitHub-Changed-Files: 1
X-GitHub-Commits: 1
X-GitHub-Deletions: 15
X-GitHub-Head-Branch: typeinfocache_getOidStatement
X-GitHub-Head-SHA: dc5142aa9edc966786d6ee6207c9d4127b3aa4ea
X-GitHub-Issue: 3244
X-GitHub-Labels: backward-incompatible
X-GitHub-Merge-SHA: a9066a430a77080b43e335ae714f512efdedc9b8
X-GitHub-Repo: pgjdbc/pgjdbc
X-GitHub-State: closed
X-GitHub-Type: pull_request
X-GitHub-Url: https://github.com/pgjdbc/pgjdbc/pull/3244
Content-Type: text/plain; charset=utf-8

This makes the query simpler, and it aligns the lookup semantics with the one of the database.

There is a couple of places where `TypeInfoCache` still uses `typname = ?`, however, I am not sure regarding their semantics, so making a small change for now.

diff --git a/pgjdbc/src/main/java/org/postgresql/jdbc/TypeInfoCache.java b/pgjdbc/src/main/java/org/postgresql/jdbc/TypeInfoCache.java
index 387648a6e1..24806cc7e6 100644
--- a/pgjdbc/src/main/java/org/postgresql/jdbc/TypeInfoCache.java
+++ b/pgjdbc/src/main/java/org/postgresql/jdbc/TypeInfoCache.java
@@ -409,21 +409,9 @@ private PreparedStatement getOidStatement(String pgTypeName) throws SQLException
     if (dotIndex == -1 && !hasQuote && !isArray) {
       PreparedStatement getOidStatementSimple = this.getOidStatementSimple;
       if (getOidStatementSimple == null) {
-        String sql;
-        // see comments in @getSQLType()
-        // -- go with older way of unnesting array to be compatible with 8.0
-        sql = "SELECT pg_type.oid, typname "
-              + "  FROM pg_catalog.pg_type "
-              + "  LEFT "
-              + "  JOIN (select ns.oid as nspoid, ns.nspname, r.r "
-              + "          from pg_namespace as ns "
-              + "          join ( select s.r, (current_schemas(false))[s.r] as nspname "
-              + "                   from generate_series(1, array_upper(current_schemas(false), 1)) as s(r) ) as r "
-              + "         using ( nspname ) "
-              + "       ) as sp "
-              + "    ON sp.nspoid = typnamespace "
-              + " WHERE typname = ? "
-              + " ORDER BY sp.r, pg_type.oid DESC LIMIT 1;";
+        String sql = "SELECT pg_type.oid, typname "
+            + "  FROM pg_catalog.pg_type "
+            + " WHERE oid = ?::regtype";
         this.getOidStatementSimple = getOidStatementSimple = conn.prepareStatement(sql);
       }
       // coerce to lower case to handle upper case type names