Re: [Firebird-devel] Improve release filenames

[Dmitry Yemanov]

25.08.2022 04:15, Adriano dos Santos Fernandes wrote:


Here is my updated proposal based on the discussion so far:

Firebird-5.0.0.2816-0-windows-x86-withDebugSymbols.exe
Firebird-5.0.0.2816-0-windows-x86-withDebugSymbols.zip
Firebird-5.0.0.2816-0-windows-x86.exe
Firebird-5.0.0.2816-0-windows-x86.zip
Firebird-5.0.0.2816-0-windows-x64-withDebugSymbols.exe
Firebird-5.0.0.2816-0-windows-x64-withDebugSymbols.zip
Firebird-5.0.0.2816-0-windows-x64.exe
Firebird-5.0.0.2816-0-windows-x64.zip
Firebird-5.0.0.2816-0-linux-x64.tar.gz
Firebird-5.0.0.2816-0-linux-x64-debugSymbols.tar.gz
Firebird-5.0.0.2816-0-android-arm.tar.gz (armv7, other?)
Firebird-5.0.0.2816-0-android-arm-withDebugSymbols.tar.gz
Firebird-5.0.0.2816-0-android-arm64.tar.gz
Firebird-5.0.0.2816-0-android-arm64-withDebugSymbols.tar.gz
Firebird-5.0.0.2816-0-linux-x86.tar.gz
Firebird-5.0.0.2816-0-linux-x86-debugSymbols.tar.gz
Firebird-5.0.0.2816-0-source.tar.xz
Firebird-5.0.0.2816-0-macos-x64.pkg


Looks good to me.


Dmitry



Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Improve release filenames

[Dmitry Yemanov]

24.08.2022 15:35, Jiří Činčura wrote:


I would unify the `pdb`, `debuginfo`, etc. into simple `debug` suffix.


Agreed.


Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Current value for parallel workers

[Dmitry Yemanov]

16.08.2022 13:02, Jiří Činčura wrote:


Session context variable seems to be a good fit. Monitoring table looks like 
overkill.


I think DBA may want a monitoring table (MON$WORKERS?) that shows how 
many workers are currently active, what attachment are they bound to, 
and what task (backup/sweep/etc) are they used for. Maybe some other 
metrics could be added later.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] New statement: EXECUTE SQL

[Dmitry Yemanov]

15.08.2022 20:42, Vlad Khorsun wrote:


Also, I don't like 'sql' word, especially after 'execute statement' 
and 'execute
block'. Too much, as for me :) Syntax with 'with' instead of 'execute 
sql' looks
much better to me, but it is already used in CTE's, thus it seems as not 
the best choice :(


using (p1 integer = :p1, p2 varchar(255) = :p2)
do select * from t where t.id = :p1 and t.name = :p2

or just

using (p1 integer = :p1, p2 varchar(255) = :p2)
select * from t where t.id = :p1 and t.name = :p2

?


Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Version of 3.0 snapshots

[Dmitry Yemanov]

29.06.2022 13:31, Gabor Boros wrote:


3.0.10 already released and the snapshots still show 3.0.10. Please 
increase the revision number. Thank you!


Done, thanks for reminding!


Dmitry



Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Stdin redirection with pipe in Windows ISQL

[Dmitry Yemanov]

15.06.2022 22:56, Adriano dos Santos Fernandes wrote:


In Linux, when we do:

echo "select 1 from rdb\$database; select 2 from rdb\$database;" | isql
t.fdb

It shows:

 CONSTANT

1


 CONSTANT

2

In Windows,

echo select 1 from rdb$database; select 2 from rdb$database; | isql t.fdb

It shows:

SQL>
 CONSTANT

1

SQL>
 CONSTANT

2

The difference is due to stdin_redirected() considering file_type ==
FILE_TYPE_PIPE as a not redirection.

I think this is wrong.


See:

https://github.com/FirebirdSQL/firebird/commit/c8c7c3a44dad9572a55d6fed68f1c399a19366db

Initially (FB2) the code was:

if (GetFileType(in) == FILE_TYPE_DISK)
  Interactive = false;

then (circa 2.5) it was changed to:

if (GetFileType(in) == FILE_TYPE_CHAR)
  Interactive = true;

and later fixed to:

if (file_type == FILE_TYPE_CHAR || file_type == FILE_TYPE_PIPE)
  Interactive = true;


Are there any reason for that?


We have an old customer who uses (and distributes among clients, IIRC) 
their own GUI frontend for ISQL that tunnels console I/O using pipes and 
it should work as "interactive" mode.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Record storage

[Dmitry Yemanov]

09.06.2022 16:18, Adriano dos Santos Fernandes wrote:



09.06.2022 15:16, Adriano dos Santos Fernandes wrote:

Yes, it should work. However, I'm not going to remove the limit until we
introduce a denser compression. Also, we have a number of places where
records is stored unpacked in memory (rpb's, RecordBuffer, HashJoin,
etc), so longer records could increase server memory usage. This should
be improved somehow.


Yes, but it's so bad when one needs to improve their schema and hit a limit.

And sometimes the arbitrary limit is hit by few margin, like switching a
field encoding or small necessary increase in length.


Agreed. In the meantime, I will not object to keeping the limit but 
raising it to e.g. 256KB.



The same with record formats limited to 255. It's so awful, and it's
related stuff, as the format could also be variable encoded to not
always use 2 bytes.


True. Another approach is also possible: (optionally) extend sweeping to 
upgrade the record format of the committed records on the data pages 
being swept, garbage collect unused formats and re-use them when the 
counter wraps around.



The problem, however, is that format-aware processing was found to be
slower. The dumb scheme presented above (with no real compression)
provided almost the same record size as RLE compression for mixed
"real-world" fields and was even denser for records with longish UTF8
fields, but it was also ~20% slower.


If the storage takes less space, is this slow down estimation calculated
also taking into account the slower number of pages read (when page is
not cached)?


Surely reading from disk is way slower than decompressing in memory, so 
less data pages to read easily outbids the increased decompression 
penalty. Things are not so cool when the working set is cached though.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Record storage

[Dmitry Yemanov]

09.06.2022 15:58, Dmitry Yemanov wrote:


(2) skip padding bytes 


A separate but interesting question is whether we need alignment (and 
thus padding bytes) at all. Most of our installations are little-endian 
and it does not crash on unaligned data access. Moreover, modern CPUs 
access aligned/unaligned data with the same speed, AFAIK. Given that our 
ODS is different between LE and BE platforms anyway, perhaps it makes 
sense to optimize the record storage for the LE case.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Record storage

[Dmitry Yemanov]

09.06.2022 15:16, Adriano dos Santos Fernandes wrote:


With some frequency people ask me why UTF-8 is slower than single byte
charsets.

The thing is, they have something using, for example, VARCHAR(30)
CHARACTER SET WIN1252 and convert to VARCHAR(30) CHARACTER SET UTF8,
test with the same data and have slower queries.

Database is also increased in size and record size (based on characters)
limit is decreased.

But if they test VARCHAR(120) CHARACTER SET WIN1252 vs VARCHAR(30)
CHARACTER SET UTF8, database size and query times are similar. But this
is just a test, it's not real world scenario user wants.

We have old problems, for example, record size limit is tracked here:
https://github.com/FirebirdSQL/firebird/issues/1130

Like commented there, I tried to just increase the constant and it seems
to just work.


Yes, it should work. However, I'm not going to remove the limit until we 
introduce a denser compression. Also, we have a number of places where 
records is stored unpacked in memory (rpb's, RecordBuffer, HashJoin, 
etc), so longer records could increase server memory usage. This should 
be improved somehow.



Then we have the RLE record compression algorithm, that "compress" bytes
that is well known to be unused. We had even patches to improve the bad
algorithm.


Yep.


I believe that is not the way to go.


So do I, although an improved RLE could be a good workaround until 
something significantly better is invented.



Let's still call it "record compression", I believe it should be more
active. Instead of work based only on the record buffer and its length,
it should have access to the record format.

Then it can encode things in more active way, trimming out unused bytes
of CHAR/VARCHAR, better encoding numbers and booleans. We may use
protocol-buffers format as inspiration.

And then probably we don't need any RLE compression as most of data (not
unused bytes) are not so repetitive.


I tried something like that in the past, but I called it "packing" as 
opposed to "compression". The idea was to (1) skip NULL fields as 
they're already marked in a leading bit mask, (2) skip padding bytes 
because they can be reconstructed using a record format, (3) copy only 
meaningful bytes of VARCHAR strings (using its vary_length which is also 
stored). The rest (numerics/dates/CHARs) was copied "as is" (without 
compression). Of course, CHARs and the real part of VARCHARs could be 
compressed one way or another, but I intentionally left it for another day.


The problem, however, is that format-aware processing was found to be 
slower. The dumb scheme presented above (with no real compression) 
provided almost the same record size as RLE compression for mixed 
"real-world" fields and was even denser for records with longish UTF8 
fields, but it was also ~20% slower. Every field processed/copied 
separately is slower than processing the record as a whole. It can be 
proved even for current RLE - the more "runs" (compressed/uncompressed) 
we have there, the slower is decompression.


I know that RedSoft tried to implement a mixed compression where RLE was 
used together with format-aware logic which decided what should become a 
compressible run. I don't recall the performance figures though, maybe 
Roman could share them.



What do you think and are there any active work in this regard?


Right now (for ODS 13.1) I'm working on improvement to the current RSE 
that does two things: (1) reduces number of runs (avoid short 
compressible runs) and (2) allow longish compressible runs with 2-byte 
(and possibly 3/4-byte) length. That should solve the problem with UTF8 
strings without any performance penalty.


