From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | andyterry <andrew(dot)terry(at)centremaps(dot)co(dot)uk>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: commit within a procedure loop - cannot commite with subtransaction |
Date: | 2019-01-21 15:15:05 |
Message-ID: | 7422ca90-de58-2bad-61e7-2365385829ca@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Gierth | 2019-01-21 16:03:32 | Re: commit within a procedure loop - cannot commite with subtransaction |
Previous Message | andyterry | 2019-01-21 12:43:11 | commit within a procedure loop - cannot commite with subtransaction |