Обсуждение: Very big transaction in a stored procedure : how can i commit in the middle of it ?

Hi,

I already know that transaction is impossible inside a function, but I think I really need a way to counter this

I have a stored procedure in pl/sql that makes about 2 000 000 insert. With the way it works, PostGreSQL il making a unique transaction with all this, resulting so bad performances I can’t wait the procedure to finish

I must find a way to make commit between INSERT.

Thanks in advance,
Célestin

Here is the skeleton of my code :

CREATE OR REPLACE FUNCTION F2(…) AS

$$ DECLARE

…

BEGIN

            …

            FOR ligne IN …

                        …

            LOOP

                        NSERT INTO

                        < place I wish I could put a commit >

            END LOOP

            …

END $$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION F1(…) AS

$$ DECLARE

BEGIN

            FOR all IN …

            LOOP

                        PERFORM F2(…)

                        < another place I could put my commit >

END LOOP

            …

END $$ LANGUAGE plpgsql;