Query processing using hybrid table secondary indexes
The subject technology determines whether a key exists in a parent table associated with a first transaction. The subject technology performs a first write operation on a child table. The subject technology determines whether a duplicate key exists in the child table based on the key of the first write operation. The subject technology determines whether there is a conflict with the key. The subject technology, in response to determining that there is no conflict with the key, performs a second write operation on a secondary index table of the child table. The subject technology determines whether a particular duplicate key exists in the secondary index table based on a particular key from the second write operation. The subject technology, in response to determining that there is the particular duplicate key in the secondary index table, throws a uniqueness exception.
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:
determining whether a key exists in a parent table associated with a first transaction;
in response to the key existing in the parent table, performing a first write operation on a child table;
determining whether a duplicate key exists in the child table based on the key of the first write operation;
in response to determining that there is no duplicate key in the child table, determining whether there is a conflict with the key;
in response to determining that there is no conflict with the key, performing a second write operation on a secondary index table of the child table;
determining whether a particular duplicate key exists in the secondary index table based on a particular key from the second write operation; and
in response to determining that there is the particular duplicate key in the secondary index table, throwing a uniqueness exception.
2 . The system of claim 1 , wherein the operations further comprise:
aborting, in response to throwing the uniqueness exception, execution of the second write operation on the secondary index table of the child table.
3 . The system of claim 1 , wherein the operations further comprise:
in response to determining that there is the conflict with the particular key, restarting a particular statement associated with the second write operation on the secondary index table of the child table.
4 . The system of claim 3 , wherein the operations further comprise:
in response to determining that there is a set of live in-flight write operations and the set of write operations committed after a read timestamp and before a write timestamp, restarting a particular statement associated with the second write operation on the secondary index table of the child table.
5 . The system of claim 1 , wherein the first transaction includes a first statement to perform a particular write operation on the parent table, and a second statement to perform the first write operation on the child table.
6 . The system of claim 1 , wherein the operations further comprise:
in response to the key not existing in the parent table, throwing a foreign key constraint exception.
7 . The system of claim 1 , wherein the operations further comprise:
in response to determining that there is the duplicate key in the child table, throwing a uniqueness exception.
8 . The system of claim 1 , wherein the operations further comprise:
in response to determining that there is the conflict with the key, restarting a particular statement associated with the first write operation on the child table.
9 . The system of claim 1 , wherein the operations further comprise:
in response to determining that there is no duplicate key in the secondary index table, determining whether there is a conflict with the particular key; and
in response to determining that there is no conflict with the particular key, performing a second read operation on the parent table of the child table to determine whether this is a set of live in-flight write operations and a set of write operations committed after a read timestamp and before a write timestamp.
10 . The system of claim 9 , wherein the operations further comprise:
in response to determining that there is not the set of live in-flight write operations nor the set of write operations committed after the read timestamp and before the write timestamp, determining whether there is a recent committed or in-flight delete operation of the parent table;
in response to determining that there is not the recent committed or in-flight delete operation of the parent table, performing a finalize operation of a particular statement associated with the first write operation on the child table; and
performing a commit operation of the first transaction.
11 . A method comprising:
determining whether a key exists in a parent table associated with a first transaction;
in response to the key existing in the parent table, performing a first write operation on a child table;
determining whether a duplicate key exists in the child table based on the key of the first write operation;
in response to determining that there is no duplicate key in the child table, determining whether there is a conflict with the key;
in response to determining that there is no conflict with the key, performing a second write operation on a secondary index table of the child table;
determining whether a particular duplicate key exists in the secondary index table based on a particular key from the second write operation; and
in response to determining that there is the particular duplicate key in the secondary index table, throwing a uniqueness exception.
12 . The method of claim 11 , further comprising:
aborting, in response to throwing the uniqueness exception, execution of the second write operation on the secondary index table of the child table.
13 . The method of claim 11 , further comprising:
in response to determining that there is the conflict with the particular key, restarting a particular statement associated with the second write operation on the secondary index table of the child table.
14 . The method of claim 13 , further comprising:
in response to determining that there is a set of live in-flight write operations and the set of write operations committed after a read timestamp and before a write timestamp, restarting a particular statement associated with the second write operation on the secondary index table of the child table.
15 . The method of claim 11 , wherein the first transaction includes a first statement to perform a particular write operation on the parent table, and a second statement to perform the first write operation on the child table.
16 . The method of claim 11 , further comprising:
in response to the key not existing in the parent table, throwing a foreign key constraint exception.
17 . The method of claim 11 , further comprising:
in response to determining that there is the duplicate key in the child table, throwing a uniqueness exception.
18 . The method of claim 11 , further comprising:
in response to determining that there is the conflict with the key, restarting a particular statement associated with the first write operation on the child table.
19 . The method of claim 11 , further comprising:
in response to determining that there is no duplicate key in the secondary index table, determining whether there is a conflict with the particular key; and
in response to determining that there is no conflict with the particular key, performing a second read operation on the parent table of the child table to determine whether this is a set of live in-flight write operations and a set of write operations committed after a read timestamp and before a write timestamp.
20 . 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:
determining whether a key exists in a parent table associated with a first transaction;
in response to the key existing in the parent table, performing a first write operation on a child table;
determining whether a duplicate key exists in the child table based on the key of the first write operation;
in response to determining that there is no duplicate key in the child table, determining whether there is a conflict with the key;
in response to determining that there is no conflict with the key, performing a second write operation on a secondary index table of the child table;
determining whether a particular duplicate key exists in the secondary index table based on a particular key from the second write operation; and
in response to determining that there is the particular duplicate key in the secondary index table, throwing a uniqueness exception.