The holdable mode ensures that the current cursor is not released immediately. This way, cursor operations can be performed across transactions. This topic describes how to use holdable cursors in stored procedures.

Background information

When a dynamic cursor is used in a stored procedure of native PostgreSQL, the dynamic cursor cannot be set to the holdable mode. However, non-stored procedures support dynamic cursors in the holdable mode. After you open a dynamic cursor in a stored procedure and modify and save transactions, if you try to obtain data again, an error is reported. This issue occurs because the cursor in the current transaction is released when the transaction is saved.

To meet your business requirements, PolarDB for PostgreSQL(Compatible with Oracle) allows you to use the holdable mode in PLSQL to open cursors in stored procedures.

The following example shows the common scenario where an error is reported in the stored procedures of native PostgreSQL:
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 following result is returned:
ERROR:  cursor "myref1" does not exist  
CONTEXT:  polar-spl function testcur_001() line 8 at FETCH
Note An error is returned because the myref1 cursor does not exist. The cause of this issue is that the cursor is automatically released after it is committed out.

Usage

  • Allow a single cursor across transactions

    To open a single cursor by using the holdable mode in PLSQL, add the HOLD keyword before the FOR field in an OPEN statement.

    Example:
    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;
    Note You can execute the following statement to use the holdable mode in PLSQL to open a single cursor:
    OPEN cursorname HOLD FOR SELECT ...
    The following result is returned:
    POLAR-SPL Procedure successfully completed
    Note After the HOLD keyword is added, the POLAR-SPL program is run as expected.
  • Allow all cursors across transactions

    You must enable the polar_plsql_enable_holdable_refcursor parameter in the console. This way, you can use the holdable mode to open all cursors.

    Note
    • This parameter can be modified without the need to restart your database. The new parameter value takes effect only for new connections. If you use a persistent connection, we recommend that you select a time to restart the connection after this parameter is modified.
    • By default, this parameter is disabled.
    • After this parameter is enabled, cursors in all stored procedures, including explicit cursors and cursor variables, are automatically set to the HOLD mode.
    • Cursors in transaction blocks are not affected.
  • When a dynamic cursor in the HOLD mode is used, make sure that the cursor is explicitly closed by using the close statement before the current stored procedure ends.
  • A dynamic cursor in the HOLD mode is not automatically released even if a stored procedure ends. The consumed memory varies based on the number of query entries returned by the cursor. In some cases, temporary files are written.
  • If the HOLD mode is used for a large number of cursors and is not closed, a large amount of memory is consumed. In this case, temporary files accumulate, and subsequent data is read at a slow speed.