AWS aurora-dsql documentation change
Summary
Updated documentation for asynchronous index creation with expanded syntax details, usage notes, examples, and clarifications about job management via sys.jobs
Security assessment
The changes focus on improving clarity, adding examples, and explaining job management procedures. While there are mentions of concurrency errors and invalid indexes requiring manual cleanup, these are operational/data integrity considerations rather than direct security vulnerabilities. No evidence of patching exploits, CVEs, or security-specific controls.
Diff
diff --git a/aurora-dsql/latest/userguide/working-with-create-index-async.md b/aurora-dsql/latest/userguide/working-with-create-index-async.md index a0a3ac097..a51c6a638 100644 --- a//aurora-dsql/latest/userguide/working-with-create-index-async.md +++ b//aurora-dsql/latest/userguide/working-with-create-index-async.md @@ -5 +5 @@ -SyntaxParametersExamplesUsage notes +SyntaxParametersUsage notesCreating an index: exampleQuerying the status of index creation: exampleQuerying the state of your index: example @@ -9 +9 @@ Amazon Aurora DSQL is provided as a Preview service. To learn more, see [Betas a -# Creating async indexes in Aurora DSQL +# Asynchronous indexes in Aurora DSQL @@ -11 +11 @@ Amazon Aurora DSQL is provided as a Preview service. To learn more, see [Betas a -The `CREATE INDEX ASYNC` command lets you create an index on a column of a specified table. `CREATE INDEX ASYNC` is an asynchronous DDL operation, so running this command doesn't block your other transactions, and Aurora DSQL immediately returns a `job_id` to you. You can see the status of an asynchronous job at any time with the `sys.jobs` system view. +The `CREATE INDEX ASYNC` command creates an index on a column of a specified table. `CREATE INDEX ASYNC` is an asynchronous DDL operation, so this command doesn't block other transactions. @@ -13 +13 @@ The `CREATE INDEX ASYNC` command lets you create an index on a column of a speci -Aurora DSQL also supports the procedures `sys.wait_for_job(job_id)` and `sys.cancel_job(job_id)`. `sys.wait_for_job` lets you block the session until the specified job completes or fails. This procedure returns a Boolean. `sys.cancel_job` lets you cancel an asynchronous job that is in progress. +Aurora DSQL immediately returns a `job_id` when you run this command. You can see the status of an asynchronous job at any time with the `sys.jobs` system view. @@ -15 +15,13 @@ Aurora DSQL also supports the procedures `sys.wait_for_job(job_id)` and `sys.can -When Aurora DSQL finishes an asynchronous index task, it also updates the system catalog to show that the index is now active. If any other transactions reference the objects in the same namespace at this time, you might see a concurrency error. +Aurora DSQL supports the following job-related procedures: + +`sys.wait_for_job(job_id)` + + +Block the session until the specified job completes or fails. This procedure returns a Boolean. + +`sys.cancel_job` + + +Cancel an asynchronous job that is in progress. + +When Aurora DSQL finishes an asynchronous index task, it updates the system catalog to show that the index is active. If other transactions reference the objects in the same namespace at this time, you might see a concurrency error. @@ -23 +35 @@ During Preview, asynchronous task completion might result in concurrency control -See the following to learn about the parameters for `CREATE INDEX ASYNC`. +`CREATE INDEX ASYNC` uses the following syntax. @@ -33 +45,9 @@ See the following to learn about the parameters for `CREATE INDEX ASYNC`. -UNIQUE +`UNIQUE` + + +Indicates to Aurora DSQL to check for duplicate values in the table when it creates the index and each time you add data. If you specify this parameter, insert and update operations that would result in duplicate entries generate an error. + +`IF NOT EXISTS` + + +Indicates that Aurora DSQL shouldn't throw an exception if an index with the same name already exists. In this situation, Aurora DSQL doesn't create the new index. Note that the index you're trying to create could have a very different structure from the index that exists. If you specify this parameter, the index name is required. @@ -34,0 +55 @@ UNIQUE +``name`` @@ -36 +56,0 @@ UNIQUE -Indicates to Aurora DSQL to check for duplicate values in the table when it creates the index and each time you add data. If you specify this parameter, insert and update operations that would result in duplicate entries will generate an error. @@ -38 +58 @@ Indicates to Aurora DSQL to check for duplicate values in the table when it crea -IF NOT EXISTS +The name of the index. You can't include the name of your schema in this parameter. @@ -39,0 +60 @@ IF NOT EXISTS +Aurora DSQL creates the index in the same schema as its parent table. The name of the index must be distinct from the name of any other object, such as a table or index, in the schema. @@ -41 +62 @@ IF NOT EXISTS -Indicates that Aurora DSQL shouldn't throw an exception if an index with the same name already exists. If an index with the same name already exists, Aurora DSQL doesn't create the new index. However, the index you're trying to create could have a very different structure than the index that already exists. If you specify this parameter, index name is required. +If you don't specify a name, Aurora DSQL generates a name automatically based on the name of the parent table and indexed column. For example, if you run `CREATE INDEX ASYNC on table1 (col1, col2)`, Aurora DSQL automatically names the index `table1_col1_col2_idx`. @@ -43 +64 @@ Indicates that Aurora DSQL shouldn't throw an exception if an index with the sam -name +`NULLS FIRST | LAST` @@ -46 +67 @@ name -The name of the index to create. You can't include the name of your schema in this parameter. Aurora DSQL always creates the index in the same schema as its parent table. The name of the index must be distinct from the name of any other object, such as table or index, in the schema. If you don't specify a name, Aurora DSQL automatically generates a name based on the name of the parent table and the name of the indexed column. For example, if you run `CREATE INDEX ASYNC on table1 (col1, col2);`, Aurora DSQL automatically names the index as `table1_col1_col2_idx`. +The sort order of null and non-null columns. `FIRST` indicates that Aurora DSQL should sort null columns before non-null columns. `LAST` indicates that Aurora DSQL should sort null columns after non-null columns. @@ -48 +69 @@ The name of the index to create. You can't include the name of your schema in th -NULLS FIRST | LAST +`INCLUDE` @@ -51 +72 @@ NULLS FIRST | LAST -Specifies the order of how to sort null columns and non-null columns. FIRST indicates that Aurora DSQL should sort null columns before non-null columns. LAST indicates that Aurora DSQL should sort null columns after non-null columns. +A list of columns to include in the index as non-key columns. You can't use a non-key column in an index scan search qualification. Aurora DSQL ignores the column in terms of uniqueness for an index. @@ -53 +74 @@ Specifies the order of how to sort null columns and non-null columns. FIRST ind -INCLUDE +`NULLS DISTINCT | NULLS NOT DISTINCT` @@ -56 +77 @@ INCLUDE -The INCLUDE clause specifies a list of columns to include in the index as non-key columns. You can't use a non-key column in an index scan search qualification, and Aurora DSQL ignores the column in terms of uniqueness for an index. +Specifies whether Aurora DSQL should consider null values as distinct in a unique index. The default is `DISTINCT`, meaning that a unique index can contain multiple null values in a column. `NOT DISTINCT` indicates that an index can't contain multiple null values in a column. @@ -58 +79,11 @@ The INCLUDE clause specifies a list of columns to include in the index as non-k -NULLS DISTINCT | NULLS NOT DISTINCT +## Usage notes + +Consider the following guidelines: + + * The `CREATE INDEX ASYNC` command doesn't introduce locks. It also doesn't affect the base table that Aurora DSQL uses to create the index. + + * During schema migration operations, the `sys.wait_for_job(job_id)` procedure is especially helpful. It ensures that subsequent DDL and DML operations target the newly created index. + + * Every time Aurora DSQL runs a new asynchronous task, it checks the `sys.jobs` view and deletes tasks that have a status of `completed`, `failed`, or `cancelled` for more than 30 minutes. Thus, `sys.jobs` primarily shows in-progress tasks and doesn’t contain information about old tasks. + + * If you cancel a task, Aurora DSQL automatically updates the corresponding entry in the `sys.jobs` system view. As Aurora DSQL runs the task, it checks the `sys.jobs` view to see if the task has been canceled. If so, Aurora DSQL stops the task. If you encounter an error that Aurora DSQL is updating your schema with another transaction, try to cancel again. After you cancel a task to create an async index, we recommend that you also drop the index. @@ -59,0 +91 @@ NULLS DISTINCT | NULLS NOT DISTINCT + * If Aurora DSQL fails to build an asynchronous index, the index stays `INVALID`. For unique indexes, DML operations are subject to uniqueness constraints until you drop the index. We recommend that you drop invalid indexes and recreate them. @@ -61 +92,0 @@ NULLS DISTINCT | NULLS NOT DISTINCT -Specifies whether Aurora DSQL should consider null values as distinct/not equals in a unique index. Default is DISTINCT, which indicates that null values are distinct, so a unique index can contain multiple null values in a column. NOT DISTINCT indicates that null values aren't distinct, so an index can't contain multiple null values in a column. @@ -63 +94,3 @@ Specifies whether Aurora DSQL should consider null values as distinct/not equal -## Examples + + +## Creating an index: example @@ -66,0 +100 @@ The following example demonstrates how to create a schema, a table, and then an + 1. Create a table named `test.departments`. @@ -75,2 +108 @@ The following example demonstrates how to create a schema, a table, and then an -Add some data into the table. - + 2. Insert a row into the table. @@ -80,2 +112 @@ Add some data into the table. -Then create the index. - + 3. Create an asynchronous index. @@ -85,2 +116 @@ Then create the index. -The CREATE INDEX command returns a job_id. - +The `CREATE INDEX` command returns a job ID, as shown below. @@ -92 +122 @@ The CREATE INDEX command returns a job_id. -With this `job_id`, you can use the procedures `sys.wait_for_job` or `sys.cancel_job` to block the session from other transactions until Aurora DSQL completes the job or cancel the job. +The `job_id` indicates that Aurora DSQL has submitted a new job to create the index. You can use the procedure `sys.wait_for_job(job_id)` to block other work on the session until the job finishes, is canceled, or times out. To cancel an active job, use the procedure `sys.cancel_job(job_id)`. @@ -94 +123,0 @@ With this `job_id`, you can use the procedures `sys.wait_for_job` or `sys.cancel -When you receive the `job_id`, then Aurora DSQL has submitted a new job to create the index. You can use the procedure `sys.wait_for_job(job_id)` to block other work on the session until the job finishes, is canceled, or if the session times out. To cancel an active async index creation job, use the procedure `sys.cancel_job(job_id)`. @@ -96 +124,0 @@ When you receive the `job_id`, then Aurora DSQL has submitted a new job to creat -To check the creation status of your index, query the `sys.jobs` system view. @@ -99 +127,6 @@ To check the creation status of your index, query the `sys.jobs` system view. - SELECT * from sys.jobs +## Querying the status of index creation: example + +Query the `sys.jobs` system view to check the creation status of your index, as shown in the following example. + + + SELECT * FROM sys.jobs @@ -112 +145,6 @@ The status column can be one of the following values: - * submitted – The task is submitted, but Aurora DSQL hasn't started to process it yet. +`submitted` + + +The task is submitted, but Aurora DSQL hasn't started to process it yet. + +`processing` @@ -114 +151,0 @@ The status column can be one of the following values: - * processing – Aurora DSQL is processing the task. @@ -116 +153 @@ The status column can be one of the following values: - * failed – the task failed. See the details column for more information. If Aurora DSQL failed to build the index, Aurora DSQL doesn't automatically remove the index definition. You must manually remove the index with the `DROP INDEX` command. +Aurora DSQL is processing the task. @@ -118 +155 @@ The status column can be one of the following values: - * completed – Aurora DSQL finished the task. +`failed` @@ -120 +156,0 @@ The status column can be one of the following values: - * cancelled – The task is canceled. @@ -121,0 +158 @@ The status column can be one of the following values: +The task failed. See the details column for more information. If Aurora DSQL failed to build the index, Aurora DSQL doesn't automatically remove the index definition. You must manually remove the index with the `DROP INDEX` command. @@ -122,0 +160 @@ The status column can be one of the following values: +`completed` @@ -124,0 +163,7 @@ The status column can be one of the following values: +Aurora DSQL + +`cancelled` + + +The task is canceled. + @@ -138 +183 @@ You can also query the state of the index via the catalog tables `pg_index`and ` -If you are creating an index with the `UNIQUE` specifier, this is indicated by the `indisunique` flag. To know whether your table is subject to uniqueness checks for concurrent writes, you can look at the `indimmediate` flag in the `pg_index`, like the query below. If the flag is false and your job has the status `processing`, it means the index is still being built, and writes to the index are not subject to uniqueness checks. If the flag is true and the job status is `processing`, it means the initial index has been built, but uniqueness checks have not been performed on all the rows in the index yet. However, for all current and future writes to the index, uniqueness checks will be performed. +## Querying the state of your index: example @@ -139,0 +185 @@ If you are creating an index with the `UNIQUE` specifier, this is indicated by t +You can query the state of the index using the catalog tables `pg_index`and `pg_class`. Specifically, the attributes `indisvalid` and `indisimmediate` tell you the state of the index. The following example shows a sample query and results. @@ -141,3 +186,0 @@ If you are creating an index with the `UNIQUE` specifier, this is indicated by t - select relname as index_name, indimmediate as check_unique, pg_get_indexdef(indexrelid) as index_definition - from pg_index, pg_class - where pg_class.oid = indexrelid and indrelid = 'test.departments'::regclass; @@ -144,0 +188,3 @@ If you are creating an index with the `UNIQUE` specifier, this is indicated by t + SELECT relname AS index_name, indisvalid AS is_valid, pg_get_indexdef(indexrelid) AS index_definition + FROM pg_index, pg_class + WHERE pg_class.oid = indexrelid AND indrelid = 'test.departments'::regclass; @@ -146 +192,2 @@ If you are creating an index with the `UNIQUE` specifier, this is indicated by t - index_name | check_unique | index_definition ------------------+----------+------------------------------------------------------------------------------------------------------------------- + index_name | is_valid | index_definition + ------------------+----------+------------------------------------------------------------------------------------------------------------------- @@ -148,9 +195 @@ If you are creating an index with the `UNIQUE` specifier, this is indicated by t - test_index1 | f | CREATE INDEX test_index1 ON test.departments USING remote_btree_index (name, manager, size) - -## Usage notes - -When using CREATE INDEX ASYNC, consider the following: - - * Running the `CREATE INDEX ASYNC` command doesn't introduce any locks to your applications and doesn't affect the base table that Aurora DSQL uses to create the index. - - * During schema migration operations, the `sys.wait_for_job(job_id)` procedure is especially helpful because you can ensure that subsequent DDL and DML operations all target the newly created index. + test_index1 | t | CREATE INDEX test_index1 ON test.departments USING remote_btree_index (name, manager, size) @@ -158 +197 @@ When using CREATE INDEX ASYNC, consider the following: - * If you cancel a task, Aurora DSQL automatically updates the corresponding entry in the `sys.jobs` system view. As Aurora DSQL runs the task, it also checks the `sys.jobs` view to see if the task has been updated to canceled. If it is, Aurora DSQL stops the task. If you encounter an error that Aurora DSQL is updating your schema with another transaction, try to cancel again. After you cancel a task to create an async index, we recommend that you also drop the index. +While Aurora DSQL creates your index, it has an initial status of `INVALID`. The `indisvalid` column for the index shows `FALSE` or `f`, which indicates that the index isn't valid. If the column shows `TRUE` or `t`, the index is ready. @@ -160 +199 @@ When using CREATE INDEX ASYNC, consider the following: - * If Aurora DSQL fails to build an async index, that index stays `INVALID`. For unique indexes, DML operations will be subject to uniqueness constraints until the index is dropped. We recommend that you drop all invalid indexes and recreate them. +The `indisunique` flag indicates that the index is `UNIQUE`. To know whether your table is subject to uniqueness checks for concurrent writes, query the `indimmediate` column in the `pg_index`, as in the query below. @@ -162 +200,0 @@ When using CREATE INDEX ASYNC, consider the following: - * Every time Aurora DSQL runs a new asynchronous task, it checks the `sys.jobs` view and deletes tasks that have the `completed`, `failed`, or `cancelled` statuses for more than 30 minutes. Doing so means `sys.jobs` primarily shows only in-progress tasks and doesn’t contain information about old tasks. @@ -163,0 +202,4 @@ When using CREATE INDEX ASYNC, consider the following: + SELECT relname AS index_name, indimmediate AS check_unique, pg_get_indexdef(indexrelid) AS index_definition + FROM pg_index, pg_class + WHERE pg_class.oid = indexrelid + AND indrelid = 'test.departments'::regclass; @@ -164,0 +207,4 @@ When using CREATE INDEX ASYNC, consider the following: + index_name | check_unique | index_definition + ------------------+----------+-------------------------------------------------------------------------------------------------------------------