For the next ODS, I was going to continue researches regarding "packing" 
and mixed "packing/compression" approaches to address the performance 
issues. Nothing is done yet.


I'd also like to consider a completely different record storage format:



where all VARCHARs are stored as  in the prefix part and their 
contents is stored in the suffix part. This makes records 
variable-length and supposedly many code must be changed for that. 
However, it reduces memory usage for records (only real length is 
stored) and it allows flexible encoding: "as is" copying or some clever 
packing for the fixed prefix and e.g. LZ4 compression for the variable 
suffix.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Add scroll fetch support in legacy API?

[Dmitry Yemanov]

04.06.2022 15:07, Mark Rotteveel wrote:

Currently scroll fetch is only exposed in the OO API. Any plans for 
exposing it in the legacy API 


Nope, we have an agreement to not extending the legacy API with new 
features.



(e.g. isc_dsql_fetch_scroll / fb_dsql_fetch_scroll)?


That's not enough, some method is also needed to mark the cursor as 
scrollable before opening. The new cursor's getInfo() should also be 
duplicated. And maybe isBof/isEof too.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Unused but still handled DPB items

[Dmitry Yemanov]

16.05.2022 18:51, Dimitry Sibiryakov пишет:

   Hello All.

Shouldn't code for handling isc_dpb_journal, isc_dpb_old_file, etc 
cleaned out or someone have plans for their reuse?


I don't think any DPB code can be reused, unless the new behaviour 
completely matches the originally intended one (which we may only 
guess). So IMO their handling code should be wiped out.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors

[Dmitry Yemanov]

28.04.2022 14:31, Mark Rotteveel wrote:

I notice that if I get the INF_RECORD_COUNT with op_info_cursor before 
the first fetch, I will get the record count, but the subsequent fetch 
will fail with a Dynamic SQL Error; SQLDA error; Data type unknown; at 
SQLVAR index 0 [SQLState:07002, ISC error code:335544583].


If I get the record count after the first fetch, everything is OK.

This seems to indicate that retrieving the record count somehow sets an 
empty SQLDA or something like that, and ignores the one from the first 
fetch.


Is this a bug? Should retrieving cursor info before the first fetch be 
disallowed by the server? Or maybe something else?


This is likely to be a bug, I will take a look.


Dmitry



Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors

[Dmitry Yemanov]

22.04.2022 14:49, Mark Rotteveel wrote:


28.11.2021 14:45, Mark Rotteveel wrote:
3) "row count" makes it possible to know the position after 
fetchLast() and everything else could be calculated locally by the 
client library, thus making the server-supported "current position" 
totally unnecessary.


Do I miss anything? Could we agree on having only "row count" returned 
via op_info_cursor and leaving "cursor position" (getRow() in Java 
API) up the connectivity library developers?


I was wondering if this - fetching rowcount of a scrollable cursor - was 
already implemented. If so, how do I get it? And if not, when can I 
expect it to be implemented?


PR #7083, was left for review but not merged yet. I see it has conflicts 
now, I will address them tomorrow and then merge the PR.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Status of 3.0.10

[Dmitry Yemanov]

13.04.2022 17:11, Pavel Cisar wrote:


because 3.0.9 has significant regression (#7137 - we've got multiple 
reports from large affected and unhappy users), I'd like ask what are 
the prospect to release v3.0.10 soon.


BTW, it would also address contracted collation performance issues that 
we hoped would be addressed by 3.0.9, but as 3.0.9 release was rushed 
ahead of schedule, this was deferred to 3.0.10.


Let's make the release this month. I will start preparing the release notes.


Dmitry



Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] FB4.0 - no current record for fetch operation

[Dmitry Yemanov]

26.03.2022 11:53, Omacht András wrote:


I have a test case which running fine on 2.5 and 3.0 but faild on 4.0 
with „no current record for fetch operation”.


Unfortunately I could not reproduce it as a simple case, but I can send 
the database in private.


Please send it to me.


Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Local tables

[Dmitry Yemanov]

15.03.2022 21:43, Adriano dos Santos Fernandes wrote:



In fact, what you priorly define as LT is IMO "declared" LTT.


I had that impression before read the standard, but then I changed my
opinion.


"Part 4: Persistent Stored Modules (SQL/PSM)" is about PSQL, AFAIU.
It includes: "12.8 " that refers to "Part 
2: Foundation (SQL/Foundation)" which defines:


 ::=
DECLARE LOCAL TEMPORARY TABLE  
[ ON COMMIT  ROWS ]

Given that Part 4 also defines DECLARE CURSOR which is also redirected 
to "Part 2: Foundation (SQL/Foundation)" I understand it as declared 
LTTs are allowed in PSQL.



I also did not found any major DBMS that implemented declared LTTs, nor
SQL-standard modules.


Sybase ASA, as you already mentioned, although maybe not so "major". 
From their docs: "Declared local temporary tables within compound 
statements exist within the compound statement."



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Local tables

[Dmitry Yemanov]

15.03.2022 20:31, Alex Peshkoff via Firebird-devel wrote:


For me, "created" LTT is similar to GTT (i.e. stored in the schema) 
but with data isolated per request (per PSQL routine). 


I'd consider about CREATE'd LTT as attachment-private object. I see 
no need to store its definition at the persistent schema.


If multiple procedures process the same layout of temporary data, IMHO 
it's handier to create such LTT definition once (as persistent) rather 
than declare the same LTT in the every procedure (or attachment).


Same for almost all objects declared in procedure that are more complex 
than INT. For example cursor definition may be rather complex and also 
usable in multiple procedures.


True. But I don't remember CREATE CURSOR in the SQL spec ;-)

Anyway, IMO "declared" LTTs are much more useful than "created" LTTs, so 
the latter ones may be deferred. Or we may live without them at all ;-)



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Local tables

[Dmitry Yemanov]

15.03.2022 20:17, Vlad Khorsun wrote:


For me, "created" LTT is similar to GTT (i.e. stored in the schema) 
but with data isolated per request (per PSQL routine). 


I'd consider about CREATE'd LTT as attachment-private object. I see 
no need to store its definition at the persistent schema.


If multiple procedures process the same layout of temporary data, IMHO 
it's handier to create such LTT definition once (as persistent) rather 
than declare the same LTT in the every procedure (or attachment).



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Local tables

[Dmitry Yemanov]

15.03.2022 17:14, Adriano dos Santos Fernandes wrote:


As part of "Support multiple rows for DML RETURNING (#6815)" feature,
BLR verbs for "local table" were created.

Local tables (LT) as defined there works outside transaction control.
For #6815 this does not matter, but I want to add LT feature that will
use these verbs.

LTs are defined inside PSQL routines and its data is separated per
routine invocation. For example a recursive routine will have different
data in its LTs per each invocation, like local variables.


What about autonomous transactions? Will procedure's LT data be visible 
for them or every autonomous transaction gets its own copy?



SQL standard defines LOCAL TEMPORARY TABLES (LTT), which may be
"created" or "declared". The semantics of these standard specs are
different and not very easy applicable to Firebird as they are based on
standard notion of modules, that is not similar to PSQL routines.


I'd just adapt their definitions to our realities. For me, "created" LTT 
is similar to GTT (i.e. stored in the schema) but with data isolated per 
request (per PSQL routine). "Declared" LTTs are defined inside the PSQL 
routine, with data isolated the same way. In fact, what you priorly 
define as LT is IMO "declared" LTT.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Local tables

[Dmitry Yemanov]

15.03.2022 18:03, Kjell Rilbe wrote:

From a linguistic point of view I find "declared" to be a nonsense 
qualification of "table". Aren't all tables declared in a sense?


Maybe, but this is what SQL spec suggests. In PSQL, we have any locals 
syntactically "declared" -- DECLARE VARIABLE, DECLARE CURSOR. So it 
looks consistent to have DECLARE LOCAL TEMPORARY TABLE there too.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Roadmap, Planning Board, version of 4.0 snapshots

[Dmitry Yemanov]

03.03.2022 12:38, Gabor Boros wrote:


3.0.9 already released, please update the Roadmap page.

5.0 Alpha not released in January, please update the Planning Board page.

4.0.1 already released but the snapshot's version still 4.0.1.


Updated, thanks.


Dmitry



Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Compiled statement cache

[Dmitry Yemanov]

03.03.2022 20:19, Alex Peshkoff via Firebird-devel wrote:


I think there are two possible ways:

- Timeout of cached statement (counting from its first appearance in
cache, not last usage)


Yes, re-preparing all statements once per relatively big timeout should 
not cause visible performance problem.


Better it should be "re-optimizing" (just CMP_post_rse) rather than 
"re-preparing". The problem is that the optimizer uses the same request 
pool and all its allocations are "delete-by-pool", so re-optimization is 
gonna to become a memory leak. Perhaps we'll need to add an explicit 
request's child pool for optimization purposes which can be re-created 
during re-optimization.



- When engine detects a condition which could change a plan, it may ask
cache for invalidation.


At the first glance that's changing index active/inactive and in the 
future bulk insert. That's first things that come to my mind.


It's more complex than that, but I think this is a 2nd priority task and 
somewhat later I'll jump in to help with the optimizer part.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Statement::verifyAccess of internal requests

[Dmitry Yemanov]

01.03.2022 21:33, Adriano dos Santos Fernandes wrote:


Do we have any good reason to make this method do for internal requests
everything it does for user's statement?

Or could we start it with this?

void Statement::verifyAccess(thread_db* tdbb)
{
if (flags & FLAG_INTERNAL)
return;

...
}


