9

I'm trying to learn PostgreSQL stored procedures. Specifically creating the procedure below in PSQL.

CREATE OR REPLACE PROCEDURE BUILD_AND_POPULATE(INOUT cresults refcursor) 
    LANGUAGE PLPGSQL 
    AS $$
    BEGIN
        BEGIN; -- I've tried removing this but the behaviour is the same
        cresults:= 'cur';
        DROP TABLE IF EXISTS procsampledata;
        CREATE TABLE procsampledata as select x,1 as c2,2 as c3, md5(random()::text) from generate_series(1,10) x;  
        COMMIT;
        OPEN cresults FOR SELECT * FROM procsampledata;  
    END;
$$;

Then I execute it like so, but receive an error:

postgres=# call build_and_populate(null);
ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function build_and_populate(refcursor) line 6 at COMMIT

I've tried with AUTOCOMMIT set to both on and off.

This is my Postgres version

 PostgreSQL 11.5 on x86_64-pc-linux-musl, compiled by gcc (Alpine 8.3.0) 8.3.0, 64-bit

Can anyone see what I'm doing wrong? Thank you!

Improve this question

2 Answers 2

Reset to default
10

The

BEGIN;

is wrong and will cause an error. You cannot start a transaction inside a procedure, because there is already an active transaction.

You can end a transaction, which implies that a new transaction is started immediately.

Without the BEGIN;, your procedure works just fine.

The problem must be with the way you are calling it. As the documentation says:

Transaction control is only possible in CALL or DO invocations from the top level or nested CALL or DO invocations without any other intervening command. For example, if the call stack is CALL proc1() → CALL proc2() → CALL proc3(), then the second and third procedures can perform transaction control actions. 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.

There is probably a SELECT in your call stack.

Another possibility is that you explicitly started a transaction with BEGIN before calling the stored procedure.

This does not work either, which is an undocumented implementation restriction that may be fixed in the future. See this thread for reference.

Improve this answer
3
  • 2
    Thanks for that Laurenz. I think that you might be on to something. I re-removed that BEGIN; statement, and also commented out the select statements in the procedure to see if they were affecting it. They made no difference. I don't think there's a SELECT in my call stack in that I have no call stack as far as I know. I create the procedure, call BEGIN; then CALL. One thing I notice, it ONLY fails if I execute BEGIN; before CALL. Is that the issue? If so, then does that mean it's not possible to return a cursor from a procedure as (in my understanding) they don't survive past their transaction Commented Sep 24, 2019 at 3:59
  • 1
    Yes, that is the reason. I have extended the answer with an explanation. Commented Sep 24, 2019 at 4:58
  • Thanks very much! I've been going around in circles with this for a while. It's good to finally get my head around it. Commented Sep 24, 2019 at 5:08
3

After establishing that BEGIN; is not allowed in a procedure and that the client-side CALL must not occur from an already started transaction, the question boils down to (as you wrote as a comment):

"does that mean it's not possible to return a cursor from a procedure as (in my understanding) they don't survive past their transaction"

It's still possible. There are two kinds of CURSORs: WITH HOLD outlive transactions and WITHOUT HOLD (the default) don't. You may point a refcursor variable to a WITH HOLD cursor in plpgsql with a bit of dynamic SQL. Here's a modified version of your procedure doing that:

CREATE OR REPLACE PROCEDURE public.build_and_populate(INOUT cresults refcursor)
 LANGUAGE plpgsql
AS $procedure$
    BEGIN
        DROP TABLE IF EXISTS procsampledata;
        CREATE TABLE procsampledata as select x,1 as c2,2 as c3, md5(random()::text) from generate_series(1,10) x;
        COMMIT;
        EXECUTE 'DECLARE ' || quote_ident(cresults::text) || ' CURSOR WITH HOLD FOR SELECT * FROM procsampledata';                                               
    END;
$procedure$

postgres=# call BUILD_AND_POPULATE('res');
 cresults 
----------
 res
(1 row)

postgres=# fetch all from res;
 x  | c2 | c3 |               md5                
----+----+----+----------------------------------
  1 |  1 |  2 | 11a3d2e637332a25118c2f4d5dac49c0
  2 |  1 |  2 | 4c5b9cb5ec79479a9daa9ae7a131a078
  3 |  1 |  2 | 320b49912e94de90c3370836706c5494
  4 |  1 |  2 | a9ccf45dbbbbec19d18b69cf4a0fc26b
  5 |  1 |  2 | 1a52801f1761cb0e357a4468803473e1
  6 |  1 |  2 | 96c395dad11ee19526bc08b5ad114905
  7 |  1 |  2 | 7d0da9a0cf2520ee871185677d7062cf
  8 |  1 |  2 | 65ecf1e68806a0b9133fa0e47056574e
  9 |  1 |  2 | bae5c688e1ef6b9c389b37ecc7a18342
 10 |  1 |  2 | ed07142f3b51a282c05370890a8af468
(10 rows)

postgres=# close res;
CLOSE CURSOR
Improve this answer
1
  • Thanks Daniel, that’s great! Commented Sep 24, 2019 at 12:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.