Re: in Pl/PgSQL, do commit every 5000 records

I got the answer. Although the compile passed, when it reaches 5000, the
commit command causes a "SPI_ERROR_TRANSACTION" exception.

Thank you for all your hint.

>> You can't do commits inside of a function.
>
>
> The example I have is:
>
> CREATE OR REPLACE function test() returns boolean AS $$
> DECLARE
> ... ...
> counter INTEGER := 0;
> BEGIN
> ... ...
> query_value := ' .....' ;
> OPEN curs1 FOR EXECUTE query_value;
> LOOP
> FETCH curs1 INTO studid;
> EXIT WHEN NOT FOUND;
>
> query_value := ' INSERT INTO ... ...';
> EXECUTE query_value ;
>
> counter := counter + 1 ;
> IF counter%5000 = 0 THEN
> counter := 0;
> COMMIT;
> END IF;
>
> END LOOP;
>
>
> CLOSE curs1; ...
> END;
>
> ... ...
>
> The above function works ok.
> "can't do commits inside of a function " , do you mean although the
> function complied ok and run successfully, but it did not really
> commit insertion actions at every 5000 records?
>
>> I think you are misremembering advice about not do inserts with a
>> transaction per row which will have
>> a lot of overhead for all of the commits
>