Honestly, I was sure we don't check access for internal requests ;-) As 
I see no need for doing that.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Knowing whether fbclient supports isc_dpb_utf8_filename

[Dmitry Yemanov]

01.03.2022 14:56, Jiří Činčura wrote:


void ISC_EXPORT isc_get_client_version ( ISC_SCHAR  *);
int  ISC_EXPORT isc_get_client_major_version ();
int  ISC_EXPORT isc_get_client_minor_version ();


Great!

How could I miss it???


Sorry for disappointing you, but they not gonna be any helpful, as they 
always return version "6.3" for InterBase compatibility ;-)



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Compiled statement cache

[Dmitry Yemanov]

26.02.2022 17:14, Adriano dos Santos Fernandes wrote:


I do want to define default cache size.

I'm thinking in 2M.

Comments?


We need to start with something, so why not. However, it would be 
helpful to know what are the "common" statement sizes for tables, 
procedures, etc. Of course, table with one column much differs from 
table with 100 computed columns, as well as a one-liner procedure 
differs from a 1MB-BLR one. But maybe you have more or less real 
databases to get these estimations from.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] INT64 and index keys

[Dmitry Yemanov]

21.02.2022 20:05, Alex Peshkoff via Firebird-devel wrote:


This is possible way to fix a bug:
https://github.com/FirebirdSQL/firebird/commit/7dd832f32e9669bcb3007dc675b3ee7cca6f6b7d 


New type of indexes is added and it works fine.


I didn't look at the patch deeply yet, so the question: what is storage 
and performance difference between idx_decimal and idx_bcd?


But I wonder - what to do with existing databases? Rebuilding affected 
indexes is enough - but such database becomes unusable for previous 
versions of engine. Normally we should increase ODS minor. But last time 
when it was done in FB3 it gave serious negative feedback. Next, 
upgrading ODS requires gbak/restore cycle, which is problematic with big 
database.


I'd consider committing the solution into v5 only. In v4, index lookups 
should work, although suboptimal for very big numbers. As for index 
ordering, we may document the issue and leave it up to users. I doubt 
those who already utilized INT128 do really use such big numbers in 
production. If they do, they may add a +0 hint to work slower but safe. 
Or we may add a (temporary) configuration switch that disables the index 
ordering for INT128. Given that we expect v5 to be released soon, it 
could be acceptable as a temporary measure.


Perhaps we could invent something clever like converting an ORDER'ed 
select into UNION ALL with the first partition (<= 10^34) ordered by 
index and the second one sorted naturally, but it looks like too much 
effort for a temporary solution.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] INT64 and index keys

[Dmitry Yemanov]

16.02.2022 13:28, Dmitry Yemanov wrote:


It looks so. Unless we miss something (Alex?), perhaps we need to 
add a runtime check that rejects key creation for INT128 values 
longer than 34 decimal digits.


Thinking twice, an overflow error should already been raised when a 
longish INT128 is converted into DECFLOAT(34) before composing the 
index key.


No, it does not happen, that int128 is rounded.


So no problems at all, except suboptimal index scan (possibly false 
matches) for longish values?


At least ORDER BY via index should be broken for them, I suppose.


Dmitry



Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] INT64 and index keys

[Dmitry Yemanov]

16.02.2022 13:24, Alex Peshkoff via Firebird-devel wrote:


It looks so. Unless we miss something (Alex?), perhaps we need to add 
a runtime check that rejects key creation for INT128 values longer 
than 34 decimal digits.


Thinking twice, an overflow error should already been raised when a 
longish INT128 is converted into DECFLOAT(34) before composing the 
index key.


No, it does not happen, that int128 is rounded.


So no problems at all, except suboptimal index scan (possibly false 
matches) for longish values?



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] INT64 and index keys

[Dmitry Yemanov]

16.02.2022 12:54, Dmitry Yemanov wrote:


It looks so. Unless we miss something (Alex?), perhaps we need to add a 
runtime check that rejects key creation for INT128 values longer than 34 
decimal digits.


Thinking twice, an overflow error should already been raised when a 
longish INT128 is converted into DECFLOAT(34) before composing the index 
key.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] INT64 and index keys

[Dmitry Yemanov]

16.02.2022 12:19, Mark Rotteveel wrote:


However, that was not my main point. My main point was that it sounds 
like an index format that was created for supporting DECFLOAT(34), and 
that it is not suitable for the full range of INT128 and NUMERIC/DECIMAL 
backed by INT128 (for the same reasons the DOUBLE PRECISION format is 
not suitable for BIGINT and NUMERIC/DECIMAL backed by BIGINT).


It looks so. Unless we miss something (Alex?), perhaps we need to add a 
runtime check that rejects key creation for INT128 values longer than 34 
decimal digits. And find a better solution in some later release.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] INT64 and index keys

[Dmitry Yemanov]

15.02.2022 18:37, Kjell Rilbe wrote:


With respect, since I'm not in the dev team, I fail to see it as an 
important feature to avoid index rebuild when changing between integer 
and numeric column types:


1. Changing between such types must be pretty rare, at least on 
production databases. During app development, yes, sure, but at that 
stage the data is probably small or non existent, so rebuild won't 
matter anyway.


Your schema upgrade may change keys from INT to BIGINT (when tables grow 
more than expected) or, more usually -- NUMERIC(N, 3) is changed to 
NUMERIC(N, 5) for quantities, or NUMERIC(12, N) is changed to 
NUMERIC(18, N), etc. And it should be applied to production. And a 
longish index rebuild will block all other DML on that table.


Maybe not so common, usually not recommended, etc -- but surely possible.


2. It already doesn't work and nobody has really complained, correct?


Correct, but it does not mean we cannot do something better. Not doing 
something is always faster than doing something ;-) so why not?


3. Changing data type of an indexed column is an operation where you do 
expect a non-negligible impact, i.e. you expect the DB engine to have to 
do "some work".


Users of other DBMS usually expect to see "some work" while adding a 
column to a table with 10^9 rows (or dropping a column there). Firebird 
does it almost instantly. So expectations are somewhat subjective and 
may be out of sync with realities...


So, I'd vote for a solution that drops the ambition to avoid index 
rebuilds, and instead focuses only on index size and index performance.


We can (and I believe we should) do both -- improve index 
size/performance and avoid index rebuilds if possible.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] INT64 and index keys

[Dmitry Yemanov]

15.02.2022 14:41, Dimitry Sibiryakov wrote:


Is "stored keys independent of the declared scale" a really useful 
and used feature?


That's a separate but also good question. Double precision keys allow 
independence of both precision (within its range) and scale. AFAIU, the 
idea was to allow changing column data types between 
FLOAT/DOUBLE/SMALLINT/INT and between different NUMERIC/DECIMAL 
precisions/scales without rebuilding the index (i.e. keys in the old and 
new formats may co-exist).


But then surprises start to appear.

First of all, it does not work this way. We rebuild an index every time 
a column's data type is changed, regardless of the old/new precision and 
scale. Even if the field is altered to the same data type. And AFAIK, 
it's not something we changed in Firebird, the original IB6 code already 
worked this way.


Second, co-existence of keys in the old and new formats is also buggy 
now, see below.


recreate table t_scale (col1 numeric(5, 2), col2 numeric(5, 2));
create index t_scale_i1 on t_scale(col1);
create index t_scale_i2 on t_scale(col2);
commit;

insert into t_scale values (1.23, 1.23);
commit;

select * from t_scale;

COL1 COL2
 
1.23 1.23

select * from t_scale where col1 = 1.23;

COL1 COL2
 
1.23 1.23

select * from t_scale where col2 = 1.23;

COL1 COL2
 
1.23 1.23

alter table t_scale alter col1 type numeric(6, 3);
alter table t_scale alter col2 type numeric(5, 1);
commit;

select * from t_scale;

COL1 COL2
 
   1.230  1.2

select * from t_scale where col1 = 1.23;

COL1 COL2
 
   1.230  1.2

select * from t_scale where col1 = 1.230;

COL1 COL2
 
   1.230  1.2

-- so far so good

select * from t_scale where col2 = 1.2;

-- NOTHING! WTF?

select * from t_scale where col2 = 1.23;

-- ALSO NOTHING!

We cannot access the existing value via the index anymore. This happens 
because (even after index rebuild) the stored key belongs to the old 
format, i.e. 1.23. It cannot be matched while searching for 1.2. It is 
matched while searching for 1.23, but then the record is read, its 
stored value 1.23 (old format) is converted into 1.2 (new format) and 
then re-compared with 1.23 thus being false again.


I can think of two ways to proceed:

1) Keep rebuilding the index every time the type is changed. Cast all 
column values into the new format before storing keys into the index. 
Forget about being scale-independent, pick the scale from the latest 
table format (and store BIGINTs or all exact numerics as integers 
without scale, as you suggest).


2) Prohibit decreasing the scale. Avoid rebuilding the indices unless 
really necessary (idx_itype is changed). Consider it a good thing (tm).


Maybe there may be other options, I didn't think deeply about this.


Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Firebird-devel] INT64 and index keys

[Dmitry Yemanov]

All,

Historically, we store most numerics as double precision inside index 
keys. It makes stored keys independent of the declared scale and allows 
both prefix and suffix compression. However, int64 keys (BIGINT and 
largish NUMERICs/DECIMALs) do not fit the double format without risk of 
rounding/truncation, so they're stored differently -- normalized to the 
maximum value that fits into double and then re-scaled. The problem is 
that this format is not well-compressable.


