I have a very specific problem, we are running Exp...

# exposed

[maxmello]

I have a very specific problem, we are running Exposed (v0.59) with Postgres using a cluster of primary (write) and replica (read) nodes, using pgbouncer.
For this, in the pgbouncer config, we define multiple connections:

read = host=pgcluster-replicas port=5432 dbname=postgres
write = host=pgcluster-primary port=5432 dbname=postgres
postgres = host=pgcluster-primary port=5432 dbname=postgres

• We are using schema per service approach where the search path is restricted for each individual services database user to only see the relevant schema(s).
• To properly differentiate between read and write nodes, we use the following dataSource strings: pgcluster-pgbouncer.postgres.svc.cluster.local:5432/read and pgcluster-pgbouncer.postgres.svc.cluster.local:5432/write
• We are connecting using the following code (we establish two connections in practice, one for read, one for write)

Database.connect(url = "jdbc:postgresql://$dataSource", ...) // dataSource is the string above

When the name of the connection is NOT EQUAL the dbname (so “read” and “write” vs “postgres”), Exposeds SchemaUtils breaks. The behavior is as follows:
• SchemaUtils.listDatabases() DOES show the correct database name “postgres”
• SchemaUtils.listTablesInAllSchemas() and SchemaUtils.listTables() show no entries
• any Table.exists() returns false, even though it exists
• TransactionManager.current().connection.schema does detect the correct schema though
• All normal operations (querying, create statements etc.) work with no problem, I think the problem lies somewhere in VendorDialect getAllTableNamesCache or so, but its very hard for me to find how/where exactly this breaks.
• If I connect using /postges at the end of the dataSource string it works, but this means we cannot use nodes with different names (read & write)

[maxmello]

https://github.com/pgjdbc/pgjdbc/pull/3390

I think I found the reason, not in Exposed but in pgjdbc. Though I have no idea where/how to approach a workaround or fix of this problem

[maxmello]

I ensured the reason is the update from org.postgresql:postgresql 42.7.4 to 42.7.5
• In 42.7.4, metaData.getTables(catalog, schema, null, arrayOf("TABLE")) with catalog = metadata.catalog (which is what Exposed uses) returns all tables of the schema. If I hardcode catalog to be the dbname (“postgres”), it also works
• In 42.7.5, only the hardcorded catalog name “postgres” works, using metadata.catalog (= “read” or “write”) no longer returns any results
This is the relevant code in exposed:
https://github.com/JetBrains/Exposed/blob/40955f38b286dcc74e7089683df4ed856df0bcba[…]tbrains/exposed/sql/statements/jdbc/JdbcDatabaseMetadataImpl.kt

Is there any chance Exposed could implement some fallback logic if catalog name != database name for postgres?
I will also file an issue for pgjdbc and possibly with pgbouncer, I am not sure where the correct place is to fix this issue.

Edit:
https://github.com/pgjdbc/pgjdbc/issues/3543
here is the issue for anyone interested, it includes steps to reproduce it (without Exposed). Depending on what is actual expected behavior, it will be interesting where this can be fixed.