Is ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS essentail for cdc update

[qihuagaosh]

I have test only with the following config:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE) COLUMNS;

It can not recognize update operation, and insert the record anyway.
I think with primary key, the code should be able to update data. Am I wrong?

My topic config:
kafka-topics.sh --bootstrap-server automq-release-kafka.automq.svc.cluster.local:9092
--command-config /tmp/client.properties
--create --topic ORACLE_TEST.HR.EMPLOYEES
--config automq.table.topic.enable=true
--config automq.table.topic.namespace=automq
--config automq.table.topic.commit.interval.ms=60000
--config automq.table.topic.cdc.field=_cdc.op
--config automq.table.topic.upsert.enable=true
--config automq.table.topic.transform.value.type=flatten_debezium
--config automq.table.topic.convert.value.type=by_schema_id
--config automq.table.topic.id.columns=[EMPLOYEE_ID]
--config automq.table.topic.partition.by='[bucket(FIRST_NAME, 3), month(HIRE_DATE)]'
--partitions 3
--replication-factor 3

Thank you in advance for the help.

[Gezi-lzq]

Hello @qihuagaosh , from the configuration items I don't think I can find any issues. Taking the example at https://github.com/AutoMQ/automq-labs/tree/main/table-topic-solutions/playground/cdc-scenario, I would like to know whether this scenario has been used for testing. How does your current testing scenario differ from this one?

[qihuagaosh]

Thank you for the quick answer. its almost the same, and cdc works.

I am only wondering why "ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS" is need for data updating.

[Gezi-lzq]

To enable Debezium to capture the before state of changed database rows, you must also enable supplemental logging for captured tables or for the entire database. The following example illustrates how to configure supplemental logging for all columns in a single inventory.customers table.

ALTER TABLE inventory.customers ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

https://debezium.io/documentation/reference/stable/connectors/oracle.html

Are you referring to this configuration? @qihuagaosh

[Gezi-lzq]

We might need to confirm that the Debezium CDC pipeline is functioning correctly, specifically whether the CDC events for subsequent updates have op set to Update. @qihuagaosh

[qihuagaosh]

We might need to confirm that the Debezium CDC pipeline is functioning correctly, specifically whether the CDC events for subsequent updates have op set to Update. @qihuagaosh

I have parsed the event, the package includes primary key values + update fields.
I think before state not needed for update.

As redo log is huge, I assume its better if we can improve this.

[Gezi-lzq]

Is it correct to describe it as only performing flatten_debezium and updating based on the specified id.columns, without any action driven by _cdc.op?
For example: if _cdc.op indicates an insert but the id already exists, should the insert be carried out as instructed by _cdc.op, or should an update be performed instead? @qihuagaosh

[aaron-seq]

Hey, I've been looking into this Oracle supplemental logging requirement and here's what I found.

The behavior you're seeing is actually expected based on how the AutoMQ Table Topic handles CDC transformations with Oracle. Let me explain the technical details:

Why ALL COLUMNS logging matters for CDC:

When you configure automq.table.topic.transform.value.type=flatten_debezium, the transformer extracts the row state from Debezium's envelope structure. Here's the key distinction:

1. PRIMARY KEY logging only provides:

   • Primary key values
   • Changed column values (after state)
   • Operation metadata

2. ALL COLUMNS logging provides:

   • Full before state (all column values before change)
   • Full after state (all column values after change)
   • This enables proper change reconstruction

The upsert vs CDC semantic:

Your configuration has both automq.table.topic.upsert.enable=true and automq.table.topic.cdc.field=_cdc.op. These work differently:

• Upsert mode (upsert.enable=true): Uses id.columns to perform insert-or-update operations. It doesn't strictly require the before state because it's doing a key-based merge.

• CDC mode (cdc.field=_cdc.op): Explicitly handles I/U/D operations based on the operation type field. This requires distinguishing between inserts and updates semantically.

The actual issue:

When you see updates being treated as inserts, it's likely because:

1. The flatten_debezium transformer is extracting only the after state without the full before state
2. Without ALL COLUMNS logging, Debezium cannot populate the complete before state in the envelope
3. The downstream Iceberg table write interprets incomplete records as new inserts rather than updates to existing rows

Testing suggestion:

Can you try adding supplemental logging for just the test table with ALL COLUMNS and verify if updates work correctly?

ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

Then perform an update operation and check if the _cdc.op field is correctly set to 'u' and the update is applied (not inserted as a new record).

Optimization path:

If ALL COLUMNS logging is confirmed necessary, you could potentially optimize by:

• Logging ALL COLUMNS only for frequently updated tables
• Using table-level supplemental logging instead of database-level
• Monitoring redo log growth and tuning retention policies

The documentation at the CDC scenario example uses ALL COLUMNS logging for MySQL, but Oracle's architecture requires explicit supplemental logging configuration. This is a fundamental requirement of Debezium's Oracle connector when capturing full row states.

Let me know if testing with ALL COLUMNS logging resolves the update recognition issue.

[qihuagaosh]

Hey, I've been looking into this Oracle supplemental logging requirement and here's what I found.

The behavior you're seeing is actually expected based on how the AutoMQ Table Topic handles CDC transformations with Oracle. Let me explain the technical details:

Why ALL COLUMNS logging matters for CDC:

When you configure automq.table.topic.transform.value.type=flatten_debezium, the transformer extracts the row state from Debezium's envelope structure. Here's the key distinction:

1. PRIMARY KEY logging only provides:

   • Primary key values
   • Changed column values (after state)
   • Operation metadata

2. ALL COLUMNS logging provides:

   • Full before state (all column values before change)
   • Full after state (all column values after change)
   • This enables proper change reconstruction

The upsert vs CDC semantic:

Your configuration has both automq.table.topic.upsert.enable=true and automq.table.topic.cdc.field=_cdc.op. These work differently:

• Upsert mode (upsert.enable=true): Uses id.columns to perform insert-or-update operations. It doesn't strictly require the before state because it's doing a key-based merge.
• CDC mode (cdc.field=_cdc.op): Explicitly handles I/U/D operations based on the operation type field. This requires distinguishing between inserts and updates semantically.

The actual issue:

When you see updates being treated as inserts, it's likely because:

1. The flatten_debezium transformer is extracting only the after state without the full before state
2. Without ALL COLUMNS logging, Debezium cannot populate the complete before state in the envelope
3. The downstream Iceberg table write interprets incomplete records as new inserts rather than updates to existing rows

Testing suggestion:

Can you try adding supplemental logging for just the test table with ALL COLUMNS and verify if updates work correctly?

ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
Then perform an update operation and check if the _cdc.op field is correctly set to 'u' and the update is applied (not inserted as a new record).

Optimization path:

If ALL COLUMNS logging is confirmed necessary, you could potentially optimize by:

• Logging ALL COLUMNS only for frequently updated tables
• Using table-level supplemental logging instead of database-level
• Monitoring redo log growth and tuning retention policies

The documentation at the CDC scenario example uses ALL COLUMNS logging for MySQL, but Oracle's architecture requires explicit supplemental logging configuration. This is a fundamental requirement of Debezium's Oracle connector when capturing full row states.

Let me know if testing with ALL COLUMNS logging resolves the update recognition issue.

Thank you for the detailed explaination, I tested with "ALL COLUMNS", it works.
I understand the root cause of update issue.

I am only wondering if we can enable updating without "ALL COLUMNS".