Below is the storage / compression statistics for the same data (10^6 
identical values) stored as INT, BIGINT and INT128, all indexed. Column 
ID is unique, column COL contains some duplicates.


T_INT (130)
Index I_INT_ID (0)
Root page: 10201, depth: 2, leaf buckets: 726, nodes: 100
Average node length: 5.85, total dup: 0, max dup: 0
Average key length: 3.00, compression ratio: 1.32
Average prefix length: 2.96, average data length: 1.00
Index I_INT_COL (1)
Root page: 23911, depth: 2, leaf buckets: 683, nodes: 100
Average node length: 5.51, total dup: 367845, max dup: 8
Average key length: 2.65, compression ratio: 1.49
Average prefix length: 3.32, average data length: 0.64

T_BIGINT (131)
Index I_BIGINT_ID (0)
Root page: 25109, depth: 3, leaf buckets: 1475, nodes: 100
Average node length: 11.90, total dup: 0, max dup: 0
Average key length: 9.04, compression ratio: 1.00
Average prefix length: 2.96, average data length: 6.04
Index I_BIGINT_COL (1)
Root page: 26580, depth: 3, leaf buckets: 1157, nodes: 100
Average node length: 9.32, total dup: 367845, max dup: 8
Average key length: 6.46, compression ratio: 1.39
Average prefix length: 5.17, average data length: 3.83

T_INT128 (132)
Index I_INT128_ID (0)
Root page: 27957, depth: 3, leaf buckets: 739, nodes: 100
Average node length: 5.96, total dup: 0, max dup: 0
Average key length: 3.10, compression ratio: 1.59
Average prefix length: 3.87, average data length: 1.05
Index I_INT128_COL (1)
Root page: 27979, depth: 2, leaf buckets: 697, nodes: 100
Average node length: 5.61, total dup: 367845, max dup: 8
Average key length: 2.76, compression ratio: 1.79
Average prefix length: 4.23, average data length: 0.70

As we can see, both BIGINT indices are almost twice bigger in storage 
than either INT (which is stored as DOUBLE in the index) or INT128 
(which is stored as DECFLOAT in the index), due to longer keys. Note 
that it also forces I_BIGINT_COL to have depth == 3 rather than 2.


Maybe we couldn't do any better before FB4, but now we have INT128 and 
DECFLOAT which use more effective index key format, so I asked myself 
whether it makes sense to utilize it for int64 values too.


Below is the stats if BIGINT would be stored as idx_decimal in the index:

T_BIGINT2 (134)
Index I_BIGINT2_ID (0)
Root page: 37193, depth: 3, leaf buckets: 739, nodes: 100
Average node length: 5.96, total dup: 0, max dup: 0
Average key length: 3.10, compression ratio: 1.59
Average prefix length: 3.87, average data length: 1.05
Index I_BIGINT2_COL (1)
Root page: 37215, depth: 2, leaf buckets: 697, nodes: 100
Average node length: 5.61, total dup: 367845, max dup: 8
Average key length: 2.76, compression ratio: 1.79
Average prefix length: 4.23, average data length: 0.70

Storage surely looks good, but internal conversions to Decimal128 are 
not free. I've done a few artificial tests (index scan in the loop with 
many iterations, CPU bound as tables fit the page cache), here are the 
results:


(1) Wide range scan:
  T_INT = 3.53s
  T_BIGINT = 3.82s
  T_INT128 = 4.36s
  T_BIGINT2 = 3.72s

More or less what's expected: different number of pages are accessed, so 
T_BIGINT2 wins as compared to T_BIGINT. Interesting that T_INT128 has 
the same index size, but is noticeably slower. Due to a bigger 
computational overhead?


(2) Narrow range scan:
  T_INT = 2.74s
  T_BIGINT = 2.79s
  T_INT128 = 2.83s
  T_BIGINT2 = 2.76s

Almost the same results for both BIGINT compressions.

(3) Unique scan:
  T_INT = 1.83s
  T_BIGINT = 1.89s
  T_INT128 = 2.09s
  T_BIGINT2 = 2.05s

This is the worst case for T_BIGINT2, it loses ~10% to T_BIGINT.

In cases (2) and (3) index size does not matter, only index depth 
matters. So the stats reflects CPU overhead of btr.cpp::compress() and 
data type conversions.


It's also likely that if BIGINT is used as PK and currently its index is 
4 levels depth, the new storage will shrink it down to 3 levels and this 
could noticeably improve both lookup and (especially) join performance.


Does anyone think we should investigate this possibility more closely? 
Any other comments?



Dmitry


Firebird-Devel mailing list, web interface at 

Re: [Firebird-devel] Dropping database default SQL SECURITY

[Dmitry Yemanov]

14.02.2022 22:40, Roman Simakov wrote:


I don't remember exactly why we decided to make it nullable. I suppose
for more backward compatibility. If a client doesn't use it it will be
NULL everywhere.


Maybe the idea was to distinguish between legacy databases (restored 
without SQL SECURITY) and explicitly declared SQL SECURITY in newer 
databases?



So in fact it can be NULL physically. But logically only two options
make sense. NULL is considered as legacy, i.e. INVOKER.
Thus you are right. For INVOKER we have two values to read and one to write.


The question is whether seeing legacy is really important. If not, I'd 
suggest to convert NULL into INVOKER during restore.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Compiled statement cache

[Dmitry Yemanov]

10.02.2022 20:18, Adriano dos Santos Fernandes wrote:


We should have better strategy for request cache inside the statement.
If they are cheap to create, it would make no sense to never destroy
them like now.


Looking at Statement::getRequest() I see it as dirty cheap, just a 
matter of few allocations.



I would calculate size of statement as sum of DSQL statement pool + JRD
statement pool after it is prepared.


Sounds reasonable.


Please note that requests (both DSQL and JRD) are also created from the
statement pools.

But if there are active requests, I think the statement should not even
be considered to be taken out of cache. It's necessary in this case, so
in reality it does not use cache space.

So I think cache size (to remove least recent used) should not consider
active (necessary) statements.


Getting rid of long-running but rare statements may be useful. If we 
speak LRU, then the cached statement should be stamped when a new child 
request is created. So we may defer "uncaching" of active request until 
it's freed by user (if its statement weren't re-stamped in the 
meantime). It's size should not be taken into account, as you say.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Compiled statement cache

[Dmitry Yemanov]

10.02.2022 19:54, Adriano dos Santos Fernandes wrote:


I come with this requirement because verifyAccess is currently part of
compilation.

But as I said and Vlad also said, we can remove roles from key and
verify (with verification cache) after get statement from cache. This
would be better.


Yes, this gets my vote too.


Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Compiled statement cache

[Dmitry Yemanov]

10.02.2022 19:21, Dimitry Sibiryakov wrote:


Only if such translation is made right. Remember charset introducers.


They're a problem, but it may only cause a cache miss, not a false 
match, right?


But apparently to transform the query before using it as a cache key 
is a right idea. Two queries different only by some whitespaces, 
comments or case (unless in literals) should not miss the cache.


In the ideal world, maybe. But this is *much* more complicated than it 
seems at the first glance.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Compiled statement cache

[Dmitry Yemanov]

10.02.2022 16:01, Adriano dos Santos Fernandes wrote:


(Jrd)Statement is reused - new jrd_req are get from same statement.


IIRC, the existing cache of internal requests preserves jrd_req's. Am I 
right that after the jrd_req->Statement refactoring the cost of creation 
of new jrd_req is trivial, so it does not make sense to preserve them?



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Compiled statement cache

[Dmitry Yemanov]

08.02.2022 16:36, Adriano dos Santos Fernandes wrote:


First what should be the statement key in the cache?

I've peek these:
- statement text
- clientDialect
- isInternalRequest
- current client charset (as external engines may change it)


Cannot the UTF8-translated SQL text (which is currently inside 
Statement::sqlText) be the key, to avoid dependency on the charset? I 
don't think that semantically different statements may have the same 
UTF8 representation. However, it would make sense to have a single 
cached statement for the same statement executed from different client 
charsets.



- active roles


Already wrote about that.


Then there is when statements should go out of cache?

I see three approaches:
- 1. Timeout after its put in the cache, updated when it is get from it
- 2. LRU based on memory consumption and max cache size
- 3. Both 1. and 2.


I'd start with 2. How are you going to calculate the memory consumption? 
Size of the statement pool + impureSize?



Should it be enabled by default?


Maybe, with a reasonable size. As Mark says, it doesn't make sense to 
disable new features by default ;-)



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Compiled statement cache

[Dmitry Yemanov]

10.02.2022 15:57, Adriano dos Santos Fernandes wrote:




If we need to take roles into an account - only for attachment with same
USER.


Even without shared cache, user can change its roles with SET ROLES and
new prepared statements should work as before even when they were
previously cached with different roles.


I'm not sure I get why security credentials should affect the cache at 
all. From the runtime POV, all BLR/SQL operations 
(current_user/current_role/rdb$*_roles) are redirected to Attachment, 
AFAIK we don't store anything role-specific inside the statement tree. 
From the security POV, we just need to execute verifyAccess() for the 
request retrieved from the cache.


What am I missing?


Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Acquire lock for relation (XXX) failed

[Dmitry Yemanov]

01.02.2022 17:11, Jiří Činčura wrote:

session 1: CREATE INDEX ... ON VERY_BIG_TABLE (...); COMMIT;


Can same be achieved by selects/insert/updates and some TPB settings (like i.e. 
isc_tpb_concurrency)?


Sure, but with isc_tpb_consistency (not isc_tpb_concurrency).


Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Acquire lock for relation (XXX) failed

