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

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

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@aklaver.com

>>>>> "andyterry" == andyterry  <andrew.terry@centremaps.co.uk> writes:

 andyterry> Hi,

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

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

What client are you using to execute this? Some clients may insert
SAVEPOINT statements (which create subtransactions) behind your back
(e.g. psql with \set ON_ERROR_ROLLBACK does this) in order to recover
from errors without aborting the whole transaction.

(turn on log_statement=all in your config, or for the user you're
executing this as, and look for the command in the server log)

-- 
Andrew (irc:RhodiumToad)

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

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