Spring JDBC: PostgreSQL 42.7.5: catalog name is treated as case insensitive

[frederikz]

Since the Postgres driver 42.7.5 PostgresTableMetaDataProvider no longer retrieves column metadata for databases which have an upper case in their name:
org.springframework.dao.InvalidDataAccessApiUsageException: Unable to locate columns for table 'XXX' so an insert statement can't be generated.
org.springframework.jdbc.core.metadata.TableMetaDataContext.createInsertString(TableMetaDataContext.java:351)

Before Postgres driver version 42.7.5 in method locateTableAndProcessMetaData of class GenericTableMetaDataProvider the table metadata returned no value for TABLE_CAT so later in processTableColumns also a null catalog name was provided and databaseMetaData.getColumns didn't filter by catalog name. Since 42.7.5 TABLE_CAT returns a value like abC which is then in processTableColumns transformed to abc by metaDataCatalogNameToUse and passed to databaseMetaData.getColumns . This then returns no value for a catalog name abc but would returnd columns with a catalog name abC.
As the Javadoc says "must match the catalog name as it is stored in the database" and other projects like liquibase/liquibase#6666 / pgjdbc/pgjdbc#3560 fixed it in their code I believe that it is a bug in Spring JDBC and not in the Postgres JDBC Driver and that metaDataCatalogNameToUse should not change the catalog name retrieved from table metadata.

[jhoeller]

Does it work when you explicitly call setStoresLowerCaseIdentifiers(false)? It looks like the Postgres DatabaseMetaData.storesLowerCaseIdentifiers() method returns true, making GenericTableMetaDataProvider convert identifiers to lower-case accordingly. This could be a bug in the Postgres JDBC driver where its metadata does not match its behavior anymore.

[frederikz]

Yes, DatabaseMetaData.storesLowerCaseIdentifiers() returns true and therefore setStoresLowerCaseIdentifiers(false) would help. I don't know if one could argue that DatabaseMetaData.storesLowerCaseIdentifiers() only applies to identifieres in an SQL statement and DatabaseMetaData.getColumns is not an SQL statement and then the Javadoc of the catalog parameter of DatabaseMetaData.getColumns applies must match the catalog name as it is stored in the database and you should not transform the catalog name you got back from the database, but at the same time it is at least confusing that the Postgres driver returns a catalog name with upper cases and will only accept it that way when DatabaseMetaData.storesLowerCaseIdentifiers() returns true.

[jhoeller]

Given the response on the pgjdbc issue, I'm overriding metaDataCatalogNameToUse and metaDataSchemaNameToUse on our PostgresTableMetaDataProvider to always return the given names as-is. As far as I can tell, case sensitivity of catalog and schema names - and in particular this interpretation of case sensitivity with JDBC metadata methods - is not common with other databases.

This should be backwards compatible with older pgjdbc versions as well since the interpretation of case sensitivity on the Postgres side has not changed, just the exposure of a catalog name in DatabaseMetaData.getTables to begin with.

[jhoeller]

@frederikz this is available in the latest 6.2.10 snapshot now. Please give it an early try and let me know whether it works for you!

[frederikz]

Thanks for the fix. It works, tested with the newer postgresql-42.7.7 it was previously not working and it is also still working with 42.7.4.

[jhoeller]

Great to hear! Thanks for the immediate feedback.