[Dmitry Yemanov]

01.02.2022 16:45, Jiří Činčura wrote:

Hi *,

What do I have to do to get an error "Acquire lock for relation (XXX) failed"?


session 1: CREATE INDEX ... ON VERY_BIG_TABLE (...); COMMIT;
session 2: INSERT INTO VERY_BIG_TABLE VALUES (..) -- in NO WAIT transaction


Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] ExprNode::FLAG_VALUE

[Dmitry Yemanov]

06.01.2022 17:13, Adriano dos Santos Fernandes wrote:


See also:

https://github.com/FirebirdSQL/firebird/issues/1355

https://github.com/FirebirdSQL/firebird/commit/626ab18c426fd32d482e02093e72e57330596174


Worth testing GROUP BY ,  ?


Since v3 we must be safe. Aggregate stream allocates impure space for
the expressions in its own region.


Correct, although I find it weird that the impure space is allocated 
dynamically rather than statically (in the request's impure area):


unsigned impureCount = m_group ? m_group->getCount() : 0;

if (!impure->groupValues && impureCount > 0)
{
  impure->groupValues = FB_NEW_POOL(*tdbb->getDefaultPool()) 
impure_value[impureCount];

  memset(impure->groupValues, 0, sizeof(impure_value) * impureCount);
}

Why is it done this way? AFAIU, impureCount is known during the compile 
time, so the whole impure area could be preallocated during the prepare 
stage.



I'd say the flag exists because things were different before. Aggregate
were using expressions' impure regions.


Then FLAG_VALUE may be safely removed.


Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Remove blr_parameter3

[Dmitry Yemanov]

14.01.2022 16:52, Adriano dos Santos Fernandes wrote:


Our code does not generate blr_parameter3.

I propose to remove this code, commenting this verb for not immediate reuse.

It looks like as it tries to put in the third parameter slot the maximum
string length moved to the parameter.

Do anyone has some background about this?


I have no clue about its purpose, so I don't mind removing it.


Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] ExprNode::FLAG_VALUE

[Dmitry Yemanov]

06.01.2022 04:28, Adriano dos Santos Fernandes wrote:


There is ExprNode::FLAG_VALUE ("Full value area required in impure
space"), inherited from old (2.5) code base nod_value.

It's set by sort subsystem and used only for parameters and variables.


Initially it as also used for fields. But at some point of time fields's 
impure area became always sizeof(impure_value_ex), unconditionally.



It makes then allocate impure space for impure_value_ex instead of
traditional dsc.


I'd say allocating sizeof(dsc) is dangerous for these nodes. Look at 
EVL_assign_to(), for example. It does:


impure_value* impure =
  (impure_value*) ((SCHAR *) request + node->nod_impure);
...
impure->vlu_desc.dsc_dtype = desc->dsc_dtype;
...
return >vlu_desc;

i.e. it works just because impure_value has "dsc vlu_desc" at the first 
position and thus the used part of impure_value is equal to sizeof(dsc). 
Change that and the things get broken. Try to use vlu_flags for 
arguments/variables (make them invariant, for example) and things get 
broken. Too fragile, IMO. I'd consider allocating sizeof(impure_value) 
instead of sizeof(dsc), just for safety sake.



Most nodes allocate space for impure_value. But not all of them.

Literals directly return the descriptor set in compile time.

I see no usage of the expressions impure_value in sort. And if they were
using, we'd certainly have a problem with literals.


See also:

https://github.com/FirebirdSQL/firebird/issues/1355

https://github.com/FirebirdSQL/firebird/commit/626ab18c426fd32d482e02093e72e57330596174

Worth testing GROUP BY ,  ?


Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors

[Dmitry Yemanov]

20.12.2021 13:58, Dimitry Sibiryakov wrote:


Even non-scrollable cursors can know total number of records if plan 
SORT is used


Sort may be hidden inside other execution nodes, so it's not always as 
easy to know. I'd rather avoid returning (or not) info depending on the 
query plan.



or they are fetched to the end


This is surely possible, but is it really needed? The cursor is likely 
to be closed soon, who would need a row count at this point?



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors

[Dmitry Yemanov]

Mark et al,

> Looked at it again, and being able to get the total row count will work
> for me. Is this information already available, or does this still need
> to be implemented?
What would you expect from the "row count" requested for a 
non-scrollable cursor? It cannot return the true count, as already 
explained here. Should it return some magic number (0? 1? -1?) or would 
error be appropriate? If the latter, should it be isc_infinap 
(information type inappropriate for object specified) or isc_infona (no 
information of this type available for object specified)?



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors

[Dmitry Yemanov]

19.12.2021 16:04, Mark Rotteveel wrote:

Looked at it again, and being able to get the total row count will work 
for me. Is this information already available, or does this still need 
to be implemented?


Implemented but not yet committed. I will post a pull request tomorrow.

One last thing I was wondering about (but haven't had time to verify): 
what happens with positional updates or deletes when scrolling?


Specifically:
- After an update: does the cursor have the original row content or the 
updated content?


Original.

- After a delete: does the row disappear from the cursor, does the 
cursor retain the original content of the row, or does an 'empty' (e.g. 
all NULL) row exist in the cursor?


The original content is retained.

Basically, it should work the same way as for SELECT ... ORDER BY 
non-indexed-field (where records are cached inside the sort).



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] UDR for reading server configuration for Firebird QA

[Dmitry Yemanov]

12.12.2021 01:21, Adriano dos Santos Fernandes wrote:


If it does not return sensitive information, I see no problem in add it 
to examples UDR project.


I like the idea about /examples, but the server configuration *is* 
sensitive information, if exposed to a non-DBA user. So the examples 
script should at least restrict the permissions for that UDR. And the QA 
framework would need to adjust the grants if it needs such an "unsecure" 
access to configuration.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors

[Dmitry Yemanov]

08.12.2021 13:49, Dimitry Sibiryakov wrote:


Storing of fetched rows is unavoidable indeed but prefetch?.. Is it 
done in background or before returning of the first row to client even 
in embedded mode?


The latter. But prefetch is done in small chunks, usually it does not hurt.


Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors

[Dmitry Yemanov]

08.12.2021 12:37, Tony Whyman wrote:


It would also be very useful to get rowcount 
returned for unidirectional cursors if that was readily possible. At 
present, it is only possible to get an accurate count of the number of 
rows in a cursor after you have fetched all of them.


It cannot be calculated without fetching all rows, even inside the 
engine. For scrollable cursors, rows are prefetched and cached by the 
engine and thus the count can be easily returned. It has its cost, but 
generally it's unavoidable anyway for scrollable cursors. However, 
prefetching uni-directional cursors in advance would be a huge overkill 
which nobody usually needs. So "row count" is likely to return zero or 
error for uni-directional cursors. However, if you desperately needs 
"row count" and ready to pay the price, then just open the cursor as 
scrollable and fetch only forward. The "row count" will be available 
automagically.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors

[Dmitry Yemanov]

28.11.2021 14:45, Mark Rotteveel wrote:


We don't have anything like this. Theoretically, we could extend 
IRecordSet with something similar (although it would also require a 
protocol change), but the question is whether it's really needed. 
Personally, I don't see it useful per se. If users want to know a 
number of rows, then perhaps an explicit getRowCount() method would be 
more useful (*). But AFAIU the Java API does not mention it.


(*) not applicable to uni-directional cursors?


The intent of the method is to report the position of the current row. 
The example it is abused by some to get the total size was just an 
illustration of why people would expect a value after requesting the 
last row. But the same would be a problem if people went to last, 
scrolled around and then want to know the current row position.


I was thinking about providing both "current position" and "row count" 
information values for a cursor. Both are doable, however the former has 
some issues:


1) Adjustments due to prefetch should be taken into account at both 
client and server sides, thus complicating the code (information 
response buffer must be parsed, value received from the engine/server 
must be replaced with the adjusted one).


2) If used "with purpose", it may be requested by the client after every 
fetch, thus multiplying the round-trips and killing the prefetch 
benefits. So it could make sense to request the position (send 
op_info_cursor) internally after every op_fetch_scroll packet. As every 
fetch request may return multiple rows, sending many positions is 
unnecessary and only position of the first row could be returned. This 
puts additional requirements to the client-side protocol implementations.


3) "row count" makes it possible to know the position after fetchLast() 
and everything else could be calculated locally by the client library, 
thus making the server-supported "current position" totally unnecessary.


Do I miss anything? Could we agree on having only "row count" returned 
via op_info_cursor and leaving "cursor position" (getRow() in Java API) 
up the connectivity library developers?



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors

[Dmitry Yemanov]

01.12.2021 16:07, Dimitry Sibiryakov wrote:


Also if IResultSet is returned from IAttachment::openCursor() there 
is no (visible) IStatement at all.


And I see the same problem for setCursorName() which is available only 
through IStatement. An oversight?



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] INF_database_info

[Dmitry Yemanov]

30.11.2021 14:58, Roman Simakov wrote:


isql after set stat returns some statistics of a query execution like

Current memory = 10647136
Delta memory = 148432
Max memory = 10728544
Elapsed time = 162.476 sec
Cpu = 0.000 sec
Buffers = 1024
Reads = 332280
Writes = 0
Fetches = 70584692

Actually the server gets it from the function INF_database_info using
dbb_stats. For SuperServer architecture it includes concurrent changes
but for Classic it does not. At least it's inconsistent.


That's legacy we used to deal with.


Also note the statistics is shown after query execution and I guess a
user expects it to be related to the query.
I think it's more correct to use att_stats here. Even taking into
account legacy clients. att_stats like dbb_stats for Classic mode.


