PreparedQuery does not take parameter types into account

[tsegismont]

This bug reports follows-up on quarkusio/quarkus#11501

The user initially reported they were not able to insert into jsonb column with Pg Client whereas the same query with Postgres JDBC works.

I proposed a better query to the user that works with both Postgres JDBC and the Pg Client as a workaround.

As for the issue, I did a little research and it seems the Pg client does not always send parameter types when preparing a query.

Consider the following table:

CREATE TABLE object_data (data JSONB)

And the following query:

INSERT INTO object_data (data)  VALUES (to_json($1)) RETURNING (data)

If you execute:

PreparedQuery<RowSet<Row>> insertQuery = pgConnection.preparedQuery("INSERT INTO object_data (data)  VALUES (to_json($1)) RETURNING (data)");
insertQuery.execute(Tuple.of("toto"), ctx.asyncAssertSuccess(rs -> {
  Row row = rs.iterator().next();
  Object value = row.getJson(0);
  System.out.println("value = " + value);
}));

You will see:

io.vertx.pgclient.PgException: { "message": "could not determine polymorphic type because input has type unknown", "severity": "ERROR", "code": "42804", "file": "parse_coerce.c", "line": "1866", "routine": "enforce_generic_type_consistency" }

But if you change this:

vertx-sql-client/vertx-sql-client/src/main/java/io/vertx/sqlclient/impl/SocketConnectionBase.java

Line 194 in a08a7d7

PrepareStatementCommand prepareCmd = prepareCommand(queryCmd, cache, false);

to

PrepareStatementCommand prepareCmd = prepareCommand(queryCmd, cache, true);

Then you get the expected result:

value = toto

My understanding is that we do not want to send the parameters because we wouldn't be able to cache the prepared statement, correct?

I believe Posgres JDBC checks if the types are the same before reusing a cached statement and, if they are not the same, prepares the statement again with new types.

[vietj]

We do not provide the parameters because some queries might use a different parameter types at execution time and that would result in a cast error in PG (e.g using a function that take any parameter as input, if you bind it with integer, then it will fail for a string).

Normally the client will detect that situation and try a rebind with the parameters when it happens and there is a check for this in PgSocketConnection:

  @Override
  public boolean isIndeterminatePreparedStatementError(Throwable t) {
    if (t instanceof PgException) {
      PgException e = (PgException) t;
      return "42P18".equals(e.getCode());
    }
    return false;
  }

The error reported in the log is _ 42804_ and we should also check this specific code.

[vietj]

Here is the original fix #699

[vietj]

Here is the issue that happens if you bind parameters #700 such queries should not be cached however we cannot detect it.

Hence we have a strategy to first prepare without parameters and when it fails we rebind with parameters.

[tsegismont]

pgjdbc stores the parameters used when preparing an re-prepares if the cached query is used with a different type:

https://github.com/pgjdbc/pgjdbc/blob/529e5dc3a8f071ad4946daf3bde1422c21a99524/pgjdbc/src/main/java/org/postgresql/core/v3/SimpleQuery.java#L181-L195

This is not great performance-wise, but that's a choice we could make.

I don't mean we should make it though. Perhaps we could document that in a new doc section that describes differences with pgjdbc?

[vietj]

I think we can first support the other code reported by PG and in the future do what you are suggesting if that remains a blocker issue.

…

On Tue, Dec 15, 2020 at 11:01 AM Thomas Segismont ***@***.***> wrote: pgjdbc stores the parameters used when preparing an re-prepares if the cached query is used with a different type: https://github.com/pgjdbc/pgjdbc/blob/529e5dc3a8f071ad4946daf3bde1422c21a99524/pgjdbc/src/main/java/org/postgresql/core/v3/SimpleQuery.java#L181-L195 This is not great performance-wise, but that's a choice we could make. I don't mean we should make it though. Perhaps we could document that in a new doc section that describes differences with pgjdbc? — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#851 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AABXDCS35UBRW22FHSTN2LDSU4XYXANCNFSM4U3DX4MQ> .

[tsegismont]

I think we can first support the other code reported by PG

Not sure what you mean

[vietj]

I mean that the fix we did works when PG reports the error with the code 42P18 and the error you reported from Quarkus uses the code 42804. So we should handle and test with error code 42804.

[vietj]

i.e when we get a 42804 we should prepare the statement with types.

[tsegismont]

Yes, thanks for claryfing.