Subject: Re: [pgjdbc/pgjdbc] PR #3568: use pgType instead of internal name to preserve the schema

From: "vlsi (@vlsi)" <[email-masked]>
Date: Wed, 19 Mar 2025 09:56:44 +0000

Could you add tests? It is unclear why would we want parsing typenames

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 19 Mar 2025 11:08:17 +0000

fixes https://github.com/pgjdbc/pgjdbc/issues/3562

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 02 Apr 2025 12:17:43 +0000

So the problem with this is that we presume that the current schema would be 'public'. Now we can add code to check for the current schema but then when we change the schema we would have to invalidate the cache. 
That might not be the worst thing in the world.
Thoughts ? @vlsi @sehrope ?

---

From: "sehrope (@sehrope)" <[email-masked]>
Date: Wed, 02 Apr 2025 12:28:29 +0000

Does this always get called with a fully qualified name? If so that seems the correct thing to do here. Otherwise how do you deal with oddball edge cases like `public` no longer being in the search path?

Note how the server respects the search path to determine how to refer to an object:

```
=> SELECT 'person'::regclass::oid;
  oid  
-------
 20768
(1 row)

=> SELECT 20768::oid::regclass;
 regclass 
----------
 person
(1 row)

=> SET search_path = '$user$';
SET

=> SELECT 20768::oid::regclass;
   regclass    
---------------
 public.person
(1 row)
```

Any magic to strip out the `public` bit would work the majority of the time due to the default search path, but it would be wrong once any changes to search path happen. Especially if `public` is entirely removed.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 02 Apr 2025 12:33:05 +0000

```
select 'public'= any(current_schemas(false));
 ?column?
----------
 t
```
can tell us if public is on the search path, but I'm loathe to query the database every time
We could update it in setSchema which would catch quite a few cases, but not all. Users are still free to execute `set search_path` on their own

---

From: "sehrope (@sehrope)" <[email-masked]>
Date: Wed, 02 Apr 2025 12:40:21 +0000

I think you left out the SQL part of that comment :D

The more we think about these metadata functions, the more weird and annoying edge cases we're going to find. The only consistent answers I can think of are:

* Always query everything at point of use
* Always cache / infer everything using initial search path etc

The first one sucks as it's likely unneeded 99.99% of the time. The second one will be wrong for any changes to search path etc  (but at least it's consistently wrong...). Maybe anybody that does that stuff should really be querying the catalogs in their application code as they're already doing non-JDBC specific actions right?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 02 Apr 2025 12:59:54 +0000

> I think you left out the SQL part of that comment :D

No just crappy formatting 
> The first one sucks as it's likely unneeded 99.99% of the time. The second one will be wrong for any changes to search path etc (but at least it's consistently wrong...). Maybe anybody that does th…

I think I'm aligned with this approach

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Wed, 09 Apr 2025 17:49:42 +0000

(on pgjdbc/src/main/java/org/postgresql/jdbc/TypeInfoCache.java:540)

What is the reason for an explicit `startsWith` check here?
Could you add tests that exercise it?

If you still want to compare it, I would suggest `String.regionMatches(ignoreCase=true, ...`, so it does not need to convert the thing to lowercase on every invocation.

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Wed, 09 Apr 2025 17:53:07 +0000

(on pgjdbc/src/main/java/org/postgresql/jdbc/TypeInfoCache.java:548)

What is the reason to put a truncated type name into the cache?

If someone calls the function with a full name like `"public"."typename"`, then it would always end up with `getOidStatement` which is unwanted.

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Wed, 09 Apr 2025 17:56:19 +0000

(on pgjdbc/src/test/java/org/postgresql/test/jdbc3/CompositeTest.java:86)

Could you please clarify how does this test differ from `org.postgresql.test.jdbc2.CustomTypeWithBinaryTransferTest#testCustomBinaryTypes` ?