Basically agreed. Although I foresee inconsistencies with disk IO 
counters. E.g. dbb_stats now includes writes performed by cache writer 
in SS and writes performed by AST in CS. They will not be accounted in 
att_stats.



Another option is to add att_stats as well and show them in isql if
stat ON.


We cannot change meaning of the existing counters in 
ISC_database_info(). Adding attachment-level IO counters to the info is 
possible, but ISQL will need to check whether new INF items are 
available and fallback to the dbb counters if connected to older engines.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Plans for 4.0.1

[Dmitry Yemanov]

01.12.2021 14:25, Pavel Cisar wrote:


would be 4.0.1 released in December, January or later?


I'd like to have it released in December. I don't see any showstoppers 
and plan to commit my own pending parts during a week or so.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Plans for 3.0.8

[Dmitry Yemanov]

01.12.2021 14:01, Gabor Boros wrote:


Please update the Roadmap page: https://firebirdsql.org/en/roadmap


Done.


Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors

[Dmitry Yemanov]

28.11.2021 14:37, Dimitry Sibiryakov wrote:


And it doesn't need to be a dedicated method of IResultSet. Something 
generic and easily extendable like IResultSet::getInfo() would be enough.


Given that any DSQL statement cannot have multiple result sets, I doubt 
IResultSet::getInfo() is really required, IStatement::getInfo() could be 
used instead. Or if we go for consistency, IResultSet::getInfo() could 
be added, but internally it calls IStatement::getInfo() (and they share 
the same subset of info codes). Opinions?



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] WNET future

[Dmitry Yemanov]

30.11.2021 22:47, Leyne, Sean wrote:



With dropping this option ability to open database file from mounted
network drive also will be dropped (or at least must be heavily reworked).


Why?

Support for being a WNET server/listening for WNET connections should be 
un-related to accessing database files on CIFS, SMB or NFS shares


By default (RemoteFileOpenAbility = 0), Firebird does not open remote 
files directory, instead it extracts a host name from the share and 
connects remotely (using WNET for SMB shares or TCP for NFS shares).



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] WNET future

[Dmitry Yemanov]

30.11.2021 20:10, Dimitry Sibiryakov wrote:


Then reworking seems easy. Just preserve ISC_analyze_pclan() to 
extract the lanman hostname and then try connecting via TCP.


The question is port number. It is changed more frequently than pipe 
name.


True, but the same applies to NFS shares. Sad, but this is not a 
commonly used feature anyway.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] WNET future

[Dmitry Yemanov]

30.11.2021 20:01, Dimitry Sibiryakov wrote:


It can\t be reworked reliably - nobody can guarantee that DNS host 
name matches lanman name.


Fortunately modern versions of gethostbyname() can resolve lanman 
names as well.


Then reworking seems easy. Just preserve ISC_analyze_pclan() to extract 
the lanman hostname and then try connecting via TCP.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Firebird-devel] WNET future

[Dmitry Yemanov]

All,

We had discussed in the past that supporting WNET does not make sense, 
as it has nearly zero advantages over TCP and pretty much nobody uses it 
nowadays.


Could we please make a final decision about that? Should it be dropped 
in v5 or somewhat later? Or should it be preserved during a foreseeable 
future?



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors

[Dmitry Yemanov]

28.11.2021 14:47, Dimitry Sibiryakov wrote:


RDB$DB_KEY for the current record as well.


It's not a property of the cursor. Consider joins, unions, procedures, 
views, etc.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors

[Dmitry Yemanov]

27.11.2021 15:04, Dimitry Sibiryakov wrote:


I would consider adding a NO_BATCH flag (which is currently triggered 
using FOR UPDATE syntax) to cursorFlags.


That would be useful in some cases. And if statements with "WHERE 
CURRENT OF" condition also were somehow marked client libraries could 
force cursor position synchronization before executing them.


I'd expect FOR UPDATE to still disable batching even without that 
NO_BATCH flag (as now), just because I can hardly see the need to 
prefetch cursor (and later reposition) if a positioned UPDATE/DELETE is 
expected.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors

[Dmitry Yemanov]

28.11.2021 14:37, Dimitry Sibiryakov wrote:


And it doesn't need to be a dedicated method of IResultSet. Something 
generic and easily extendable like IResultSet::getInfo() would be enough.


Then we may support both "current position" and "total row count" in 
getInfo(), letting the client wrapper to use whatever it needs.


But please let's cover this by a separate tracker ticket.


Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors

[Dmitry Yemanov]

28.11.2021 14:02, Mark Rotteveel wrote:


Is there a way to determine at which row the cursor is currently 
positioned? JDBC has the ResultSet.getRow()[1] method which is 
documented as: "Retrieves the current row number. The first row is 
number 1, the second number 2, and so on."


This is not problematic when using first, next, prior, relative or 
absolute, but once you do fetch_last, you don't know the row number 
until you do a first or absolute.


The usefulness of this method is limited, but sometimes it is abused by 
people wanting to know how many rows there are in a result set (by 
requesting the last row), and if I can't fulfill this requirement, I 
need to at least document this.


We don't have anything like this. Theoretically, we could extend 
IRecordSet with something similar (although it would also require a 
protocol change), but the question is whether it's really needed. 
Personally, I don't see it useful per se. If users want to know a number 
of rows, then perhaps an explicit getRowCount() method would be more 
useful (*). But AFAIU the Java API does not mention it.


(*) not applicable to uni-directional cursors?


Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors

[Dmitry Yemanov]

28.11.2021 13:11, Mark Rotteveel wrote:


In other words, it looks as if Firebird when asked for 4 rows, will 
return 4 rows and *also* buffer 4 more *on the server*, and return those 
unconditionally on the next fetch.


Looks right, although weird I didn't notice that during the testing.
To be fixed.


Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors

[Dmitry Yemanov]

27.11.2021 17:28, Mark Rotteveel wrote:


I'm running into some odd behaviour. As soon as I do a fetch_next or 
fetch_prior, any subsequent fetch ignores the fetch direction, and 
applies fetch_next (or fetch_prior).


Correction: it behaves as fetch_next (or fetch_prior) for the amount of 
rows that was fetched in the last fetch (or at least some number of rows 
buffered on the server).


I can understand needing to take into account the client-side buffer, 
but I don't think that I should take into account any rows that the 
server has buffered.


If you requested N rows from the server but now switching to the 
different fetch operation before getting all those rows, I expect the 
client to receive (and discard) the remaining part of the requested rows 
before sending the new opcode. This is what fbclient does currently.


However, I can see the point in letting the server to cleanup the queue 
instead of the client. I will give it a try.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors

[Dmitry Yemanov]

27.11.2021 12:42, Mark Rotteveel wrote:


Yes, I think that is perfectly acceptable for an initial version. 
Scrollable cursors are a bit of an oddity anyway, but having 
scrollable cursors in embedded access, but not in remote access is 
IMHO less acceptable than bad performance. As long as the performance 
behaviour is clearly documented, people can make the decision if the 
bad performance is worth the utility of scrollable cursors for 
themselves.


I've just committed network support for scrollable cursors to master. 
Prefetch logic is supported for NEXT/PRIOR navigations. Protocol 
changes are documented here:


https://github.com/FirebirdSQL/firebird/issues/7051


Are other values than 0 or 1 (CURSOR_TYPE_SCROLLABLE) currently possible 
for cursorFlags (flags)?


Nope (so far). Although remembering our old discussion re. whether 
scrollability should be represented via a syntax keyword or an API flag, 
I would consider adding a NO_BATCH flag (which is currently triggered 
using FOR UPDATE syntax) to cursorFlags.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Firebird 5 and Update...Returning

[Dmitry Yemanov]

26.11.2021 17:47, Tony Whyman wrote:


3. IBX tries to be general purpose and so prepares a statement, then 
checks the statement type, and then calls the appropriate IStatement 
method. 


Then it shouldn't be broken. It gets isc_info_sql_stmt_select, calls 
openCursor() and everything works as intended.


Changing the statement type semantics broke the logic used to 
determine which method to call.


I still don't see how it was broken, sorry. See above.


Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Firebird 5 and Update...Returning

[Dmitry Yemanov]

26.11.2021 17:28, Tony Whyman wrote:

1. IAttachment.prepare is used to parse 'Update Employee Set Hire_Date = 
? Where EMP_NO = ? Returning LAST_NAME'


2. IStatement.getType is then used to determine the statement type.


OK so far.

3. Prior to calling IStatement.Execute, the statement type is checked. 
The current (IBX) code raises an exception if the sql statement type is 
isc_info_sql_stmt_select in order to stop the wrong IStatement method 
being called.


Why? How is it different from SELECT returning isc_info_sql_stmt_select? 
They should work the same way. Is the SQL text also parsed to detect 
UPDATE and that conflicts with the returned statement type?



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors

[Dmitry Yemanov]

26.11.2021 14:51, Tony Whyman wrote:


Just compiled the updated master branch and tested with IBX. All looks 
good with the test suite returning the same set of results for bot 
remote and local databases.


Many thanks for the good work.


And my thanks to you for testing ;-)

I've noticed that the ODS is now 13.1 compared with 13.0 for Firebird 
4.0.0. Is this due to your patch or have there been other changes that 
have needed this?


Other changes (some are still in progress). My patch does not affect the 
engine at all.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors

[Dmitry Yemanov]

Mark et al,

