AWS aurora-dsql documentation change
Summary
Added constraints on unique index creation during Preview, updated async index creation examples, and revised considerations for invalid indexes.
Security assessment
The changes clarify feature limitations (unique indexes on empty tables) and index status tracking. While invalid indexes are noted to receive updates, there's no evidence of addressing a security vulnerability or introducing security features. The changes focus on functionality and operational guidance.
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..09df547eb 100644 --- a//aurora-dsql/latest/userguide/working-with-create-index-async.md +++ b//aurora-dsql/latest/userguide/working-with-create-index-async.md @@ -37,0 +38,2 @@ Indicates to Aurora DSQL to check for duplicate values in the table when it crea +During Preview, you can only create unique indexes on empty tables. If you try to create a unique index on a non-empty table, the operation fails. You can see the related error and other information in the details column of the `sys.jobs` view. + @@ -94 +96,12 @@ 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)`. +When you receive the `job_id`, then Aurora DSQL has started to create the new index on a new job. 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)`. + + + 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; + + + index_name | is_valid | index_definition + ------------------+----------+------------------------------------------------------------------------------------------------------------------- + department_pkey | t | CREATE UNIQUE INDEX department_pkey ON test.departments USING remote_btree_index (title) INCLUDE (name, manager, size) + test_index1 | t | CREATE INDEX test_index1 ON test.departments USING remote_btree_index (name, manager, size) @@ -125,25 +137,0 @@ The status column can be one of the following values: -You can also query the state of the index via the catalog tables `pg_index`and `pg_class`. Specifically, the attributes `indisvalid` and `indisimmediate` can tell you what state your index is in. While Aurora DSQL creates your index, it has an initial status of `INVALID`. The `indisvalid` flag for the index returns `FALSE` or `f`, which indicates that the index isn't valid. If the flag returns `TRUE` or `t`, the index is ready. - - - 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; - - - index_name | is_valid | index_definition - ------------------+----------+------------------------------------------------------------------------------------------------------------------- - department_pkey | t | CREATE UNIQUE INDEX department_pkey ON test.departments USING remote_btree_index (title) INCLUDE (name, manager, size) - test_index1 | t | CREATE INDEX test_index1 ON test.departments USING remote_btree_index (name, manager, size) - -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. - - - 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; - - - index_name | check_unique | index_definition ------------------+----------+------------------------------------------------------------------------------------------------------------------- - department_pkey | t | CREATE UNIQUE INDEX department_pkey ON test.departments USING remote_btree_index (title) INCLUDE (name, manager, size) - test_index1 | f | CREATE INDEX test_index1 ON test.departments USING remote_btree_index (name, manager, size) - @@ -160 +148,3 @@ 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. + * While Aurora DSQL creates your index, it has an initial status of `INVALID`. The `indisvalid` flag for the index returns `FALSE` or `f`, which indicates that the index isn't valid. If the flag returns `TRUE` or `t`, the index is ready. + + * If Aurora DSQL fails to build an async index, that index stays `INVALID`. This invalid index takes up storage space and can receive updates and inserts from other queries. We recommend that you drop all invalid indexes and recreate them.