Sintaxis Parámetros Notas de uso Creación de un índice Consulta de un índice Errores en la creación de índices únicos Infracciones de unicidad

Índices asíncronos en Aurora DSQL

El comando CREATE INDEX ASYNC crea un índice en una o más columnas de una tabla específica. Este comando es una operación DDL asíncrona que no bloquea otras transacciones. Cuando ejecute CREATE INDEX ASYNC, Aurora DSQL devuelve de forma inmediata un job_id.

Puede supervisar el estado de este trabajo asíncrono mediante la vista de sistema sys.jobs. Mientras el trabajo de creación de índices esté en curso, puede usar estos procedimientos y comandos:

sys.wait_for_job(job_id)'your_index_creation_job_id'

Bloquea la sesión actual hasta que el trabajo especificado finalice o se produzca un error. Devuelve un valor booleano que indica éxito o error.

DROP INDEX

Cancela un trabajo de creación de índices en curso.

Cuando se completa la creación de índices asíncrona, Aurora DSQL actualiza el catálogo del sistema para marcar el índice como activo.

nota

Tenga en cuenta que las transacciones simultáneas que acceden a objetos en el mismo espacio de nombres durante esta actualización pueden encontrar errores de simultaneidad.

Cuando Aurora DSQL finaliza una tarea de índice asíncrona, actualiza el catálogo del sistema para mostrar que el índice está activo. Si otras transacciones hacen referencia a los objetos en el mismo espacio de nombres en ese momento, podría aparecer un error de simultaneidad.

Sintaxis

CREATE INDEX ASYNC utiliza la siguiente sintaxis.


CREATE [ UNIQUE ] INDEX ASYNC [ IF NOT EXISTS ] name ON table_name 
     ( { column_name } [ NULLS { FIRST | LAST } ] ) 
     [ INCLUDE ( column_name [, ...] ) ] 
     [ NULLS [ NOT ] DISTINCT ]

Parámetros

UNIQUE

Indica a Aurora DSQL que compruebe si hay valores duplicados en la tabla cuando crea el índice y cada vez que agrega datos. Si especifica este parámetro, las operaciones de inserción y actualización que dan lugar a entradas duplicadas generan un error.

IF NOT EXISTS

Indica que Aurora DSQL no debe lanzar una excepción si ya existe un índice con el mismo nombre. En esta situación, Aurora DSQL no crea el nuevo índice. Tenga en cuenta que el índice que intenta crear podría tener una estructura muy diferente del índice que existe. Si especifica este parámetro, el nombre del índice es obligatorio.

name

El nombre del índice. No puede incluir el nombre del esquema en este parámetro.

Aurora DSQL crea el índice en el mismo esquema que la tabla principal. El nombre del índice debe ser distinto del nombre de cualquier otro objeto, como una tabla o un índice, en el esquema.

Si no especifica un nombre, Aurora DSQL genera un nombre automáticamente basándose en el nombre de la tabla principal y la columna indexada. Por ejemplo, si ejecuta CREATE INDEX ASYNC on table1 (col1, col2), Aurora DSQL asigna automáticamente el nombre table1_col1_col2_idx al índice.

NULLS FIRST | LAST

El orden de clasificación de las columnas nulas y no nulas. FIRST indica que Aurora DSQL debe ordenar las columnas nulas antes que las columnas no nulas. LAST indica que Aurora DSQL debe ordenar las columnas nulas después de las columnas no nulas.

INCLUDE

Una lista de columnas para incluir en el índice como columnas no clave. No puede utilizar una columna no clave en una cualificación de búsqueda de examen de índice. Aurora DSQL ignora la columna en términos de unicidad para un índice.

NULLS DISTINCT | NULLS NOT DISTINCT

Especifica si Aurora DSQL debe considerar los valores nulos como distintos en un índice único. El valor predeterminado es DISTINCT, lo que significa que un índice único puede contener múltiples valores nulos en una columna. NOT DISTINCT indica que un índice no puede contener múltiples valores nulos en una columna.

Notas de uso

Tenga en cuenta estas directrices:

El comando CREATE INDEX ASYNC no introduce bloqueos. Tampoco afecta la tabla base que Aurora DSQL utiliza para crear el índice.
Durante las operaciones de migración de esquemas, el procedimiento sys.wait_for_job(job_id)'your_index_creation_job_id' resulta útil. Garantiza que las operaciones DDL y DML posteriores se dirijan al índice recién creado.
Cada vez que Aurora DSQL ejecuta una nueva tarea asíncrona, comprueba la vista sys.jobs y elimina las tareas que tienen un estado de completed o failed durante más de 30 minutos. Así, sys.jobs muestra principalmente las tareas en curso y no contiene información sobre las tareas antiguas.
Si Aurora DSQL no crea un índice asíncrono, el índice permanece como INVALID. Para los índices únicos, las operaciones DML están sujetas a restricciones de unicidad hasta que descarte el índice. Le recomendamos que elimine los índices no válidos y los vuelva a crear.

Creación de un índice: ejemplo

En el siguiente ejemplo se muestra cómo crear un esquema, una tabla y, a continuación, un índice.

Cree una tabla denominada test.departments.


CREATE SCHEMA test;

 CREATE TABLE test.departments (name varchar(255) primary key NOT null, 
     manager varchar(255), 
     size varchar(4));

Inserte una fila en la tabla.


