Use holdable cursors in stored procedures

In native PostgreSQL, cursors opened inside a stored procedure are released when the transaction commits. PolarDB for PostgreSQL (Compatible with Oracle) extends PL/SQL to support holdable cursors in stored procedures, keeping a cursor open across transaction boundaries.

The problem: cursors closed after commit

When a stored procedure commits a transaction, PostgreSQL releases all open cursors in that transaction. Any subsequent FETCH on the same cursor fails:

CREATE TABLE test001(id numeric);
INSERT INTO test001 VALUES (1), (2), (3);

CREATE OR REPLACE PROCEDURE testcur_001
IS
    DECLARE
        myref1 refcursor;
        i numeric;
    BEGIN
        OPEN myref1 FOR SELECT * from test001;
        commit;
        fetch myref1 into i;
        dbms_output.put_line(i);
        close myref1;
    END;

EXEC testcur_001;
DROP TABLE test001;

The procedure fails with:

ERROR:  cursor "myref1" does not exist
CONTEXT:  polar-spl function testcur_001() line 8 at FETCH

Note The cursor myref1 no longer exists because it was released when the transaction committed.

PolarDB for PostgreSQL (Compatible with Oracle) provides two ways to keep cursors alive across commits.

Option 1: Make a single cursor holdable

Add the HOLD keyword between the cursor name and FOR in the OPEN statement:

OPEN cursorname HOLD FOR SELECT ...

The following example uses this syntax to keep myref1 open after the commit:

CREATE TABLE test001(id numeric);

INSERT INTO test001 VALUES (1), (2), (3);

CREATE OR REPLACE PROCEDURE testcur_001
IS
    DECLARE
        myref1 refcursor;
        i numeric;
    BEGIN
        OPEN myref1 HOLD FOR SELECT * from test001;
        commit;
        fetch myref1 into i;
        dbms_output.put_line(i);
        close myref1;
    END;

EXEC testcur_001;

DROP TABLE test001;

Expected output:

POLAR-SPL Procedure successfully completed

Option 2: Make all cursors holdable

Enable the polar_plsql_enable_holdable_refcursor parameter in the console to automatically apply holdable mode to all cursors in all stored procedures.

Property	Value
Default state	Disabled
Database restart required	No
Connection scope	New connections only
Affected cursor types	Explicit cursors, cursor variables (refcursor)
Exception	Cursors inside transaction blocks are not affected

Important

For persistent connections, reconnect after enabling the parameter.

Usage notes

Follow these rules when using holdable cursors to avoid memory and performance issues:

Always close dynamic holdable cursors explicitly. Call close cursorname before the stored procedure ends. Unlike regular cursors, a dynamic cursor in HOLD mode is not automatically released when the procedure exits.
Monitor memory usage. A holdable cursor retains its result set in memory. In some cases, temporary files are written. The larger the result set, the more resources it consumes.
Avoid leaving many holdable cursors open. Unclosed holdable cursors accumulate temporary files and degrade read performance over time.