1

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ugur Yilmaz
  • 469
  • 6
  • 17

1 Answers1

2

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.

Bill Weiner
  • 8,835
  • 2
  • 7
  • 18
  • I get the following error: "ERROR: current transaction is aborted, commands ignored until end of transaction block [ErrorId: 1-63efdb42-5ac7af715d8a0a573c2fefa7]" – dzhukov Feb 17 '23 at 19:56
  • So that just means that some command in the transaction block has errored so all commands are ignored until an ROLLBACK, END, or COMMIT. That just means something else had an error is not the causing event. You need to look earlier in the messages for what originally errored. – Bill Weiner Feb 17 '23 at 22:48
  • This code returns an error that the cursor does not exist. I wonder if something has changed. – Robert Riley Mar 06 '23 at 18:30
  • Is you connection in “autocommit” mode? Cursors only last for the duration of a transaction. These commands need to run without a commit coming between the call and the fetch – Bill Weiner Mar 07 '23 at 10:55
  • @RobertRiley did you find the solution? spent almost 2 days trying to sort this. even made changes to bench to make it manual transaction. I believe END inside the SP closes the cursor. – just10minutes Apr 14 '23 at 05:17
  • I decided to just write to a log table instead of print the result. – Robert Riley Apr 19 '23 at 14:14