# US 12,007,990 B1 · Deferred constraints support in distributed database systems

> Agents: this data is available as JSON from the patentgrep API and MCP server — see https://patentgrep.com/llms.txt

- Number: US 12,007,990 B1
- Document ID: US12007990B1
- Status: Granted
- Kind: B1
- Application number: 18/447,934
- Filed: 2023-08-10
- Granted: 2024-06-11
- Earliest claimed: 2023-08-10
- Assignees: Snowflake Inc.
- Applicants: Snowflake Inc.
- Inventors: Nikolaos Romanos Katsipoulakis; Dimitrios Tsirogiannis; William Waddington; Zhaohui Zhang
- Web page: https://patentgrep.com/patent/US12007990B1

## CPC

- G06F 16/24537: {of operators}
- G06F 16/24542: {Plan optimisation}
- G06F 16/27: Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
- G06F: ELECTRIC DIGITAL DATA PROCESSING (computer systems based on specific computational models G06N)

## Abstract

The subject technology receives a query, the query including a set of statements, the set of statements including a first statement comprising a first data manipulation language operation. The subject technology determines 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. The subject technology generates a key value constraint check operator as a node in a query plan. The subject technology generates a key value DML operator as a second node in the query plan, the key value DML operator corresponding to the first DML operation. The subject technology performs, 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.

## Claims

### Claim 1

A system comprising:
at least one hardware processor; and
a memory storing instructions that cause the at least one hardware processor 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.

### Claim 2 (depends on 1)

The system of claim 1, wherein the operations further comprise:
determining that at least one key that violated the first constraint still violates the first constraint after each DML operation from the set of statements has completed execution.

### Claim 3 (depends on 2)

The system of claim 2, wherein the operations further comprise:
in response to the determining, terminating execution of the query.

### Claim 4 (depends on 1)

The system of claim 1, wherein the operations further comprise:
determining that each key from the set of violating keys no longer violates the first constraint after each DML operation from the set of statements has completed execution.

### Claim 5 (depends on 4)

The system of claim 4, wherein the operations further comprise:
in response to the determining, finalizing execution of the query; and
returning a set of results from finalizing execution of the query.

### Claim 6 (depends on 1)

The system of claim 1, wherein the set of violating keys comprises at least one key that violated the first constraint.

### Claim 7 (depends on 1)

The system of claim 1, wherein the second statement indicating that the first constraint is statement deferrable comprises a set constraint statement.

### Claim 8 (depends on 7)

The system of claim 7, wherein the set constraint statement is executed at a beginning of a transaction to define the first constraint.

### Claim 9 (depends on 1)

The system of claim 1, wherein each DML operation from the set of statements comprises an insert operation, update operation, delete operation, or merge operation.

### Claim 10

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.

### Claim 11 (depends on 10)

The method of claim 10, further comprising:
determining that at least one key that violated the first constraint still violates the first constraint after each DML operation from the set of statements has completed execution.

### Claim 12 (depends on 11)

The method of claim 11, further comprising:
in response to the determining, terminating execution of the query.

### Claim 13 (depends on 10)

The method of claim 10, further comprising:
determining that each key from the set of violating keys no longer violates the first constraint after each DML operation from the set of statements has completed execution.

### Claim 14 (depends on 13)

The method of claim 13, further comprising:
in response to the determining, finalizing execution of the query; and
returning a set of results from finalizing execution of the query.

### Claim 15 (depends on 10)

The method of claim 10, wherein the set of violating keys comprises at least one key that violated the first constraint.

### Claim 16 (depends on 10)

The method of claim 10, wherein the second statement indicating that the first constraint is statement deferrable comprises a set constraint statement.

### Claim 17 (depends on 16)

The method of claim 16, wherein the set constraint statement is executed at a beginning of a transaction to define the first constraint.

### Claim 18 (depends on 10)

The method of claim 10, wherein each DML operation from the set of statements comprises an insert operation, update operation, delete operation, or merge operation.

### Claim 19

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.

### Claim 20 (depends on 19)

The non-transitory computer-storage medium of claim 19, wherein the operations further comprise:
determining that at least one key that violated the first constraint still violates the first constraint after each DML operation from the set of statements has completed execution.

### Claim 21 (depends on 20)

The non-transitory computer-storage medium of claim 20, wherein the operations further comprise:
in response to the determining, terminating execution of the query.

### Claim 22 (depends on 19)

The non-transitory computer-storage medium of claim 19, wherein the operations further comprise:
determining that each key from the set of violating keys no longer violates the first constraint after each DML operation from the set of statements has completed execution.

### Claim 23 (depends on 22)

The non-transitory computer-storage medium of claim 22, wherein the operations further comprise:
in response to the determining, finalizing execution of the query; and
returning a set of results from finalizing execution of the query.

### Claim 24 (depends on 19)

The non-transitory computer-storage medium of claim 19, wherein the set of violating keys comprises at least one key that violated the first constraint.

### Claim 25 (depends on 19)

The non-transitory computer-storage medium of claim 19, wherein the second statement indicating that the first constraint is statement deferrable comprises a set constraint statement.

### Claim 26 (depends on 25)

The non-transitory computer-storage medium of claim 25, wherein the set constraint statement is executed at a beginning of a transaction to define the first constraint.

### Claim 27 (depends on 19)

The non-transitory computer-storage medium of claim 19, wherein each DML operation from the set of statements comprises an insert operation, update operation, delete operation, or merge operation.