Re: Procedure support improvements

Peter Eisentraut <[email protected]> Wed, 28 Aug 2019 18:44:54 +0200

Newsgroups	gmane.comp.db.postgresql.devel.general,gmane.comp.db.postgresql.jdbc
Organization	2ndQuadrant
Message-ID	<[email protected]>

On 2019-08-26 20:08, Laurenz Albe wrote:
> test=> CREATE OR REPLACE PROCEDURE testproc() LANGUAGE plpgsql AS
>        $$BEGIN PERFORM 42; COMMIT; PERFORM 'x'; END;$$;
> CREATE PROCEDURE
> test=> CALL testproc();
> CALL
> test=> BEGIN;
> BEGIN
> test=> CALL testproc();
> ERROR:  invalid transaction termination
> CONTEXT:  PL/pgSQL function testproc() line 1 at COMMIT
> 
> Oops.
> I find that indeed surprising.
> 
> What is the rationale for this?

It's mostly an implementation restriction.  You would need to teach
SPI_commit() and SPI_rollback() to manipulate the top-level transaction
block state appropriately and carefully.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services