UpdateResult.getKeys() returns a PGobject instead of a String

[colinchilds]

I found this issue while trying to upgrade from vert.x 3 to vert.x 4.3.3. After some testing, I noticed that it works correctly in 4.3.0 but starts failing in 4.3.1.

I have many tables with a text id and a jsonb data column. After running updateWithParams and getting the keys, I used to be able to get the data column with result.getKeys().getString(1) but that now throws an error, as the key is a PGobject instead of a string.

This can be easily reproduced by simply running the documentation code found here on vert.x 4.3.0 and again on 4.3.1+ with a jsonb column. result.getKeys() contains different values between the two.

[colinchilds]

After looking into this further, I've noticed that many of my queries that return jsonb are breaking. Here's another simple example that returns a PGobject now instead of a String:

connection.query("select '[\"test\"]'::jsonb", res -> {
    var results = res.result().getResults();
    System.out.println(results.get(0).getValue(0).getClass());
});

[colinchilds]

@tsegismont would you be able to help me look into this? It's blocking my efforts to upgrade dozens of projects to the latest vertx and is a bit time sensitive.

[vietj]

@pmlopes can you have a look ?

[colinchilds]

Hey @pmlopes just wanted to ping you again to see if you could help me look into this. Thank you!

[pmlopes]

@zero88 I think this issue arises from the new "encode/decode" functionality. I'm not sure if the vendor specific type checks are correct. I believe they should use the type OTHER instead of null. However this will break the support for UUID. Which also is seen as type OTHER for postgres.

Can you help you clear the issue here?

[colinchilds]

@zero88 just bumping this again to see if you can help out. Thanks!

[zero88]

@colinchilds thanks for the heads up, and so sorry for the late response. let me re-check it. Do you mind sharing your environment

• vertx version
• pgJDBC version
• PostgreSQL server version
• Which datasource library and version? hikari/agroal or c3p0?

As your description, I understand you are using legacy JDBCClient, right?

[zero88]

nvm, I'm able to reproduce it. Will dig more

[vietj]

thanks @zero88

[zero88]

because PG driver wraps an unknown type by JDBC Standards (JDBCType.OTHER) into PGobject, then the vendorTypeClass will be PGObject in most cases.

• If using row get field by vendorTypeClass == PGObject, the field value will be an instance of PGObject.
• If using row get field by another class, the field value will be cast to the given class if possible (this process is handled internally depending on JDBC driver)
• If using row get field without class, the field value will be the runtime type, it can be string, uuid, pgobject, or whatever type depending on JDBC driver (and its version). Luckily, if it is a custom type from JDBC driver, toString method will convert to a human-readable value instead of a memory address.

So, to deal with the JDBC drivers world and make it compatible with Vertx JsonObject, all unhandled JDBC type will be converted to String.

A reference log in Vertx < 4.3.1 indicates some types are affect with new code

``` 09:19:36.391 [vert.x-worker-thread-1] DEBUG io.vertx.ext.jdbc.spi.JDBCDecoder - Fallback to string when handling the unhandled JDBCType in Vertx JDBCColumnDescriptor[columnName=(f_misc_uuid), jdbcTypeWrapper=(JDBCTypeWrapper[vendorTypeNumber=(1111), vendorTypeName=(uuid), vendorTypeClass=(class java.util.UUID), jdbcType=(OTHER)])] 09:19:36.392 [vert.x-worker-thread-1] DEBUG io.vertx.ext.jdbc.spi.JDBCDecoder - Fallback to string when handling the unhandled JDBCType in Vertx JDBCColumnDescriptor[columnName=(f_interval), jdbcTypeWrapper=(JDBCTypeWrapper[vendorTypeNumber=(1111), vendorTypeName=(interval), vendorTypeClass=(class org.postgresql.util.PGInterval), jdbcType=(OTHER)])] 09:19:36.392 [vert.x-worker-thread-1] DEBUG io.vertx.ext.jdbc.spi.JDBCDecoder - Fallback to string when handling the unhandled JDBCType in Vertx JDBCColumnDescriptor[columnName=(f_duration), jdbcTypeWrapper=(JDBCTypeWrapper[vendorTypeNumber=(1111), vendorTypeName=(interval), vendorTypeClass=(class org.postgresql.util.PGInterval), jdbcType=(OTHER)])] 09:19:36.392 [vert.x-worker-thread-1] DEBUG io.vertx.ext.jdbc.spi.JDBCDecoder - Fallback to string when handling the unhandled JDBCType in Vertx JDBCColumnDescriptor[columnName=(f_udt_address), jdbcTypeWrapper=(JDBCTypeWrapper[vendorTypeNumber=(2002), vendorTypeName=(full_address), vendorTypeClass=(class java.lang.String), jdbcType=(STRUCT)])] 09:19:36.392 [vert.x-worker-thread-1] DEBUG io.vertx.ext.jdbc.spi.JDBCDecoder - Fallback to string when handling the unhandled JDBCType in Vertx JDBCColumnDescriptor[columnName=(f_json_object), jdbcTypeWrapper=(JDBCTypeWrapper[vendorTypeNumber=(1111), vendorTypeName=(json), vendorTypeClass=(class org.postgresql.util.PGobject), jdbcType=(OTHER)])] 09:19:36.392 [vert.x-worker-thread-1] DEBUG io.vertx.ext.jdbc.spi.JDBCDecoder - Fallback to string when handling the unhandled JDBCType in Vertx JDBCColumnDescriptor[columnName=(f_json_array), jdbcTypeWrapper=(JDBCTypeWrapper[vendorTypeNumber=(1111), vendorTypeName=(json), vendorTypeClass=(class org.postgresql.util.PGobject), jdbcType=(OTHER)])] 09:19:36.392 [vert.x-worker-thread-1] DEBUG io.vertx.ext.jdbc.spi.JDBCDecoder - Fallback to string when handling the unhandled JDBCType in Vertx JDBCColumnDescriptor[columnName=(f_json_number), jdbcTypeWrapper=(JDBCTypeWrapper[vendorTypeNumber=(1111), vendorTypeName=(json), vendorTypeClass=(class org.postgresql.util.PGobject), jdbcType=(OTHER)])] 09:19:36.392 [vert.x-worker-thread-1] DEBUG io.vertx.ext.jdbc.spi.JDBCDecoder - Fallback to string when handling the unhandled JDBCType in Vertx JDBCColumnDescriptor[columnName=(f_json_string), jdbcTypeWrapper=(JDBCTypeWrapper[vendorTypeNumber=(1111), vendorTypeName=(json), vendorTypeClass=(class org.postgresql.util.PGobject), jdbcType=(OTHER)])] 09:19:36.392 [vert.x-worker-thread-1] DEBUG io.vertx.ext.jdbc.spi.JDBCDecoder - Fallback to string when handling the unhandled JDBCType in Vertx JDBCColumnDescriptor[columnName=(f_json_boolean_true), jdbcTypeWrapper=(JDBCTypeWrapper[vendorTypeNumber=(1111), vendorTypeName=(json), vendorTypeClass=(class org.postgresql.util.PGobject), jdbcType=(OTHER)])] 09:19:36.392 [vert.x-worker-thread-1] DEBUG io.vertx.ext.jdbc.spi.JDBCDecoder - Fallback to string when handling the unhandled JDBCType in Vertx JDBCColumnDescriptor[columnName=(f_json_boolean_false), jdbcTypeWrapper=(JDBCTypeWrapper[vendorTypeNumber=(1111), vendorTypeName=(json), vendorTypeClass=(class org.postgresql.util.PGobject), jdbcType=(OTHER)])] 09:19:36.392 [vert.x-worker-thread-1] DEBUG io.vertx.ext.jdbc.spi.JDBCDecoder - Fallback to string when handling the unhandled JDBCType in Vertx JDBCColumnDescriptor[columnName=(f_json_null_value), jdbcTypeWrapper=(JDBCTypeWrapper[vendorTypeNumber=(1111), vendorTypeName=(json), vendorTypeClass=(class org.postgresql.util.PGobject), jdbcType=(OTHER)])] 09:19:36.392 [vert.x-worker-thread-1] DEBUG io.vertx.ext.jdbc.spi.JDBCDecoder - Fallback to string when handling the unhandled JDBCType in Vertx JDBCColumnDescriptor[columnName=(f_json_null), jdbcTypeWrapper=(JDBCTypeWrapper[vendorTypeNumber=(1111), vendorTypeName=(json), vendorTypeClass=(class org.postgresql.util.PGobject), jdbcType=(OTHER)])] 09:19:36.392 [vert.x-worker-thread-1] DEBUG io.vertx.ext.jdbc.spi.JDBCDecoder - Fallback to string when handling the unhandled JDBCType in Vertx JDBCColumnDescriptor[columnName=(f_jsonb_object), jdbcTypeWrapper=(JDBCTypeWrapper[vendorTypeNumber=(1111), vendorTypeName=(jsonb), vendorTypeClass=(class java.lang.String), jdbcType=(OTHER)])] 09:19:36.393 [vert.x-worker-thread-1] DEBUG io.vertx.ext.jdbc.spi.JDBCDecoder - Fallback to string when handling the unhandled JDBCType in Vertx JDBCColumnDescriptor[columnName=(f_jsonb_array), jdbcTypeWrapper=(JDBCTypeWrapper[vendorTypeNumber=(1111), vendorTypeName=(jsonb), vendorTypeClass=(class java.lang.String), jdbcType=(OTHER)])] 09:19:36.393 [vert.x-worker-thread-1] DEBUG io.vertx.ext.jdbc.spi.JDBCDecoder - Fallback to string when handling the unhandled JDBCType in Vertx JDBCColumnDescriptor[columnName=(f_jsonb_number), jdbcTypeWrapper=(JDBCTypeWrapper[vendorTypeNumber=(1111), vendorTypeName=(jsonb), vendorTypeClass=(class java.lang.String), jdbcType=(OTHER)])] 09:19:36.393 [vert.x-worker-thread-1] DEBUG io.vertx.ext.jdbc.spi.JDBCDecoder - Fallback to string when handling the unhandled JDBCType in Vertx JDBCColumnDescriptor[columnName=(f_jsonb_string), jdbcTypeWrapper=(JDBCTypeWrapper[vendorTypeNumber=(1111), vendorTypeName=(jsonb), vendorTypeClass=(class java.lang.String), jdbcType=(OTHER)])] 09:19:36.393 [vert.x-worker-thread-1] DEBUG io.vertx.ext.jdbc.spi.JDBCDecoder - Fallback to string when handling the unhandled JDBCType in Vertx JDBCColumnDescriptor[columnName=(f_jsonb_boolean_true), jdbcTypeWrapper=(JDBCTypeWrapper[vendorTypeNumber=(1111), vendorTypeName=(jsonb), vendorTypeClass=(class java.lang.String), jdbcType=(OTHER)])] 09:19:36.393 [vert.x-worker-thread-1] DEBUG io.vertx.ext.jdbc.spi.JDBCDecoder - Fallback to string when handling the unhandled JDBCType in Vertx JDBCColumnDescriptor[columnName=(f_jsonb_boolean_false), jdbcTypeWrapper=(JDBCTypeWrapper[vendorTypeNumber=(1111), vendorTypeName=(jsonb), vendorTypeClass=(class java.lang.String), jdbcType=(OTHER)])] 09:19:36.393 [vert.x-worker-thread-1] DEBUG io.vertx.ext.jdbc.spi.JDBCDecoder - Fallback to string when handling the unhandled JDBCType in Vertx JDBCColumnDescriptor[columnName=(f_jsonb_null_value), jdbcTypeWrapper=(JDBCTypeWrapper[vendorTypeNumber=(1111), vendorTypeName=(jsonb), vendorTypeClass=(class java.lang.String), jdbcType=(OTHER)])] 09:19:36.393 [vert.x-worker-thread-1] DEBUG io.vertx.ext.jdbc.spi.JDBCDecoder - Fallback to string when handling the unhandled JDBCType in Vertx JDBCColumnDescriptor[columnName=(f_jsonb_null), jdbcTypeWrapper=(JDBCTypeWrapper[vendorTypeNumber=(1111), vendorTypeName=(jsonb), vendorTypeClass=(class java.lang.String), jdbcType=(OTHER)])] 09:19:36.393 [vert.x-worker-thread-1] DEBUG io.vertx.ext.jdbc.spi.JDBCDecoder - Fallback to string when handling the unhandled JDBCType in Vertx JDBCColumnDescriptor[columnName=(f_point), jdbcTypeWrapper=(JDBCTypeWrapper[vendorTypeNumber=(1111), vendorTypeName=(point), vendorTypeClass=(class org.postgresql.geometric.PGpoint), jdbcType=(OTHER)])] 09:19:36.393 [vert.x-worker-thread-1] DEBUG io.vertx.ext.jdbc.spi.JDBCDecoder - Fallback to string when handling the unhandled JDBCType in Vertx JDBCColumnDescriptor[columnName=(f_line), jdbcTypeWrapper=(JDBCTypeWrapper[vendorTypeNumber=(1111), vendorTypeName=(line), vendorTypeClass=(class org.postgresql.geometric.PGline), jdbcType=(OTHER)])] 09:19:36.393 [vert.x-worker-thread-1] DEBUG io.vertx.ext.jdbc.spi.JDBCDecoder - Fallback to string when handling the unhandled JDBCType in Vertx JDBCColumnDescriptor[columnName=(f_lseg), jdbcTypeWrapper=(JDBCTypeWrapper[vendorTypeNumber=(1111), vendorTypeName=(lseg), vendorTypeClass=(class org.postgresql.geometric.PGlseg), jdbcType=(OTHER)])] 09:19:36.393 [vert.x-worker-thread-1] DEBUG io.vertx.ext.jdbc.spi.JDBCDecoder - Fallback to string when handling the unhandled JDBCType in Vertx JDBCColumnDescriptor[columnName=(f_box), jdbcTypeWrapper=(JDBCTypeWrapper[vendorTypeNumber=(1111), vendorTypeName=(box), vendorTypeClass=(class org.postgresql.geometric.PGbox), jdbcType=(OTHER)])] 09:19:36.393 [vert.x-worker-thread-1] DEBUG io.vertx.ext.jdbc.spi.JDBCDecoder - Fallback to string when handling the unhandled JDBCType in Vertx JDBCColumnDescriptor[columnName=(f_closed_path), jdbcTypeWrapper=(JDBCTypeWrapper[vendorTypeNumber=(1111), vendorTypeName=(path), vendorTypeClass=(class org.postgresql.geometric.PGpath), jdbcType=(OTHER)])] 09:19:36.393 [vert.x-worker-thread-1] DEBUG io.vertx.ext.jdbc.spi.JDBCDecoder - Fallback to string when handling the unhandled JDBCType in Vertx JDBCColumnDescriptor[columnName=(f_opened_path), jdbcTypeWrapper=(JDBCTypeWrapper[vendorTypeNumber=(1111), vendorTypeName=(path), vendorTypeClass=(class org.postgresql.geometric.PGpath), jdbcType=(OTHER)])] 09:19:36.393 [vert.x-worker-thread-1] DEBUG io.vertx.ext.jdbc.spi.JDBCDecoder - Fallback to string when handling the unhandled JDBCType in Vertx JDBCColumnDescriptor[columnName=(f_polygon), jdbcTypeWrapper=(JDBCTypeWrapper[vendorTypeNumber=(1111), vendorTypeName=(polygon), vendorTypeClass=(class org.postgresql.geometric.PGpolygon), jdbcType=(OTHER)])] 09:19:36.393 [vert.x-worker-thread-1] DEBUG io.vertx.ext.jdbc.spi.JDBCDecoder - Fallback to string when handling the unhandled JDBCType in Vertx JDBCColumnDescriptor[columnName=(f_circle), jdbcTypeWrapper=(JDBCTypeWrapper[vendorTypeNumber=(1111), vendorTypeName=(circle), vendorTypeClass=(class org.postgresql.geometric.PGcircle), jdbcType=(OTHER)])] ```

[zero88]

To resolve this ticket, I think we need to revert decodeUnhandledType @pmlopes to support UUID natively, I think we can add a check if the value is an instance of java lang type or UUID, which should skip toString. However, AFAIK, MySQL treats UUID as JDBCType.BINARY, so this solution does not guard every kind of database.

[vietj]

can we revert the commit partially ?

[colinchilds]

@zero88 Any update on this?

[zero88]

I've recently reviewed PR#273 to understand the changes reasons.

After digging in deep, there are 3 issues here:

• For the function/procedure on PostgreSQL, the ParameterMetaData of PgCallableStatement does not return correct information as other JDBC driver (e.g: MSSQL), but ResultSetMetadata is correct. I think it works as a design since PostgreSQL can use select * function() syntax.

  A reference log PgCallableStatement with animal_stats

  ``` ResultSet metadata columns: 2 ResultSet [1]: java.lang.Long---5--int8--count ResultSet [2]: java.lang.Float--7--float4--perc -------------------------------------------- Parameter metadata count : 3 Parameter [1]: java.lang.Boolean---7--bool--in Parameter [2]: java.lang.String--1111--void--in Parameter [3]: java.lang.String--1111--void--in ======================================= ```

  So, I suggest JDBC client/pool keep the callable outputs, to look up later, rather than depend on ParameterMetaData of CallableStatement.

• the old JDBCDecoder (<v4.3.0) is correct in most cases. I will revert PR#273 partially and combine it with PR-267

• support for UUID: Is it reasonable to keep/cast an unhandled type in case of the runtime value is a native java type such as java.util.UUID, java.util.Date, java.sql.Time, etc... but JDBCType = OTHER?

Any inputs @vietj @pmlopes ?

I will try to provide a fix this weekend after receiving decision from Vertx team @colinchilds

[colinchilds]

Thank you for the update!

[colinchilds]

Any chance we could get this into the next vertx release? I was really hoping to get all my services onto vertx 4 by the end of the year if possible.

[colinchilds]

Sorry for bumping this yet again. I don't mean to be annoying, I just am really anxious for a fix. @zero88 did you get a decision from the vertx team?

[zero88]

@colinchilds I created a PR. @vietj @pmlopes could you pls have a look? Thanks

[colinchilds]

@vietj and @pmlopes just bumping this again. Thanks!

[colinchilds]

Just FYI, I pulled the PR branch and tested my use cases locally and they worked. It seems to have fixed the issue for me. Would love to get this merged and put in the next version! Can I bribe you guys with Amazon gift cards or something? Is that allowed? I'm getting kinda desperate here.

[vietj]

@colinchilds we'll going to review it

[colinchilds]

Thank you so much!

[colinchilds]

Hey, just wanted to say thanks again for everyone who helped out with this. Do we have any idea when it could potentially make into a release?

[vietj]

we will release this week

On Wed, Nov 16, 2022 at 9:31 PM colinchilds @.***> wrote:

Hey, just wanted to say thanks again for everyone who helped out with this. Do we have any idea when it could potentially make into a release?

— Reply to this email directly, view it on GitHub https://github.com/vert-x3/vertx-jdbc-client/issues/291#issuecomment-1317631733, or unsubscribe https://github.com/notifications/unsubscribe-auth/AABXDCXB2LAP5G5KDKC6ORLWIVADTANCNFSM6AAAAAAQDS3BRA . You are receiving this because you were mentioned.Message ID: @.***>

[colinchilds]

Excellent, that's great news. Thank you