Yes, I think that is perfectly acceptable for an initial version. 
Scrollable cursors are a bit of an oddity anyway, but having scrollable 
cursors in embedded access, but not in remote access is IMHO less 
acceptable than bad performance. As long as the performance behaviour is 
clearly documented, people can make the decision if the bad performance 
is worth the utility of scrollable cursors for themselves.


I've just committed network support for scrollable cursors to master. 
Prefetch logic is supported for NEXT/PRIOR navigations. Protocol changes 
are documented here:


https://github.com/FirebirdSQL/firebird/issues/7051

The test set to cover different navigation scenarios is being created, 
once it's ready we'll validate the results between PSQL cursors, 
embedded access, network access w/prefetch and network access 
wo/prefetch (FOR UPDATE added to SELECTs). Obviously, we expect them to 
match ;-)


I'd appreciate if Mark could test scrollability from the Jaybird side 
too (i.e. without fbclient involved), but the new protocol should be 
supported for that.


The committed version is linked to protocol 17 which was introduced for 
4.0.1. Thus technically, this improvement can be backported and released 
together with v4.0.1. However, I'm not sure this risk is acceptable for 
a point release and I'm ready to introduce protocol 18 in master for v5. 
Opinions, please.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Request IDs, MON$STATEMENTS and MON$STATEMENT_ID

[Dmitry Yemanov]

09.11.2021 03:21, Adriano dos Santos Fernandes wrote:


I think all statements should be split in MON$STATEMENTS and
MON$COMPILED_STATEMENTS.


What about execute_immediate? Should they be reported only inside 
MON$STATEMENTS or also have a record in MON$COMPILED_STATEMENTS but as 
not shareable (bound to the current attachment) or be globally shared 
but "shortlived" (destroyed when the request is finished)?



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] ON DISCONNECT triggers and MON$ATTACHMENTS

[Dmitry Yemanov]

09.11.2021 12:00, Alex Peshkoff wrote:


Currently ON DISCONNECT triggers are not called when an attachment is 
deleted from MON$ATTACHMENTS by another attachment. Is it the correct 
behavior? 


On my mind - not.
The only case when ON DISCONNECT triggers not to be called is server 
shutdown.


Agreed.


Dmitry



Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Request IDs, MON$STATEMENTS and MON$STATEMENT_ID

[Dmitry Yemanov]

08.11.2021 19:54, Adriano dos Santos Fernandes wrote:


In this case I think we better sooner introduce MON$COMPILED_STATEMENTS
with some duplicate information already present in MON$STATEMENTS,


Obviously, SQL text and plan belong to the statement. The rest looks 
request-specific (timestamp will represent request startup time).


However, MON$ATTACHMENT_ID is tricky. It's needed in 
MON$COMPILED_STATEMENTS now, but likely to be not needed once the shared 
statement cache is implemented. Also deprecate it then? Or maybe in the 
future some statements could not be globally cacheable and instead 
prepared per-attachment (as now)?



declaring some columns of MON$STATEMENTS as deprecated to be removed in
future versions.


We never removed columns from system tables and I'm not sure this is a 
good practice, as apps / components may have field names hardcoded / 
cached. Returning NULL for deprecated columns should be perfectly 
enough, IMO.



And make (now) MON$STATEMENTS ids represent request IDs generated on
request start (for DSQL and routines).


This may break existing apps (Dimitry Sibiryakov's question). But given 
that an alternative (via new MON$ table) will be available, perhaps this 
is not so critical.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Request IDs, MON$STATEMENTS and MON$STATEMENT_ID

[Dmitry Yemanov]

08.11.2021 17:54, Dimitry Sibiryakov wrote:


Nope, I believe the new ID must be generated also by findRequest() if 
the clone was found in the cache.


Will it make impossible to detect repeatable execution of a prepared 
statement as opposite for execution of a new one every time?


findRequest() is not used for user DSQL requests, only for 
procedures/functions. So the top-level statement ID will be always 
stable, as before.


But if/when we separate compiled (cached) statements and running 
statements, the ID will be reset before every execution and your 
question may become actual. We'll either need a different "runtime" ID 
or some backpointer (ID) from request to its statement.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Request IDs, MON$STATEMENTS and MON$STATEMENT_ID

[Dmitry Yemanov]

08.11.2021 16:50, Adriano dos Santos Fernandes wrote:


Despite its name, what MON$STATEMENTS show are requests (not statements).


True, but statement/request separation didn't exist when MON$STATEMENTS 
was implemented ;-) And end users deal with statements, they have no 
idea what "request" is.


Nevertheless, it would be good to find a way to separate cached 
statements from its running instances (aka requests), ideally without 
breaking backward compatibility. It will be important once we start 
caching user statements.



When a stored routine is executed, finished and executed again, its
request is re-used. Re-used request maintain its ID and is mapped to
MON$STATEMENTS.MON$STATEMENT_ID.


Looks like a bug.


Is it an important design of MON$STATEMENTS that this (subsequent
execution of a request maintain its ID) happens?


Nope, I believe the new ID must be generated also by findRequest() if 
the clone was found in the cache.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Plans for 3.0.8

[Dmitry Yemanov]

06.11.2021 10:30, Omacht András wrote:


it's November now.

Any chance of coming out in the near future?

3.0.7 was released October 20, 2020.


I'm going to tag the tree tomorrow. The release will come shortly after.


Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Cascade replication

[Dmitry Yemanov]

27.10.2021 14:52, Pro Turm wrote:

Is it possible and how does a replica become primary at some point?


It never does it by itself. Firebird provides replication, but 
high-availability cluster (which you're talking about) is way more than 
just replication. Custom automatization is required.



e.g. in A->B->C, when A fails to become B->C ?


In read-only replica, this is impossible, as all changes are produced by 
A and the whole chain stops working without A.
In read-write replica, the B->C part will continue working without A 
automagically. But you need somehow to reconnect everybody from A to B 
(and make sure A never reappears).


Further, is it possible and how that from A->B->C the following happens  
A<-B->C ?


Impossible without manual DBA actions.


Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors

[Dmitry Yemanov]

23.10.2021 17:13, Mark Rotteveel wrote:


If record buffering is hard, it could have been done **without** it. 
Then it would have been the choice of the user whether it is worth the 
performance implications or not.


So you consider acceptable that forward-only usage of scrollable cursors 
is 10x slower than for regular cursors? Personally, I consider it hard 
to explain.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Cascade replication

[Dmitry Yemanov]

27.10.2021 09:06, Karol Bieniaszewski wrote:


Can you point me

https://github.com/FirebirdSQL/firebird/commit/e6a33454e871b9f9a368ccf281081e867c2b18cf 



what is enabled here and on which side?

If i understand correctly it is configured on primary database side not 
on replica?


Nope, it's configured on the replica side and allows the received 
changes to be propagated further (if replica is also configured as a 
primary, i.e. A->B->C).



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

[Dmitry Yemanov]

12.10.2021 18:26, Dimitry Sibiryakov wrote:


System tables are operated in system transaction


They're modified in user transaction(s).


Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Partitioning (was: Tablespaces proposal)

[Dmitry Yemanov]

12.10.2021 16:21, Dimitry Sibiryakov wrote:


   INSERT:

   Nothing except time wasting to calculation partitioning key and 
creation of a new partition if necessary (ignorable).


Slightly faster inserts into indices.


   UPDATE:
   DELETE:


Faster index GC after them.


   BACKUP:

Backup of separate partition is something nobody asked for so far. At 
least I cannot remember a ticket for gbak accepting filter condition 
inside of a table


It makes zero sense for gbak, because it restores the database as a 
whole and cannot append/replace records in existing database. But it may 
be possible with partitions in different files and nbackup.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

[Dmitry Yemanov]

12.10.2021 15:26, Dimitry Sibiryakov wrote:


In this case your vision of partitioning is quite special because in 
others' implementations it has nothing to do with multiple files


Partition is a page set. Different page sets may be surely stored inside 
a single database file, but they may also be stored in different files. 
It makes a lot of sense to nbackup only some partition(s), or store them 
on slow drives, etc -- the same as for tablespaces.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

[Dmitry Yemanov]

12.10.2021 14:52, Dimitry Sibiryakov wrote:



GOALS
==
1) Extend the current limits on database size


   Current limit is 16 TB and can be extended without explicit 
tablespace managing by something similar to OS memory mapping technique 
effectively adding some external-sourced bits to current 32 bits page 
number.



2) Keep non active parts of a database on slow disks (having big volume)


   This part is meaningless because good storages provide storage 
tiering at block level.



3) Split indices from the database


   It is useful only if tablespace can have bigger page size reducing 
index depth.


There's also 4th goal: provide internal infrastructure (splitting page 
spaces into multiple files) to support partitioning later. I expect 
these two features to share a lot.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

[Dmitry Yemanov]

12.10.2021 13:36, Kjell Rilbe wrote:


Support for many page sizes requires changes in page cache 
management and should

be considered together. I don't see it as "must have" feature, btw.


That's the feature that our DB would benefit most from probably, since 
some tables are orders of magnitude(s) larger than most others, so to be 
able to have a larger page size for those tables' indices only would 
probably be nice.


Given that FB4 supports 32KB page size, are those indices still deeper 
than 3 levels?



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

[Dmitry Yemanov]

11.10.2021 22:22, Lucas Schatz wrote:


Just to clarify, the use of tablespace will be optional, right?


Sure.


Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Charset of replicated SQL

[Dmitry Yemanov]

11.10.2021 18:11, Dimitry Sibiryakov wrote:


In which case charset received by IReplTransaction::executeSqlIntl() 
can be different from charset of attachment received by 
IReplicatedSession::init()?


Ideally, never.

I can imagine only the case of cascade replication when the Applier 
hacks replicator's connection charset.


