Switching from blr_quad to blr_blob2 results in "Unknown blob ID 0:1 in the batch message"

[Mark Rotteveel]

I'm trying to switch from blr_quad to blr_blob2 in Jaybird, but when I
do so, executing server-side batches result in error "Unknown blob ID
0:1 in the batch message" (335545197).

Jaybird currently does not use the batch API to *create* blobs, it just
populates blob ids of blobs created separately. Jaybird does not set
TAG_BLOB_POLICY, but I've tried setting it to 0, 1, 2, and 3, to no avail.

Normal statements and emulated batches behave correctly with this
change. I'm not sure if I'm doing something wrong (though I don't think
so given normal statements and emulated batches work OK), or if I'm
missing something in the batch API.

Any idea what the problem could be?

For context, the only change I make is in Jaybird's
`DefaultBlrCalculator` from:

case SQL_BLOB:
// TODO Use blr_blob2 instead; added in 2.5
bout.write(blr_quad);
bout.write(0); // scale?
break;

to

case SQL_BLOB:
bout.write(blr_blob2);
bout.write(field.getSubType());
bout.write(field.getSubType() >> 8);
bout.write(field.getScale());
bout.write(0);
break;

(I've also tried just writing 4x 0 instead of subtype and scale)

Mark
--
Mark Rotteveel

[Alex Peshkoff]

On 6/7/23 14:33, Mark Rotteveel wrote:
> I'm trying to switch from blr_quad to blr_blob2 in Jaybird, but when I
> do so, executing server-side batches result in error "Unknown blob ID
> 0:1 in the batch message" (335545197).
>
> Jaybird currently does not use the batch API to *create* blobs, it
> just populates blob ids of blobs created separately.

Do you emulate registerBlob() call over the wire? Do you use
op_batch_regblob? (Hmm - no idea how could it work without
op_batch_regblob but ask anyway...)

> Jaybird does not set TAG_BLOB_POLICY, but I've tried setting it to 0,
> 1, 2, and 3, to no avail.
>

That's very important setting for batch api to work correctly with
blobs. If it does not affect result - sooner of all something got broken
very early.
With what value did it work when blr_quad was used?

> Normal statements and emulated batches behave correctly with this
> change. I'm not sure if I'm doing something wrong (though I don't
> think so given normal statements and emulated batches work OK), or if
> I'm missing something in the batch API.
>
> Any idea what the problem could be?
>
> For context, the only change I make is in Jaybird's
> `DefaultBlrCalculator` from:
>
>         case SQL_BLOB:
>             // TODO Use blr_blob2 instead; added in 2.5
>             bout.write(blr_quad);
>             bout.write(0); // scale?
>             break;
>
> to
>
>         case SQL_BLOB:
>             bout.write(blr_blob2);
>             bout.write(field.getSubType());
>             bout.write(field.getSubType() >> 8);
>             bout.write(field.getScale());
>             bout.write(0);
>             break;
>
> (I've also tried just writing 4x 0 instead of subtype and scale)
>

Mark, problem appears to be with how you use batches. Please take a look
- does blr_blob2 return different from blr_quad sequence of batch IDs?

[Mark Rotteveel]

On 07-06-2023 15:39, 'Alex Peshkoff' via firebird-devel wrote:
> On 6/7/23 14:33, Mark Rotteveel wrote:
>> I'm trying to switch from blr_quad to blr_blob2 in Jaybird, but when I
>> do so, executing server-side batches result in error "Unknown blob ID
>> 0:1 in the batch message" (335545197).
>>
>> Jaybird currently does not use the batch API to *create* blobs, it
>> just populates blob ids of blobs created separately.
>
> Do you emulate registerBlob() call over the wire? Do you use
> op_batch_regblob? (Hmm - no idea how could it work without
> op_batch_regblob but ask anyway...)

No, I just created blobs the normal way (using op_create_blob or
op_create_blob2), and use the returned blob IDs as the value in the
batch row.

>> Jaybird does not set TAG_BLOB_POLICY, but I've tried setting it to 0,
>> 1, 2, and 3, to no avail.
>>
>
> That's very important setting for batch api to work correctly with
> blobs. If it does not affect result - sooner of all something got broken
> very early.
> With what value did it work when blr_quad was used?

I didn't set it explicitly, but testing it now, all values (0 - 3) work
when using blr_quad.

>> Normal statements and emulated batches behave correctly with this
>> change. I'm not sure if I'm doing something wrong (though I don't
>> think so given normal statements and emulated batches work OK), or if
>> I'm missing something in the batch API.
>>
>> Any idea what the problem could be?

[..]

>
> Mark, problem appears to be with how you use batches. Please take a look
> - does blr_blob2 return different from blr_quad sequence of batch IDs?

What do you mean? Neither blr_blob2 or blr_quad "generate" anything.

Mark
--
Mark Rotteveel

[Mark Rotteveel]

On 07-06-2023 15:39, 'Alex Peshkoff' via firebird-devel wrote:

> On 6/7/23 14:33, Mark Rotteveel wrote:
>> I'm trying to switch from blr_quad to blr_blob2 in Jaybird, but when I
>> do so, executing server-side batches result in error "Unknown blob ID
>> 0:1 in the batch message" (335545197).
>>
>> Jaybird currently does not use the batch API to *create* blobs, it
>> just populates blob ids of blobs created separately.
>
> Do you emulate registerBlob() call over the wire? Do you use
> op_batch_regblob? (Hmm - no idea how could it work without
> op_batch_regblob but ask anyway...)

OK, sending op_batch_regblob and registering the blob id as itself does
the trick. But honestly, I would think - as proven by the fact it works
with blr_quad - this shouldn't be necessary.

Also, it works no matter which value of TAG_BLOB_POLICY I use (even
invalid values!), so that is a bit confusing.

Another thing I found curious is why op_batch_regblob only registers one
blob at a time. Why not allow it to register multiple blobs at once? I
know it uses deferred packets, but it feels a bit inefficient nonetheless.

Also, I see in the Firebird code that there is a limit of 64 pending
deferred packets. Is that a real limit, or is that to prevent other
issues, and if so, what issues?

Mark
--
Mark Rotteveel

[Alex Peshkoff]

On 6/7/23 17:39, Mark Rotteveel wrote:
> On 07-06-2023 15:39, 'Alex Peshkoff' via firebird-devel wrote:
>> On 6/7/23 14:33, Mark Rotteveel wrote:
>>> I'm trying to switch from blr_quad to blr_blob2 in Jaybird, but when
>>> I do so, executing server-side batches result in error "Unknown blob
>>> ID 0:1 in the batch message" (335545197).
>>>
>>> Jaybird currently does not use the batch API to *create* blobs, it
>>> just populates blob ids of blobs created separately.
>>
>> Do you emulate registerBlob() call over the wire? Do you use
>> op_batch_regblob? (Hmm - no idea how could it work without
>> op_batch_regblob but ask anyway...)
>
> OK, sending op_batch_regblob and registering the blob id as itself
> does the trick. But honestly, I would think - as proven by the fact it
> works with blr_quad - this shouldn't be necessary.
>

That's because blr_quad is never used internally by firebird to
represent blobs. In batch code I do not work with BLR, I work with SQL
types, where SQL_BLOB and SQL_QUAD are different types. And all
BLOB-specific processing is performed only for SQL_BLOB, cause I have no
idea what exactly represents SQL_QUAD - blob or unaligned 64-bit number.
Therefore yes, when using quad one can put IDs of existing blobs into it
and everythig will work.

> Also, it works no matter which value of TAG_BLOB_POLICY I use (even
> invalid values!),

Yes, because quad is not blob. BTW, invalid values default to 0 (no
blobs at all), but for quads it's ok too.

> so that is a bit confusing.
>
> Another thing I found curious is why op_batch_regblob only registers
> one blob at a time. Why not allow it to register multiple blobs at
> once? I know it uses deferred packets, but it feels a bit inefficient
> nonetheless.

Certainly. Moeover, using traditional BLOB API is very inefficient,
specially when compared with batch. For huge blobs it does not matter
much cause sending huge data will require much more resources (including
time) compared with sending deferred packet (and even open/close blob
over the wire!), but for small BLOB sending it using native batch tools
is much better than traditional BLOBs API. Be sure - compared with
traditional API use of registerBlob() does not matter from efficiency
POV. I've checked with gbak's restore (using localhost: access) of
artificial database with single table, containing many small blobs. In
that case restore using batch runs approximately 10 times faster than
non-batched gbak. So if you care about batch performance please do not
use traditional API for BLOBs.

>
> Also, I see in the Firebird code that there is a limit of 64 pending
> deferred packets. Is that a real limit, or is that to prevent other
> issues, and if so, what issues?

OOM on client, very slow run and problems with error recovery in
applications (all for really huge query of packets, do not remember
details better).
64 appeared to be good compromise.

[Mark Rotteveel]

On 07-06-2023 17:09, 'Alex Peshkoff' via firebird-devel wrote:
>> OK, sending op_batch_regblob and registering the blob id as itself
>> does the trick. But honestly, I would think - as proven by the fact it
>> works with blr_quad - this shouldn't be necessary.
>>
>
> That's because blr_quad is never used internally by firebird to
> represent blobs.

It used to be in Firebird 2.1 and earlier ;)

> In batch code I do not work with BLR, I work with SQL
> types, where SQL_BLOB and SQL_QUAD are different types. And all
> BLOB-specific processing is performed only for SQL_BLOB, cause I have no
> idea what exactly represents SQL_QUAD - blob or unaligned 64-bit number.
> Therefore yes, when using quad one can put IDs of existing blobs into it
> and everythig will work.
>
>> Also, it works no matter which value of TAG_BLOB_POLICY I use (even
>> invalid values!),
>
> Yes, because quad is not blob. BTW, invalid values default to 0 (no
> blobs at all), but for quads it's ok too.

No, I meant that when using blr_blob2, using *any* value for
TAG_BLOB_POLICY works. Is it a tag that is only relevant for the
fbclient implementation client-side, and not the server? Or does that
only come into play when creating blobs through the batch API?

>> so that is a bit confusing.
>>
>> Another thing I found curious is why op_batch_regblob only registers
>> one blob at a time. Why not allow it to register multiple blobs at
>> once? I know it uses deferred packets, but it feels a bit inefficient
>> nonetheless.
>
> Certainly. Moeover, using traditional BLOB API is very inefficient,
> specially when compared with batch. For huge blobs it does not matter
> much cause sending huge data will require much more resources (including
> time) compared with sending deferred packet (and even open/close blob
> over the wire!), but for small BLOB sending it using native batch tools
> is much better than traditional BLOBs API. Be sure - compared with
> traditional API use of registerBlob() does not matter from efficiency
> POV. I've checked with gbak's restore (using localhost: access) of
> artificial database with single table, containing many small blobs. In
> that case restore using batch runs approximately 10 times faster than
> non-batched gbak. So if you care about batch performance please do not
> use traditional API for BLOBs.

When I implemented the server-side batch updates in Jaybird, I decided
to skip the blob part for now, because 1) I find it unclear how to use
and populate and 2) it probably requires some - potentially complex -
refactoring of Jaybird's internals to achieve that. I'll tackle that later.

