Transactional DDL EARLY ACCESS

Learn how to roll back DDL statements in a transaction

YugabyteDB can roll back DDL statements that run inside a transaction block. All DDLs supported in YugabyteDB provide the same rollback capabilities as PostgreSQL. These include DDLs on tables, indexes, roles, and materialized views.

For more information and current limitations, see Transactional DDL.

Roll back a DDL statement

Before you start

This example requires transactional DDL (disabled by default). Create and connect to a YugabyteDB universe, then enable ysql_yb_ddl_transaction_block_enabled as described in Enable transactional DDL.

The following example shows an ALTER TABLE inside a transaction. The insert and the schema change commit together, or they both roll 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)

To speed up bulk loads into tables created in the same transaction, see Faster writes to new tables. That optimization's transaction-block support depends on transactional DDL.