eclipse-vertx / eclipse-vertx/vertx-sql-client

PreparedQuery aborts transaction when run certain MERGE query in transaction

Open
#1,511 2 comments 0 reactions 1 assignee Claimed by @tsegismont View on GitHub
bug
Dominant language
Java
Stars
912
Forks
212
Avg merge
17h 30m
Merged PRs (30d)
3

Description

### Version

vertx-pg-client 4.5.13

### Context

In Postgres 15, it introduced [MERGE query](https://www.postgresql.org/docs/15/sql-merge.html).
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:
```sql
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):
```sql
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:
```sql
CREATE TABLE test_numstr (
id VARCHAR(256) NOT NULL,
test_string VARCHAR(256),
test_number INTEGER,
PRIMARY KEY (id)
);
```
Unit tests:
```java
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)`

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.