[pkg-firebird-general] Bug#693202: high memory usage when PSQL selects from stored procedure modifying data

[Damyan Ivanov]

Source: firebird2.5
Version: 2.5.1
Severity: important
Tags: upstream fixed-upstream
Forwarded: http://tracker.firebirdsql.org/browse/CORE-3895

When a code in a stored procedure selects from another stored procedure which 
modifies a table via UPDATE, memory is consumed on every invocation.

Test case from usptream bug report:

1. create metadata
create table t (id integer not null);

set term ^;

create or alter procedure selproc (p_id integer) returns (id integer)
AS
begin
  insert into t values (:p_id);
  id = p_id;
  suspend;
end
^

create or alter procedure main returns (id integer)
as
declare I integer = 0;
begin
  while (i < 1000) do begin
    select id from selproc(:i) into :id;
    i = i + 1;
  end
  suspend;
end
^

set term ;^

2. run test and look at memory usage (below i removed not needed stats)

SQL> set stat on;

SQL> select * from main;

          ID
============
         999

Current memory = 4852448
Delta memory = 257764
Max memory = 4859608

SQL> select * from main;

          ID
============
         999

Current memory = 4961764
Delta memory = 109316
Max memory = 4974260

SQL> select * from main;

          ID
============
         999

Current memory = 5071132
Delta memory = 109368
Max memory = 5081956

you see - memory usage is incremented at each run by 109368 bytes

SQL> commit;
Current memory = 4735108
Delta memory = -336024
Max memory = 5081956

memory is returned on commit, while it shoud be returned at the statement 
execution finish.

If "select from selproc" is replaced by "execute procedure selproc" then there 
is no such high memory usage.