Record fetching and data buffering

[Tomasz Tyrakowski]

Hello

I was wondering: is there a way to adjust / disable the buffering of
records returned by the server, so that it returns them one by one
rather than in groups?
The scenario is as follows. There's a stored procedure doing some
lenghty processing of a couple of thousand rows. The procedure is
selectable and returns a new row with a simple increasing counter after
processing each actual row in the database (processing of each row takes
~ 0.2s).
When I select from the procedure, I don't get back a row with the
counter every 0.2s, but instead the query is stuck for a minute or so,
then I get back a couple of hundred rows at once (with valid counter
values of course), then it gets stuck again, etc.
Of course I realize that in a typical data fetching scenario buffering
increases efficiency (especially with real transfer over the wire - in
my scenario it would be a single number enveloped by a TCP header, IP
header and Ethernet frame - quite a waste ;) ), but I was wondering if I
could just turn it off for this particular use case (preferably only for
this single query / transaction).
I'm pretty sure there's no middleware doing the buffering, it has to be
done between libfbclient and the Firebird server, because the behavior
is the same when I select the procedure in isql and even if I call
directly libfbclient API (IResultSet::fetchNext) from C++ code (well,
actually from Dart code, but it calls fetchNext directly via FFI, so in
this case it's the same - no extra layer which could be blamed for data
buffering).
Trying to duckduck the issue I found many references, but pointing to
database buffering (db cache), not the buffering of a result set.

Thanks in advance for any tips.

best regards
Tomasz

[Dimitry Sibiryakov]

Tomasz Tyrakowski wrote 20.03.2024 12:11:
> I was wondering: is there a way to adjust / disable the buffering of records
> returned by the server, so that it returns them one by one rather than in groups?

Yes: SELECT FOR UPDATE.

https://firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref50/fblangref50-dml.html#fblangref-dml-for-update

--
WBR, SD.

[Mark Rotteveel]

On 20/03/2024 12:11, Tomasz Tyrakowski wrote:
> I'm pretty sure there's no middleware doing the buffering, it has to be
> done between libfbclient and the Firebird server, because the behavior
> is the same when I select the procedure in isql and even if I call
> directly libfbclient API (IResultSet::fetchNext) from C++ code (well,
> actually from Dart code, but it calls fetchNext directly via FFI, so in
> this case it's the same - no extra layer which could be blamed for data
> buffering).

In the low-level protocol, it is possible to control the (maximum) fetch
size, but this isn't configurable in the legacy C or the C++ OO API: it
is always calculated, with a minimum of 10 rows.

You can force the server to return rows one-by-one with a FOR UPDATE
statement.

Mark
--
Mark Rotteveel

[Tomasz Tyrakowski]

Yes, it looks like it does exactly what I need. Thanks a lot Dimitry!
I like the beginning of the doc: "FOR UPDATE does not do what its name
suggests" :D

cheers
Tomasz

[Dimitry Sibiryakov]

Tomasz Tyrakowski wrote 20.03.2024 12:57:
> I like the beginning of the doc: "FOR UPDATE does not do what its name suggests" :D

Actually it is not quite true. This clause really makes possible using of
positioned updates (WHERE CURRENT OF) and without it the result of such update
is... unpredictable.

--
WBR, SD.

[Mark Rotteveel]

No, the statement is correct: the only thing FOR UPDATE does right now
is disable buffering. For SQL standard behaviour, a statement should be
rejected if it has the FOR UPDATE clause if it's not actually updatable,
but Firebird doesn't perform such a check: you can add FOR UPDATE to any
select statement.

And, IIRC, but haven't double-checked, using WHERE CURRENT OF <name>
should fail if the specified cursor is not actually marked as FOR
UPDATE, which Firebird doesn't either. So while specifying FOR UPDATE is
needed to correctly use positioned update, in the current
implementation, that is pretty much accidental, and not enforced.

Mark
--
Mark Rotteveel

[Dimitry Sibiryakov]

'Mark Rotteveel' via firebird-support wrote 20.03.2024 13:08:
> No, the statement is correct: the only thing FOR UPDATE does right now is
> disable buffering. For SQL standard behaviour, a statement should be rejected if
> it has the FOR UPDATE clause if it's not actually updatable, but Firebird
> doesn't perform such a check: you can add FOR UPDATE to any select statement.

Yes, because Firebird has no such standard limitation. Actual update query is
not related in any way with select used for cursor, it is enough if RDB$DB_KEY
is compatible between them.
You can consider it as following sequence:

FETCH RDB$DB_KEY FROM <cursor> INTO :TMP;
UPDATE <whatever> WHERE RDB$DB_KEY = :TMP;

--
WBR, SD.

[Mark Rotteveel]

On 20/03/2024 13:16, 'Dimitry Sibiryakov' via firebird-support wrote:
> 'Mark Rotteveel' via firebird-support wrote 20.03.2024 13:08:
>> No, the statement is correct: the only thing FOR UPDATE does right now
>> is disable buffering. For SQL standard behaviour, a statement should
>> be rejected if it has the FOR UPDATE clause if it's not actually
>> updatable, but Firebird doesn't perform such a check: you can add FOR
>> UPDATE to any select statement.
>
>   Yes, because Firebird has no such standard limitation. Actual update
> query is not related in any way with select used for cursor, it is
> enough if RDB$DB_KEY is compatible between them.

