Deferred constraints support in distributed database systems

A method comprising:

• receiving a query, the query including a set of statements, the set of statements including a first statement comprising a first data manipulation language (DML) operation on a first table;
• determining that the set of statements includes at least one statement defining a first constraint, and a second statement indicating that the first constraint is statement deferrable for performing validation of the first constraint;
• generating a query plan based at least in part on the query, the generating comprising:
• generating a key value constraint check operator as a node in the query plan;
• generating a key value DML operator as a second node in the query plan, the key value DML operator corresponding to the first DML operation;
• generating a buffer operator as a particular node in the query plan, the particular node being a child node of the node corresponding to the key value constraint check operator, the buffer operation ensuring that the key value constraint check operator is performed after each DML operation from the set of statements has already been performed;
• and
• executing the generated query plan, the executing comprising:
• performing, using the key value constraint check operator, a validation process of the first constraint based at least in part on a set of violating keys of each DML operation from the set of statements.

A non-transitory computer-storage medium comprising instructions that, when executed by one or more processors of a machine, configure the machine to perform operations comprising:

• receiving a query, the query including a set of statements, the set of statements including a first statement comprising a first data manipulation language (DML) operation on a first table;
• determining that the set of statements includes at least one statement defining a first constraint, and a second statement indicating that the first constraint is statement deferrable for performing validation of the first constraint;
• generating a query plan based at least in part on the query, the generating comprising:
• generating a key value constraint check operator as a node in the query plan;
• generating a key value DML operator as a second node in the query plan, the key value DML operator corresponding to the first DML operation;
• generating a buffer operator as a particular node in the query plan, the particular node being a child node of the node corresponding to the key value constraint check operator, the buffer operation ensuring that the key value constraint check operator is performed after each DML operation from the set of statements has already been performed;
• and
• executing the generated query plan, the executing comprising:
• performing, using the key value constraint check operator, a validation process of the first constraint based at least in part on a set of violating keys of each DML operation from the set of statements.