Close cursors

One my customer reported very problematic memory issues of currently tested code freshly ported from Oracle's PL/SQL to PL/pgSQL. He rewrites very big application based on usage of thousands stored procedures and functions, and views. This application is ported to Postgres by ora2pg. It is working pretty well, but some patterns has different overhead, and some patterns can be fatal.

In your application he often (100 000x) call code (in long transaction):

OPEN qNAJUPOSPL FOR EXECUTE mSqNAJUPOSPL;
LOOP
  FETCH qNAJUPOSPL INTO mID_NAJVUPOSPL , mID_NAJDATSPLT , mID_PREDPIS;
  EXIT WHEN NOT FOUND; /* apply on qNAJUPOSPL */
END LOOP;

The problem is undisclosed cursor there. PLpgSQL cursors are just references to SQL cursors. The lifecycle of cursor variable is defined by scope, but lifecycle of SQL cursor is limited by transaction. Any active cursor can have allocated lot of resources (it is "snapshot" of active query). Unfortunately, after leaving of scope of cursor's variable, the related cursor is not closed automatically (and there is not some garbage collector too).  Inside large application is not easy to find this issue, mainly when you don't know what you should to find. So, I wrote new warning to plpgsql_check (2.7.0). This warning is enabled by default. 

Default check is designed to check previously opened cursor when cursor is opening.

CREATE OR REPLACE FUNCTION public.test()
 RETURNS void
 LANGUAGE plpgsql
AS $function$
declare
  c refcursor;
  q text;
  r record;
begin
  q := 'select * from pg_class limit 10';
  open c for execute q;
  loop
    fetch c into r;
    exit when not found;
    raise notice '%', r;
  end loop;
end;
$function$

do $$                            
begin             
  perform test();
  perform test();
end;
$$;
NOTICE:  (2619,pg_statistic,11,10029,0,10,2,2619,0,19,410,19,2840,t,f,p,r,31,0,f,f,f,f,f,t,n,f,0,728,1,{postgres=arwdDxt/postgres},,)
NOTICE:  (2619,pg_statistic,11,10029,0,10,2,2619,0,19,410,19,2840,t,f,p,r,31,0,f,f,f,f,f,t,n,f,0,728,1,{postgres=arwdDxt/postgres},,)
DO

When plpgsql_check is loaded, then the warning is displayed:

(2023-12-06 09:07:32) postgres=# load 'plpgsql_check';
LOAD
(2023-12-06 09:07:51) postgres=# do $$                
begin
  perform test();
  perform test();
end;
$$;
NOTICE:  (2619,pg_statistic,11,10029,0,10,2,2619,0,19,410,19,2840,t,f,p,r,31,0,f,f,f,f,f,t,n,f,0,728,1,{postgres=arwdDxt/postgres},,)
WARNING:  cursor is not closed
DETAIL:  PL/pgSQL function test() line 8 at OPEN
SQL statement "SELECT test()"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE:  (2619,pg_statistic,11,10029,0,10,2,2619,0,19,410,19,2840,t,f,p,r,31,0,f,f,f,f,f,t,n,f,0,728,1,{postgres=arwdDxt/postgres},,)
DO

There is possible to set strict mode, and then the cursors are checked at the function's exit:

(2023-12-06 09:07:52) postgres=# set plpgsql_check.strict_cursors_leaks to on;
SET
(2023-12-06 09:09:56) postgres=# do $$                                        
begin
  perform test();
  perform test();
end;
$$;
NOTICE:  (2619,pg_statistic,11,10029,0,10,2,2619,0,19,410,19,2840,t,f,p,r,31,0,f,f,f,f,f,t,n,f,0,728,1,{postgres=arwdDxt/postgres},,)
WARNING:  cursor is not closed
DETAIL:  PL/pgSQL function test() during function exit
SQL statement "SELECT test()"
PL/pgSQL function inline_code_block line 3 at PERFORM
NOTICE:  (2619,pg_statistic,11,10029,0,10,2,2619,0,19,410,19,2840,t,f,p,r,31,0,f,f,f,f,f,t,n,f,0,728,1,{postgres=arwdDxt/postgres},,)
WARNING:  cursor is not closed
DETAIL:  PL/pgSQL function test() during function exit
SQL statement "SELECT test()"
PL/pgSQL function inline_code_block line 4 at PERFORM
DO

compiled dll of plpgsql_check 2.5.4 and Orafce 4.7.0 for PostgreSQL 15 and 16

I compiled and uploaded zip files with latest orafce and plpgsql_check for PostgreSQL 15 and PostgreSQL 16.

Setup:

1. download orafce-4.7.0-x64.zip or plpgsql_check-2.5.4-x64.zip and extract files
2. copy related dll file to PostgreSQL lib directory (NN is number of pg release)
   orafce-NN.dll -> "c:\Program Files\PostgreSQL\NN\lib"
3. remove suffix "-15" or "-16" from dll file
   orafce-NN.dll -> orafce.dll
4. copy *.sql and *.control files to extension directory
   *.sql, *.control -> "c:\Program Files\PostgreSQL\NN\share\extension"
5. execute with super user rights SQL command CREATE EXTENSION
   CREATE EXTENSION orafce;
   

prepared dll of orafce 4.0.1 and plpgsql_check 2.2.5 for PostgreSQL 14 and PostgreSQL 15

I compiled and uploaded zip files with latest orafce and plpgsql_check for PostgreSQL 14 and PostgreSQL 15.

Setup:

1. download orafce-4.0.1.zip or plpgsql_check-2.2.5.zip and extract files
2. copy related dll file to PostgreSQL lib directory (NN is number of pg release)
   orafce-NN.dll -> "c:\Program Files\PostgreSQL\NN\lib"
3. remove suffix "-14" or "-15" from dll file
   orafce-NN.dll -> orafce.dll
4. copy *.sql and *.control files to extension directory
   *.sql, *.control -> "c:\Program Files\PostgreSQL\NN\share\extension"
5. execute with super user rights SQL command CREATE EXTENSION
   CREATE EXTENSION orafce;
   

pspg 5.6.0

I released pspg 5.6.0. There is only one (not too visible change). It allows to use true color themes in "konsole" terminal (when TERM) is xterm-direct.

pspg and nushell

I very like the concept of nushell. The concept of Unix pipes is famous (still), but little bit aged (we have more resources than was possible 40 years ago). Working with multi-column data with classical Unix tools is fragile and really not very friendly. More time I asked why conceptual development stopped at 80 years. nushell allows to work well with relations. There is similar project relational pipes. In old times the Microsoft has something similar based on DAO

I didn't find C API of nushell (it is 100% rust oriented), so I cannot to support nushell in pspg directly, but support via CSV format works perfectly. 

 

and same result displayed in pspg:

Update: pspg 5.5.10 supports nushell formats (table_mode) rounded (default) and heavy. The conversion to csv is not necessary.

orafce 4.0.0

Today I released orafce 4.0.0

The big change is merging orafce_sql project - so now directly in orafce is possible to use dbms_sql package.

Second change is code cleaning - support for PostgreSQL 9.6 and 10 was removed.

pspg 5.5.8

I released new version of pspg . This release can be interesting for users that uses some BSD platforms (like FreeBSD). Unfortunately, these platforms doesn't support alternate screen, and then can users can see some unwanted visual effects, when pspg is closed. Most unwanted are chars of bottom menu in command line.

New pspg introduces set of options, that can be used in this situation: --on-exit-reset (reset terminal), --on-exit-clean (clean terminal) and --on-exit-erase-line (erase last (bottom line)).