Hi
one my customer migrated a pretty large application from Oracle, and when did performance tests, he found very high memory usage related probably to unclosed cursors. The overhead is significantly bigger than on Oracle (probably Oracle closes cursors after leaving cursor's variable scope, I don't know. Maybe it just uses a different pattern with shorter transactions on Oracle). He cannot use FOR cycle, because he needs to hold code in form that allows automatic translation from PL/SQL to PL/pgSQL for some years (some years he will support both platforms).
DECLARE qNAJUPOSPL refcursor;
BEGIN
OPEN qNAJUPOSPL FOR EXECUTE mSqNAJUPOSPL;
LOOP
FETCH qNAJUPOSPL INTO mID_NAJVUPOSPL , mID_NAJDATSPLT , mID_PREDPIS;
EXIT WHEN NOT FOUND; /* apply on qNAJUPOSPL */
END LOOP;
END;
Because plpgsql and postgres can be referenced just by name then it is not possible to use some reference counters and close cursors when the reference number is zero. Can we introduce some modifier that forces closing the unclosed cursor before the related scope is left?
Some like `DECLATE curvar refcursor LOCAL`
Another way to solve this issue is just warning when the number of opened cursors crosses some limit. Later this warning can be disabled, increased or solved. But investigation of related memory issues can be easy then.