Understanding data definition language (DDL) in Aurora DSQL

PDFRSS

Aurora DSQL features a Multi-AZ distributed and shared-nothing database layer built on top of multi-tenant compute and storage fleets. Because there isn't a single primary database node or leader, the database catalog is distributed, and schema changes are managed as distributed transactions. As such, there are a few ways in which DDL behaves differently in Aurora DSQL than PostgreSQL.

• Aurora DSQL throws a concurrency control violation error if you run one transaction while another transaction updates a resource. Consider the following example.

  • Create table foobar in session 1.

  • After Aurora DSQL creates the table foobar, you run the statement SELECT * from foobar in session 2. Aurora DSQL returns with the error SQL Error [40001]: ERROR: schema has been updated by another transaction, please retry: (OC001).

    Note

    During preview, there is a known issue that increases the scope of this concurrency control error to all objects within the same schema/namespace.

• Transactions in Aurora DSQL can contain only one DDL statement and can't have both DDL and DML statements. For example, you can't create a table and insert data into the same table within the same transaction.

  For example, Aurora DSQL supports the following statements.

  BEGIN;
  CREATE TABLE FOO (ID_col integer);
  COMMIT;

  BEGIN;
  INSERT into FOO VALUES (1);
  COMMIT;

  Aurora DSQL doesn't support the following.

  BEGIN;
  CREATE TABLE FOO (ID_col integer);
  INSERT into FOO VALUES (1);
  COMMIT;

• Finally, Aurora DSQL runs DDL statements asynchronously. This means that changes to large tables, such as adding an index or modifying a column, can run without downtime or performance impact. For more information about Aurora DSQL's asynchronous job manager, see Creating async indexes in Aurora DSQL.