Transactional DDL TECH PREVIEW

Learn how YugabyteDB handles DDLs in a transaction

Data Definition Language (DDL) statements modify the structure of your data. PostgreSQL allows executing such statements within a transactional block and supports rolling them back as part of a transaction rollback. For two transactions undergoing conflicting DDLs, it also provides isolation guarantees by taking appropriate object locks during DDL processing.

YugabyteDB's transactional DDL provides similar guarantees for rolling back DDL operations done inside a transaction block. However, because object locks are still under active development, isolation guarantees are weaker than in PostgreSQL. See Limitations for more information.

Enable transactional DDL

TP Support for transactional DDL is disabled by default, and to enable the feature, set the yb-tserver flag ysql_yb_ddl_transaction_block_enabled to true.

Because ysql_yb_ddl_transaction_block_enabled is a preview flag, to use it, add the flag to the allowed_preview_flags_csv list (that is, allowed_preview_flags_csv=ysql_yb_ddl_transaction_block_enabled).

Rollback capabilities

All DDLs supported in YugabyteDB provide the same rollback capabilities as PostgreSQL. These include DDLs on tables, indexes, roles, and materialized views.

Note that some DDL statements such as DDLs on database or tablespaces are disallowed in a transaction block in PostgreSQL, and are also disallowed in YugabyteDB.

The following example demonstrates how DDL statements, such as ALTER TABLE, behave in a PostgreSQL-compatible transaction in YugabyteDB. It highlights the atomicity of transactions, where all changes (both DML and DDL) are either committed together or entirely rolled back.

yugabyte=# CREATE TABLE foo (bar int);
yugabyte=# INSERT INTO foo VALUES (1);
yugabyte=# BEGIN;
yugabyte=*# INSERT INTO foo VALUES (2);
yugabyte=*# ALTER TABLE foo ADD COLUMN name text;
yugabyte=*# INSERT INTO foo VALUES (3, 'test');
yugabyte=*# SELECT * FROM foo;

 bar | name
-----+------
   1 |
   2 |
   3 | test
(3 rows)

yugabyte=*# ROLLBACK;
yugabyte=# SELECT * FROM foo;

 bar
-----
   1
(1 row)

Limitations

Concurrent DDLs on the same database are unsupported and will lead to conflict and read restart required errors. Your applications must handle these by retrying the statements.
Savepoints are unsupported for DDL statements. As a result, you cannot create a savepoint in a transaction block that has executed a DDL statement. Similarly, you cannot execute a DDL statement in a transaction block in which a savepoint has been created.

For an overview of common concepts used in YugabyteDB's implementation of distributed transactions, see Distributed transactions.