Batched function calls result in syntax errors on Postgres

[yeroc]

We had some code that implemented batched calls of a SQL function. Simplified code:

  private void batchingCalls(Handle handle, List<String> physIdList)
  {
    try (PreparedBatch sql = handle.prepareBatch("{call package.add_to_item_array( p_piid => :physId )}"))
    {
      for (String physId : physIdList)
      {
        sql.bind("physId", physId).add();
      }
      sql.execute();
    }
  }

This code works fine on Oracle but fails when run against Postgres (actually EnterpriseDB's flavour) with the following:

org.jdbi.v3.core.statement.UnableToExecuteStatementException: java.sql.BatchUpdateException: Batch entry 0 {call package.add_to_item_array( p_piid => '10361369' )} was aborted: ERROR: syntax error at or near "{"
  Position: 1  Call getNextException to see other errors in the batch. [statement:"{call package.add_to_item_array( p_piid => :physId )}", arguments:{positional:{}, named:{physId:10361370}, finder:[]}]
org.jdbi.v3.core.statement.UnableToExecuteStatementException: java.sql.BatchUpdateException: Batch entry 0 {call package.add_to_item_array( p_piid => '10361369' )} was aborted: ERROR: syntax error at or near "{"
  Position: 1  Call getNextException to see other errors in the batch. [statement:"{call package.add_to_item_array( p_piid => :physId )}", arguments:{positional:{}, named:{physId:10361370}, finder:[]}]
        at org.jdbi.v3.core.statement.PreparedBatch.internalBatchExecute(PreparedBatch.java:264)
        at org.jdbi.v3.core.statement.PreparedBatch.execute(PreparedBatch.java:122)
        at pgbatchjdbi.batchingCalls(pgbatchjdbi.java:123)
        at pgbatchjdbi.lambda$saveLtoGeom$0(pgbatchjdbi.java:99)
        at org.jdbi.v3.core.HandleConsumer.lambda$asCallback$0(HandleConsumer.java:32)
        at org.jdbi.v3.core.Jdbi.withHandle(Jdbi.java:367)
        at org.jdbi.v3.core.Jdbi.useHandle(Jdbi.java:383)
        at pgbatchjdbi.main(pgbatchjdbi.java:66)
Caused by: java.sql.BatchUpdateException: Batch entry 0 {call package.add_to_item_array( p_piid => '10361369' )} was aborted: ERROR: syntax error at or near "{"
  Position: 1  Call getNextException to see other errors in the batch.
        at org.postgresql.jdbc.BatchResultHandler.handleError(BatchResultHandler.java:165)
        at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:877)
        at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:916)
        at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1684)
        at com.p6spy.engine.wrapper.StatementWrapper.executeBatch(StatementWrapper.java:98)
        at org.jdbi.v3.core.statement.SqlLoggerUtil.wrap(SqlLoggerUtil.java:31)
        at org.jdbi.v3.core.statement.PreparedBatch.internalBatchExecute(PreparedBatch.java:256)
        ... 17 more
        Suppressed: org.postgresql.util.PSQLException: ERROR: syntax error at or near "{"
  Position: 1
                at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
                at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
                at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
                at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:316)
                at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:874)
                ... 22 more
Caused by: [CIRCULAR REFERENCE: org.postgresql.util.PSQLException: ERROR: syntax error at or near "{"
  Position: 1]

I've confirmed this isn't a driver/database issue by porting the above code to raw JDBC which works fine so I'm at a loss as to why it fails using JDBI:

  private void batchingCalls(Connection conn, List<String> physIdList) throws SQLException
  {
    try (CallableStatement sql = conn.prepareCall("{call package.add_to_item_array( p_piid => ? )}"))
    {
      for (String physId : physIdList)
      {
        sql.setString(1, physId);
        sql.addBatch();
      }
      sql.executeBatch();
    }
  }

NOTE: We're not using the PostgresPlugin typically but I did try it with the Postgres plugin and got the same result.

[hgschmie]

Hi @yeroc,

Thank you for opening an issue with the Jdbi project! We will need a bit of time digging into this. This sounds like a bug in Jdbi. I am a bit surprised seeing the [CIRCULAR REFERENCE: org.postgresql.util.PSQLException: ERROR: syntax error at or near "{" error message.

[yeroc]

@hgschmie Thanks for responding.

Looking at the stacktrace more carefully I suspect the issue is that JDBI must be always batching these on a JDBC PreparedStatement (given that we're seeing "org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1684)" in the stack trace). Does there need to be a conditional check made to examine the SQL and if it's prefixed with "{call" then batch into a CallableStatement (via Connection.prepareCall()) instead?

I suspect this worked for us on Oracle because that driver must be more lenient about PreparedStatement batches containing function calls.

[yeroc]

Alternatively, I suppose a separate Handle.prepareCallableBatch() method could be used to make this explicit. There's something seductive about wallpapering over this particular JDBC nuance but it could be that dragons lurk in the shadows when trying to auto-detect whether it's more appropriate to batch onto a PreparedStatement vs CallableStatement.

[hgschmie]

Closing, still tracked in #2390.