Error during migrate to Cloud SQL for PostgreSQL from Oracle using Data Migration Service (DMS)

[hanspetter]

Hi. We are experiencing some error messages during CDC (change data) processing. The error seems to be related to a non-nullable CLOB column.

[DATABASE] error trying to write processed rows: failed writing row 1 of 1 to db: failed to write rows: failed to write row to veilarbdialog.henvendelse with 8 columns: null value in column "tekst" of relation "henvendelse" violates not-null constraint 23502 Details: Failing row contains (618407, 2024-08-13 14:50:08.446, VEILEDER, Z994201, null, 1148387, null, 0). Line: 1889 Position: 0 Schema: veilarbdialog Table: henvendelse Column: tekst

I have a theory that lob columns are processed in a later transaction relative to the rest of the data, and that this causes intermittent errors during replication, but this behavior is not documented.

The target table seems to end up in a correct state, but the job remains in 'Running with errors' status, and this affects our confidence that the replication is correct, which is of crucial importance in the production environment. Could you shed some light on this? Thanks.

[ms4446 · Accepted solution]

The issue you're encountering with DMS during the migration of CLOB columns from Oracle to PostgreSQL arises from an attempt to insert a NULL value into a non-nullable column. This leads to a constraint violation in the target PostgreSQL table, specifically for the tekst column in the henvendelse table. The error occurs during CDC processing, where DMS is likely handling LOBs in a separate transaction or stage, causing temporary inconsistencies in the target database.

There are a few potential causes for this problem. First, the handling of LOBs in separate transactions can lead to situations where the main row data is inserted first, and the LOB data is processed later. If the LOB processing fails or is delayed, it can temporarily leave NULL values in non-nullable columns, triggering errors. Second, differences in how Oracle and PostgreSQL manage CLOBs and related data types may contribute to issues during migration, especially when the target column has strict constraints. Finally, if data replication occurs in batches, with LOB columns processed separately, timing issues might lead to temporary inconsistencies.

To address this issue, start by ensuring data consistency in your Oracle database before migration, making sure there are no NULL values in non-nullable columns. If this is not feasible, consider data cleansing techniques during the migration. Modifying the migration process is another potential solution. This could involve temporarily adjusting the target schema in PostgreSQL to allow NULL values in the tekst column, with plans to revert this after migration is complete. Another approach is to handle LOB columns separately, migrating them after the rest of the data has been processed. Using data transformation rules in DMS to replace NULL values with default values or empty strings during migration can also help.

Given that the target table eventually reaches a correct state despite the errors, it is important to monitor error logs closely to ensure no data loss or corruption occurs. Engaging with Google Cloud support may provide additional insights into whether this behavior is expected and offer more specific guidance. 

[hanspetter]

Thanks for the rapid reply. This confirms our assumption that the clobs are processed in a separate transaction. We will implement the workaround of setting the column to nullable in postgres during the migration process. However, it would be very nice if the migration job could adjust its status automatically when the rows eventually are processed correctly. We have seen the job automatically update its status on other intermittent error conditions (like when max connections are exhausted in postgres).