Table meta data is broken if catalog name not equal database name

[MaxMello]

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

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,

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, 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

[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

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

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

[vlsi]

@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.

[MaxMello]

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?

[vlsi]

Yeah, it looks like we should use current_database() rather than get database name from the url

[vlsi]

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

[davecramer]

So I think we should just ask for current database when we connect.