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;

2007 - Maporama International - Outgoing mail scanned by BlackSpider