pgjdbc/pgjdbc GitHub issues and pull requests (mirror)
help / color / mirror / Atom feedFrom: MaxMello (@MaxMello) <[email protected]>
To: pgjdbc/pgjdbc <[email protected]>
Subject: [pgjdbc/pgjdbc] issue #3543: Table meta data is broken if catalog name not equal database name
Date: Mon, 03 Mar 2025 11:07:58 +0000
Message-ID: <[email protected]> (raw)
**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());
}
}
}
}
```
view thread (6+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: github://pgjdbc/pgjdbc
Cc: [email protected], [email protected]
Subject: Re: [pgjdbc/pgjdbc] issue #3543: Table meta data is broken if catalog name not equal database name
In-Reply-To: <<[email protected]>>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox