Add R2DBC support for HSTORE

[quantranhong1999]

Expected behavior

When I record.get a Hstore column, I expect the runtime return type is the same as compilation time's return type - which is Hstore.

Actual behavior

But the return type at runtime is java.util.LinkedHashMap.

Steps to reproduce the problem

    @Test
    void getHstoreTest() {
        // Setup DSLContext
        Settings jooqSettings = new Settings()
            .withRenderFormatted(true)
            .withStatementType(StatementType.PREPARED_STATEMENT);
        Connection r2dbcConnection = postgresExtension.getConnection().block();
        DSLContext dslContext = DSL.using(r2dbcConnection, SQLDialect.POSTGRES, jooqSettings);

        // Create extension Hstore
        postgresExecutor.connection()
            .flatMapMany(connection -> connection.createStatement("CREATE EXTENSION IF NOT EXISTS hstore")
                .execute())
            .flatMap(Result::getRowsUpdated)
            .then()
            .block();

        // Create a table with a Hstore column
        Table<Record> table = DSL.table("person");
        DataType<Hstore> hstoreDataType = DefaultDataType.getDefaultDataType("hstore").asConvertedDataType(new HstoreBinding());
        Field<Integer> idColumn = DSL.field("id", SQLDataType.INTEGER.identity(true));
        Field<Hstore> hstoreColumn = DSL.field("hstore_column", hstoreDataType.notNull());

        Mono.from(dslContext.createTableIfNotExists(table)
                .column(idColumn)
                .column(hstoreColumn)
                .constraints(DSL.constraint().primaryKey(idColumn)))
            .block();

        // Insert data into the table
        Mono.from(dslContext.insertInto(table, idColumn, hstoreColumn)
                .values(1, Hstore.hstore(Map.of("key", "value"))))
            .then()
            .block();

        // Get the Hstore. Test would fail here at runtime
        Hstore hstore = Mono.from(dslContext.select()
            .from(table)
            .limit(1))
            .map(record -> record.get(hstoreColumn))
            .block();
    }

The build has no problem. But at runtime the test fails with the error:

java.lang.ClassCastException: class java.util.LinkedHashMap cannot be cast to class org.jooq.postgres.extensions.types.Hstore (java.util.LinkedHashMap is in module java.base of loader 'bootstrap'; org.jooq.postgres.extensions.types.Hstore is in unnamed module of loader 'app')

jOOQ Version

jOOQ 3.16.23 and jooq-postgres-extensions 3.16.23

Database product and version

PostgreSQL 16.1

Java Version

openjdk 11

OS Version

Ubuntu 22.04

JDBC driver name and version (include name if unofficial driver)

r2dbc-postgresql 1.0.3.RELEASE

[lukaseder]

Thanks for your message. That's just what the r2dbc driver does out of the box:

// No jOOQ involved:
List<Object> o;
System.out.println(
    o = Flux.from(cf.create())
        .flatMap(c -> c.createStatement("select '\"a\"=>\"b\"'::hstore").execute())
        .flatMap(it -> it.map((r, m) -> r.get(0)))
        .collectList()
        .block()
    );

System.out.println(o.get(0).getClass());

This prints:

[{a=b}]
class java.util.LinkedHashMap

You're not actually using jOOQ's binding in your plain SQL query:

        Hstore hstore = Mono.from(dslContext.select()
            .from(table)
            .limit(1))
            .map(record -> record.get(hstoreColumn))
            .block();

Think about it. How would jOOQ know your intention of binding Hstore to this query? You're not supplying any such information to jOOQ. Now, I'm not convinced that the HstoreBinding will work with R2DBC. From what I can tell, the pgjdbc driver works with a String encoding for the value, whereas the r2dbc driver already converted the value for you. So, you'll have to roll your own.

I keep this issue open to support Hstore also with R2DBC in the future (but you'll still have to write a correct query that provides jOOQ with the binding!)

[quantranhong1999]

How would jOOQ know your intention of binding Hstore to this query? You're not supplying any such information to jOOQ.

It is true that if I put specifically hstoreColumn in the .select, then it would return just fine the Hstore:

        Hstore hstore = Mono.from(dslContext.select(hstoreColumn)
            .from(table)
            .limit(1))
            .map(record -> record.get(hstoreColumn))
            .block();

I expected to not need to cast hstore during the SELECT as I created a column with the hstore data type already (so I expected it to return the hstore data type directly without any casting).

BTW I am new to hstore / Postgres / jOOQ so I likely mis-used the query.

Thank you for your clarification!