pgjdbc/pgjdbc GitHub issues and pull requests (mirror)
help / color / mirror / Atom feed[pgjdbc/pgjdbc] PR #3565: Use query to find the current catalog instead of relying on the database in the connection URL or connection properties as this could be different if connected through a pooler or proxy
13+ messages / 4 participants
[nested] [flat]
* [pgjdbc/pgjdbc] PR #3565: Use query to find the current catalog instead of relying on the database in the connection URL or connection properties as this could be different if connected through a pooler or proxy
@ 2025-03-14 20:33 "davecramer (@davecramer)" <[email protected]>
0 siblings, 0 replies; 13+ messages in thread
From: davecramer (@davecramer) @ 2025-03-14 20:33 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [pgjdbc/pgjdbc] PR #3565: Use query to find the current catalog instead of relying on the database in the connection URL or connection properties as this could be different if connected through a pooler or proxy
@ 2025-04-01 18:39 ` "davecramer (@davecramer)" <[email protected]>
11 siblings, 0 replies; 13+ messages in thread
From: davecramer (@davecramer) @ 2025-04-01 18:39 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
@vlsi I'd like to merge this. Any issues ?
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [pgjdbc/pgjdbc] PR #3565: Use query to find the current catalog instead of relying on the database in the connection URL or connection properties as this could be different if connected through a pooler or proxy
@ 2025-04-01 19:28 ` "vlsi (@vlsi)" <[email protected]>
11 siblings, 0 replies; 13+ messages in thread
From: vlsi (@vlsi) @ 2025-04-01 19:28 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
(on pgjdbc/src/main/java/org/postgresql/jdbc/PgConnection.java)
```suggestion
private @Nullable String catalog;
```
I guess we have `@SuppressWarning` for `PgConnection` constructor, so it results in silencing warning for the newly added field.
However, the field is nullable, so let's specify nullable type.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [pgjdbc/pgjdbc] PR #3565: Use query to find the current catalog instead of relying on the database in the connection URL or connection properties as this could be different if connected through a pooler or proxy
@ 2025-04-01 19:30 ` "vlsi (@vlsi)" <[email protected]>
11 siblings, 0 replies; 13+ messages in thread
From: vlsi (@vlsi) @ 2025-04-01 19:30 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
(on pgjdbc/src/main/java/org/postgresql/jdbc/PgConnection.java)
```suggestion
String catalog = this.catalog;
if (catalog == null) {
try (Statement stmt = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery("select current_catalog")) {
if (rs.next()) {
this.catalog = catalog = rs.getString(1);
}
}
}
return catalog;
```
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [pgjdbc/pgjdbc] PR #3565: Use query to find the current catalog instead of relying on the database in the connection URL or connection properties as this could be different if connected through a pooler or proxy
@ 2025-04-02 12:07 ` "sehrope (@sehrope)" <[email protected]>
11 siblings, 0 replies; 13+ messages in thread
From: sehrope (@sehrope) @ 2025-04-02 12:07 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Let me preface this by saying I'm not opposed to this PR ... though thinking about this a couple questions came to mind.
What happens if the DB changes? The whole point of this PR is to skip the fixed value in connection properties and pass through to the actual database, potentially bypassing any intermediate connection pool(s). If the pooler reroutes the connection somewhere else then it'd be wrong as we would have cached the previous value. I suppose that type of pooling is less common, but it does come up for things like live migrations and upgrades.
There's also a slight change in behavior here. Previously the method was a pass through to `QueryExecutor.getDatabase()` which returned a cached `String` value. Now that we always execute the command on the first call, this means you cannot call this method while the connection is otherwise in use. I'm worried this may lead to some weird edge case where a user runs a query or COPY operation, and then tries to use this data while processing the result.
We could resolve this by caching the value at connection startup. But I don't think we want to add more mandatory startup round trips.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [pgjdbc/pgjdbc] PR #3565: Use query to find the current catalog instead of relying on the database in the connection URL or connection properties as this could be different if connected through a pooler or proxy
@ 2025-04-02 12:09 ` "davecramer (@davecramer)" <[email protected]>
11 siblings, 0 replies; 13+ messages in thread
From: davecramer (@davecramer) @ 2025-04-02 12:09 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Actually one point of the PR is to make sure we get the right database. If you use pg_bouncer then you connect to some database in pg_bouncer which redirects you to the actual database.
Q: How can the database change ?
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [pgjdbc/pgjdbc] PR #3565: Use query to find the current catalog instead of relying on the database in the connection URL or connection properties as this could be different if connected through a pooler or proxy
@ 2025-04-02 12:17 ` "sehrope (@sehrope)" <[email protected]>
11 siblings, 0 replies; 13+ messages in thread
From: sehrope (@sehrope) @ 2025-04-02 12:17 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
The situation I'm describing is where the pooler swaps out the physical connection. There's no guarantee I get the same catalog when it opens a new connection.
Interestingly I found this pgbouncer fork (replacement?) which seems to do exactly what I'm describing: https://github.com/awslabs/pgbouncer-fast-switchover
Imagine configuring that with multiple targets that have different database names. Could be the same actual database, just the return value of `SELECT catalog_name` is different.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [pgjdbc/pgjdbc] PR #3565: Use query to find the current catalog instead of relying on the database in the connection URL or connection properties as this could be different if connected through a pooler or proxy
@ 2025-04-02 12:28 ` "davecramer (@davecramer)" <[email protected]>
11 siblings, 0 replies; 13+ messages in thread
From: davecramer (@davecramer) @ 2025-04-02 12:28 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Well our options are to query the database every time or cache it.
99.99999% would be OK with caching it.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [pgjdbc/pgjdbc] PR #3565: Use query to find the current catalog instead of relying on the database in the connection URL or connection properties as this could be different if connected through a pooler or proxy
@ 2025-04-02 12:41 ` "sehrope (@sehrope)" <[email protected]>
11 siblings, 0 replies; 13+ messages in thread
From: sehrope (@sehrope) @ 2025-04-02 12:41 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Or return the original value and have people who are doing that pooling stuff explicitly query for the live one themselves.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [pgjdbc/pgjdbc] PR #3565: Use query to find the current catalog instead of relying on the database in the connection URL or connection properties as this could be different if connected through a pooler or proxy
@ 2025-04-02 13:39 ` "vlsi (@vlsi)" <[email protected]>
11 siblings, 0 replies; 13+ messages in thread
From: vlsi (@vlsi) @ 2025-04-02 13:39 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
It would be great if `catalog_name` was `GUC_REPORT`.
The bouncer could emit notifications if the `catalog_name` ever changed.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [pgjdbc/pgjdbc] PR #3565: Use query to find the current catalog instead of relying on the database in the connection URL or connection properties as this could be different if connected through a pooler or proxy
@ 2025-04-02 13:49 ` "davecramer (@davecramer)" <[email protected]>
11 siblings, 0 replies; 13+ messages in thread
From: davecramer (@davecramer) @ 2025-04-02 13:49 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
> It would be great if `catalog_name` was `GUC_REPORT`. The bouncer could emit notifications if the `catalog_name` ever changed.
That is really a pg_bouncer problem. In reality the server will never change the catalog once the connection is established. I think we could get pg_bouncer to emit `database` however.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [pgjdbc/pgjdbc] PR #3565: Use query to find the current catalog instead of relying on the database in the connection URL or connection properties as this could be different if connected through a pooler or proxy
@ 2025-04-02 16:06 ` "vlsi (@vlsi)" <[email protected]>
11 siblings, 0 replies; 13+ messages in thread
From: vlsi (@vlsi) @ 2025-04-02 16:06 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
My feeling exactly. However, if we expect `pg_bouncer` to emit `database`, we should expect the DB to emit it as well.
However, hope the current PR is good enough.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [pgjdbc/pgjdbc] PR #3565: Use query to find the current catalog instead of relying on the database in the connection URL or connection properties as this could be different if connected through a pooler or proxy
@ 2025-05-31 05:14 ` "landryb (@landryb)" <[email protected]>
11 siblings, 0 replies; 13+ messages in thread
From: landryb (@landryb) @ 2025-05-31 05:14 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
thanks for this PR, 42.7.6 fixes a regression that was introduced with #3390.
Using 42.7.3 against a database cnx handled by pgbouncer having a different name/alias than the real database, geotools was able to list attributes/fields in a layer/table without issues, while it started failing after an update to 42.7.5.
turning `log_statement` on, i saw that the query done had `AND current_database() = 'pgbouncer_alias'` which wasn't the real table name, thus no attributes were returned and geotools failed accessing the layer geometry.
replacing 42.7.5 jar by 42.7.6 jar, everything works as before, so thanks @davecramer !
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2025-05-31 05:14 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-03-14 20:33 [pgjdbc/pgjdbc] PR #3565: Use query to find the current catalog instead of relying on the database in the connection URL or connection properties as this could be different if connected through a pooler or proxy "davecramer (@davecramer)" <[email protected]>
2025-04-01 18:39 ` "davecramer (@davecramer)" <[email protected]>
2025-04-01 19:28 ` "vlsi (@vlsi)" <[email protected]>
2025-04-01 19:30 ` "vlsi (@vlsi)" <[email protected]>
2025-04-02 12:07 ` "sehrope (@sehrope)" <[email protected]>
2025-04-02 12:09 ` "davecramer (@davecramer)" <[email protected]>
2025-04-02 12:17 ` "sehrope (@sehrope)" <[email protected]>
2025-04-02 12:28 ` "davecramer (@davecramer)" <[email protected]>
2025-04-02 12:41 ` "sehrope (@sehrope)" <[email protected]>
2025-04-02 13:39 ` "vlsi (@vlsi)" <[email protected]>
2025-04-02 13:49 ` "davecramer (@davecramer)" <[email protected]>
2025-04-02 16:06 ` "vlsi (@vlsi)" <[email protected]>
2025-05-31 05:14 ` "landryb (@landryb)" <[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