Your conflating things here. There is behaviour which the standard
requires (i.e. FOR UPDATE checking if the resulting cursor would
actually be updatable and positioned updates and deletes only working on
updatable cursors), and things Firebird does so positioned
updates/deletes work (switching of buffering with FOR UPDATE), and other
things you could do instead (updates using a row-key like RDB$DB_KEY).

The SQL standard specifies that FOR UPDATE is implied if a query is
simply updatable (and otherwise FOR READ ONLY is implied), and that if
FOR UPDATE is explicitly specified, it must be simply updatable. The
Firebird behaviour of disabling buffering with FOR UPDATE is pretty much
outside the standard (but needed for correct positioned updates).

Mark
--
Mark Rotteveel

[Dimitry Sibiryakov]

'Mark Rotteveel' via firebird-support wrote 20.03.2024 13:30:
> There is behaviour which the standard requires (i.e. FOR UPDATE checking if the resulting cursor would actually be updatable and positioned updates and deletes only working on updatable cursors)

Ok, but I have no copy of standard so I cannot check how it defines
"updatable cursor". I suspect that it could be "cursor that can be used in
UPDATE statement" but Firebird does not implement updating of cursors. What it
implements is updating of tables behind cursors.

> and other things you could do instead (updates using a row-key like RDB$DB_KEY).

Not "you could do", it is how Firebird implements it.

> The Firebird behaviour of disabling buffering with FOR UPDATE is pretty much outside the standard (but needed for correct positioned updates).

Just another implementation detail, nothing more.

--
WBR, SD.

[Mark Rotteveel]

On 20/03/2024 13:37, 'Dimitry Sibiryakov' via firebird-support wrote:
> 'Mark Rotteveel' via firebird-support wrote 20.03.2024 13:30:
>> There is behaviour which the standard requires (i.e. FOR UPDATE
>> checking if the resulting cursor would actually be updatable and
>> positioned updates and deletes only working on updatable cursors)
>
>   Ok, but I have no copy of standard so I cannot check how it defines
> "updatable cursor". I suspect that it could be "cursor that can be used
> in UPDATE statement" but Firebird does not implement updating of
> cursors. What it implements is updating of tables behind cursors.
>
>> and other things you could do instead (updates using a row-key like
>> RDB$DB_KEY).
>
>   Not "you could do", it is how Firebird implements it.

Again, your conflating things here. The fact that Firebird implements
positioned updates/deletes by using RDB$DB_KEY is an implementation
detail, it doesn't mean it can simply flaunt the requirements of the SQL
standard when implementing things specified by the standard.

So when a statement uses WHERE CURRENT OF ..., it should follow the
standard requirements which checks if the cursor is actually updatable,
while on the other hand, if a statement uses WHERE RDB$DB_KEY = ... it
doesn't need to do so.

>> The Firebird behaviour of disabling buffering with FOR UPDATE is
>> pretty much outside the standard (but needed for correct positioned
>> updates).
>
>   Just another implementation detail, nothing more.

Given the standard specifies that FOR UPDATE is implied *if* the query
is simply updatable, Firebird falls foul of that by doing something
extra if you do specify it explicitly (and instead fails to do what the
standard does require, namely requiring that the cursor is actually
updatable).

Sure, with over 25 years of history, that is no longer something that
can be removed, but it is not compliant.

Mark
--
Mark Rotteveel

[Tomasz Tyrakowski]

On 20.03.2024 at 12:57, Tomasz Tyrakowski wrote:
> Yes, it looks like it does exactly what I need. Thanks a lot Dimitry!
> I like the beginning of the doc: "FOR UPDATE does not do what its name
> suggests" :D

Just in case anyone tries to use this solution in Delphi with IBO: IBO
silently removes "FOR UPDATE" from the queries. I've been trying to
locate the guilty piece of code in IBO sources, but without success so
far. While all worked fine in isql and in C++/Dart, my Delphi code
stubbornly kept working as before (buffered records). When I got
frustrated and finally turned on the SQL monitor in IBO, it turned out
my query with "FOR UPDATE" in SQL contents of TIBOQuery was sent to the
server with "FOR UPDATE" part silently removed (as if it's never been
there).
There's some code in IBO dealing with the SQLForUpdate property
(detecting the "FOR UPDATE" clause and probably manipulating it in some
way), but I haven't had time to analyze it properly.
So I just allow myself to post a quick warning to anyone trying to use
the "FOR UPDATE" solution to avoid buffering with IBO: it might not work
as intended.

regards
Tomasz

[Tomasz Tyrakowski]

On 20.03.2024 at 19:17, Tomasz Tyrakowski wrote:
> So I just allow myself to post a quick warning to anyone trying to use
> the "FOR UPDATE" solution to avoid buffering with IBO: it might not work
> as intended.

In order not to leave the subject hanging without any conclusion:
TIBOQuery removes "for update" from the SQL statement contents, while
TIB_Cursor leaves it intact (so just choose the right component for the
job). I can more or less understand why TIBOQuery interferes so much in
the actual SQL statement - the requirements of the Delphi's TDataSet API
(which TIBOQuery implements) are pretty high. If you intend to implement
this behavior:
query.Edit();
query.FieldByName('AFIELD').AsString := 'aValue';
query.Post(); // <== automatic update query fired here
and expect it to work for all kinds of queries (automatically composing
the update statement and sending it to the database to reflect the
changes made by the client code), you'll probably end up parsing and
trying to "understand" the statement client-side. I'm not picking at IBO
in any way (honestly, the complexity of the task of being fully
compatible with TDataSet is really high), just finishing the subject
after some experimentation, in case someone stumbles upon the same
problem in the future.

regards
Tomasz