"Too many temporary blobs" with blob_append when select a stored procedue using rows-clause

[realcolaflasche]

Hi,

This is my select that works in Firebird 4.0.4 and Firebird 5: select * from SP_COUNT(1, 18000) join SP_TEST on 1 = 1

If I use the ROWS-Clause rows 17500 to 18000 I got this error:

Unsuccessful execution caused by system error that does not preclude successful execution of subsequent statements. Too many temporary blobs. At procedure SP_TEST line: 6, col: 3.

Full Statement: select * from SP_COUNT(1, 18000) join SP_TEST on 1 = 1 rows 17500 to 18000

This is the Source of the used stored procedures: first, you need a possibility to generate records, i use this method:

create or alter procedure SP_COUNT ( ASTART bigint, AEND bigint) returns ( OVALUE bigint) as begin :OValue = :AStart;

while (:OValue <= :AEND) do begin suspend; :OValue = :OValue + 1; end end

than you need a stored procedure using Blob_append like this:

create or alter procedure SP_TEST returns ( Memo BLOB SUB_TYPE 1 SEGMENT SIZE 80 CHARACTER SET UTF8 COLLATE UTF8) as begin :Memo = blob_append(null, ''); :Memo = blob_append(:Memo, 'Hello'); :Memo = blob_append(:Memo, ' World');

suspend; end

Regards, Jan

[asfernandes]

This changed created a bug in v5 (and temporarily in master - fixed by another commit).

Here is the test:

execute block returns (vb varchar(20))
as
    declare b blob;
    declare bhandle integer;
    declare read_data varbinary(20);
begin
    -- Create a BLOB handle in the temporary space.
    b = rdb$blob_util.new_blob(true, true);

    -- Add chunks of data.
    b = blob_append(b, '1');
    b = blob_append(b, '2345');
    b = blob_append(b, '67');
    b = blob_append(b, '8');

    if (rdb$blob_util.is_writable(b)) then
    begin
        vb = '';
        bhandle = rdb$blob_util.open_blob(b);

        while (true)
        do
        begin
            read_data = rdb$blob_util.read_data(bhandle, null);
            if (read_data is null) then
                break;

            vb = vb || read_data || '-';
        end

        execute procedure rdb$blob_util.close_handle(bhandle);

        suspend;
    end
end!

Correct result:

1-2345-67-8-

With this change, it became:

12345678-

Then in master, https://github.com/FirebirdSQL/firebird/pull/8046 changed the path and make the bug not appear.

There is also fact that BLOB_APPEND does not copy segmented blobs correctly, but anyway, rdb$blob_util.new_blob is another way to create blob as BLOB_APPEND, so it makes no sense to have it closed automatically.

[hvlad]

BLOB_APPEND creates stream blobs and didn't care about segments by design. How it should handle the case with both stream and segmented input blobs ? BTW, I don't remember - why RDB$BLOB_UTIL have no way to write to the blob ? It would handle the need in segments when really required.

About fix for subject issue (#8056). I can't say I like it much. Do you have better idea of how to close never used blobs produced as in this case (skipped records), or in case of OUTER JOIN, etc ?

[asfernandes]

Actually the fix here is incomplete, there are ways to cause the problem anyway:

execute block
as
    declare b blob;
begin
    while (true) do
        b = blob_append(null, '1');
end!

Also, it may make legit cases of multiple routines manipulating a blob slower, reverting the objective of BLOB_APPEND.

RDB$BLOB_UTIL uses BLOB_APPEND mechanism to also create segmented blobs.

Therefore, I'm making this incomplete fix not interfere with that case with segmented blobs at least.

The final solution for this problem should be with garbage collector or better accounting of blob references.

[pavel-zotov]

Actually the fix here is incomplete, there are ways to cause the problem

I can't understand: whether commit #e93f6ae should change output of example that you've provided ?

e93f6ae3f20b5889585f399d07b74f94ce9cf015
Author: Adriano dos Santos Fernandes
Date: 2024-04-03 (Wed, 03 Apr 2024)
  Changed paths: M src/jrd/exe.cpp
  Make fix for #8056 not break RDB$BLOB_UTIL with segmented blobs.

Output:

ISQL Version: WI-T6.0.0.305 Firebird 6.0 73551f3 (02-apr-2024 18:15 UTC)
Server version:
WI-T6.0.0.305 Firebird 6.0 73551f3
WI-T6.0.0.305 Firebird 6.0 73551f3/tcp (PZ)/P19:C
WI-T6.0.0.305 Firebird 6.0 73551f3/tcp (PZ)/P19:C
Statement failed, SQLSTATE = HY000
Too many temporary blobs
-At block line: 6, col: 9
=== vs ===
ISQL Version: WI-T6.0.0.312 Firebird 6.0 Initial (build after #3496c5d, 04-apr-2024 20:13 UTC)
Server version:
WI-T6.0.0.312 Firebird 6.0 Initial
WI-T6.0.0.312 Firebird 6.0 Initial/tcp (PZ)/P19:C
WI-T6.0.0.312 Firebird 6.0 Initial/tcp (PZ)/P19:C
Statement failed, SQLSTATE = HY000
Too many temporary blobs
-At block line: 6, col: 9

So, what differs ?

[asfernandes]

So, what differs ?

My commit is about https://github.com/FirebirdSQL/firebird/issues/8056#issuecomment-2032627160, that was broken by https://github.com/FirebirdSQL/firebird/commit/906e270de669430b39673543fecc6cb3d9cb4e5f