Re: Procedure support improvements

From: Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>
To: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, Dave Cramer <pg(at)fastcrypt(dot)com>
Cc: Greg Nancarrow <gregn4422(at)gmail(dot)com>, David Rader <david(dot)rader(at)gmail(dot)com>, pgsql-jdbc(at)lists(dot)postgresql(dot)org, pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Re: Procedure support improvements
Date: 2019-08-28 16:44:54
Message-ID: 99b87249-d08c-8082-340e-84273150b59e@2ndquadrant.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-jdbc

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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message David Fetter 2019-08-28 16:47:50 Re: RFC: seccomp-bpf support
Previous Message Peter Eisentraut 2019-08-28 16:43:10 Re: Email to hackers for test coverage

Browse pgsql-jdbc by date

  From Date Subject
Next Message Dave Cramer 2019-08-29 15:14:02 [pgjdbc/pgjdbc] bda74d: Log ignoring rollback when no transaction in progr...
Previous Message Dave Cramer 2019-08-28 13:23:04 [pgjdbc/pgjdbc] f89e62: fix: In logical decoding the if the backend was re...