COMMIT IN STORED PROCEDURE WHILE IN A LOOP

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;

Responses

Browse pgsql-general by date

  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