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
>