Open ndeodhar opened 5 years ago
This is being used by Hasura also IIRC, so good feature to add!
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?
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.
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.
Very much needed feature for intensely evolving DB schema. Any plans to implement this in the near future?
can we vote this issue up a bit ? Yugabyte is not usable when creeate/drop table operations can cause incosistent states for the database
The task just links back to this thread. The lack of this feature renders Yugabyte unusable for every projects that rely on automigrations
@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.
Great to hear, but it still does not work. I picked up on the good news and started a quick 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.
@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.
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
Hi, guys, for this problem, do you have any solutions? Temporary
No unfortunateley not, this missing feature renders yugabyte unusable for us sadly. We are waiting on it one day :)
Jira Link: DB-1367
In YSQL DDL execution mostly follows the same logic and code as in Postgres. However, there are several additional complexities
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:
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.
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.