čt 30. 11. 2023 v 6:45 odesílatel Pavel Stehule <pavel.steh...@gmail.com> napsal:
> 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. > it can be implemented like extra warning for OPEN statement. > > Comments, notes? > > Regards > > Pavel > > > > >
