Query processing using hybrid table secondary indexes
The subject technology obtains a read timestamp of a first transaction. The subject technology performs a first read operation on a parent table associated with the first transaction to determine a set of committed versions of the parent table. The subject technology determines whether a key exists in the parent table based on the first transaction. The subject technology, in response to the key existing in the parent table, performs a first write operation on a child table. The subject technology determines whether a duplicate key exists in the child table. The subject technology, in response to determining that there is no duplicate key in the child table, 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.
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:
obtaining a read timestamp of a first transaction;
performing a first read operation on a parent table associated with the first transaction to determine a set of committed versions of the parent table;
determining whether a key exists in the parent table based on the 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 a particular key from the second write operation;
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 the read timestamp and before a write timestamp.
2. The system of claim 1 , 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.
3. 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.
4. 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.
5. 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.
6. 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.
7. The system of claim 1 , wherein the operations further comprise:
in response to determining that there is the duplicate key in the secondary index 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 particular key, restarting a particular statement associated with the second write operation on the secondary index table of the child table.
9. The system of claim 2 , wherein the operations further comprise:
in response to determining that there is the set of live in-flight write operations and the set of write operations committed after the read timestamp and before the write timestamp, restarting a particular statement associated with the second write operation on the secondary index table of the child table.
10. A method comprising:
obtaining a read timestamp of a first transaction;
performing a first read operation on a parent table associated with the first transaction to determine a set of committed versions of the parent table;
determining whether a key exists in the parent table based on the 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 a particular key from the second write operation;
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 the read timestamp and before a write timestamp.
11. The method of claim 10 , further comprising:
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.
12. The method of claim 10 , 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.
13. The method of claim 10 , further comprising:
in response to the key not existing in the parent table, throwing a foreign key constraint exception.
14. The method of claim 10 , further comprising:
in response to determining that there is the duplicate key in the child table, throwing a uniqueness exception.
15. The method of claim 10 , 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.
16. The method of claim 10 , further comprising:
in response to determining that there is the duplicate key in the secondary index table, throwing a uniqueness exception.
17. The method of claim 10 , 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.
18. The method of claim 11 , further comprising:
in response to determining that there is the set of live in-flight write operations and the set of write operations committed after the read timestamp and before the write timestamp, restarting a particular statement associated with the second write operation on the secondary index table of the child table.
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:
obtaining a read timestamp of a first transaction;
performing a first read operation on a parent table associated with the first transaction to determine a set of committed versions of the parent table;
determining whether a key exists in the parent table based on the 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 a particular key from the second write operation;
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 the read timestamp and before a write timestamp.
20. The non-transitory computer-storage medium of claim 19 , 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.
21. The non-transitory computer-storage medium of claim 19 , 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.
22. The non-transitory computer-storage medium of claim 19 , wherein the operations further comprise:
in response to the key not existing in the parent table, throwing a foreign key constraint exception.
23. The non-transitory computer-storage medium of claim 19 , wherein the operations further comprise:
in response to determining that there is the duplicate key in the child table, throwing a uniqueness exception.
24. The non-transitory computer-storage medium of claim 19 , 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.
25. The non-transitory computer-storage medium of claim 19 , wherein the operations further comprise:
in response to determining that there is the duplicate key in the secondary index table, throwing a uniqueness exception.
26. The non-transitory computer-storage medium of claim 19 , 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.
27. The non-transitory computer-storage medium of claim 20 , wherein the operations further comprise:
in response to determining that there is the set of live in-flight write operations and the set of write operations committed after the read timestamp and before the write timestamp, restarting a particular statement associated with the second write operation on the secondary index table of the child table.