AWS Aurora based on PG 13 I am writing a sproc to copy a schema into another. Here is the relevant portion of the code. Basically I want to commit after every table is created. In big schemas with hundreds of table I do not want to run entire operation in one transaction.
I am getting error at COMMIT -> cannot commit while a subtransaction is active. Is commit not possible in a loop BEGIN FOR object IN SELECT table_name::text FROM information_schema.TABLES WHERE table_schema = source_schema AND table_type = 'BASE TABLE' LOOP buffer := dest_schema || '.' || object; BEGIN sql_stmt := 'CREATE TABLE ' || buffer || ' (LIKE ' || source_schema || '.' || object; sql_stmt := sql_stmt || ' INCLUDING COMMENTS INCLUDING IDENTITY INCLUDING CONSTRAINTS INCLUDING DEFAULTS INCLUDING GENERATED INCLUDING INDEXES)' ; --RAISE NOTICE '%' , sql_stmt ; execute sql_stmt ; COMMIT; sql_stmt := 'INSERT INTO ' || buffer || ' OVERRIDING SYSTEM VALUE SELECT * FROM ' || source_schema || '.' || object ; --RAISE NOTICE '%' , sql_stmt ; execute sql_stmt ; COMMIT; EXCEPTION WHEN OTHERS THEN GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT, text_var2 = PG_EXCEPTION_DETAIL, text_var3 = PG_EXCEPTION_HINT; print_msg := 'ERROR:->' || text_var1 || '|' || text_var2 || '|' || text_var3 ; RAISE NOTICE '%' , print_msg ; END ; END LOOP; END;
