Subject: [pgjdbc/pgjdbc] PR #3606: fix: add support for multiple schemas via PgConnection.setSchema

From: "cgm-aw (@cgm-aw)" <[email-masked]>
Date: Tue, 15 Apr 2025 14:30:49 +0000

The JDBC connection string already supports multiple schemas, now programmatically setting multiple schemas also works.

Closes issue 3605.

### 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. [ ] Does your submission pass tests? -> Only partially, I get authentication errors sometimes, although I set the credentials correctly. Seems like some tests are flaky.
2. [x] Does `./gradlew styleCheck` pass ?

### Changes to Existing Features:

* [x] Have you added an explanation of what your changes do and why you'd like us to include them?
* [x] Have you written new tests for your core changes, as applicable?
* [x] Have you successfully run tests with your changes locally?

---

From: "svendiedrichsen (@svendiedrichsen)" <[email-masked]>
Date: Tue, 15 Apr 2025 18:48:06 +0000

(on pgjdbc/src/main/java/org/postgresql/jdbc/PgConnection.java:1706)

Wouldn't it be easier to use streams for this?
i.e. `Arrays.stream(str.split(",")).map(s -> "'" + s + "'").collect(Collectors.joining(","))`

---

From: "cgm-aw (@cgm-aw)" <[email-masked]>
Date: Wed, 16 Apr 2025 06:15:01 +0000

`Utils.escapeLiteral` requires a StringBuilder and throws a checked exception, so I kept it "old school" since I didn't find it more readable with streams. I could refactor it like this to use streams:
```
  public void setSchema(@Nullable String schema) throws SQLException {
    checkClosed();
    try (Statement stmt = createStatement()) {
      if (schema == null) {
        stmt.executeUpdate("SET SESSION search_path TO DEFAULT");
      } else {
        // We allow a space after the comma
        String encodedSchemas = Arrays.stream(schema.split(", ?"))
            .map(s -> {
              try {
                return Utils.escapeLiteral(new StringBuilder(), s, getStandardConformingStrings());
              } catch (SQLException e) {
                throw new RuntimeException(e);
              }
            }).map(sb -> "'" + sb + "'")
            .collect(Collectors.joining(","));
        String sql = "SET SESSION search_path TO " + encodedSchemas;
        stmt.executeUpdate(sql);
        LOGGER.log(Level.FINE, "  setSchema = {0}", encodedSchemas);
      }
    }
  }
```

Let me know what you prefer.

---

From: "svendiedrichsen (@svendiedrichsen)" <[email-masked]>
Date: Wed, 16 Apr 2025 06:23:49 +0000

That's fine with me. Just asking.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 17 Apr 2025 12:33:20 +0000

So interestingly enough `getSchema()` returns the result of `select current_schema()` which the doc say this:
```
current_schema () → name

Returns the name of the schema that is first in the search path (or a null value if the search path is empty). This is the schema that will be used for any tables or other named objects that are created without specifying a target schema.
```

What is the intended use of setting a schema with 2 entries ? 

Dave

---

From: "cgm-aw (@cgm-aw)" <[email-masked]>
Date: Thu, 17 Apr 2025 12:53:05 +0000

Yes `current_schema()` is a bit misleading, although the documentation states it correctly: "Returns the name of the schema that is **first** in the search path" - it does not say that it shows **all** schemas on the search_path. To view all schemas on the path, you have to call `show search_path`.
Check this output:
```
postgres=# set session search_path to 'postgres','public';
SET
postgres=# select current_schema();
 current_schema 
----------------
 public
(1 row)

postgres=# show search_path;
   search_path    
------------------
 postgres, public
(1 row)
```

So Postgres does indeed support multiple schemas on the search path, which is why the JDBC driver allows setting multiple schemas in the connection string.

I need this feature because in our enterprise setting, I cannot edit the JDBC connection string and I have multiple database functions which call other functions without the schema in their prefix, which I also cannot edit.

So to make my setup work, I have to be able to programmatically add multiple schemas to my search_path. 
Of course I could call `set session search_path to...` in my application, but I think support in the driver would be nice 😃

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 17 Apr 2025 13:00:24 +0000

> Yes `current_schema()` is a bit misleading, although the documentation states it correctly: "Returns the name of the schema that is **first** in the search path" - it does not say that it shows **all** schemas on the search_path. To view all schemas on the path, you have to call `show search_path`. Check this output:
> 
> ```
> So to make my setup work, I have to be able to programmatically add multiple schemas to my search_path. Of course I could call `set session search_path to...` in my application, but I think support in the driver would be nice 😃

So I think we need to fix getSchema then as it returns `current_schema()`

---

From: "cgm-aw (@cgm-aw)" <[email-masked]>
Date: Thu, 17 Apr 2025 13:16:47 +0000

I'm happy to change it, but this would alter the behavior of the method and might break existing code. Maybe rather update the documentation, that `getSchema()` returns the results of `current_schema()`, which shows the first element on the search_path?
I guess this is up to you to decide; personally, I would not change `getSchema()`. Obviously setting a list of schemas is an edge case, since I'm the first to report it. If I connect with psql and just run `show search_path` I get this:

```
-----------------
 "$user", public
(1 row)

I'm out of my depth here, but it seems like the schema `"$user"` is hidden from `current_schema()`. I never heard of this $user schema, so I cannot really explain what is going on.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 17 Apr 2025 13:26:15 +0000

