Message-ID: <gh-pgjdbc-pgjdbc-3543@github.com>
From: "MaxMello (@MaxMello)" <noreply+MaxMello@github.com>
To: "pgjdbc/pgjdbc" <noreply+pgjdbc-pgjdbc@github.com>
Date: Mon, 03 Mar 2025 11:07:58 +0000
Subject: [pgjdbc/pgjdbc] issue #3543: Table meta data is broken if catalog name not equal database name
List-Id: <gh-pgjdbc-pgjdbc.github.com>
X-GitHub-Author-Id: 6271594
X-GitHub-Author-Login: MaxMello
X-GitHub-Issue: 3543
X-GitHub-Repo: pgjdbc/pgjdbc
X-GitHub-State: open
X-GitHub-Type: issue
X-GitHub-Url: https://github.com/pgjdbc/pgjdbc/issues/3543
Content-Type: text/plain; charset=utf-8

**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());
            }
        }
    }
}
```