Redshift cursor doesn't exist after creating a stored procedure

Question

I was following the aws redshift documentation to create a stored procedure and return the data results by using a cursor. It throws an error saying cursor doesn't exist even though I do exactly same as the documentation. Not sure if I'm missing a step or I need to turn on some settings in my redshift instance for my username.

Here's my query;

CREATE OR REPLACE PROCEDURE test_procedure (param IN varchar(max), rs_out INOUT refcursor)
AS $$
BEGIN
  OPEN rs_out FOR SELECT * FROM test_db where id=param;
END;
$$ LANGUAGE plpgsql;

BEGIN;
CALL test_procedure('id_1','mycursor');
FETCH ALL FROM mycursor;
COMMIT;

Here's the aws documentation for reference; https://docs.aws.amazon.com/redshift/latest/dg/stored-procedure-result-set.html

Can you confirm that your bench isn't in "autocommit" mode? This being set will break you out of your transaction and close the cursor — Bill Weiner
I tried both auto and manual transaction modes... It didn't make any difference @BillWeiner — Ugur Yilmaz

Bill Weiner Bill Weiner · Accepted Answer · 2021-06-21T17:47:09

I ran your code (name changes so you can run it too) and things work just fine.

code:

drop table if exists test_proc;
create table test_proc ( txt varchar(32), num int, id varchar(8));
insert into test_proc values ('this is text', 5, 'id_1');
insert into test_proc values ('this is also text', 6, 'id_2');
commit;

select * from test_proc;

CREATE OR REPLACE PROCEDURE test_procedure (param IN varchar(max), rs_out INOUT refcursor)
AS $$
BEGIN
  OPEN rs_out FOR SELECT * FROM test_proc where id=param;
END;
$$ LANGUAGE plpgsql;

BEGIN;
CALL test_procedure('id_1','mycursor');
FETCH 1000 FROM mycursor;
COMMIT;

select * from svl_statementtext where pid = pg_backend_pid() order by starttime desc limit 100;

I don't think the issue is with the code but rather with your transaction ending too soon. You can try running this code to see if you get different results but I expect you will have issues.

Notice the last statement? This will list all commands issues by the session (pid). What is important is that CALL and FETCH (which will show up as " SELECT * FROM test_proc where id= $1" - with the leading space) have the same XID (transaction id). If they don't then the cursor will not exist. In this case you will also see a COMMIT between these statement which is causing the transaction to close. This is a sign that your bench is connecting to Redshift in "autocommit" mode and inserting a commit at every ';'. How to configure your bench to not be in autocommit mode varies by bench.

Redshift cursor doesn't exist after creating a stored procedure

1 Answers