[BUG] Error when syncing a NULL JSONB column from Postgres to ClickHouse

[aldofunes]

Settings

I have enabled both these settings:

• PEERDB_CLICKHOUSE_ENABLE_JSON: true
• PEERDB_NULLABLE: true

Context

I'm using the docker tag stable-v0.34.2

Here is a sample table DDL

create table meta
(
    id         uuid primary key
    metadata   jsonb,
    created_at timestamp with time zone not null default CURRENT_TIMESTAMP
);

The columns are created correctly in CH:

create table default.publi_meta
(
    id                 UUID,
    created_at         DateTime64(6),
    metadata           Nullable(JSON),
    _peerdb_synced_at  DateTime64(9) default now64(),
    _peerdb_is_deleted Int8,
    _peerdb_version    Int64
)
    engine = ReplacingMergeTree(_peerdb_version)
        PRIMARY KEY id
        ORDER BY id
        SETTINGS index_granularity = 8192;

Problem

However, when the initial sync is triggered, PeerDB seems to attempt to cast the empty string to a JSON because I get these errors in the logs

failed to sync records: QRepSync Error: code: 349, message: Cannot convert NULL value to non-Nullable type: while executing 'FUNCTION CAST(__table1.meta_9 :: 8, 'JSON'_String :: 10) -> CAST(__table1.meta_9, 'JSON'_String) JSON : 7'

I'm thinking that the flow should look at the PEERDB_NULLABLE and the table definition and cast to Nullable(JSON) when appropriate

[aldofunes]

I was browsing the commit history and noticed this one: https://github.com/PeerDB-io/peerdb/commit/52591d5c1d426994ff0bed24bc15cbff533012d0, which looks like it intends to address this issue. Am I missing something?

[jgao54]

@aldofunes thanks for your contribution.

we currently do not support replication JSON/JSONB postgres type to JSON/Nullable(JSON) clickhouse type (it is only supported for MongoDB at the moment, which cannot be Nullable). This is partly because ClickHouse's JSON data type does not support non-object type (for example, top-level string/number/boolean are supported in PG JSON, but not in CH JSON). So we have not yet gotten around to test this fully for Postgres/MySQL.