From: | Ravi Krishna <s_ravikrishna(at)aol(dot)com> |
---|---|
To: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | COMMIT IN STORED PROCEDURE WHILE IN A LOOP |
Date: | 2022-10-18 20:14:05 |
Message-ID: | 353091661.109766.1666124045054@mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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;
From | Date | Subject | |
---|---|---|---|
Next Message | Christophe Pettus | 2022-10-18 20:16:29 | Re: COMMIT IN STORED PROCEDURE WHILE IN A LOOP |
Previous Message | Robert Haas | 2022-10-18 19:18:28 | Re: Exponentiation confusion |