Rowset Insert Error Handling with Undo Operations
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems face inefficiencies when inserting large numbers of rows into tables with dependent objects, as errors in constraint violations or expression evaluations often lead to the entire insert operation being aborted, requiring multiple attempts to successfully insert all rows.
Innovation Solution
A method for executing a rowset insert operation that treats constraint violations and expression evaluation errors as non-fatal, allowing the execution to proceed while maintaining database consistency through the use of a query plan that includes undo operations and blocking operators to manage errors and updates in dependent tables and materialized views.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If the insert operation proceeds asynchronously into all dependent objects, then the insert operation can continue despite errors in some rows, but the database consistency may be compromised if errors are not properly handled
Solution Approach 1:
The system performs preliminary validation of rows before inserting them into dependent objects. The query plan identifies and validates rows upfront, separating valid rows from error rows before the async insert process begins, preventing consistency issues while maintaining throughput
Solution Approach 2:
The patent introduces an intermediary validation mechanism that acts as a buffer between the base table insert and dependent object updates. This intermediary layer checks rows for constraint violations and expression errors, allowing the system to proceed with valid rows while isolating problematic ones, thus maintaining both productivity and consistency
2Reliability
If any error is raised due to constraint violation or expression evaluation, then the whole insert operation is aborted to maintain consistency, but the number of attempts needed increases significantly
Solution Approach 1:
The insert operation is segmented into independent row-level transactions. Instead of treating the entire rowset as a single atomic operation, the system processes rows individually or in smaller batches, allowing successful rows to be committed even when some rows encounter errors, thereby reducing the need for multiple full retries
Solution Approach 2:
The patent converts error rows from operation-terminating events into isolated incidents. By identifying and handling error rows separately, the system transforms what would be fatal failures into manageable exceptions, allowing the bulk of valid rows to be inserted successfully without requiring complete operation retries
3Reliability
If error rows are removed and the insert operation is retried, then consistency is maintained, but the total number of attempts needed increases from N+1 to potentially many more attempts
Solution Approach 1:
The system performs preliminary identification and validation of error rows before the insert operation begins. By pre-processing the rowset to separate valid and invalid rows, the system eliminates the need for repeated retry cycles, achieving consistency in a single pass while maintaining high productivity
Solution Approach 2:
The patent implements a feedback mechanism where the results of validation checks are used to immediately adjust the insert strategy. Error rows are identified and excluded based on feedback from constraint and expression validation, preventing wasted retry attempts and improving overall insert efficiency
Data Source
AI summary
There is provided a computer-executed method of executing a rowset insert against a database. The method comprises receiving a structured query language statement (SQL) specifying the rowset insert. Additionally, the method comprises raising an error for a first row of the rowset insert. The method also comprises updating a table for a second row of the rowset insert. Further, the method comprises updating a materialized view of the table based on the second row. Additionally, the method comprises performing a commit to the database. The table comprises the second row, and the updated materialized view is consistent with the table.