INSERT INTO test.departments VALUES ('Human Resources', 'John Doe', '10')

Cree un índice asíncrono.
```
CREATE INDEX ASYNC test_index on test.departments(name, manager, size);
```
El comando CREATE INDEX devuelve un ID de trabajo, como se muestra a continuación.
```
job_id 
-------------------------- 
jh2gbtx4mzhgfkbimtgwn5j45y
```
Con job_id se indica que Aurora DSQL ha enviado un nuevo trabajo para crear el índice. Puede utilizar el procedimiento sys.wait_for_job(job_id)'your_index_creation_job_id' para bloquear otros trabajos en la sesión hasta que el trabajo finalice o se agote el tiempo de espera.

Consulta del estado de la creación del índice: ejemplo

Consulte la vista del sistema sys.jobs para comprobar el estado de creación del índice, como se muestra en el siguiente ejemplo.


SELECT * FROM sys.jobs

Aurora DSQL devuelve una respuesta similar a la siguiente.


           job_id           |   status   | details 
----------------------------+------------+---------
 vs3kcl3rt5ddpk3a6xcq57cmcy | completed  | 
 ihbyw2aoirfnrdfoc4ojnlamoq | processing |

La columna de estado puede ser uno de los siguientes valores.

`submitted`	`processing`	`failed`	`completed`
La tarea se ha enviado, pero Aurora DSQL aún no ha empezado a procesarla.	Aurora DSQL está procesando la tarea.	La tarea ha fallado. Consulte la columna de detalles para obtener más información. Si Aurora DSQL no ha podido crear el índice, no elimina automáticamente la definición del índice. Debe eliminar manualmente el índice con el comando `DROP INDEX`.	Aurora DSQL

También puede consultar el estado del índice a través de las tablas pg_index y pg_class del catálogo. En concreto, los atributos indisvalid y indisimmediate pueden indicarle en qué estado se encuentra el índice. Mientras Aurora DSQL crea el índice, este tiene un estado inicial de INVALID. La marca indisvalid del índice devuelve FALSE o f, lo que indica que el índice no es válido. Si la marca devuelve TRUE o t, el índice está listo.


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 btree_index (title) INCLUDE (name, manager, size)
 test_index1      |     t    | CREATE INDEX test_index1 ON test.departments USING btree_index (name, manager, size)

Errores en la creación de índices únicos

Si el trabajo de creación de índices únicos asíncrona muestra un estado erróneo con el detalle Found duplicate key while validating index for UCVs, esto indica que no se pudo crear un índice único debido a infracciones de restricciones de exclusividad.

Resolución de errores en la creación de índices únicos

Elimine las filas de la tabla principal que tengan entradas duplicadas para las claves especificadas en su índice secundario único.
Elimine el índice erróneo.
Emita un nuevo comando de creación de índice.

Detección de infracciones de unicidad en las tablas principales

La siguiente consulta SQL le ayuda a identificar los valores duplicados en una columna específica de la tabla. Esto resulta especialmente útil cuando se necesita imponer la unicidad en una columna que actualmente no está configurada como clave principal o que no tiene una restricción única, como las direcciones de correo electrónico en una tabla de usuarios.

Los ejemplos siguientes muestran cómo crear una tabla de usuarios de ejemplo, rellenarla con datos de ejemplo que contengan duplicados conocidos y, a continuación, ejecutar la consulta de detección.

Definición del esquema de la tabla


-- Drop the table if it exists
DROP TABLE IF EXISTS users;

-- 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
);

Inserción de datos de ejemplo que incluyan conjuntos de direcciones de correo electrónico duplicadas


-- Insert sample data with explicit IDs
INSERT INTO users (user_id, email, first_name, last_name) VALUES
    (1, 'john.doe@example.com', 'John', 'Doe'),
    (2, 'jane.smith@example.com', 'Jane', 'Smith'),
    (3, 'john.doe@example.com', 'Johnny', 'Doe'),
    (4, 'alice.wong@example.com', 'Alice', 'Wong'),
    (5, 'bob.jones@example.com', 'Bob', 'Jones'),
    (6, 'alice.wong@example.com', 'Alicia', 'Wong'),
    (7, 'bob.jones@example.com', 'Robert', 'Jones');

Ejecución de una consulta de detección de duplicados


-- 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;

Visualización de todos los registros con direcciones de correo electrónico duplicadas


 user_id |         email          | first_name | last_name |         created_at         | duplicate_count 
---------+------------------------+------------+-----------+----------------------------+-----------------
       4 | akua.mansa@example.com | Akua       | Mansa     | 2025-05-21 20:55:53.714432 |               2
       6 | akua.mansa@example.com | Akua       | Mansa     | 2025-05-21 20:55:53.714432 |               2
       1 | john.doe@example.com   | John       | Doe       | 2025-05-21 20:55:53.714432 |               2
       3 | john.doe@example.com   | Johnny     | Doe       | 2025-05-21 20:55:53.714432 |               2
(4 rows)

Si intentáramos la instrucción de creación del índice ahora, produciría un error:


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)

Aviso JavaScript está desactivado o no está disponible en su navegador.

Para utilizar la documentación de AWS, debe estar habilitado JavaScript. Para obtener más información, consulte las páginas de ayuda de su navegador.

Convenciones del documento

Claves principales

Tablas y comandos del sistema