[YSQL] YSQL DDL Atomicity

[deeps1991]

Jira Link: DB-2996

Description

Currently YSQL table metadata is stored in both the PG catalog and DocDB sys-catalog. This ticket tracks building infrastructure to roll-back any DocDB side schema changes if the DDL transaction is aborted.

The basic idea is to update the DocDB schema and PG catalog in a 2 phase manner. We bind the DocDB schema change to its corresponding YSQL transaction, maintain both the previous and the in-flux changes in the schema and poll the status of the YSQL transaction. Just two versions of schema is sufficient, because we do not support Concurrent DDL on a table. Once the transaction completes, based on the transaction status, roll-back or roll-forward the changes in the DocDB schema such that they are both consistent.

Preview/Early Adoption

Status	Task	Comments
✅	Part 1: YB-Master BG Task for DDL Verification	DocDB schema changes are bound to a YSQL DDL transaction id. A YB-Master background task is started up for each such DDL, which polls the txn status, compares both schemas and resolves inconsistencies without affecting concurrent DML. Support DROP TABLE, ALTER TABLE ADD COLUMN, Rename of Table/Column
✅	Part 2: YSQL sends DDL status to YB-Master	YSQL sends the transaction status to the YB-Master at the end of every DDL transaction, eliminating the need for the expensive YB-Master background task in non-crash scenarios
✅	Part 3: Rollback ALTER TABLE DROP COLUMN	Drop Column should happen in 2 phases - first mark the column for deletion and prevent concurrent accesses to the column. After the DDL transaction commits, perform actual cleanup of the data in the column. Otherwise, the delete marker is rolled-back.
✅	Part 4: Support Rollback for Indexes, Tablegroups, Colocated Tables and Materialized Views	Extend Rollback support from just regular tables to for Tablegroups, Colocated Tables, Materialized Views and Indexes
✅	Part 5: YB-Backup Support	YB-Backup script must be able to handle tables that are undergoing transaction verification at the time of backup
✅	Part 6: Support Sequences	#16309

GA

Status	Task	Comments
⬜️	Part 7: 2-phased Rollback of DROP TABLE/INDEX	Like DROP COLUMN, implement DROP TABLE/INDEX in 2 phases. First mark the table/index for deletion and prevent concurrent accesses to the table/index. After the DDL transaction commits, perform actual cleanup of the data in the table/index.
⬜️	Part 8: YB-Admin tool to fix inconsistent schemas	YB-Admin support that can help identify and resolve any tables that have mismatched schemas
⬜️	Part 9: Handle Upgrade	Handle the case where a cluster already has tables that have inconsistent DocDB metadata and PG catalog metadata so that future operations that compare PG catalog and DocDB schema do not fail.
⬜️	Part 10: Support concurrent PITR and DDL Rollback	Once #14679 lands, support PITR + Rollback running in parallel
⬜️	Part 11: YB-Master identifies stuck rollback tasks	In extremely rare cases, we may fail scheduling the YB-Master background task. Add a task to the catalog manager to identify such stuck DDL verification tasks and resume them.

Longer Term/Post GA

Status	Task	Comments
⬜️	Support rolling back other forms of Alter	Support for altering the schema, pg_type.
⬜️	Rework Tablespaces	Set the tablespace id in the DocDB schema rather than using a periodic background task to read the PG catalog
⬜️	Support Rollback of DDL in Savepoints	Support ROLLBACK TO SAVEPOINT when the save point contains DDL operations

[adil246]

Adding other folks who might be interested in this issue @phendric-uk @jasonriddell