[YSQL] Support DDL operations within transaction

[ndeodhar]

Jira Link: DB-1367

In YSQL DDL execution mostly follows the same logic and code as in Postgres. However, there are several additional complexities

1. the catalog metadata need to be safely replicated across multiple nodes and caches on all nodes appropriately invalidated
2. storage (DocDB) objects (e.g. tablets and associated SST files, Raft groups, etc.) need to be created and modified
   This tracking issue covers the roadmap to getting Postgres-compatible behavior with respect to DDL transactions.

Phase 1: Limited Transactional DDL

In this stage, Postgres metadata changes are run in an autonomous transaction for each DDL statement. This means standalone DDL statements behaves essentially like postgres when it comes to the Postgres catalog modifications.
However:

1. In specific cases, DocDB object modifications could fail without getting rolled back. These are done at the end (just before DDL transaction commit), but it is still possible for a failed DDL to not fully roll back DocDB changes (and require manual SQL commands to repair).
2. For DDLs within transaction blocks, autonomous transaction means that in case the transaction block is rolled back, all executed DDL statements remain applied.

Status	Task	Comments
✅	[YSQL] Limited transactional DDL: execute each DDL statement in its own separate transaction #3108	Done in b68b84b

Phase 2: Full DDL Atomicity

In this stage, storage (DocDB) object modifications are also associated with the DDL transaction and are automatically rolled back in case the parent DDL transaction fails for any reason. So this addresses limitation 1. above.

Status	Task	Comments
✅	[YSQL] YSQL DDL Atomicity #13358	Done in multiple commits. Turned on by default in #22097.

Phase 3: Full transaction DDL

Transaction block of DDL and DML statements execute in the same transaction (like in Postgres). This addresses limitation 2. above.

Status	Task	Comments
🕓	[YSQL] Full transactional DDL: allow arbitrary mix of DDL and DML in transactions #3109	Currently in progress.

[rkarthik007]

This is being used by Hasura also IIRC, so good feature to add!

[sorenisanerd]

If not slated for v2.0, when can we expect to see this. Transactional DDL is a key feature of Postgres over e.g. Oracle.

What happens if I attemt to execute DDL inside a transaction with current yugabyte?

[sorenisanerd]

Does yugabyte suffer from this specific limitation, too? https://www.cockroachlabs.com/docs/stable/online-schema-changes.html#schema-change-ddl-statements-inside-a-multi-statement-transaction-can-fail-while-other-statements-succeed

It violates the promises of a transaction.

[sf-kansara]

A workaround for this would be to do DDL changes inside BEGIN block and "undo" all those changes in EXCEPTION block.

-- Setup
CREATE TABLE public.test_table_2
(
    id SERIAL,
    name VARCHAR(30),
    CONSTRAINT test_table_2_pkey PRIMARY KEY (id)
);
INSERT INTO public.test_table_2(name) VALUES ('John Doe');
INSERT INTO public.test_table_2(name) VALUES ('John Dough');

DDL change management

DO $$ 
BEGIN
	INSERT INTO public.test_table_2(name) VALUES ('abc');
	ALTER TABLE public.test_table_2 ADD COLUMN new_column VARCHAR(2) DEFAULT NULL;
	INSERT INTO public.test_table_2(name, new_column) VALUES ('abc', 'abcdefgh');
EXCEPTION WHEN OTHERS THEN
	ALTER TABLE public.test_table_2 DROP COLUMN new_column;
	RAISE EXCEPTION USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
END $$;

Notice that first "legal" insert is also rolled back and we manually reverted DDL changes in EXCEPTION block.

[sybbear]

Very much needed feature for intensely evolving DB schema.
Any plans to implement this in the near future?

[78bits]

can we vote this issue up a bit ? Yugabyte is not usable when creeate/drop table operations can cause incosistent states for the database

[78bits]

The task just links back to this thread. The lack of this feature renders Yugabyte unusable for every projects that rely on automigrations

[mrajcevic01]

@78bits @sybbear @sorenisanerd we have updated this issue with additional insights into the work we have been putting into the phased approach of supporting DDL operations within transactions. Please let us know if you have additional questions.

[78bits]

Great to hear, but it still does not work. I picked up on the good news and started a quick test :

1. firing up a yugabyte instance from docker (version version 2.21.0.1 build 1)
2. running this query
   ``create schema test;

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

rollback;
begin;

drop table if exists test.value_types cascade;
CREATE TABLE test.value_types (
id UUID NOT NULL DEFAULT uuid_generate_v4(),
type VARCHAR NOT NULL,
description VARCHAR NOT NULL,
CONSTRAINT "value_types_primary" PRIMARY KEY (id)
);

INSERT INTO
test.value_types (type, description)
VALUES
('int', 'Integer -2^32 .. +2^32'),
('float', 'Floating value'),
('string', 'String value');

drop table if exists test.entries;
CREATE TABLE test.entries (
id UUID NOT NULL DEFAULT uuid_generate_v4() PRIMARY KEY,
type_id UUID NOT null REFERENCES test.value_types (id)
);

commit;

select * from test.entries;```
The transaction fails, but the tables are created anyhow.

[mrajcevic01]

@78bits did you enable the flags called out #22097 before attempting this? I am reaching out internally to confirm but it does not appear that these will be enabled by default until v2024.1 (which should be available here soon)

EDIT: I was able to confirm that we enabled this by default in v2024.1. We do not recommend using it in v2.20+ because some recent bug fixes were only made in 2024.1 but not backported earlier.

[78bits]

Adding those flags (--ysql_yb_ddl_rollback_enabled,--report_ysql_ddl_txn_status_to_master,--ysql_ddl_transaction_wait_for_ddl_verification) made the script work. Thank you