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
entireoperation 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
DEFAULTSINCLUDING 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;