pgjdbc/pgjdbc GitHub issues and pull requests (mirror)
help / color / mirror / Atom feed[pgjdbc/pgjdbc] PR #3606: fix: add support for multiple schemas via PgConnection.setSchema
19+ messages / 4 participants
[nested] [flat]
* [pgjdbc/pgjdbc] PR #3606: fix: add support for multiple schemas via PgConnection.setSchema
@ 2025-04-15 14:30 "cgm-aw (@cgm-aw)" <[email protected]>
0 siblings, 0 replies; 19+ messages in thread
From: cgm-aw (@cgm-aw) @ 2025-04-15 14:30 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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?
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [pgjdbc/pgjdbc] PR #3606: fix: add support for multiple schemas via PgConnection.setSchema
@ 2025-04-15 18:48 "svendiedrichsen (@svendiedrichsen)" <[email protected]>
17 siblings, 0 replies; 19+ messages in thread
From: svendiedrichsen (@svendiedrichsen) @ 2025-04-15 18:48 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
(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(","))`
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [pgjdbc/pgjdbc] PR #3606: fix: add support for multiple schemas via PgConnection.setSchema
@ 2025-04-16 06:15 "cgm-aw (@cgm-aw)" <[email protected]>
17 siblings, 0 replies; 19+ messages in thread
From: cgm-aw (@cgm-aw) @ 2025-04-16 06:15 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
(on pgjdbc/src/main/java/org/postgresql/jdbc/PgConnection.java:1706)
`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.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [pgjdbc/pgjdbc] PR #3606: fix: add support for multiple schemas via PgConnection.setSchema
@ 2025-04-16 06:23 "svendiedrichsen (@svendiedrichsen)" <[email protected]>
17 siblings, 0 replies; 19+ messages in thread
From: svendiedrichsen (@svendiedrichsen) @ 2025-04-16 06:23 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
(on pgjdbc/src/main/java/org/postgresql/jdbc/PgConnection.java:1706)
That's fine with me. Just asking.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [pgjdbc/pgjdbc] PR #3606: fix: add support for multiple schemas via PgConnection.setSchema
@ 2025-04-17 12:33 "davecramer (@davecramer)" <[email protected]>
17 siblings, 0 replies; 19+ messages in thread
From: davecramer (@davecramer) @ 2025-04-17 12:33 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [pgjdbc/pgjdbc] PR #3606: fix: add support for multiple schemas via PgConnection.setSchema
@ 2025-04-17 12:53 "cgm-aw (@cgm-aw)" <[email protected]>
17 siblings, 0 replies; 19+ messages in thread
From: cgm-aw (@cgm-aw) @ 2025-04-17 12:53 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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 😃
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [pgjdbc/pgjdbc] PR #3606: fix: add support for multiple schemas via PgConnection.setSchema
@ 2025-04-17 13:00 "davecramer (@davecramer)" <[email protected]>
17 siblings, 0 replies; 19+ messages in thread
From: davecramer (@davecramer) @ 2025-04-17 13:00 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
> 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 😃
So I think we need to fix getSchema then as it returns `current_schema()`
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [pgjdbc/pgjdbc] PR #3606: fix: add support for multiple schemas via PgConnection.setSchema
@ 2025-04-17 13:16 "cgm-aw (@cgm-aw)" <[email protected]>
17 siblings, 0 replies; 19+ messages in thread
From: cgm-aw (@cgm-aw) @ 2025-04-17 13:16 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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:
```
postgres=# show search_path;
search_path
-----------------
"$user", public
(1 row)
postgres=# select current_schema();
current_schema
----------------
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.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [pgjdbc/pgjdbc] PR #3606: fix: add support for multiple schemas via PgConnection.setSchema
@ 2025-04-17 13:26 "davecramer (@davecramer)" <[email protected]>
17 siblings, 0 replies; 19+ messages in thread
From: davecramer (@davecramer) @ 2025-04-17 13:26 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
> 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:
>
> ```
> postgres=# show search_path;
> search_path
> -----------------
> "$user", public
> (1 row)
>
> postgres=# select current_schema();
> current_schema
> ----------------
> 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.
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [pgjdbc/pgjdbc] PR #3606: fix: add support for multiple schemas via PgConnection.setSchema
@ 2025-04-17 13:43 "cgm-aw (@cgm-aw)" <[email protected]>
17 siblings, 0 replies; 19+ messages in thread
From: cgm-aw (@cgm-aw) @ 2025-04-17 13:43 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
> 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.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [pgjdbc/pgjdbc] PR #3606: fix: add support for multiple schemas via PgConnection.setSchema
@ 2025-04-19 11:59 "davecramer (@davecramer)" <[email protected]>
17 siblings, 0 replies; 19+ messages in thread
From: davecramer (@davecramer) @ 2025-04-19 11:59 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [pgjdbc/pgjdbc] PR #3606: fix: add support for multiple schemas via PgConnection.setSchema
@ 2025-04-23 09:16 "cgm-aw (@cgm-aw)" <[email protected]>
17 siblings, 0 replies; 19+ messages in thread
From: cgm-aw (@cgm-aw) @ 2025-04-23 09:16 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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`.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [pgjdbc/pgjdbc] PR #3606: fix: add support for multiple schemas via PgConnection.setSchema
@ 2025-04-23 10:43 "davecramer (@davecramer)" <[email protected]>
17 siblings, 0 replies; 19+ messages in thread
From: davecramer (@davecramer) @ 2025-04-23 10:43 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
@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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [pgjdbc/pgjdbc] PR #3606: fix: add support for multiple schemas via PgConnection.setSchema
@ 2025-04-23 12:15 "cgm-aw (@cgm-aw)" <[email protected]>
17 siblings, 0 replies; 19+ messages in thread
From: cgm-aw (@cgm-aw) @ 2025-04-23 12:15 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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 😃
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [pgjdbc/pgjdbc] PR #3606: fix: add support for multiple schemas via PgConnection.setSchema
@ 2026-01-06 13:28 "cgm-aw (@cgm-aw)" <[email protected]>
17 siblings, 0 replies; 19+ messages in thread
From: cgm-aw (@cgm-aw) @ 2026-01-06 13:28 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
@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?
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [pgjdbc/pgjdbc] PR #3606: fix: add support for multiple schemas via PgConnection.setSchema
@ 2026-01-07 10:35 "davecramer (@davecramer)" <[email protected]>
17 siblings, 0 replies; 19+ messages in thread
From: davecramer (@davecramer) @ 2026-01-07 10:35 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Trying to kick off CI again
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [pgjdbc/pgjdbc] PR #3606: fix: add support for multiple schemas via PgConnection.setSchema
@ 2026-01-07 11:31 "davecramer (@davecramer)" <[email protected]>
17 siblings, 0 replies; 19+ messages in thread
From: davecramer (@davecramer) @ 2026-01-07 11:31 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
@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 ?
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [pgjdbc/pgjdbc] PR #3606: fix: add support for multiple schemas via PgConnection.setSchema
@ 2026-01-07 16:20 "sehrope (@sehrope)" <[email protected]>
17 siblings, 0 replies; 19+ messages in thread
From: sehrope (@sehrope) @ 2026-01-07 16:20 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
@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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [pgjdbc/pgjdbc] PR #3606: fix: add support for multiple schemas via PgConnection.setSchema
@ 2026-01-07 16:56 "davecramer (@davecramer)" <[email protected]>
17 siblings, 0 replies; 19+ messages in thread
From: davecramer (@davecramer) @ 2026-01-07 16:56 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
@sehrope
Thanks, I was having trouble justifying it. My real concern is it would break hibernate, etal
Dave
^ permalink raw reply [nested|flat] 19+ messages in thread
end of thread, other threads:[~2026-01-07 16:56 UTC | newest]
Thread overview: 19+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-04-15 14:30 [pgjdbc/pgjdbc] PR #3606: fix: add support for multiple schemas via PgConnection.setSchema "cgm-aw (@cgm-aw)" <[email protected]>
2025-04-15 18:48 ` "svendiedrichsen (@svendiedrichsen)" <[email protected]>
2025-04-16 06:15 ` "cgm-aw (@cgm-aw)" <[email protected]>
2025-04-16 06:23 ` "svendiedrichsen (@svendiedrichsen)" <[email protected]>
2025-04-17 12:33 ` "davecramer (@davecramer)" <[email protected]>
2025-04-17 12:53 ` "cgm-aw (@cgm-aw)" <[email protected]>
2025-04-17 13:00 ` "davecramer (@davecramer)" <[email protected]>
2025-04-17 13:16 ` "cgm-aw (@cgm-aw)" <[email protected]>
2025-04-17 13:26 ` "davecramer (@davecramer)" <[email protected]>
2025-04-17 13:43 ` "cgm-aw (@cgm-aw)" <[email protected]>
2025-04-19 11:59 ` "davecramer (@davecramer)" <[email protected]>
2025-04-23 09:16 ` "cgm-aw (@cgm-aw)" <[email protected]>
2025-04-23 10:43 ` "davecramer (@davecramer)" <[email protected]>
2025-04-23 12:15 ` "cgm-aw (@cgm-aw)" <[email protected]>
2026-01-06 13:28 ` "cgm-aw (@cgm-aw)" <[email protected]>
2026-01-07 10:35 ` "davecramer (@davecramer)" <[email protected]>
2026-01-07 11:31 ` "davecramer (@davecramer)" <[email protected]>
2026-01-07 16:20 ` "sehrope (@sehrope)" <[email protected]>
2026-01-07 16:56 ` "davecramer (@davecramer)" <[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