Dropping an index without blocking locks
Techniques for processing “destructive” database statements are provided. Destructive database statements, when processed, cause metadata of a database object to be changed. Examples of such database statements include ones that delete an index, that set a column as unused, and that drop a constraint on a column. When such a statement is received, a change is made to metadata of a database object. Such a metadata change may involve setting an index as unusable, disabling a constraint, or invalidating a cursor. After the metadata change, a first time is determined. Then, it is determined when one or more database transactions that were pending at the first time have committed. After those database transaction(s) have committed, one or more operations are performed, such as dropping an index or dropping a constraint.
1. A method comprising:
receiving an instruction to modify metadata that is associated with a column of a table;
in response to receiving the instruction:
initiating a first transaction;
after initiating the first transaction and before committing the first transaction:
acquiring a lock on the table;
initiating a second transaction within the first transaction;
after initiating the second transaction, storing data that indicates that a constraint is disabled;
committing the second transaction;
after committing the second transaction and before committing the first transaction:
determining a particular timestamp;
waiting for one or more transactions that are pending at a time associated with the particular timestamp to commit;
in response to determining that the one or more transactions have committed, dropping the constraint;
committing the first transaction;
wherein the method is performed by one or more computing devices.
2. The method of claim 1 , wherein the instruction is to drop the constraint of the column of the table.
3. The method of claim 2 , further comprising:
determining whether the constraint is a primary key constraint;
in response to determining that the constraint is a primary key constraint, modifying index metadata of the table to indicate that an index associated with the column is unusable.
4. The method of claim 1 , wherein:
the instruction is to set the column unused;
the method further comprising, prior to determining the particular timestamp, storing second data that indicates that the column is unusable.
5. The method of claim 4 , further comprising:
prior to determining the particular timestamp, determining whether the constraint on the column exists;
wherein storing the data comprises storing the data that indicates that the constraint is disabled in response to determining that the constraint on the column exists.
6. The method of claim 1 , further comprising:
in response to receiving the instruction and prior to determining the particular timestamp:
determining whether an index exists on the column;
if it is determined that an index exists on the column, then storing index data that indicates that the index is unusable;
in response to determining that the one or more transactions have committed, dropping the index if it is determined that the index exists.
7. The method of claim 1 , further comprising:
in response to receiving the instruction and prior to determining the particular timestamp, invalidating one or more cursors.
8. The method of claim 1 , wherein waiting comprises calling a function that takes, as input, the particular timestamp and an object identifier that identifies the column or the table.
9. One or more non-transitory computer-readable media storing instructions which, when executed by one or more processors, cause:
receiving an instruction to modify metadata that is associated with a column of a table;
in response to receiving the instruction:
initiating a first transaction;
after initiating the first transaction and before committing the first transaction:
acquiring a lock on the table;
initiating a second transaction within the first transaction;
after initiating the second transaction, storing data that indicates that a constraint is disabled;
committing the second transaction;
after committing the second transaction and before committing the first transaction:
determining a particular timestamp;
waiting for one or more transactions that are pending at a time associated with the particular timestamp to commit;
in response to determining that the one or more transactions have committed, dropping the constraint;
committing the first transaction.
10. The one or more non-transitory computer-readable media of claim 9 , wherein the instruction is to drop the constraint of the column of the table.
11. The one or more non-transitory computer-readable media of claim 10 , wherein the instructions, when executed by the one or more processors, further cause:
determining whether the constraint is a primary key constraint;
in response to determining that the constraint is a primary key constraint, modifying index metadata of the table to indicate that an index associated with the column is unusable.
12. The one or more non-transitory computer-readable media of claim 9 , wherein:
the instruction is to set the column unused;
the instructions, when executed by the one or more processors, further cause, prior to determining the particular timestamp, storing second data that indicates that the column is unusable.
13. The one or more non-transitory computer-readable media of claim 12 , wherein the instructions, when executed by the one or more processors, further cause:
prior to determining the particular timestamp, determining whether the constraint on the column exists;
wherein storing the data comprises storing the data that indicates that the constraint is disabled in response to determining that the constraint on the column exists.
14. The one or more non-transitory computer-readable media of claim 9 , wherein the instructions, when executed by the one or more processors, further cause:
in response to receiving the instruction and prior to determining the particular time stamp:
determining whether an index exists on the column;
if it is determined that an index exists on the column, then storing index data that indicates that the index is unusable;
in response to determining that the one or more transactions have committed, dropping the index if it is determined that the index exists.
15. The one or more non-transitory computer-readable media of claim 9 , wherein the instructions, when executed by the one or more processors, further cause:
in response to receiving the instruction and prior to determining the particular timestamp, invalidating one or more cursors.
16. The one or more non-transitory computer-readable media of claim 9 , wherein waiting comprises calling a function that takes, as input, the particular timestamp and an object identifier that identifies the column or the table.