pgjdbc/pgjdbc GitHub issues and pull requests (mirror)
help / color / mirror / Atom feed[pgjdbc/pgjdbc] issue #3543: Table meta data is broken if catalog name not equal database name
6+ messages / 3 participants
[nested] [flat]
* [pgjdbc/pgjdbc] issue #3543: Table meta data is broken if catalog name not equal database name
@ 2025-03-03 11:07 "MaxMello (@MaxMello)" <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: MaxMello (@MaxMello) @ 2025-03-03 11:07 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
**Describe the issue**
We are using pgbouncer with multiple nodes (read and write nodes), which have different client names, but the same database name. (Example setup provided below)
[https://www.pgbouncer.org/config.html#section-databases](https://www.pgbouncer.org/config.html#section-databases)
The config looks like this:
```
write = host=postgres port=5432 dbname=postgres
read = host=postgres port=5432 dbname=postgres
```
The connection string then looks like this:
```
jdbc:postgresql://localhost:6432/read
jdbc:postgresql://localhost:6432/write
```
As you can see, the actual database name is not equal to what is specificed in the data source url, as is intended by pgbouncer for these kind of use cases.
`connection.getCatalog()` returns `read` or `write` is this case.
Since 42.7.5,
```java
ResultSet tables = metaData.getTables(connection.getCatalog(), "public", null, new String[] {"TABLE"});
```
returns no results. Hardcoding the catalog value to "postgres" (the dbname above) returns the results in both 42.7.4 and 42.7.5.
I am pretty sure that pull request https://github.com/pgjdbc/pgjdbc/pull/3390 is the result of this change of behavior.
The subsequent problem is that the library we use, [Exposed](https://github.com/JetBrains/Exposed), uses `connection.getCatalog()` to get the table meta data info, so I have no possibility of hardcoding the catalog name myself. The question really is, what is the expected behavior, at which point (pgbouncer, pgjdbc, Exposed) does this need to be fixed?
**Driver Version?**
42.7.5
**Java Version?**
21
**OS Version?**
linux alpine 3.19.1
**PostgreSQL Version?**
15
**To Reproduce**
Steps to reproduce the behaviour (Docker required):
1. Create these local files:
pgbouncer.init
```ini
[databases]
write = host=postgres port=5432 dbname=postgres
read = host=postgres port=5432 dbname=postgres
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = session
ignore_startup_parameters = extra_float_digits
```
userlist.txt
```
"test" "test"
```
docker-compose.yaml
```yaml
version: '3.9'
services:
postgres:
image: postgres:15
container_name: postgres
environment:
POSTGRES_USER: test
POSTGRES_PASSWORD: test
POSTGRES_DB: postgres
ports:
- "5432:5432"
volumes:
- postgres_data:/var/lib/postgresql/data
pgbouncer:
image: edoburu/pgbouncer:latest
container_name: pgbouncer
depends_on:
- postgres
ports:
- "6432:6432"
volumes:
- ./pgbouncer.ini:/etc/pgbouncer/pgbouncer.ini
- ./userlist.txt:/etc/pgbouncer/userlist.txt
environment:
DB_USER: test
DB_PASSWORD: test
command: ["pgbouncer", "/etc/pgbouncer/pgbouncer.ini"]
volumes:
postgres_data:
```
2. Run Docker compose and create one table
`docker-compose up -d` or `docker compose up -d` (depending on docker version and/or if docker-compose is installed)
`docker exec -it postgres psql -U test -d postgres -c "CREATE TABLE test_table (id SERIAL PRIMARY KEY, name TEXT);"`
3. Run example java code below
**Expected behaviour**
Log output of code below running with version 42.7.4 (this would also be the expected output for 42.7.5)
```
Catalog detected in JDBC connection: read
Table found for catalog read: test_table
Table found for catalog postgres test_table
Catalog detected in JDBC connection: write
Table found for catalog write: test_table
Table found for catalog postgres test_table
```
Actual output using 42.7.5:
```
Catalog detected in JDBC connection: read
No tables found for catalog: read
Table found for catalog postgres test_table
Catalog detected in JDBC connection: write
No tables found for catalog: write
Table found for catalog postgres test_table
```
TableMetaData.java
```java
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.Properties;
public class TableMetaData {
public static void main(String[] args) throws Exception {
printTableMetaData("read");
printTableMetaData("write");
}
private static void printTableMetaData(String connectionStringEnd) throws Exception {
String url = "jdbc:postgresql://localhost:6432/" + connectionStringEnd;
Properties props = new Properties();
props.setProperty("user", "test");
props.setProperty("password", "test");
try (Connection connection = DriverManager.getConnection(url, props)) {
DatabaseMetaData metaData = connection.getMetaData();
String catalog = connection.getCatalog();
System.out.println("Catalog detected in JDBC connection: " + catalog);
ResultSet tables = metaData.getTables(catalog, "public", null, new String[]{"TABLE"});
if (!tables.next()) {
System.out.println("No tables found for catalog: " + catalog);
} else {
do {
System.out.println("Table found for catalog " + catalog + ": " + tables.getString("TABLE_NAME"));
} while (tables.next());
}
ResultSet tablesWithHardcodedCatalog = metaData.getTables("postgres", "public", null, new String[]{"TABLE"});
if (!tablesWithHardcodedCatalog.next()) {
System.out.println("No tables found for catalog \"postgres\"");
} else {
do {
System.out.println("Table found for catalog postgres " + tablesWithHardcodedCatalog.getString("TABLE_NAME"));
} while (tables.next());
}
}
}
}
```
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3543: Table meta data is broken if catalog name not equal database name
@ 2025-03-03 11:12 ` "vlsi (@vlsi)" <[email protected]>
4 siblings, 0 replies; 6+ messages in thread
From: vlsi (@vlsi) @ 2025-03-03 11:12 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
@MaxMello , thanks for the reproducer. Thank you a lot.
It would definitely be great to integrate those steps into our test suite.
It might be executing all the tests through pgbouncer, or just adding extra tests with pgbouncer.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3543: Table meta data is broken if catalog name not equal database name
@ 2025-03-03 13:43 ` "MaxMello (@MaxMello)" <[email protected]>
4 siblings, 0 replies; 6+ messages in thread
From: MaxMello (@MaxMello) @ 2025-03-03 13:43 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
One additonal info, calling `connection.getCatalog()` will return "read" or "write", but `metaData.getCatalogs()` will not return those values, instead only the proper dbname ("postgres").
I traced back how `getCatalog()` is implemented and if I understand the code correctly, it will at one point call `PGProperty.PG_DBNAME.getOrDefault(info);` which is "just" the path part of the connection url?
Compared to that, `getCatalogs()` "properly" queries the database. Is it maybe requried to use something like `SELECT current_database();` to get the database / catalog name, instead of relying on the connection properties? At least in this use case with pgbouncer?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3543: Table meta data is broken if catalog name not equal database name
@ 2025-03-03 15:22 ` "vlsi (@vlsi)" <[email protected]>
4 siblings, 0 replies; 6+ messages in thread
From: vlsi (@vlsi) @ 2025-03-03 15:22 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Yeah, it looks like we should use `current_database()` rather than get database name from the url
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3543: Table meta data is broken if catalog name not equal database name
@ 2025-03-03 15:46 ` "vlsi (@vlsi)" <[email protected]>
4 siblings, 0 replies; 6+ messages in thread
From: vlsi (@vlsi) @ 2025-03-03 15:46 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
At the same time, it looks like the database could report `database name` when client connects to the db. I mean it would be great if the database name was `GUC_REPORT`
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3543: Table meta data is broken if catalog name not equal database name
@ 2025-03-08 11:05 ` "davecramer (@davecramer)" <[email protected]>
4 siblings, 0 replies; 6+ messages in thread
From: davecramer (@davecramer) @ 2025-03-08 11:05 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
So I think we should just ask for current database when we connect.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2025-03-08 11:05 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-03-03 11:07 [pgjdbc/pgjdbc] issue #3543: Table meta data is broken if catalog name not equal database name "MaxMello (@MaxMello)" <[email protected]>
2025-03-03 11:12 ` "vlsi (@vlsi)" <[email protected]>
2025-03-03 13:43 ` "MaxMello (@MaxMello)" <[email protected]>
2025-03-03 15:22 ` "vlsi (@vlsi)" <[email protected]>
2025-03-03 15:46 ` "vlsi (@vlsi)" <[email protected]>
2025-03-08 11:05 ` "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