Enhancement: Row-level dependency resolution for complex circular FK relationships in DUMP

[apstndb]

Summary

Implement row-level dependency resolution for handling complex circular foreign key relationships in DUMP operations.

Current Limitation

The current dependency resolver (#426) works at the table level, which means it cannot handle circular foreign key dependencies between tables. When such cycles are detected, the export fails with an error.

Proposed Enhancement

Implement a more sophisticated dependency resolution that:

1. Analyzes dependencies at the row level, not just table level
2. Groups interdependent rows that need to be inserted together
3. Wraps these row groups in transactions to satisfy foreign key constraints

Example Scenarios

Scenario 1: Circular FK between tables

-- Current: Would fail with circular dependency error
-- Proposed: Would group related rows in a transaction
BEGIN;
INSERT INTO Department (Id, Name, ManagerId) VALUES (1, 'Engineering', 101);
INSERT INTO Employee (Id, Name, DeptId) VALUES (101, 'Alice', 1);
COMMIT;

Scenario 2: Complex multi-table dependencies

-- Tables with complex interdependencies
-- Proposed: Analyze actual data and create minimal transaction groups
BEGIN;
-- Group 1: Independent rows
INSERT INTO Categories (Id, Name) VALUES (1, 'Electronics');
INSERT INTO Suppliers (Id, Name) VALUES (1, 'SupplierA');
COMMIT;

BEGIN;
-- Group 2: Interdependent rows requiring same transaction
INSERT INTO Products (Id, CategoryId, PreferredSupplierId) VALUES (1, 1, 1);
INSERT INTO SupplierProducts (SupplierId, ProductId, IsPrimary) VALUES (1, 1, true);
COMMIT;

Technical Approach

1. Dependency Graph Construction:

   • Query actual data values, not just schema
   • Build directed graph of row-level dependencies
   • Identify strongly connected components (SCCs)

2. Row Grouping Algorithm:

   • Find minimal sets of rows that must be in same transaction
   • Use Tarjan's algorithm for SCC detection
   • Optimize for minimal transaction size

3. Export Generation:

   • Generate BEGIN/COMMIT blocks for each row group
   • Maintain order within groups for INTERLEAVE relationships
   • Handle NULL foreign keys specially (can be inserted independently)

Benefits

• Enables export of databases with circular FK dependencies
• More accurate representation of data relationships
• Smaller transaction sizes where possible
• Better compatibility with complex real-world schemas

Challenges

• Performance impact of analyzing actual data values
• Memory usage for large tables
• Complexity of implementation
• Need to handle NULL values and optional FKs correctly

Priority

Medium-High - This would significantly improve the robustness of DUMP operations for complex schemas, though the current table-level solution handles most common cases.

Related Issues

• Implement table dependency resolver for INTERLEAVE and FK relationships #426: Current table-level dependency resolver
• Implement DUMP statements for database export with INTERLEAVE support #425: DUMP statement implementation