AWS aurora-dsql documentation change
Summary
Updated documentation for CREATE INDEX ASYNC operations with improved examples, added sections about unique index failures, and concurrency considerations
Security assessment
Changes focus on operational aspects of index creation and failure handling. The added note about concurrency errors during catalog updates describes a normal database concurrency consideration rather than a security vulnerability. Documentation improvements for handling unique constraint violations and monitoring index states are operational best practices without security implications.
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 dd0b89a7c..4f93e7a38 100644 --- a//aurora-dsql/latest/userguide/working-with-create-index-async.md +++ b//aurora-dsql/latest/userguide/working-with-create-index-async.md @@ -5,3 +5 @@ -SyntaxParametersUsage notesCreating an index: exampleQuerying the status of index creation: exampleQuerying the state of your index: example - -Amazon Aurora DSQL is provided as a Preview service. To learn more, see [Betas and Previews ](https://aws.amazon.com/service-terms/) in the AWS Service Terms. +SyntaxParametersUsage notesCreating an indexQuerying an indexUnique index build failuresUniqueness violations @@ -11,3 +9 @@ Amazon Aurora DSQL is provided as a Preview service. To learn more, see [Betas a -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. - -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. +The `CREATE INDEX ASYNC` command creates an index on one or more columns of a specified table. This command is an asynchronous DDL operation that doesn't block other transactions. When you run `CREATE INDEX ASYNC`, Aurora DSQL immediately returns a `job_id`. @@ -15 +11 @@ Aurora DSQL immediately returns a `job_id` when you run this command. You can se -You can use the following procedures and commands when the index creation job is in progress: +You can monitor the status of this asynchronous job using the `sys.jobs` system view. While the index creation job is in progress, you can use these procedures and commands: @@ -17 +13 @@ You can use the following procedures and commands when the index creation job is -`sys.wait_for_job(job_id)` +`sys.wait_for_job(job_id)`'your_index_creation_job_id'`` @@ -20 +16 @@ You can use the following procedures and commands when the index creation job is -Block the session until the specified job completes or fails. This procedure returns a Boolean. +Blocks the current session until the specified job completes or fails. Returns a Boolean value indicating success or failure. @@ -25 +21,7 @@ Block the session until the specified job completes or fails. This procedure ret -Cancel an index build job that is in progress. +Cancels an in-progress index build job. + +When the asynchronous index creation completes, Aurora DSQL updates the system catalog to mark the index as active. + +###### Note + +Note that concurrent transactions accessing objects in the same namespace during this update might encounter concurrency errors. @@ -81 +83 @@ Consider the following guidelines: - * During schema migration operations, the `sys.wait_for_job(job_id)` procedure is useful. It ensures that subsequent DDL and DML operations target the newly created index. + * During schema migration operations, the `sys.wait_for_job(job_id)`'your_index_creation_job_id'`` procedure is useful. It ensures that subsequent DDL and DML operations target the newly created index. @@ -98 +100 @@ The following example demonstrates how to create a schema, a table, and then an - CREATE TABLE test.departments (name varchar(255) primary key not null, + CREATE TABLE test.departments (name varchar(255) primary key NOT null, @@ -116 +118 @@ The `CREATE INDEX` command returns a job ID, as shown below. -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 or times out. +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)`'your_index_creation_job_id'`` to block other work on the session until the job finishes or times out. @@ -136 +138 @@ Aurora DSQL returns a response similar to the following. -The status column can be one of the following values: +The status column can be one of the following values. @@ -138 +140,3 @@ The status column can be one of the following values: -`submitted` +`submitted` | `processing` | `failed` | `completed` +---|---|---|--- +The task is submitted, but Aurora DSQL hasn't started to process it yet. | Aurora DSQL is processing the task. | 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 @@ -139,0 +144 @@ 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. @@ -141 +145,0 @@ The status column can be one of the following values: -The task is submitted, but Aurora DSQL hasn't started to process it yet. @@ -143 +147,3 @@ The task is submitted, but Aurora DSQL hasn't started to process it yet. -`processing` + 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 +152,4 @@ The task is submitted, but Aurora DSQL hasn't started to process it yet. -Aurora DSQL is processing the task. + index_name | is_valid | index_definition + ------------------+----------+------------------------------------------------------------------------------------------------------------------- + department_pkey | t | CREATE UNIQUE INDEX department_pkey ON test.departments USING btree_index (title) INCLUDE (name, manager, size) + test_index1 | t | CREATE INDEX test_index1 ON test.departments USING btree_index (name, manager, size) @@ -148 +157 @@ Aurora DSQL is processing the task. -`failed` +## Unique index build failures @@ -149,0 +159 @@ Aurora DSQL is processing the task. +If your asynchronous unique index build job shows a failed state with the detail `Found duplicate key while validating index for UCVs`, this indicates that a unique index could not be built due to uniqueness constraint violations. @@ -151 +161 @@ Aurora DSQL is processing the task. -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. +###### To resolve unique index build failures @@ -153 +163 @@ The task failed. See the details column for more information. If Aurora DSQL fai -`completed` + 1. Remove any rows in your primary table that have duplicate entries for the keys specified in your unique secondary index. @@ -154,0 +165 @@ The task failed. See the details column for more information. If Aurora DSQL fai + 2. Drop the failed index. @@ -156 +167 @@ The task failed. See the details column for more information. If Aurora DSQL fai -Aurora DSQL + 3. Issue a new create index command. @@ -158 +168,0 @@ Aurora DSQL -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. @@ -161,3 +170,0 @@ You can also query the state of the index via the catalog tables `pg_index`and ` - 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; @@ -164,0 +172 @@ You can also query the state of the index via the catalog tables `pg_index`and ` +## Detecting uniqueness violations in primary tables @@ -166,4 +174 @@ You can also query the state of the index via the catalog tables `pg_index`and ` - index_name | is_valid | index_definition - ------------------+----------+------------------------------------------------------------------------------------------------------------------- - department_pkey | t | CREATE UNIQUE INDEX department_pkey ON test.departments USING btree_index (title) INCLUDE (name, manager, size) - test_index1 | t | CREATE INDEX test_index1 ON test.departments USING btree_index (name, manager, size) +The following SQL query helps you identify duplicate values in a specified column of your table. This is particularly useful when you need to enforce uniqueness on a column that isn't currently set as a primary key or doesn't have a unique constraint, such as email addresses in a user table. @@ -171 +176 @@ You can also query the state of the index via the catalog tables `pg_index`and ` -## Querying the state of your index: example +The examples below demonstrate how to create a sample users table, populate it with test data containing known duplicates, and then run the detection query. @@ -173 +178 @@ You can also query the state of the index via the catalog tables `pg_index`and ` -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. +**Define table schema** @@ -176,3 +181,2 @@ You can query the state of the index using the catalog tables `pg_index`and `pg_ - 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; + -- Drop the table if it exists + DROP TABLE IF EXISTS users; @@ -180,4 +184,8 @@ You can query the state of the index using the catalog tables `pg_index`and `pg_ - index_name | is_valid | index_definition - ------------------+----------+------------------------------------------------------------------------------------------------------------------- - department_pkey | t | CREATE UNIQUE INDEX department_pkey ON test.departments USING btree_index (title) INCLUDE (name, manager, size) - test_index1 | t | CREATE INDEX test_index1 ON test.departments USING btree_index (name, manager, size) + -- Create the users table with a simple integer primary key + CREATE TABLE users ( + user_id INTEGER PRIMARY KEY, + email VARCHAR(255), + first_name VARCHAR(100), + last_name VARCHAR(100), + created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP + ); @@ -185 +193 @@ You can query the state of the index using the catalog tables `pg_index`and `pg_ -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. +**Insert sample data that includes sets of duplicate email addresses** @@ -187 +194,0 @@ While Aurora DSQL creates your index, it has an initial status of `INVALID`. The -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. @@ -188,0 +196,9 @@ The `indisunique` flag indicates that the index is `UNIQUE`. To know whether you + -- Insert sample data with explicit IDs + INSERT INTO users (user_id, email, first_name, last_name) VALUES + (1, '[email protected]', 'John', 'Doe'), + (2, '[email protected]', 'Jane', 'Smith'), + (3, '[email protected]', 'Johnny', 'Doe'), + (4, '[email protected]', 'Alice', 'Wong'), + (5, '[email protected]', 'Bob', 'Jones'), + (6, '[email protected]', 'Alicia', 'Wong'), + (7, '[email protected]', 'Robert', 'Jones'); @@ -190,4 +206,16 @@ The `indisunique` flag indicates that the index is `UNIQUE`. To know whether you - 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; +**Run duplicate detection query** + + + -- Query to find duplicates + WITH duplicates AS ( + SELECT email, COUNT(*) as duplicate_count + FROM users + GROUP BY email + HAVING COUNT(*) > 1 + ) + SELECT u.*, d.duplicate_count + FROM users u + INNER JOIN duplicates d ON u.email = d.email + ORDER BY u.email, u.user_id; + +**View all records with duplicate email addresses** @@ -195,4 +222,0 @@ The `indisunique` flag indicates that the index is `UNIQUE`. To know whether you - index_name | check_unique | index_definition - ------------------+----------+------------------------------------------------------------------------------------------------------------------- - department_pkey | t | CREATE UNIQUE INDEX department_pkey ON test.departments USING btree_index (title) INCLUDE (name, manager, size) - test_index1 | f | CREATE INDEX test_index1 ON test.departments USING btree_index (name, manager, size) @@ -200 +224,23 @@ The `indisunique` flag indicates that the index is `UNIQUE`. To know whether you -If the column shows `f` and your job has the status `processing`, the index is still being created. Writes to the index aren't subject to uniqueness checks. If the column shows `t` and the job status is `processing`, the initial index has been built, but uniqueness checks have not been performed on all rows in the index. However, for all current and future writes to the index, Aurora DSQL will perform uniqueness checks. + user_id | email | first_name | last_name | created_at | duplicate_count + ---------+------------------------+------------+-----------+----------------------------+----------------- + 4 | [email protected] | Akua | Mansa | 2025-05-21 20:55:53.714432 | 2 + 6 | [email protected] | Akua | Mansa | 2025-05-21 20:55:53.714432 | 2 + 1 | [email protected] | John | Doe | 2025-05-21 20:55:53.714432 | 2 + 3 | [email protected] | Johnny | Doe | 2025-05-21 20:55:53.714432 | 2 + (4 rows) + +**If we were to try the index creation statement now, it would fail:** + + + postgres=> CREATE UNIQUE INDEX ASYNC idx_users_email ON users(email); + job_id + ---------------------------- + ve32upmjz5dgdknpbleeca5tri + (1 row) + + postgres=> select * from sys.jobs; + job_id | status | details | job_type | class_id | object_id | object_name | start_time | update_time + ----------------------------+-----------+-----------------------------------------------------+-------------+----------+-----------+------------------------+------------------------+------------------------ + qpn6aqlkijgmzilyidcpwrpova | completed | | DROP | 1259 | 26384 | | 2025-05-20 00:47:10+00 | 2025-05-20 00:47:32+00 + ve32upmjz5dgdknpbleeca5tri | failed | Found duplicate key while validating index for UCVs | INDEX_BUILD | 1259 | 26396 | public.idx_users_email | 2025-05-20 00:49:49+00 | 2025-05-20 00:49:56+00 + (2 rows)