I'm troubleshooting why some update and delete events are lost in my postgres-cdc setup. I was wondering if, similar to PostgreSQL's requirement for refresh materialize view concurrently
, RisingWave also requires unique indexes or primary keys on materialized views to handle live updates correctly.
Rick Otten
Asked on Dec 05, 2023
Materialized Views (MVs) in RisingWave don't require explicit primary key definitions as the system derives the primary key for each internal storage table, including MV tables, automatically. However, if you're using a Postgres CDC source, you need to define primary keys on the source tables to handle updates and deletes correctly. Additionally, ensure that the replica identity of your Postgres tables is set to FULL to emit update and delete events for all columns, not just primary key columns. If you're experiencing issues with updates and deletes not being propagated, it might be worth checking the publication in Postgres to ensure it covers the tables in question. Also, inspect the compute node's logs for any warnings or errors that might indicate problems with the replication process.