From: | andyterry <andrew(dot)terry(at)centremaps(dot)co(dot)uk> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | commit within a procedure loop - cannot commite with subtransaction |
Date: | 2019-01-21 12:43:11 |
Message-ID: | 1548074591211-0.post@n3.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2019-01-21 15:15:05 | Re: commit within a procedure loop - cannot commite with subtransaction |
Previous Message | Rangaraj G | 2019-01-21 11:24:07 | Memory and hard ware calculation : |