Allow to retrieve a proxy of Statement#getResultSet during `boolean PreparedStatement.execute()`

[reda-alaoui]

Hibernate recently changed the way it fetches insert generated keys for PostgresSQL. Instead of relying on Statement#getGeneratedKeys() and Statement#RETURN_GENERATED_KEYS, it uses Statement#getResultSet this way :

if ( !statement.execute( sql ) ) {
	while ( !statement.getMoreResults() && statement.getUpdateCount() != -1 ) {
		// do nothing until we hit the resultset
	}
}
rs = statement.getResultSet();

Because of this, we need a way to retrieve a Repeatable Statement#getResultSet cached during boolean PreparedStatement.execute().

[reda-alaoui]

	/**
	 * Does this dialect fully support returning arbitrary generated column values
	 * after execution of an {@code insert} statement, using native SQL syntax?
	 * <p>
	 * Support for identity columns is insufficient here, we require something like:
	 * <ol>
	 * <li>{@code insert ... returning ...}
	 * <li>{@code select from final table (insert ... )}
	 * </ol>
	 *
	 * @return {@code true} if {@link org.hibernate.id.insert.InsertReturningDelegate}
	 *         works for any sort of primary key column (not just identity columns), or
	 *         {@code false} if {@code InsertReturningDelegate} does not work, or only
	 *         works for specialized identity/"autoincrement" columns
	 *
	 * @see org.hibernate.id.insert.InsertReturningDelegate
	 *
	 * @since 6.2
	 */
	public boolean supportsInsertReturning() {
		return false;
	}

Looks like this is the feature that needs support. For PostgresDialect, it returns true.

[ttddyy]

Hi @reda-alaoui,

Thanks for the report.

So, if I understand correctly, when inserting a record to Postgres with new Hibernate, it issues a query like INSERT INTO ... RETURNING (id, ...) and uses the returned id for generated id instead of using the getGeneratedKeys method.

Since the RETURNING clause executes as a regular query and JDBC does not have a dedicated API to handle it, it is challenging for datasource-proxy to distinguish when a query is generating keys and apply the corresponding generated-key's result-set proxy creation logic.

I’m thinking how to address this, and one potential approach could be:

• Make ResultSetProxyLogicFactory to be applied more dynamically at every ResultSet creation. So that, it can change the proxy creation based on the executing query. (need code change in datasource-proxy)
• By parsing the query, if it includes a RETURNING clause, you can create a RepeatableReadResultSetProxyLogic. Otherwise, you can fall back on SimpleResultSetProxyLogic or a similar logic.

[reda-alaoui]

Hi @ttddyy ,
Are you saying that option 2 can be implemented without modifying datasource-proxy?

[ttddyy]

Are you saying that option 2 can be implemented without modifying datasource-proxy?

Ah, no. They are not separate options, just one option. So, it needs a code change in datasource-proxy.

[ttddyy]

Hi @reda-alaoui

I've created a poc for this issue.

The datasource-proxy side change is in gh-114-pg-returning branch.
Using this branch, I created a sample project: https://github.com/ttddyy-org/dsproxy-gh114

The main part is DynamicResultSetProxyLogicFactory, which determines the proxy based on the executing query. When the query contains returning, it creates a repeatable read resultset.

When you get a chance, can you check whether this could work for your usage in postgres returning queries?