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

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(dot)klaver(at)aklaver(dot)com