quarkus debezium outbox - postgres payload JSONB column

[Wilson Arockiasamy]

Hello,

Any one experiencing following issue, while persistence of Outbox table payload column for JSONB/JSON ?

1) I have  class,  public class OutboxEvent implements ExportedEvent<String, SampleInfo> 

even.fire(OutboxEvent );

2) I see quarkus outbox-extension log

2021-06-10 09:44:42 DEBUG [io.de.ou.qu.de.OutboxProcessor] (build-18) Outbox entity HBM mapping:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<hibernate-mapping xmlns="http://www.hibernate.org/xsd/orm/hbm">

    <identifier-generator class="uuid2" name="uuid2"/>

    <class table="outbox" entity-name="io.debezium.outbox.quarkus.internal.OutboxEvent">

        <id name="id" type="java.util.UUID">

            <column name="id"/>

            <generator class="uuid2"/>

        </id>

        <property name="aggregateType" not-null="true" type="string">

            <column name="aggregatetype"/>

        </property>

        <property name="aggregateId" not-null="true" type="java.lang.String">

            <column name="aggregateid"/>

        </property>

        <property name="type" not-null="true" type="string">

            <column name="type"/>

        </property>

        <property name="timestamp" not-null="true" type="Instant">

            <column name="timestamp"/>

        </property>

        <property name="payload" not-null="false" type="com.test.service.dto.SampleInfo">

            <column name="payload" sql-type="JSONB"/>

        </property>

        <property name="tracingspancontext" not-null="false" type="string">

            <column length="256" name="tracingspancontext"/>

        </property>

    </class>

</hibernate-mapping>

3) when Hibernate persist SampleInfo object to payload column of outbox table I see below error. Looks like hibernate is convert SampleInfo object as byte array.

2021-06-10 09:44:50 WARN  [or.hi.en.jd.sp.SqlExceptionHelper] (vert.x-worker-thread-0) SQL Error: 0, SQLState: 42804

2021-06-10 09:44:50 ERROR [or.hi.en.jd.sp.SqlExceptionHelper] (vert.x-worker-thread-0) ERROR: column "payload" is of type jsonb but expression is of type bytea

  Hint: You will need to rewrite or cast the expression.

  Position: 123

[Chris Cranford]

Hi Wilson -

The error you mentioned is indicative that Hibernate is treating the payload as a SerializableType rather than a JsonBinaryType.  Could you please provide your class definition for SampleInfo as well as your settings in the Quarkus application.properties file?

Thanks,
Chris

--
You received this message because you are subscribed to the Google Groups "debezium" group.
To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/62d9fffc-f2af-4f61-82d3-d4d8f5970aafn%40googlegroups.com.

[Wilson Arockiasamy]

Hi Chris,

Thanks for you time. Here is my SampleInfo class and application.properties. As you mentioned debezium outbox is not using com.vladmihalcea.hibernate.type.jsonJsonBinaryType from hibernate-types-52.jar. I created a convertor (JsonNodeConverter -> SampleInfo to com.fasterxml.jackson.databind.JsonNode) didn't work.

@Builder

@Data

@NoArgsConstructor

@AllArgsConstructor

public class SampleInfo 

{

    private Metadata metadata;

    private String id;

    private String type;

    private String name;

    private String data;

}

application.properties

quarkus.datasource.db-kind=postgresql

quarkus.datasource.jdbc.url=jdbc:postgresql://${platform.services.host}:5432/db

quarkus.hibernate-orm.log.sql=false

quarkus.hibernate-orm.dialect=org.hibernate.dialect.PostgreSQLDialect

# Debezium configuration

quarkus.debezium-outbox.table-name=outbox

quarkus.debezium-outbox.payload.column-definition=jsonb

#quarkus.debezium-outbox.payload.converter=com.test..service.converter.JsonNodeConverter

quarkus.debezium-outbox.remove-after-insert=false

Thank you,

Wilson.

[Chris Cranford]

Hi WIlson -

I don't believe we've tested using the outbox event dispatching mechanism with this setup unfortunately. 

That said, as an alterative, you could use JsonNode as the payload type in the event and then use ObjectMapper to convert your SampleInfo into a JsonNode.  In this case, you wouldn't use JSONB as the data-type for the column but instead let it use the default which is VARCHAR.  This would work nicely because then the emitted event on the Kafka side would be a nested JSON string which can easily be consumed downstream as well.

Would that work for you?

Chris

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/b97ede5c-faff-4e65-bab8-caf3aff77d16n%40googlegroups.com.

[Wilson Arockiasamy]

Hi Chris,

Thanks for the details and your time. Our original implementation was JsonNode then the default converter (JsonNodeAttributeConverter ) convert to 

Json string stored in payload varchar column. We are using JSONB for other tables which uses entity manager persistence(with hibernate-types-52)and it works fine.

We thought using same JSONB for outbox table as well. I am not sure how to convert SampleInfo object to  com.vladmihalcea.hibernate.type.json.JsonBinaryType.

Please let me know If you have an idea of the conversion. so that I can try in below property.

quarkus.debezium-outbox.payload.converter=com.test.TestJsonBinaryTypeConverter 

Thank you!

Wilson.

[Gunnar Morling]

> you wouldn't use JSONB as the data-type for the column but instead let it use the default which is VARCHAR.

Why would using JsonNode imply VARCHAR as a column type? Can't one use JsonNode and still use JSONB? It's much nicer in terms of querying etc.

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/f1dc9ae5-04ab-20fa-8b64-f9c48d688a21%40gmail.com.