Hibernate 6 native queries always query Postgresql metadata to resolve type

Hibernate ORM
1

For security reasons, my database team has removed access to the pg_attribute and pg_attrdef tables.

Now that I have migrated from Hibernate 5 to 6, all my EntityManger native queries fail.

ResultSetAccess has the JDBC result set but always calls resolveType, which then makes metadata queries that return permission denied.

The only success so far has been to use addScalar calls for every column name. I have hundreds of native queries so this presents a lot of risk to refactor everything.

I’m looking for any setting or additional code that will keep my native queries working without refactoring all the individual native queries.

It is a Spring Boot 3.4.2 application with Hibernate 6.6.5 and Posgresql 42.7.5 JDBC driver and Postgresql 16.6.

2

There is no setting to control this. You can create a feature request in our issue tracker, but unless you plan on investing into this, I doubt that anyone will work on this anytime soon.

Clearly, Hibernate ORM needs to know the types of result set items to be able to do some normalization. If you don’t care about that, you can also use the Session#doReturningWork to use JDBC APIs directly.

3

Thank you for your prompt response.

This isn’t an ORM mapping issue—the result is simply an object array:

List<Object[]> list = (List<Object[]>) entityManager.createNativeQuery(“select id, name from mytable”).getResultList();

With Hibernate 5.4.28, CustomLoader used JdbcResultSetMetadata for type discovery. The results were already retrieved, and metadata provided types like int4 for id and varchar for name, which Hibernate resolved using Dialect#hibernateTypeNames.

In Hibernate 6, however, metadata resolution now queries pg_catalog, even when the result set metadata is available. This introduces permission issues in restricted database environments.

Is there a way to configure Hibernate 6 to rely on ResultSetMetaData instead of querying pg_catalog?"

4

Hibernate ORM is not querying pg_catalog, this is the doing of the JDBC driver. Please share a stack trace that shows where this query is coming from.

5

I understand. I’ve stepped thru the code for 5 & 6.

With Hibernate 5, the java.sql.ResultSetMetaData was wrapped in a Hibernate JdbcResultMetadata. It used the position to get the int column type and then used its own Hibernate type names from the dialect to resolve the type.

Hibernate 6 uses the ResultSetMetaData#getColumnTypeName(int) directly - that’s when the Postgresql driver queries the database to check if it is an auto increment column, which may alter the type.

I will discuss with my team on how we can proceed.

Thanks for your help.

6

Thanks for the insight. So I guess that the driver e.g. knows that this is a Types.INTEGER, but to understand whether it is a serial or just int4 it does that catalog query?

If this is the case for other drivers as well and causes a slowdown (because that info isn’t cached), then we might have to defer acquiring that, because usually, we only need the actual type name in a few cases e.g. when it’s a struct, array, uuid etc.

Do you happen to know if other methods on the ResultSetMetadata might also cause that catalog query to be executed?

7

I tested a simple query using doReturningWork.
The ResultSetMetaData methods that queried the catalog were:

public String getColumnTypeName(int)
public String getTableName(int)
public boolean isAutoIncrement(int)
public boolean isNullable(int)
8

Thanks again for the insight. Even though we could and probably should reduce the amount of queries that are executed in this scenario by deferring the call to getColumnTypeName(), it might still happen that the pgjdbc driver executes catalog queries for certain operations.

Please create an improvement request in our issue tracker and link this conversation. Also, it might be worth opening a ticket on the pgjdbc driver issue tracker and request that the implementation of getColumnTypeName() only calls isAutoIncrement() when it actually makes a difference i.e. when the pg type name is one of the int2/int4/int8 types and might be turned into a serial.