pgjdbc/pgjdbc GitHub issues and pull requests (mirror)help / color / mirror / Atom feed
[pgjdbc/pgjdbc] PR #3244: refactor: use oid=?::regtype when fetching pg_type by name instead of joining pg_namespace 5+ messages / 2 participants [nested] [flat]
* [pgjdbc/pgjdbc] PR #3244: refactor: use oid=?::regtype when fetching pg_type by name instead of joining pg_namespace @ 2024-05-12 13:42 "vlsi (@vlsi)" <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: vlsi (@vlsi) @ 2024-05-12 13:42 UTC (permalink / raw) To: pgjdbc/pgjdbc <[email protected]> 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [pgjdbc/pgjdbc] PR #3244: refactor: use oid=?::regtype when fetching pg_type by name instead of joining pg_namespace @ 2024-05-12 14:04 ` "vlsi (@vlsi)" <[email protected]> 3 siblings, 0 replies; 5+ messages in thread From: vlsi (@vlsi) @ 2024-05-12 14:04 UTC (permalink / raw) To: pgjdbc/pgjdbc <[email protected]> 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) ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [pgjdbc/pgjdbc] PR #3244: refactor: use oid=?::regtype when fetching pg_type by name instead of joining pg_namespace @ 2024-05-12 14:39 ` "davecramer (@davecramer)" <[email protected]> 3 siblings, 0 replies; 5+ messages in thread From: davecramer (@davecramer) @ 2024-05-12 14:39 UTC (permalink / raw) To: pgjdbc/pgjdbc <[email protected]> I agree that the change will be fine. ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [pgjdbc/pgjdbc] PR #3244: refactor: use oid=?::regtype when fetching pg_type by name instead of joining pg_namespace @ 2025-10-05 20:15 ` "vlsi (@vlsi)" <[email protected]> 3 siblings, 0 replies; 5+ messages in thread From: vlsi (@vlsi) @ 2025-10-05 20:15 UTC (permalink / raw) To: pgjdbc/pgjdbc <[email protected]> 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;` ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [pgjdbc/pgjdbc] PR #3244: refactor: use oid=?::regtype when fetching pg_type by name instead of joining pg_namespace @ 2025-10-06 00:47 ` "vlsi (@vlsi)" <[email protected]> 3 siblings, 0 replies; 5+ messages in thread From: vlsi (@vlsi) @ 2025-10-06 00:47 UTC (permalink / raw) To: pgjdbc/pgjdbc <[email protected]> There's a recent discussion on the similar topic: https://www.postgresql.org/message-id/09F9CAD6-5096-43CC-B6A7-685703E4714D%40justatheory.com ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-10-06 00:47 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-05-12 13:42 [pgjdbc/pgjdbc] PR #3244: refactor: use oid=?::regtype when fetching pg_type by name instead of joining pg_namespace "vlsi (@vlsi)" <[email protected]> 2024-05-12 14:04 ` "vlsi (@vlsi)" <[email protected]> 2024-05-12 14:39 ` "davecramer (@davecramer)" <[email protected]> 2025-10-05 20:15 ` "vlsi (@vlsi)" <[email protected]> 2025-10-06 00:47 ` "vlsi (@vlsi)" <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox