PreparedQuery aborts transaction when run certain MERGE query in transaction

[ben1222]

Version

vertx-pg-client 4.5.13

Context

In Postgres 15, it introduced MERGE query.
When we start to try using it with vertx-pg-client, we encountered an exception current transaction is aborted, commands ignored until end of transaction block (25P02) when executing the PreparedQuery in transaction.
One strange thing is that the same query could work when run without transaction.

After some investigation, we narrowed down the issue and here's an example of the table:

CREATE TABLE test_numstr (
  id VARCHAR(256) NOT NULL,
  test_string VARCHAR(256),
  test_number INTEGER,
  PRIMARY KEY (id)
);

And the prepared query (I know that we can use INSERT... ON CONFLICT (id) DO UPDATE... for this particular case but the original query is having more complicated ON clause and this one is just to illustrate the issue):

MERGE INTO test_numstr
USING (SELECT $1, $2, $3) AS t(id, test_string, test_number)
ON test_numstr.id = t.id
WHEN MATCHED THEN
  UPDATE SET test_string = $4, test_number = $5
WHEN NOT MATCHED THEN
  INSERT (id, test_string, test_number) VALUES (t.id, t.test_string, t.test_number);

If I read it correctly, when vertx sending the prepare command, it first try send without type information:
https://github.com/eclipse-vertx/vertx-sql-client/blob/4.5.13/vertx-sql-client/src/main/java/io/vertx/sqlclient/impl/SocketConnectionBase.java#L261
And when postgres server complained about unable to determine type, it will resend the prepare command with type information:
https://github.com/eclipse-vertx/vertx-sql-client/blob/4.5.13/vertx-sql-client/src/main/java/io/vertx/sqlclient/impl/SocketConnectionBase.java#L309

This approach works when all the parameter types can be inferred by postgres server or when execute without transaction.
However, when this is executed inside a transaction, the error in first prepare command (that without parameter type info) will abort the transaction and there's no chance to retry the second prepare command (that with parameter type info) - it will just respond with aborted transaction error.

// Response of first prepare command (without type info):
ERROR:  column "test_number" is of type integer but expression is of type text at character 267
HINT:  You will need to rewrite or cast the expression.

// Response of second prepare command (with type info):
ERROR:  current transaction is aborted, commands ignored until end of transaction block

The JDBC does not have the problem because they seems always send the parameter type info to the postgres server.

Currently we only find one way to workaround the issue:
Add explicit cast to that integer parameter, i.e.: ... USING (SELECT $1, $2, $3::integer) AS t(id, test_string, test_number) ...
This could work, but it is hard to maintain - whenever a new query is added, or an old query is modified, or a parameter type is changed, we may need to re-evaluate and add/update such cast.

I see an old issue #851 mentioned about the parameter types in PreparedQuery and looks like it was intended to not including parameter type info at first.
So, we are wondering if it is possible to at least provide an option to force include the parameter type info for a particular query/a connection/a pool?
(on the other hand, not sure if it is possible to provide correct type info for, e.g., custom type)

Do you have a reproducer?

Tried create an unit test in PreparedStatementTest.java:
Additional table:

CREATE TABLE test_numstr (
  id VARCHAR(256) NOT NULL,
  test_string VARCHAR(256),
  test_number INTEGER,
  PRIMARY KEY (id)
);

Unit tests:

  private static final String MERGE_WITH_INT_PARAMETER_QUERY = "MERGE INTO test_numstr " +
    "USING (SELECT $1, $2, $3) AS t(id, test_string, test_number) " +
    "ON test_numstr.id = t.id " +
    "WHEN MATCHED THEN " +
    "  UPDATE SET test_string = $4, test_number = $5 " +
    "WHEN NOT MATCHED THEN " +
    "  INSERT (id, test_string, test_number) VALUES (t.id, t.test_string, t.test_number);";

  @Test
  public void testPrepareMergeWithIntParameter(TestContext ctx) {
    final String ID_VALUE = "some_id";
    final String STRING_VALUE = "testPrepareMergeWithIntParameter";
    final int NUMBER_VALUE = 999;
    PgConnection.connect(vertx, options(), ctx.asyncAssertSuccess(conn -> {
      conn
        .preparedQuery(MERGE_WITH_INT_PARAMETER_QUERY)
        .execute(Tuple.of(ID_VALUE, STRING_VALUE, NUMBER_VALUE, STRING_VALUE, NUMBER_VALUE), ctx.asyncAssertSuccess());
    }));
  }

  @Test
  public void testPrepareMergeWithIntParameterInTransaction(TestContext ctx) {
    final String ID_VALUE = "some_id_2";
    final String STRING_VALUE = "testPrepareMergeWithIntParameterInTransaction";
    final int NUMBER_VALUE = 999;
    PgConnection.connect(vertx, options(), ctx.asyncAssertSuccess(conn -> {
      conn
        .begin()
        .compose(txn -> conn.preparedQuery(MERGE_WITH_INT_PARAMETER_QUERY)
          .execute(Tuple.of(ID_VALUE, STRING_VALUE, NUMBER_VALUE, STRING_VALUE, NUMBER_VALUE))
          .compose(
            rows -> txn.commit(),
            err -> txn.rollback()
              .compose(v -> Future.failedFuture(err), err2 -> {
                err.addSuppressed(err2);
                return Future.failedFuture(err);
              })
          )
        )
        .andThen(ctx.asyncAssertSuccess());
    }));
  }

The testPrepareMergeWithIntParameter could pass (because it is not run in transaction), while the testPrepareMergeWithIntParameterInTransaction fails with current transaction is aborted, commands ignored until end of transaction block (25P02)

[tsegismont]

Thanks for the detailed report. I will look into it, I think the mechanism we have in place should work regardless of the transactional state