Subject: [pgjdbc/pgjdbc] PR #3528: Implement performance fix for catalog check in DatabaseMetaData

From: "SophiahHo (@SophiahHo)" <[email-masked]>
Date: Tue, 18 Feb 2025 20:47:50 +0000

Fixes DatabaseMetaData performance issue https://github.com/pgjdbc/pgjdbc/issues/3511

### All Submissions:

* [x] Have you followed the guidelines in our [Contributing](https://github.com/pgjdbc/pgjdbc/blob/master/CONTRIBUTING.md) document?
* [x] Have you checked to ensure there aren't other open [Pull Requests](../../pulls) for the same update/change?

<!-- You can erase any parts of this template not applicable to your Pull Request. -->

### New Feature Submissions:

1. [x] Does your submission pass tests?
2. [x] Does `./gradlew styleCheck` pass ?
3. [ ] Have you added your new test classes to an existing test suite in alphabetical order?

### Changes to Existing Features:

* [ ] Does this break existing behaviour? If so please explain.
* [x] Have you added an explanation of what your changes do and why you'd like us to include them?
* [ ] Have you written new tests for your core changes, as applicable?
* [x] Have you successfully run tests with your changes locally?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 19 Feb 2025 00:43:47 +0000

Did you check to make sure the plan is the same as previously ?

---

From: "SophiahHo (@SophiahHo)" <[email-masked]>
Date: Wed, 19 Feb 2025 14:21:25 +0000

@davecramer I assume the plan would be different, because I verified that the change drastically improved the performance of `getCrossReference`. With the same arguments and environment, version 42.7.5 took 3835 ms and the new version took 1504 ms.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 19 Feb 2025 15:22:11 +0000

@SophiahHo thanks!

---

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

@SophiahHo reviewing this as I understand this you are adding `and FALSE` if the catalog isn't equal to the current catalog. Why even run the SQL? We know the ResultSet is going to be empty, we could fabricate an empty ResultSet instead.

---

From: "SophiahHo (@SophiahHo)" <[email-masked]>
Date: Thu, 03 Apr 2025 14:27:04 +0000

@davecramer [Fabricating an empty ResultSet would be pages and pages long](https://stackoverflow.com/questions/64632584/how-to-return-empty-resultset). Additionally, returning an empty ResultSet would be incorrect behaviour if the database is down, the database user credentials were revoked or changed, etc., which should throw an Exception.

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Wed, 09 Apr 2025 18:39:13 +0000

I think it should be uncommon for the client code to ask for a list of "procedures in a foreign catalog", so it should be fine to go with `and false` trick.
At the same time, certain cases make it easy to return empty resultset without making a database roundtrip.

@SophiahHo , in some of the cases, we use `org.postgresql.core.BaseStatement#createDriverResultSet(Field[] fields, List<Tuple> tuples)` can create resultset. For instance, `getProcedureColumns` prepares `Field[]` declaration, and then it executes a query.
In that case we could use something like

```java
if (catalog != null && !catalog.equals(...)) {
  // Returns empty resultset
  ((BaseStatement) createMetaDataStatement()).createDriverResultSet(f, v)
}
```

I guess Dave meant something like that. WDYT?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 09 Apr 2025 19:03:59 +0000

@vlsi I already merged #3588

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Sun, 13 Apr 2025 08:42:54 +0000

Does it mean we should close the current PR then?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Sun, 13 Apr 2025 10:51:56 +0000

> Does it mean we should close the current PR then?

Yes, I would think so.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Mon, 14 Apr 2025 14:46:30 +0000

Closed in favour of #3588