Yes, in this case it may be possible. And I cannot imagine other cases 
so far.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Tablespaces proposal

[Dmitry Yemanov]

11.10.2021 18:41, Vlad Khorsun wrote:



2. *ALTER TABLESPACE  FILE '/path/to/file'*


   In DDL, ALTER usually combined with ADD | SET | DROP, so let follow this
convention. I.e. ALTER TABLESPACE  SET FILE '/path/to/file'


I'm not so sure about "usually", e.g. ALTER INDEX INACTIVE, ALTER DOMAIN 
TYPE X, etc. As for me, SET is better for cases where DROP may also be 
applied. But in fact we have both kinds of SET usage in the grammar and 
I cannot say which is better.


Should we consider optional AT before TABLESPACE in all CREATE 
 [AT] TABLESPACE  statements ?


I'd rather consider IN instead of AT, but also not insisting.


5. ALTER TABLE  *ALTER TABLESPACE { | MAIN}*

Data of the table will be moved to the specified tablespace or the 
main database.


   This statement not alters tablespace itself, i.e. there should be SET 
(and DEFAULT):


   ALTER TABLE  SET TABLESPACE [TO]  | DEFAULT


+1


   ALTER TABLE ALTER COLUMN ... SET TABLESPACE [TO] { | DEFAULT}


Do you mean blobs or vertical partitioning here?


It's possible to create up to 253 tablespaces.


   Two ID's is reserved for DEFAULT and TEMPORARY tablespaces, correct ?
I would reserve some more ID's for future system usage. I don't see it as
limitation for end users.


For regular tablespaces (created explicitly) - sure. But if we think 
about automatically created partitions, even 253 tablespaces may become 
a sad limit.



nbackup support is postponed.


   At this stage I agree, but this must be 1st goal after initial 
implementation, IMO.


The first PR may come without it, but I'm against releasing it until 
nbackup is supported.



Page size is identical for every tablespace and the main database.


Support for many page sizes requires changes in page cache management 
and should

be considered together. I don't see it as "must have" feature, btw.


Me neither.


Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Firebird 4 Replicaton - INET/inet_error: read errno during replicaton

[Dmitry Yemanov]

05.10.2021 09:44, Nils Bödeker пишет:


Are there any plans when the next subrelease are published.


Yes, during the next couple of months.


Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Plans for 3.0.8?

[Dmitry Yemanov]

06.09.2021 13:31, Omacht András wrote:


is 3.0.8 expected to be released in the near future?


Yes, September-October.


Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Dialect 3 inconsistent round/trunc - configurable calculation method needed

[Dmitry Yemanov]

02.09.2021 12:17, Mark Rotteveel wrote:

I find the suggestion to make this configurable an interesting one, but 
this wouldn't fundamentally resolve the issue you have, it just would 
change the compound error of calculation. For example, we followed your 
suggestion and provide an option to use double precision semantics for 
division or change the calculation so it provides half-up rounding 
instead of - effectively - floor rounding), then the result of 2.00 / 
3.00 would be 0.6667 (what you want), but the result of - for example - 
2.00 / 3.00 * 100 would be 66.6700, and I guess you would expect it to 
be 66.6667 and would prefer if the entire calculation happens in double 
precision (which brings along its own host of problems, because 
fundamentally double precision is not usable for exact calculations and 
has its own set of precision problems and compound errors depending on 
order of evaluation and actual values).


Let's discuss possible options and what benefits they would bring us. 
Being compatible with other RDBMS vendors may also count as a benefit ;-)


With all that said, I suggest you create a feature request ticket 
("type: new feature") for making it configurable, so it can be tracked 
and evaluated (and voted on by the community), but I wouldn't hold my 
breath (though I'm not a Firebird core developer, which means this is 
not my decision).


I agree to consider [configurable] alternatives to how numeric division 
is handled.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Dialect 3 inconsistent round/trunc - configurable calculation method needed

[Dmitry Yemanov]

31.08.2021 23:53, Mark Rotteveel wrote:

The only debatable feature of dialect 3 division is the fact the 
calculation stops (equivalent to floor rounding), while reduction of 
scale through assignment or cast applies half-up rounding


"Whether to round or truncate when performing division is 
implementation-defined." (c) SQL spec ;-)


However, being compliant does not necessarily mean being useful, the 
standard leaves too many gaps here.


this behaviour is consistent with integer division, otherwise 
NUMERIC(18,0) division and BIGINT division would have to behave 
differently (e.g. 14/3 = 5 for NUMERIC(18,0) vs = 4 for BIGINT), which 
would be confusing as hell


This is what PostgreSQL does. They use integral division only if both 
arguments are integers (not numerics!). However, this is not very 
confusing for them, because their "implementation-defined scale" is 
always the maximum possible (instead of our scaleA+scaleB), so they get:


select 14::bigint / 3::bigint = 4
select 14::numeric(18, 0) / 3::numeric(18, 0) = 4.667

the latter may produce 5 when casted to BIGINT explicitly, but from 
another side it will produce 4.67 when casted to NUMERIC(18, 2).



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Dialect 3 inconsistent round/trunc - configurable calculation method needed

[Dmitry Yemanov]

01.09.2021 09:29, Molnár Attila wrote:


In dialect 3 you have to cast to double always for not to loose precision.


I believe you may avoid the casts if you store everything as NUMERIC 
inside the db, but your procedures use DOUBLE (or better DECFLOAT) for 
inputs/outputs/locals.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] isc_info_sql_stmt_type/isc_info_sql_stmt_flags

[Dmitry Yemanov]

19.08.2021 18:29, Dimitry Sibiryakov wrote:


I'm not sure why the client application would need to know the 
statement type at all ;-)


Distinguish DDL from DML and transaction control - quite useful if 
you accept SQL from outside of application.


I mostly meant it's pointless to know the exact DML statement type.


Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] isc_info_sql_stmt_type/isc_info_sql_stmt_flags

[Dmitry Yemanov]

19.08.2021 17:30, Mark Rotteveel wrote:


Since RETURNING was created, we change values of isc_info_sql_stmt_type
so clients can understand that statements with RETURNING have values.

With GH-6815, RETURNING will return cursors (except for INSERT INTO ...
VALUES RETURNING which works as before).

Should we avoid mangle isc_info_sql_stmt_type for cursor-based
RETURNING, as there is isc_info_sql_stmt_flags since v3 which is able to
put FLAG_HAS_CURSOR and make client know it must open a cursor, while
still know the statement type?


I think the biggest concern here is older tools or drivers that are not 
aware of isc_info_sql_stmt_flags, but do use isc_info_sql_stmt_type to 
inform how to handle/execute a statement, and would thus not be able to 
handle this correctly. Given we already 'abuse' isc_info_sql_stmt_type 
by returning isc_info_sql_stmt_exec_procedure for current RETURNING 
statements, I think returning isc_info_sql_stmt_select for the new 
situation is acceptable and probably even necessary.


I agree.

If we want/need some API to correctly identify the type of statement 
even when RETURNING is present, we would need to add yet another info 
item (e.g. isc_info_sql_stmt_type2 or isc_info_sql_real_stmt_type, or 
something like that and then maybe deprecate isc_info_sql_stmt_type in 
favour of the new statement type item and isc_info_sql_stmt_flags).


Perhaps, although I'm not sure why the client application would need to 
know the statement type at all ;-) It still may be confusing, e.g. MERGE 
actually performing as INSERT/UPDATE/DELETE.



Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] File and table sizes in Firebird 4.0

[Dmitry Yemanov]

14.08.2021 10:02, Mark Rotteveel пишет:


Now I see you mentioned 32 TB as the database size limit. How was it 
calculated? It should be the same 128 TB, AFAIK.


The 32 TB is the old value on the page, when the entire page was for 
Firebird 2.5. I have no idea who wrote the previous version of that page 
or if that value was correct for Firebird 2.5.


It was correct: 2^31 (page number was signed) * 16 KB = 32 TB.

For FB 3.0 it should be: 2^32 * 16 KB = 64 TB.


Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] File and table sizes in Firebird 4.0

[Dmitry Yemanov]

13.08.2021 10:04, Mark Rotteveel wrote:

I'd still like an answer to the following questions (assume page size 
32KB for all):


- What is the maximum file size of a Firebird 4 database


Theoretically unlimited (depends on OS and filesystem). Practically, it 
cannot be bigger than 128 TB (2^32 pages * 32KB).


Now I see you mentioned 32 TB as the database size limit. How was it 
calculated? It should be the same 128 TB, AFAIK.



- What is the maximum size of a single table?


It wasn't changed, so should be ~18 TB (my quick calculation shows 22 TB 
actually, but it could be a mistake of mine).



- What are the maximum number of rows per table?


Still the same (2^40).

Max blob size for 32 KB page should be around 512 GB, IIRC.


Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Blobs and OCTET_LENGTH

[Dmitry Yemanov]

07.08.2021 12:31, Mark Rotteveel wrote:

Currently, for blobs that are 4GB or longer, OCTET_LENGTH will silently 
truncate the length. This means that a 4GB blob is reported as length 0, 
a 5GB blob as 1,073,741,824.


It's not OCTET_LENGTH who's guilty, it's blob itself:

In memory:
ULONG blb_length; // Total length of data sans segments

In ODS:
ULONG blh_length; // Total length of data


Dmitry


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] replication/Protocol.h

[Dmitry Yemanov]

27.07.2021 17:32, Dimitry Sibiryakov wrote:


isn't header defining replication protocol supposed to be public?


Yes, it is. Please add a ticket.


Dmitry




Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel