Subject: [pgjdbc/pgjdbc] PR #3244: refactor: use oid=?::regtype when fetching pg_type by name instead of joining pg_namespace

From: "vlsi (@vlsi)" <[email-masked]>
Date: Sun, 12 May 2024 13:42:58 +0000

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

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Sun, 12 May 2024 14:04:05 +0000

The change is not compatible with the previous behavior. However, I believe the incompatibility is subtle enough so we can ship it without a connection property.

The database uses `search_path` to lookup the type, however, previously a type from any schema could match.

See https://github.com/pgjdbc/pgjdbc/commit/b383f6d2c8f19e2b5b867039ca96071ba8d495e1

I think we should follow the DB behavior, and we should support only the types listed on the `search_path` (== use DB's `::regtype` cast)

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Sun, 12 May 2024 14:39:36 +0000

I agree that the change will be fine.

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Sun, 05 Oct 2025 20:15:17 +0000

It turns out `::regtype` is not reliable in PostgreSQL.

For instance `select 'any'::regtype` fails with

```
ERROR: syntax error at or near "any"
invalid type name "any"
```

`select 'cardinal_number'::regtype;` fails with

```
ERROR: type "cardinal_number" does not exist
```

However, if I create `create domain cardinal_number as int8`, then `'cardinal_number'::regtype` heals.
Here's an SQL to query types that can't process with a cast to `regtype`: `select * from pg_type where typname <> 'any' and to_regtype(typname) is null;`

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Mon, 06 Oct 2025 00:47:04 +0000

There's a recent discussion on the similar topic: https://www.postgresql.org/message-id/09F9CAD6-5096-43CC-B6A7-685703E4714D%40justatheory.com