plpgsql: nested blocks are unaware of each other's cursors

[DrewKimball]

A PL/pgSQL block has to be aware of all cursors created within its scope, including those created within nested blocks. Currently, this is not the case, as demonstrated in the following logic test:

statement ok
CREATE FUNCTION f(n INT) RETURNS INT AS $$
  DECLARE
    x REFCURSOR;
    y REFCURSOR;
  BEGIN
    OPEN x FOR SELECT 100;
    BEGIN
      OPEN y FOR SELECT 200;
      IF n = 0 THEN
        RETURN 1 // 0;
      END IF;
    EXCEPTION
      WHEN division_by_zero THEN
        RETURN (SELECT count(*) FROM pg_cursors);
    END;
    RETURN 1 // 0;
  EXCEPTION
    WHEN division_by_zero THEN
      RETURN (SELECT count(*) FROM pg_cursors);
  END
$$ LANGUAGE PLpgSQL;

query I
SELECT f(0);
----
1

query I
SELECT f(1);
----
1

Both cursors in the f(1) case should have been closed due to the exception, and yet one remains open.

Jira issue: CRDB-37056

[blathers-crl]

Hi @DrewKimball, please add branch-* labels to identify which branch(es) this GA-blocker affects.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[michae2]

[triage] consequence would be a cursor staying open that should be closed. @DrewKimball to either remove GA-blocker or fix