I have two tables let's call it tbl_parent and tbl_child in Microsoft SQL Server, and trying to transfer the data of these tables to AWS aurora PostgreSQL.
Here tbl_child has a foreign key reference on tbl_parent, so to avoid FK issues during the full load I have mentioned higher load-order for tbl_parent than table tbl_child. The same was suggested in aws documentation.
So with that load-order setting, I was expecting that the data would only be inserted into tbl_child after the complete insertion is done in tbl_parent .
Also I want to do a parallel load into child table, so I have specified table-settings rule to perform parallel load as suggested in aws documentation
For some reason I see the data into tbl_child is getting inserted even before tbl_parent load is complete, and I'm getting Foreign key issues like below ...
2021-07-14T18:53:43 [TARGET_LOAD ]W: Load command output: psql: /usr/lib64/libcom_err.so.2: no version information available (required by /rdsdbbin/awsdms/lib/libgssapi_krb5.so.2) psql: /usr/lib64/libcom_err.so.2: no version information available (required by /rdsdbbin/awsdms/lib/libkrb5.so.3) ERROR: insert or update on table "tbl_child" violates foreign key constraint "tbl_child_tbl_parent_fkey" DETAIL: Key (parent_id)=(1468137) is not present in table "tbl_parent". (csv_target.c:1018)
In case if it helps please find the mapping rules json below
{
"rules": [
{
"rule-type": "selection",
"rule-id": "101",
"rule-name": "101",
"object-locator": {
"schema-name": "dbo",
"table-name": "tbl_parent"
},
"rule-action": "include",
"filters": [],
"load-order": 2
},
{
"rule-type": "selection",
"rule-id": "102",
"rule-name": "102",
"object-locator": {
"schema-name": "dbo",
"table-name": "tbl_child"
},
"rule-action": "include",
"filters": [],
"load-order": 1
},
{
"rule-type": "table-settings",
"rule-id": "131",
"rule-name": "Parallel_Range_Child",
"object-locator": {
"schema-name": "dbo",
"table-name": "tbl_child"
},
"parallel-load": {
"type": "ranges",
"columns": [
"child_id"
],
"boundaries": [
[
"100"
],
[
"200"
],
[
"300"
]
]
}
}
]
}