SimpleJdbcCall does not find stored function with PostgreSQL driver 42.2.11

[rmartseniuk]

Affects: <5.2.5.RELEASE>

We use Spring Boot 2.2.6.RELEASE, which includes jdbc driver PostgreSQL version 42.2.11.

We use SimpleJdbcCall for calling stored function in PostgreSQL database version 11.1.

But when we called stored function, we got InvalidDataAccessApiUsageException with message Unable to determine the correct call signature - no procedure/function/signature for 'set_application_variables'.

I looked under the hood and saw that at the stage compile stored function and gather meta information for it, we call PgDatabaseMetaData.getProcedures(String catalog, String schemaPattern, String procedureNamePattern).
In the version 42.2.11 PostgreSQL jdbc driver was added verification for returning only procedures.

public ResultSet getProcedures(String catalog, String schemaPattern, String procedureNamePattern)
      throws SQLException {
    ...
    if (connection.haveMinimumServerVersion(ServerVersion.v11)) {  //check version of database
      sql += " AND p.prokind='p'"; //check that it is stored procedure
    }
   ...

Class where we call stored function.

    @PostConstruct
    private void init() {
        this.jdbcCallStoredProcedure = new SimpleJdbcCall(dataSource)
                .withSchemaName(SCHEMA_NAME)
                .withFunctionName(STORED_FUNCTION_NAME)
                .declareParameters(
                        new SqlParameter(APP_IGNORE_AUDIT, Types.BOOLEAN),
                        new SqlParameter(APP_USERNAME, Types.VARCHAR),
                        new SqlParameter(APP_TRACE_ID, Types.VARCHAR));
    }

    public void save(AuditInfo auditInfo) {
        try {
            SqlParameterSource procedureParameters = new MapSqlParameterSource()
                    .addValue(APP_IGNORE_AUDIT, auditInfo.isIgnoreAudit())
                    .addValue(APP_USERNAME, auditInfo.getUserName())
                    .addValue(APP_TRACE_ID, auditInfo.getTraceId());
            jdbcCallStoredProcedure.executeFunction(Void.class, procedureParameters);
        } catch (RuntimeException e) {
            log.error("Cannot call stored procedure - set_application_variables which sets transaction variable" +
                    " for passing application parameters to audit table in the database.", e);
        }
    }

Stored function.

create or replace function vg_common_db_audit.set_application_variables(app_ignore_audit boolean, app_username text, app_trace_id text) returns void as $body$
                begin
                    --some logic
                end;
$body$ language plpgsql;

Workaround: as workaround we turned off verification meta data via JDBC with method withoutProcedureColumnMetaDataAccess() in SimpleJdbcCall.

[jhoeller]

This is arguably a breaking change mid-way in the PostgreSQL driver, but given that they're unlikely to revert it and that JDBC 4 did indeed introduce a distinction between procedures and functions, I've revised our GenericCallMetaDataProvider implementation: It explicitly checks DatabaseMetaData.getFunctions as well and correspondingly builds metadata from DatabaseMetaData.getFunctionColumns, provided that DatabaseMetaData.getProcedures did not return a match initially.

I suppose this addresses your scenario. Please test it against the upcoming Spring Framework 5.2.8 snapshot once committed.