[YSQL] "ERROR: Declare Cursor Variable not supported yet" stops me implementing my use-case

[bllewell]

Using 11.2-YB-2.0.9.0-b0 on Ubuntu 18.04.

This compilation attempt is enough to demonstrate the restriction:

\set VERBOSITY verbose
create  procedure p()
  language plpgsql
as $body$
declare
  cur refcursor;
begin
  null;
end;
$body$;

The attempt causes this error:

ERROR:  0A000: Declare Cursor Variable not supported yet
LINE 5:   cur refcursor;
          ^
HINT:  Please report the issue on https://github.com/YugaByte/yugabyte-db/issues
LOCATION:  ybc_not_support, pl_gram.y:4119

I've copied the text of my intended function at the end so that you can see why it benefits from dynamic SQL. I developed and tested it in vanilla PG 11.2 where (of course) it works fine.

At least I have a choice of two workarounds:

(1) Use the list_agg() built-in in the select list so that the query returns a single result, use the simple execute Stmt into result (which I have shown works) and step through the array of records programmatically. This is, anyway, a generally useful way to implement "bulk collect" (as Oracle Database calls it and for which plpgsql has no corresponding functionality). Only thing is, in YB it performs worse than a straight "cursor for loop" unless the result set is large. (The break-even comes at about 500 rows.) In PG it never harms performance, and the benefit kicks in with much smaller result sets.

(2) Define the required number of static cursors and choose the one to open with a case statement. This is no good in general, because not all the facts are at hand until run time. It also suffers from the combinatorial explosion effect, so it's viable (but tedious to program and QA) only when the number of, and cardinality of, the degrees of freedom increases. In my use case it's 2*3 (two tables and three possible ratios).

The real function

create or replace function speed_ratios(table_name in varchar, which_ratio in int)
  returns table(n int, t varchar)
  language plpgsql
as $body$
declare
  quot constant varchar(1) not null := '''';

  cur refcursor;
  qry_template constant varchar not null := '
    select
      to_char(nr_results_pr_query, ''99999999'') ||
      to_char(?ratio, ''99999999990.99'')||''x''
    from "?table"
    order by nr_results_pr_query';

  ratio_1 constant varchar not null := 'cursor_for_loop_time_avg / array_agg_plpgsql_time_avg';
  ratio_2 constant varchar not null := 'cursor_for_loop_time_avg / array_agg_sql_time_avg';
  ratio_3 constant varchar not null := 'array_agg_sql_time_avg / array_agg_plpgsql_time_avg';
  ratio constant varchar not null :=
    case which_ratio
      when 1 then ratio_1
      when 2 then ratio_2
      when 3 then ratio_3
    end;
begin
  n := 0;
  declare
    qry varchar not null :=
      replace(replace(qry_template, '?table', table_name), '?ratio', ratio);
  begin
    open cur for execute qry;
    loop
      fetch cur into t;
      exit when not found;
      n := n + 1; return next;
    end loop;
    close cur;
  end;

  for j in 1..2 loop
    t := '';
    n := n + 1; return next;
  end loop;
end;
$body$;

[acoliver]

Also kills VSCode

[Shubham3453]

hi @ddorian ,

i also got an Error

i was taking dump in yugabyte with --insert parameter  but got an Error at the end of dump.
/home/ist/yugabyte-2.2.0.0/postgres/bin/ysql_dump --host 127.0.0.1 --port 5433 --username yugabyte --insert --verbose --file "/home/ist/dump_workflowmanagement.sql" --schema=workflowmanagement    --dbname=ABCD

ysql_dump: [archiver (db)] query failed: ERROR:  DECLARE CURSOR not supported yet
LINE 1: DECLARE _pg_dump_cursor CURSOR FOR SELECT * FROM ONLY workfl...
        ^
HINT:  Please report the issue on https://github.com/YugaByte/yugabyte-db/issues
ysql_dump: [archiver (db)] query was: DECLARE _pg_dump_cursor CURSOR FOR SELECT * FROM ONLY workflowmanagement.act_ge_bytearray

[xvaara]

Do you have plans to implement cursor support?

I think it would also allow to use postgres_fdw to connect to yugabytedb from regular postgres.

[kmuthukk]

@xvaara -- Cursor support's implementation is nearing completion (in the final leg of code reviews/testing).

[nocaway]

Commit 9d66392 added support for cursor. Our next releases will have this work. However, there are a few pending issues.

Issue #6627 is under-development. We will fix this issue ASAP.

We don't yet have a specific schedule planning for the following issues.

• Issue [YSQL] Scanning snapshots for CURSOR is set at different TIMESTAMP between Postgres and YugaByte DBMS #6629
• Issue [YSQL] Positional and Directional FETCH Not Yet Supported #6514
• Issue [YSQL] Locking rows when using CURSOR not yet supported #6541