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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
