PG composite type sync failed

[cyliu0]

Describe the bug

In PG

mydb=# create type complex as (a int, b date);
CREATE TYPE
mydb=# create table t1 (a int primary key, c complex);
CREATE TABLE
mydb=# insert into t1 values (1, ROW(1, '2023-10-30'));
INSERT 0 1
mydb=# select * from t1;
 a |       c        
---+----------------
 1 | (1,2023-10-30)
(1 row)

In RW

dev=> create table t1 (a int primary key, c struct <a int, b date>) with (
connector = 'postgres-cdc',
hostname = 'postgres',
port = '5432',
username = 'myuser',
password = '123456',
database.name = 'mydb',
schema.name = 'public',
table.name = 't1',
slot.name = 't1'
);
CREATE_TABLE
dev=> select * from t1;
 a | c 
---+---
 1 | 
(1 row)

Error message/log

No response

To Reproduce

No response

Expected behavior

No response

How did you deploy RisingWave?

No response

The version of RisingWave

nightly-20231030

Additional context

No response

[fuyufjh]

Our struct is exactly corresponding to PG's composite type. I think we are supposed to support this. @StrikeW Can you pls take a look?

[StrikeW]

It seems debezium doesn't support the composite type in PG natively, we need to write custom data converter to hook into the debezium framework. ps. 2002 is STRUCT in jdbc type. related: https://issues.redhat.com/browse/DBZ-994

2023-10-31 15:41:59,551 WARN [debezium-postgresconnector-RW_CDC_1005-change-event-source-coordinator] relational.TableSchemaBuilder:433 - Unexpected JDBC type '2002' for column 'c' that will be ignored 2023-10-31 15:41:59,551 WARN [debezium-postgresconnector-RW_CDC_1005-change-event-source-coordinator] relational.TableSchemaBuilder:358 - No converter found for column public.t1.c of type complex. The column will not be part of change events for that table.

[fuyufjh]

So, do we have any conclusion on how to solve this? 🥹

[lmatz]

how about we put this limitation to the documentation first? (If it will take a while or is not high-priority)

[StrikeW]

So, do we have any conclusion on how to solve this? 🥹

It seems a bit complicated than I expect and need some investigation according to https://issues.redhat.com/browse/DBZ-994, I wonder what's the priority of this requirement. @fuyufjh

[lmatz]

I wonder what's the priority of this requirement

I am not aware of any user/POC who has ever raised this issue, but cc: @neverchanje if there are any If no urgent need, I suggest documentation first

[hengm3467]

Trying to summarize the issue here: When ingesting CDC data from PG, composite data types in PG cannot be parsed correctly in RisingWave. The cause of the issue is that Debezium doesn't support the composite data type in PG, and RisingWave uses Debezium to ingest PG CDC data. @cyliu0 @StrikeW Is the summary accurate? Thanks.

[StrikeW]

@hengm3467 Exactly.

[github-actions[bot]]

This issue has been open for 60 days with no activity.

If you think it is still relevant today, and needs to be done in the near future, you can comment to update the status, or just manually remove the no-issue-activity label.

You can also confidently close this issue as not planned to keep our backlog clean. Don't worry if you think the issue is still valuable to continue in the future. It's searchable and can be reopened when it's time. 😄