> I'm happy to change it, but this would alter the behavior of the method and might break existing code. Maybe rather update the documentation, that `getSchema()` returns the results of `current_schema()`, which shows the first element on the search_path? I guess this is up to you to decide; personally, I would not change `getSchema()`. Obviously setting a list of schemas is an edge case, since I'm the first to report it. If I connect with psql and just run `show search_path` I get this:
> 
> ```

FYI `If one of the list items is the special name $user, then the schema having the name returned by CURRENT_USER is substituted, if there is such a schema and the user has USAGE permission for it. (If not, $user is ignored.)`

but in regards to getSchema, currently `getSchema()` returns whatever schema was set in `setSchema()`

In a way this patch breaks that

---

From: "cgm-aw (@cgm-aw)" <[email-masked]>
Date: Thu, 17 Apr 2025 13:43:40 +0000

> FYI If one of the list items is the special name $user, then the schema having the name returned by CURRENT_USER is substituted, if there is such a schema and the user has USAGE permission for it. (If not, $user is ignored.)

Ah thanks, I didn't know that.
I guess there are points to both sides - a `get` should return the state that a `set` established, but also the new `getSchema` implementation might break code for people who didn't even know they had multiple schemas on their search path. Adding the new behavior to the changelog should be enough to clarify this, though.
The [Javadoc](https://docs.oracle.com/en/java/javase/17/docs/api/java.sql/java/sql/Connection.html#getSchema()) doesn't have much to say on this. 
Changing `getSchema` is obviously trivial, so let me know which behavior you want.

---

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

this would have to go into a major version release as it changes behaviour. 
Last time I did something "minor" thinking it would be OK, it wasn't

---

From: "cgm-aw (@cgm-aw)" <[email-masked]>
Date: Wed, 23 Apr 2025 09:16:26 +0000

So how do we proceed?
My proposal: we keep the PR as it is, `getSchema()` will not be changed. 

My reasoning behind this is: Currently, if I set multiple schemas for a user via `ALTER ROLE <user> SET search_path = schema1,schema2` or via JDBC connection string and then call `getSchema()` via JDBC, I will only get one schema back.
So people already expect `getSchema()` to only return one schema, even if they configured multiple. 

A change in `getSchema()` is unexpected and Postgres states that `current_schema()` only returns one schema.
Someone who wants to see all schemas can still call `SHOW search_path`.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 23 Apr 2025 10:43:57 +0000

@cgm-aw I wish it were that easy.
Past experience has demonstrated that people use the drivers in ways I hadn't imagined.
I'm fine with changing the set/getSchema to handle multiple schemas; but get needs to return what was passed in set. 
We would just have to push this off to a major release. I think we are close to a minor release, and we can fast follow with a major release

---

From: "cgm-aw (@cgm-aw)" <[email-masked]>
Date: Wed, 23 Apr 2025 12:15:13 +0000

Ok, I changed `getSchema` to call `SHOW search_path`.
I noticed this in a test case:
Before (current_schema()):
```
execute("SET search_path TO \"$user\",public,schema2");
assertEquals(TestUtil.getUser(), conn.getSchema()); // Now fails
```

After (show search_path):
```
execute("SET search_path TO \"$user\",public,schema2");
assertEquals("\"$user\", public, schema2", conn.getSchema()); // OK
```

So this means that `current_schema()` resolves the schema `$user` to the respective user name, while `SHOW search_path` keeps the variable in its output. Since you would use the variable in the SET command, I guess this is fine.

Let me know if the PR is ok now, then I will look forward to the release 😃

---

From: "cgm-aw (@cgm-aw)" <[email-masked]>
Date: Tue, 06 Jan 2026 13:28:19 +0000

@davecramer Any news on this? We switched to the workaround of calling `set schema` explicitly, but I'm wondering if a new major version of the driver is planned?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 07 Jan 2026 10:35:28 +0000

Trying to kick off CI again

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 07 Jan 2026 11:31:28 +0000

@cgm-aw It looks like the setSchema tests are failing.
I've thought about this PR a bit more. 
What do you think of keeping the behaviour the way it is now unless the user calls setSchema with multiple schema's. At that point we return multiple schema's but not before.

That way we keep the current behaviour unless the client specifically asks for it.
@vlsi ?

Thoughts ?

---

From: "sehrope (@sehrope)" <[email-masked]>
Date: Wed, 07 Jan 2026 16:20:05 +0000

@davecramer I think we should leave this alone as there's no way to have a single string parameter handle both funky names _and_ lists of funky names without some kind of oddball / breaking behavior:

```sql
=> CREATE SCHEMA "bad,name";
CREATE SCHEMA

=> CREATE TABLE "bad,name"."postgres allows crazy things like this" (id int);
CREATE TABLE

=> \d "bad,name"."postgres allows crazy things like this"
Table "bad,name.postgres allows crazy things like this"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           |          | 

=> CREATE SCHEMA "even worse "" schema name with embedded double quote";
CREATE SCHEMA
```

The "split via commas with optional spaces" will not work. I think we should leave that stuff alone. Ditto for having the `getSchema()` method not change as it'd be breaking existing behavior too. Anybody that wants the full value can run `SHOW search_path`.

@cgm-aw If you want to set the search path to multiple values via JDBC properties, you can use `options` with a `-c foo,bar,baz` value to set multiple schemas in the search path. There's an example of that in the docs: https://jdbc.postgresql.org/documentation/use/#connection-parameters

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 07 Jan 2026 16:56:03 +0000

@sehrope 
Thanks, I was having trouble justifying it. My real concern is it would break hibernate, etal

Dave