commit within a procedure loop - cannot commite with subtransaction

[andyterry]

Hi,

Using a procedure for the first time to run some processing for each row in
a table, generating output to a target table. The following works without
COMMIT the example below gives:

INFO: Error Name:cannot commit while a subtransaction is active
INFO: Error State:2D000

Could someone point me in the right direction so i can understand why and
how i might rework my methodology?


CREATE OR REPLACE PROCEDURE my_functions.first_procedure(
)
LANGUAGE 'plpgsql'

AS $BODY$

DECLARE

grd_geom geometry(Polygon,27700);
grd_gid integer;
rec data.areas%rowtype;

BEGIN

DELETE FROM data.output;
DELETE FROM data.temp_output;

FOR rec IN SELECT * FROM data.areas
LOOP
grd_geom := rec.geom;
grd_gid := rec.gid;

PERFORM my_functions.processing_function(grd_geom);
DELETE FROM data.temp_output;
COMMIT;
END LOOP;
RETURN;

 

END;

$BODY$;

GRANT EXECUTE ON PROCEDURE my_functions.first_procedure() TO postgres;
GRANT EXECUTE ON PROCEDURE my_functions.first_procedure() TO PUBLIC;

Thanks

Andy



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: commit within a procedure loop - cannot commite with subtransaction

[andyterry]

Thanks Adrian,

It is PostgreSQL 11.
The procedure listed is calling one postgres function which is in turn
calling another postgres function but no additional procedure, just
functions.
It's a bit lengthy to explain what the functions are doing so i'll try
testing with a less complex task i think so i can narrow things down.

Appreciate your response

Andy




--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: commit within a procedure loop - cannot commite with subtransaction

[andyterry]

Thanks Andrew,

The database is currently chewing through the load (without commits) as i'm
doing a batch of rows at a time.

I'm calling the procedure in pgadmin (4-4.2) as i was testing some bits from
there but i'll also try a psql run

Appreciate the pointers, Andy



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html