>> Also, I see in the Firebird code that there is a limit of 64 pending
>> deferred packets. Is that a real limit, or is that to prevent other
>> issues, and if so, what issues?
>
> OOM on client, very slow run and problems with error recovery in
> applications (all for really huge query of packets, do not remember
> details better).
> 64 appeared to be good compromise.

OK, I'll do something similar in Jaybird. I assume for the V16 protocol,
I can use op_ping instead of op_batch_sync, right?

Mark
--
Mark Rotteveel

[Alex Peshkoff]

On 6/7/23 19:03, Mark Rotteveel wrote:
> On 07-06-2023 17:09, 'Alex Peshkoff' via firebird-devel wrote:
>>> OK, sending op_batch_regblob and registering the blob id as itself
>>> does the trick. But honestly, I would think - as proven by the fact
>>> it works with blr_quad - this shouldn't be necessary.
>>>
>>
>> That's because blr_quad is never used internally by firebird to
>> represent blobs.
>
> It used to be in Firebird 2.1 and earlier ;)
>

Almost pre-historical times :-) And for sure THAT protocols did not use
batches.

>> In batch code I do not work with BLR, I work with SQL types, where
>> SQL_BLOB and SQL_QUAD are different types. And all BLOB-specific
>> processing is performed only for SQL_BLOB, cause I have no idea what
>> exactly represents SQL_QUAD - blob or unaligned 64-bit number.
>> Therefore yes, when using quad one can put IDs of existing blobs into
>> it and everythig will work.
>>
>>> Also, it works no matter which value of TAG_BLOB_POLICY I use (even
>>> invalid values!),
>>
>> Yes, because quad is not blob. BTW, invalid values default to 0 (no
>> blobs at all), but for quads it's ok too.
>
> No, I meant that when using blr_blob2, using *any* value for
> TAG_BLOB_POLICY works. Is it a tag that is only relevant for the
> fbclient implementation client-side, and not the server? Or does that
> only come into play when creating blobs through the batch API?

No, it's used when registering blobs too. But you are rignt - with
registerBlob() only remote redirector internally is using it. In
server's engine it's always set to generated-by-user by remote server code.

>
>>> Also, I see in the Firebird code that there is a limit of 64 pending
>>> deferred packets. Is that a real limit, or is that to prevent other
>>> issues, and if so, what issues?
>>
>> OOM on client, very slow run and problems with error recovery in
>> applications (all for really huge query of packets, do not remember
>> details better).
>> 64 appeared to be good compromise.
>
> OK, I'll do something similar in Jaybird. I assume for the V16
> protocol, I can use op_ping instead of op_batch_sync, right?
>

Sooner of all it will work - but op_batch_sync is simpler and therefore
a bit faster. Using op_batch_sync has one additional '+' - may be it
will do something else in the future.

[Mark Rotteveel]

Thanks. I'll use op_ping for the v16 implementation and op_batch_sync
for v18 (Jaybird doesn't have a v17 implementation at this time).

Mark
--
Mark Rotteveel