On 1/21/19 4:43 AM, andyterry wrote:
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?
1) This is Postgres 11, correct?
2) I have to believe the issue is the:
PERFORM my_functions.processing_function(grd_geom);
https://www.postgresql.org/docs/11/plpgsql-transactions.html
"...But if the call stack is CALL proc1() → SELECT func2() → CALL
proc3(), then the last procedure cannot do transaction control, because
of the SELECT in between."
3) What is my_functions.processing_function() doing?
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
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
