Re: [Firebird-devel] CALL statement
Hi!
I'd like to re-discuss this also taking into consideration named
arguments feature.
Named arguments is standard feature where arguments call be passed to
routines using => token, example:
routine_name(param1 => 1, param2 => 2)
Then back to standard CALL statement, it works also with named
parameters and it works with output parameters using SQL standard way of
output parameters, where a routine is defined with parameters using IN,
OUT or INOUT all mixed, so:
CALL routine_name(input_param1 => 1, output_param1 => ?, input_param2 => 2)
or
CALL routine_name(1, ?, 2) -- (routine_name was defined with IN
INPUT_PARAM1, OUT OUTPUT_PARAM1, IN INPUT_PARAM2 in this order)
We can make CALL work in Firebird using standard syntax, the question is
if we can make it or something else better. CALL with named parameters
is really good, but positional syntax not so much.
Considering that one created a Firebird procedure with these parameters:
Input:
- I1 mandatory
- I2 DEFAULT true
Output:
- O1
- O2
CALL proc_name(1, default, ?, ?)
I'm not sure this "default" is standard, but I would add it for
obviously reason to be able to use default parameters.
Maybe we just can say that CALL with positional syntax is not good with
many parameters.
Note that named arguments would also be added to EXECUTE PROCEDURE as
well function calls.
Therefore I would change my proposal to:
- Add named arguments - for EXECUTE PROCEDURE, function invocation and
CALL statement
- Add DEFAULT for argument passing
- Add standard CALL statement
- Maybe re-discuss (*) extension to CALL or EXECUTE PROCEDURE in another
proposal
* That would be because CALL is awful to return all output parameters
and also awful in the way it mix input parameters having defaults with
output parameters in positional syntax. And EXECUTE PROCEDURE is easy to
return all output parameters, but its syntax is awful in others ways
(many variants, too much verbose).
Adriano
On 31/05/2021 10:19, Adriano dos Santos Fernandes wrote:
> Hi!
>
> EXECUTE PROCEDURE is full of weirdness, so I propose that standard SQL
> CALL is adapted for our needs.
>
> EXECUTE PROCEDURE [ . ]
> [ | ( ) ]
> [ RETURNING_VALUES |
> RETURNING_VALUES ( ) ]
>
> It does not allow one to "select" what just it wants.
>
> So if one changes the procedure output parameters, clients (DSQL and
> PSQL) needs to be changed. It's like "SELECT *" which is sure a bad
> practice.
>
> It has this weird RETURNING_VALUES and multiple syntax about parenthesis.
>
> It does not allow to just execute and ignore output parameters.
>
> I propose that CALL syntax:
>
> CALL [ . ] ( )
> [ RETURNING { * | } ]
> [ INTO ]
>
> A CALL without RETURNING and without INTO will execute the procedure and
> ignore possible output parameters.
>
> A CALL without RETURNING and with INTO (PSQL only), works like with
> "RETURNING *" and EXECUTE PROCEDURE ... RETURNING_VALUES.
>
> Examples:
>
> -- Like EXECUTE PROCEDURE in PSQL
> CALL proc(1, 2) INTO v1, v2
>
> -- Can ignore some output parameters in PSQL
> CALL proc(1, 2) RETURNING o1 INTO a1
>
> -- Can ignore some output parameters in DSQL
> CALL proc(1, 2) RETURNING o1
>
> -- Ignore all output parameters in DSQL and PSQL
> CALL proc(1, 2)
>
>
> Adriano
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Building Firebird Linux with --with-builtin-tommath
On 15/09/2022 08:18, Alex Peshkoff via Firebird-devel wrote: > On 9/15/22 14:00, Adriano dos Santos Fernandes wrote: >> Em qui., 15 de set. de 2022 07:06, Alex Peshkoff via Firebird-devel >> escreveu: >> >> > >> > Why don't force build with static libs? >> > >> >> In what way it's better than own dynamic libraries? >> >> >> >> Not put files in system lib outside of a package manager. >> > > Using own dynamic libraries does not require putting something into > system lib . > I'm talking about this: > This is solved by install script - it creates symlink to /inst/path/firebird/lib/.tm/libtommath.so.0 in system lib directory (provided it's missing). Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Building Firebird Linux with --with-builtin-tommath
On 15/09/2022 07:52, Mark Rotteveel wrote: > On 15-09-2022 03:52, Adriano dos Santos Fernandes wrote: >> On 05/09/2022 08:13, Mark Rotteveel wrote: >>> That is not really embedded IMHO. Firebird Embedded with Firebird 3.0 >>> has become unwieldy and even harder to use than earlier versions, >>> especially if you compare it to other embedded database systems. >> >> How it become harder? > > Basically, in Firebird 2.5 and earlier, you needed fbembed.dll / > libfbembed.so and the intl folder, and you were done. Now you need a > whole constellation of files. > Despite the problem with system libraries, which in many cases we could use static versions (I believe we cannot only with ICU), dealing with dozen of files has the same complexity of dealing with 2. > Other embedded database are basically on DLL/.so and you're done. > Other that are *embedded only* or that can also be used (and focus on it) as a server like Firebird? Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Building Firebird Linux with --with-builtin-tommath
Em qui., 15 de set. de 2022 07:06, Alex Peshkoff via Firebird-devel < firebird-devel@lists.sourceforge.net> escreveu: > > > > Why don't force build with static libs? > > > > In what way it's better than own dynamic libraries? > Not put files in system lib outside of a package manager. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Building Firebird Linux with --with-builtin-tommath
On 05/09/2022 08:13, Mark Rotteveel wrote: > On 05-09-2022 13:00, Alex Peshkoff via Firebird-devel wrote: >> When adding it I cared about an ability to fix secirtiy issues in >> 3d-party libraries not rebuilding and reinstalling FB packages. With >> used schema one can simply install fixed system package for tommath in >> a case of some issues in it. None were found - but that was when >> tommath was at pre-release stage. > > I find these inconsistencies quite confusing, but lets leave it at that. > Also, it's different in the sense that official package builds and package tomcrypt, but not tommath. Should this be changed and be consistent in FB 5, i.e., build and package both? >> BTW, we always recommended to those who need embedded-only access full >> install (classic) and server stop. > Embedded (just built, not installed) works very ok for me. What should be the problem? Lock directory access only? > That is not really embedded IMHO. Firebird Embedded with Firebird 3.0 > has become unwieldy and even harder to use than earlier versions, > especially if you compare it to other embedded database systems. > How it become harder? Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Building Firebird Linux with --with-builtin-tommath
On 05/09/2022 07:20, Mark Rotteveel wrote: > > That is part of the figuring out. I don't normally write shell scripts > either, but before that I would also need to figure out exactly what I > need to do before I can even start on that. > > And since I consider this packaging Firebird embedded in a JAR for easy > use from Java just a "nice to have", I will shelve this until I have > more energy to tackle this. > If you are going to build Firebird for deployment, be aware of it needs an "old" environment to work in more Linux versions. A good place to start is looking in the proposed environment for v5: https://github.com/FirebirdSQL/firebird/tree/master/builds/docker/linux/i586-x86_64 Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Improve release filenames
On 25/08/2022 07:38, Dimitry Sibiryakov wrote: > > Is this zero after build number necessary? > It's packaging number. We had this type of problem in Windows build in the past, where retag was not necessary and the package number was increased. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Improve release filenames
On 25/08/2022 07:19, Mark Rotteveel wrote: > On 25-08-2022 03:15, Adriano dos Santos Fernandes wrote: >> On 24/08/2022 08:45, Adriano dos Santos Fernandes wrote: >>> >>> I propose this name convention starting with Firebird 5: >>> >> >> 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 > > Sounds OK. On suggestion: use "with-debug-symbols" and "debug-symbols", > but that is just because I'm a bit allergic to capitals in filenames ;) > Me too, but I want to use dashes to separate components in this case, and then it would be inconsistent have a single information spread into multiple parts. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Improve release filenames
On 24/08/2022 08:45, Adriano dos Santos Fernandes wrote: > > I propose this name convention starting with Firebird 5: > 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 Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Improve release filenames
On 24/08/2022 09:32, Alex Peshkoff via Firebird-devel wrote: > On 8/24/22 15:03, Adriano dos Santos Fernandes wrote: >> On 24/08/2022 08:59, Mark Rotteveel wrote: >>> On 24-08-2022 13:45, Adriano dos Santos Fernandes wrote: >>>> This is our Firebird 4.0.2 released files: >>>> >>> [..] >>>> It's difficult to understand, they do not mention OS and hardware >>>> architectures are inconsistent. >>>> >>>> I propose this name convention starting with Firebird 5: >>> [..] >>>> Firebird-5.0.0.2816-0-linux-x64.tar.gz >>>> Firebird-5.0.0.2816-0-linux-x64-debuginfo.tar.gz >>>> Firebird-5.0.0.2816-0-linux-arm.tar.gz (armv7, other?) >>>> Firebird-5.0.0.2816-0-linux-arm-debuginfo.tar.gz >>>> Firebird-5.0.0.2816-0-linux-arm64.tar.gz >>>> Firebird-5.0.0.2816-0-linux-arm64-debuginfo.tar.gz >>> [..] >>> >>> In general I'm OK with this, but currently our download page claims the >>> ARM32 and ARM64 build are for Android, and not generically Linux. >>> >>> So, are they actually generically Linux, or specifically for Android? >>> >> You might be correct. >> >> Alex can certainly explain better. > > Specifically for Android. I suppose this to be reflected in the name > cause it's quite possible that we will provide arm64 builds for generic > linux. > What about our android arm for 32 bits? Should we name it arm, arm32, armv7, armv7l, other? Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Improve release filenames
On 24/08/2022 12:04, Alex Peshkoff via Firebird-devel wrote: >> >> The Linux builds are not complete builds compared to Windows pdb >> builds (e.g. config files, documentation, security database, etc are >> missing) >> > > Yep, they contain only binaries with debug-info. > Linux "debuginfo" packages contains only the debug symbols. Different than Android "withDebugInfo" packages, that have full package with debug symbols embedded in the executables/libraries. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Improve release filenames
On 24/08/2022 12:33, Paul Reeves wrote: > On Wed, 24 Aug 2022 16:04:20 +0200 > Mark Rotteveel wrote: > >> On 24-08-2022 15:36, Dimitry Sibiryakov wrote: >>> Mark Rotteveel wrote 24.08.2022 15:32: If the term is confusing or ambiguous, it already is so in its current form. >>> >>> Yes, it is. That's why I would suggest to change that. >> >> The Windows pdb packages are complete builds though. So maybe those >> should be "debug", while the Linux builds should be "debug-symbols" or >> something like that. > > The windows pdb packages contain complete builds partly because of the > requirement that anything installable must be uninstallable. Just dropping > a bunch of pdb's into the install dir will soone or later leave a mess > behind it. > > More importantly, it is essential that the symbols exactly match the > binaries, otherwise the debugger will complain and the whole exercise will > be largely useless. Shipping a complete kit guarantees that everything will > match up and can be uninstalled easily. > > But I agree with Dimitry - we should take care to distinguish kits > containing debug symbols from actual 'Debug' builds. > But that leaves a question. Who installs the "debug" binaries? I expect people looking for this is people having a problem and wanting to understand a crash. Then they would need to overwrite their installation or install it in another place. This does not look as a good workflow. It would be easy to drop the debug symbols in their installed location. The install location already has modifiable files that is not uninstalled. If the debug symbols do not matches the executables it probably will not make much harm: - Someone put them there, updated installation and did not updated the debug symbols - Someone did a wrong job - Someone will not have a good experience with the debug - Someone will correct their poor job Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Improve release filenames
On 24/08/2022 08:59, Mark Rotteveel wrote: > On 24-08-2022 13:45, Adriano dos Santos Fernandes wrote: >> This is our Firebird 4.0.2 released files: >> > [..] >> It's difficult to understand, they do not mention OS and hardware >> architectures are inconsistent. >> >> I propose this name convention starting with Firebird 5: > [..] >> Firebird-5.0.0.2816-0-linux-x64.tar.gz >> Firebird-5.0.0.2816-0-linux-x64-debuginfo.tar.gz >> Firebird-5.0.0.2816-0-linux-arm.tar.gz (armv7, other?) >> Firebird-5.0.0.2816-0-linux-arm-debuginfo.tar.gz >> Firebird-5.0.0.2816-0-linux-arm64.tar.gz >> Firebird-5.0.0.2816-0-linux-arm64-debuginfo.tar.gz > [..] > > In general I'm OK with this, but currently our download page claims the > ARM32 and ARM64 build are for Android, and not generically Linux. > > So, are they actually generically Linux, or specifically for Android? > You might be correct. Alex can certainly explain better. I'm also not sure about arm64 vs aarch64 differences. Here https://github.com/asfernandes/firebird/releases/tag/v4.0.0-aarch64-asfernandes I built a Linux RaspBerry pi and named aarch64 but never published it as official. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] Improve release filenames
Hi! This is our Firebird 4.0.2 released files: Firebird-4.0.2.2816-0-Win32-pdb.exe Firebird-4.0.2.2816-0-Win32-pdb.zip Firebird-4.0.2.2816-0-Win32.exe Firebird-4.0.2.2816-0-Win32.zip Firebird-4.0.2.2816-0-x64-pdb.exe Firebird-4.0.2.2816-0-x64-pdb.zip Firebird-4.0.2.2816-0-x64.exe Firebird-4.0.2.2816-0-x64.zip Firebird-4.0.2.2816-0.amd64.tar.gz Firebird-4.0.2.2816-0.arm.tar.gz Firebird-4.0.2.2816-0.arm64.tar.gz Firebird-4.0.2.2816-0.i686.tar.gz Firebird-4.0.2.2816-0.tar.xz Firebird-debuginfo-4.0.2.2816-0.amd64.tar.gz Firebird-debuginfo-4.0.2.2816-0.i686.tar.gz Firebird-withDebugInfo-4.0.2.2816-0.arm.tar.gz Firebird-withDebugInfo-4.0.2.2816-0.arm64.tar.gz (missing MacOS which would be: Firebird-4.0.2.2816-x86_64.pkg) It's difficult to understand, they do not mention OS and hardware architectures are inconsistent. I propose this name convention starting with Firebird 5: Firebird-5.0.0.2816-0-windows-x86-pdb.exe Firebird-5.0.0.2816-0-windows-x86-pdb.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-pdb.exe Firebird-5.0.0.2816-0-windows-x64-pdb.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-debuginfo.tar.gz Firebird-5.0.0.2816-0-linux-arm.tar.gz (armv7, other?) Firebird-5.0.0.2816-0-linux-arm-debuginfo.tar.gz Firebird-5.0.0.2816-0-linux-arm64.tar.gz Firebird-5.0.0.2816-0-linux-arm64-debuginfo.tar.gz Firebird-5.0.0.2816-0-linux-x86.tar.gz Firebird-5.0.0.2816-0-linux-x86-debuginfo.tar.gz Firebird-5.0.0.2816-0-source.tar.xz Firebird-5.0.0.2816-0-macos-x64.pkg Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Profiler plugin is not found. Windows snapshot Firebird-5.0.0.684-0-x64
On 23/08/2022 07:53, Simonov Denis via Firebird-devel wrote:
> 23.08.2022 13:38, Norbert Saint Georges пишет:
>> Simonov Denis via Firebird-devel a écrit :
>>> SQL> connect inet://localhost:3055/employee user SYSDBA password
>>> 'masterkey';
>>> Database: inet://localhost:3055/employee, User: SYSDBA
>>> SQL> select rdb$profiler.start_session('Profile Session 1') from
>>> rdb$database;
>>>
>>> START_SESSION
>>> =
>>> Statement failed, SQLSTATE = HY000
>>> Profiler plugin is not found
>>> -At function 'RDB$PROFILER.START_SESSION'
>>> SQL>
>>
>> Server Version: LI-T5.0.0.681 Firebird 5.0 Initial
>> this ok :-)
>>
>
> This is because the Windows build does not have the
> plugins/Default_Profiler.dll dynamic library, while the Linux build does
> have the plugins/libDefault_Profiler.so library.
>
Should be fixed in next snapshot.
Adriano
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Release build, snapshot, GitHub CI, AppVeyor
On 22/08/2022 08:24, Gabor Boros wrote: > Hi All, > > Isn't that too much? > In Gabor's ideal world..., GitHub CI "from a branch"=snapshot, "from a > tag"=release. :-) > > It's just a technical question from me, not more not less. > For example if release and snapshot use the same build infrastructure > (compiler, etc.) is good. Or not? :-) (Now the actual/snapshot builds > come from / available at three different places.) > It's about build tools versions, needed work, resistance to changes, etc. You can consider AppVeyor as just another place doing builds with different tools versions, which is good for portability. GitHub Actions is currently considered as a way to validate our builds with different tools versions. A downside is that github login is necessary for download and URLs are not stable - this is bad for automation. Now you have one more place to look: https://github.com/FirebirdSQL/snapshots/releases/tag/snapshot-master It stores (unofficial - until/if the team decides it's official) daily snapshots for the master branch with stable URL without a need to login. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Named parameters in client libraries
On 19/08/2022 05:09, Mark Rotteveel wrote: > On 19-08-2022 02:36, Adriano dos Santos Fernandes wrote: >> On 18/08/2022 04:37, Mark Rotteveel wrote: >>> In other words, most specific type with the highest >>> precision/scale/length. When confronted with combinations of non-string >>> types and string/blob types, use the most specific non-string type. >> >> I don't think this is correct, specially the ones I listed above. >> >> 1: I would define it as VARCHAR with size using same rules of COALESCE >> with both types >> >> 5: same case as 1, VARCHAR >> >> 12: should be error, the two types are incompatible > > They shouldn't be according to section 4.6 of the SQL standard, > specifically Table 3 — Datetime data type conversions. > Conversion is a thing, but this is not about conversion. It's not logic for the case of parameters as well it's not logic in COALESCE. >> So it would be something like COALESCE. > > I think that is the wrong approach for parameters. When deriving a type > for COALESCE, it makes sense to infer the type to the widest possible > type (VARCHAR) to allow all possible stored values to be represented, > but with parameters, it is IMHO better to use the most specific type, as > that will yield useful information to the user through the metadata, and > probably result in the best diagnostic information when specifying > invalid values. > You prefer large over smaller VARCHARs, prefer TIMESTAMP over TIME - widest types But prefer TIMESTAMP and NUMBERS over VARCHAR - most specific I see no logic in your approach. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Named parameters in client libraries
On 19/08/2022 06:06, Tony Whyman wrote: > On 19/08/2022 00:42, Adriano dos Santos Fernandes wrote: >> On 18/08/2022 18:53, Tony Whyman wrote: >>> IBX for Lazaus follows the original Deplhi IBX approach of adopting PSQL >>> syntax for named parameters. >> Can a name be used more than once, and if yes, how is its type deduction? >> >> >> Adriano > > Uniqueness of parameters names is not required. If you have two or > parameters with the same name in a given SQL Statement then both get set > at the same time and to the same value. > > Type conversions are an issue for Firebird rather than IBX and are > handled identically to positional parameters. > Can't the user's library inspect parameters and its type? If yes, how would it be returned if a parameter name is used more than once with different Firebird types deduced in the different question-marks generated? Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Named parameters in client libraries
On 18/08/2022 04:37, Mark Rotteveel wrote: > My guess is that they either: > > 1) don't allow mixing types > 2) use the most specific type that is compatible with all positions, > otherwise error > 3) use the first position > 4) use the last position > > I think rule 2 is probably the best, but I'm not really sure. > I think it's the only realistic way. > Some examples for rule 2 > 1) TIME, TIMESTAMP, VARCHAR(..) => TIMESTAMP > 5) VARCHAR(50), DOUBLE PRECISION => DOUBLE PRECISION > 12) TIME WITH TIME ZONE, TIMESTAMP WITHOUT TIME ZONE => TIMESTAMP WITH > TIME ZONE > ... > > In other words, most specific type with the highest > precision/scale/length. When confronted with combinations of non-string > types and string/blob types, use the most specific non-string type. I don't think this is correct, specially the ones I listed above. 1: I would define it as VARCHAR with size using same rules of COALESCE with both types 5: same case as 1, VARCHAR 12: should be error, the two types are incompatible So it would be something like COALESCE. I believe types would be computed in a first pass and in a later pass expressions already know the type of parameters. select * from rdb$database where 1 + :a = ? or 'x' || :a = 'xx' This would deduce :a to be a VARCHAR and the compilation would fail as 1 + VARCHAR is not allowed in compilation. I'm not saying it's easy to implement this, specially as I do not agree in many ways on how Firebird deduce parameter types now preferring to go longer in the AST. But I think is the logical way. If someone has an easier and logical approach I would love to know. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Named parameters in client libraries
On 18/08/2022 04:01, Mark Rotteveel wrote: > On 18-08-2022 04:02, Adriano dos Santos Fernandes wrote: >> How are client libraries (Jaybird, .NET Provider, Delphi ones) >> describing its named parameters to their users? >> >> I mean, given this SQL: >> >> select * >> from rdb$database >> where :param = 1 or :param = '2' >> >> We have here single name used in context with multiple types. >> >> I suppose you transform this to: >> >> select * >> from rdb$database >> where ? = 1 or ? = '2' >> >> Which will map to two Firebird parameters with different types. >> >> But for the user of the library, I suppose it's one parameter, correct? >> >> And what type (and the deduction rules) this parameter will be >> described as? > > JDBC doesn't define support for named parameters in > java.sql.PreparedStatement, only in java.sql.CallableStatement, which is > for calling stored procedures, and that support is optional, so Jaybird > doesn't have any support for named parameters. > > So, if Firebird is going to implement real named parameters, Jaybird is > probably not going to support them, and in that case I would really love > if this is controlled through a DPB item so when disabled, use of a > named parameter results in an error (e.g. "named parameters in DSQL are > not enabled" or something). Maybe I can then allow users aware of named > parameters to enable it explicitly to map it them themselves, for > example if something like `column1 = :param1 and column2 = :param2 and > colum3 = :param1` results in two parameters (param1 = position 1, and > param2 = position 2), and setting by position still works. > > The reason Jaybird probably is not going to support this, is because > most people don't use the JDBC API directly, but through things like > Hibernate, so adding vendor extensions to the JDBC API is pretty much > wasted effort because those libraries only use the JDBC API. I could add > such methods to the FirebirdPreparedStatement interface, but that would > then be a niche feature for people who do use JDBC, and are able to > unwrap to the FirebirdPreparedStatement interface (which is not always > possible when obtained from a connection pool). > > In the Java world, named parameters are usually emulated by layers like > Hibernate, or Spring's NamedParameterJdbcTemplate, which will map names > to positional parameters, and set the values of those positional > parameters appropriately. > It's sad that JDBC does not report names in ParameterMetaData. There is not much to worry for Jaybird. And there should not be a setting to disable it, specially in this case. We don't put settings to disallow MERGE, UPDATE OR INSERT, etc. Layers that do emulations in client could continue doing it and pass positional parameters to Firebird. I just hope they do it well. In node-firebird-driver-native, even which a submitted PR, I prefer to avoid do it in the client. Named parameters will continue to work in positional way too. If you have expression ":A = 1 or :B = 2 or :A = 3 or :C = 4", order of them will be "A, B, C". Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Named parameters in client libraries
On 18/08/2022 04:42, Jiří Činčura wrote: > In .NET parameters are named `@param`, aka the delimiter is `@`. > >> I suppose you transform this to: >> >> select * >> from rdb$database >> where ? = 1 or ? = '2' > > Correct. > >> Which will map to two Firebird parameters with different types. > > Correct. > >> But for the user of the library, I suppose it's one parameter, correct? > > Correct. Developer defines just `@param` and assigns value. > >> And what type (and the deduction rules) this parameter will be described as? > > Developer can set the type of parameter, which is later used to know how to > read it from it's value. For the describe this set of info_sql values is used > to get the info. Does it have something like JDBC's ParameterMetaData? If yes, how is the type of parameter names used more than once (comparing to different types) described? Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Named parameters in client libraries
On 18/08/2022 18:53, Tony Whyman wrote: > IBX for Lazaus follows the original Deplhi IBX approach of adopting PSQL > syntax for named parameters. Can a name be used more than once, and if yes, how is its type deduction? Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] Named parameters in client libraries
Hi Mark, Jiri, all! How are client libraries (Jaybird, .NET Provider, Delphi ones) describing its named parameters to their users? I mean, given this SQL: select * from rdb$database where :param = 1 or :param = '2' We have here single name used in context with multiple types. I suppose you transform this to: select * from rdb$database where ? = 1 or ? = '2' Which will map to two Firebird parameters with different types. But for the user of the library, I suppose it's one parameter, correct? And what type (and the deduction rules) this parameter will be described as? Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] New statement: EXECUTE SQL
On Tue, Aug 16, 2022 at 3:48 AM Mark Rotteveel wrote:
>
> Am I reading this correctly that your proposal is to use the following
> syntax?
>
> execute sql (p1 integer, p2 varchar(50))
> do
>select * from t where t.id = :p1 and t.name = :p2
>
> So, no explicit positional parameter markers ('?')?
>
>
Not exactly. I split proposal in two:
1) Add named parameters
This will become valid (in server):
-
select *
from rdb$database
where rdb$relation_id = :relation_id or
rdb$relation_id = :relation_id
Will report a single parameter, with name RELATION_ID.
-
Unnamed parameters (question marks) will still be allowed.
-
select *
from rdb$database
where rdb$relation_id = :relation_id or
rdb$relation_id = ? or
rdb$relation_id = :relation_id
This will report parameters in order: RELATION_ID, unnamed
-
With this, EXECUTE BLOCK can be simplified and "= ?" in input parameters
becomes optional.
2) Add EXECUTE SQL (maybe with another name)
It will support named and unnamed parameters.
So, given your example, it would be valid, but this one too:
-
execute sql (p1 integer)
do
select *
from t
where t.id = :p1 and
t.name = :p2 and
t.whatever = ?
-
Explicit parameters in the argument list will be like EXECUTE BLOCK (may
use or not "= ?").
In this way we create a path that we should have already created many times
ago, to avoid clients doing server business.
Only who can want would use unnamed parameters, and since these are
top-level commands, one would not use them if their framework does not
support them.
Adriano
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] New statement: EXECUTE SQL
On 15/08/2022 14:42, Vlad Khorsun wrote: > > I like the idea but not syntax. As already mentioned, there it will be > hard for > app\component devs to parse the whole statement looking for parameters. > Note, > semicolon usually mark "client" named parameters and it will be near to > impossible > for, say, Delphi components to correctly preprocess statement like below: > > execute sql (p1 integer = :p1) > do > select * from t where t.id = :p1 and t.name = :p2 > Client parsers already need to stop at some place. But I understand what you mean, so just read below... > > You may expect after preprocessing by app it will be like: > > execute sql (p1 integer = ?) > do > select * from t where t.id = :p1 and t.name = ? > > > but actually it will be like: > > execute sql (p1 integer = ?) > do > select * from t where t.id = ? and t.name = ? > > without complex re-writing of existing preprocessors. > No, I expect that client parsers may not do unnecessary things, at least with this statement. We may report named parameters in the right way (currently EXECUTE BLOCK does not report then, do not know why). For the parsers using :name syntax, that's very easy. Just detect the new statement and do not pre-parse it. AFAIK .NET uses @name syntax. Since that is not a Firebird valid syntax, it should also not be difficult to detect and replace it. So this will not be processed by client at all. It will just read parameter names (p1, p2) from the described statement: execute sql (p1 integer = ?) do select * from t where t.id = :p1 and t.name = :p2 In fact the ugly "= ?" becomes completely unnecessary and better removed in this statement. And it's up to us to continue allowing unnamed parameters in this statement or not. I think we should and people using client libraries that require named (instead positional) parameters could just not use them. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] New statement: EXECUTE SQL
On 15/08/2022 16:20, Dimitry Sibiryakov wrote: > I see no difference between proposed syntax and WITH except of > definition of functions instead of derived tables in CTE part. > What is wrong with expanding it into something like this: > > WITH I prefer to not reuse a standard keyword for this. And the case with parameters makes WITH a complete different beast. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] New statement: EXECUTE SQL
On 15/08/2022 14:47, Dmitry Yemanov wrote: > using (p1 integer = :p1, p2 varchar(255) = :p2) > do select * from t where t.id = :p1 and t.name = :p2 I like this one. Or replacing USING by GIVEN. I'm not sure the parameter-less case is nice: using declare function ... do ... Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] New statement: EXECUTE SQL
On 15/08/2022 07:27, Dimitry Sibiryakov wrote: > Adriano dos Santos Fernandes wrote 15.08.2022 12:24: >> Firebird does not have any support for client named parameters. > > IMessageMetadata has field for name which is currently unused. What > else client support do you have on mind? > And it's not filled for: select 1 from rdb$database where rdb$relation_id = ?; Looks like not filled for EXECUTE BLOCK input parameters too, which I think it should, so as with EXECUTE SQL. What I mean is that we do not support this (except in EXECUTE STATEMENT): select 1 from rdb$database where rdb$relation_id = :relation_id If we start accept this, probably relation_id should be automatically declared and "joined" the explicit EXECUTE SQL parameter list. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] New statement: EXECUTE SQL
On 15/08/2022 04:16, Simonov Denis via Firebird-devel wrote: > > Very good feature, reminds me of the functionality from Oracle > > WITH Procedure p1 ... > AS > ... > FUNCTION f1 ... > AS > ... > SELECT... > Plus, DECLARE VAR := ? But things are very less useful for them due to their BEGIN...END blocks not capable of produce multiple rows result, nor subfunctions not able to be pipelined, etc. > However, it's not entirely clear to me how client applications handle > named parameters (which will be replaced with "?")? It seems to me > unnamed parameters "?" (which in client applications will most likely be > named) leave either in the or in the request > itself. I vote for them to remain only in the request. > Firebird does not have any support for client named parameters. As Mark told, it will require client support. > Just try to implement EXECUTE STATEMENT with your syntax and support for > named parameters, then it will become clear what I'm talking about. My > option is this. > I suppose if standalone statements is going to have support of named parameters as EXECUTE STATEMENT, undeclared named parameters are going to be automatically declared and comes after the explicit ones. > Syntax: > > execute sql > [] > do > > Here is how it can be used: > > execute sql > declare function subfunc (i1 integer) returns integer > as > begin > return i1; > end > > declare procedure subproc (i1 integer) returns (o1 integer) > as > begin > o1 = i1; > suspend; > end > do > select subfunc(?) + o1 > from subproc(?) > This reduces a lot the usefulness of the statement and I do not agree. A path I can follow is to be like Oracle and have the "parameters" declared as variables: - execute sql declare variable p1 integer = ?; declare variable p2 integer = ?; declare function subfunc (i1 integer) returns integer as begin return i1; end declare procedure subproc (i1 integer) returns (o1 integer) as begin o1 = i1; suspend; end do select subfunc(:p1) + o1 from subproc(:p2 + ?) - But I still prefer parameters to be parameters, like in EXECUTE BLOCK. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] New statement: EXECUTE SQL
Em dom., 14 de ago. de 2022 03:55, Mark Rotteveel escreveu: > > > Would this still allow the full SELECT syntax (including WITH clauses, > OFFSET/FETCH, GROUP BY, etc), and things like RETURNING for INSERT, etc? > Yes. > I assume this is a toplevel statement only, so it can't occur as a > derived table or subquery inside another statement. Is that correct? > Yes. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] New statement: EXECUTE SQL
On 13/08/2022 20:28, Adriano dos Santos Fernandes wrote: > and it's to use with SELECT, UPDATE, DELETE and MERGE And also INSERT and UPDATE OR INSERT... Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] New statement: EXECUTE SQL
Hi! When one starts with a DSQL command and need to adapt it to EXECUTE BLOCK (for example to use sub routines or use a single parameter in many places), work is difficult when there are many parameters and output fields. Everything must be explicitly declared. I propose new DSQL statement that improve a lot this workflow (and others when not all power of EXECUTE BLOCK is necessary, but it's verbosity is inevitable). I'm calling it EXECUTE SQL, and it's to use with SELECT, UPDATE, DELETE and MERGE, with or without RETURNING. It seats between lack of resources + simplicity of direct SQL command and power + verbosity of EXECUTE BLOCK. Syntax: execute sql [ ( ) ] [ ] do Here is how it can be used: execute sql (p1 integer = ?, p2 integer = ?) declare function subfunc (i1 integer) returns integer as begin return i1; end declare procedure subproc (i1 integer) returns (o1 integer) as begin o1 = i1; suspend; end do select subfunc(:p1) + o1 from subproc(:p2 + ?) Note that parameters may be declared or directly (only in the DO command) used like now. Output is not declared. It's inferred from the DO command. Statement type of the DO command is returned. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] BLOB_APPEND and NULL
Em 11/08/2022 12:58, Vlad Khorsun escreveu:
11.08.2022 17:26, Mark Rotteveel wrote:
On 11-08-2022 16:21, Vlad Khorsun wrote:
11.08.2022 17:10, Mark Rotteveel wrote:
Why was this NULL behaviour chosen?
To make BLOB_APPEND more convenient for users.
I don't understand how using a different NULL behaviour then standard for
operations in SQL/Firebird is convenient.
There is no standard defined operation for appending blobs, afaik.
To me it sounds like something that will result in confusion because of the
difference with normal concatenation, but OK.
The BLOB_APPEND is not CONCATENATION, it is non-standard function with
custom semantics.
As well many of our system functions, and we choose to implement the
standard behavior on them.
This remember-me JavaScript:
> null + null
0
> 'null' + null
'nullnull'
The answer is the same - for user convenience. When I want to append
something to the
already existing blob, I doesn't expect to destroy my blob just because
nothing is
appended to it.
It's is inconsistent, and I do not see that as convenience.
Some user would think the convenience would be to treat it as strings
('null', 'NULL', 'Null').
Adriano
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] DPB and collation
Em 05/08/2022 06:40, Jiří Činčura escreveu: Hi *, What's the DPB tag for collation when creating database. For charset there's isc_dpb_set_db_charset. There isn't one. CREATE DATABASE immediately executes a ALTER CHARACTER SET SET DEFAULT COLLATION. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Why "affect-version: 4.0.2" and "fix-version: 4.0.3"?
Em 07/07/2022 11:12, Gabor Boros escreveu: Hi All, 4.0.2 not released or tagged yet. Why some issues have these labels? https://github.com/FirebirdSQL/firebird/issues?q=label%3A%22affect-version%3A+4.0.2%22+ https://github.com/FirebirdSQL/firebird/issues?q=label%3A%22fix-version%3A+4.0.3%22+ Maybe someone (or even me) created the fix-version: 4.0.3 label and that confused me when labeling the issues, as I never remember what is the latest released version number. I will fix these labels. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] GBAK problem after parallel backups changes
On 26/06/2022 03:55, Vlad Khorsun wrote: > 26.06.2022 2:32, Adriano dos Santos Fernandes wrote: >> Em sáb., 25 de jun. de 2022 20:15, Vlad Khorsun escreveu: >> >> 26.06.2022 1:31, Adriano dos Santos Fernandes wrote: >> > Em sáb., 25 de jun. de 2022 18:46, Vlad Khorsun escreveu: >> >> >> I see, thanks. Actually, restore is OK, the problem is that it >> reported reason >> why it can't use Batch API for particular table. It should be >> fixed now, unless >> you use DEBUG build to run FBTCS. >> >> >> I always use debug build to run it. > > Ok, I disabled it for DEBUG too. > Ok, thanks. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] GBAK problem after parallel backups changes
Em sáb., 25 de jun. de 2022 20:15, Vlad Khorsun escreveu: > 26.06.2022 1:31, Adriano dos Santos Fernandes wrote: > > Em sáb., 25 de jun. de 2022 18:46, Vlad Khorsun escreveu: > > >I see, thanks. Actually, restore is OK, the problem is that it reported > reason > why it can't use Batch API for particular table. It should be fixed now, > unless > you use DEBUG build to run FBTCS. > I always use debug build to run it. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] GBAK problem after parallel backups changes
Em sáb., 25 de jun. de 2022 18:46, Vlad Khorsun escreveu: > 25.06.2022 23:59, Adriano dos Santos Fernandes wrote: > > Hi! > > > > After these changes, restore of dialect 1 databases with keywords are > > making TCS tests fail with this content (or example): > > > > gbak: ERROR:Dynamic SQL Error > > gbak: ERROR:SQL error code = -104 > > gbak: ERROR:Token unknown - line 1, column 47 > > gbak: ERROR:POSITION > >Could you point me to the database that is failed to restore ? > sh_test.gbk Adriano > Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Cases when DROP INDEX DDL triggers should be called
On 22/06/2022 11:01, Ilya Eremin wrote: > Hello! > > In FB3+, when I create an index on a table's field and then drop this > table, DROP INDEX DDL triggers are not called. Is it done this way on > purpose? I did not find a clear description of such case in the > documentation. > This is as designed. Only the object manipulated makes the trigger fire. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] GBAK problem after parallel backups changes
Hi! After these changes, restore of dialect 1 databases with keywords are making TCS tests fail with this content (or example): gbak: ERROR:Dynamic SQL Error gbak: ERROR:SQL error code = -104 gbak: ERROR:Token unknown - line 1, column 47 gbak: ERROR:POSITION Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Cleaning up Visual Studio detection during build and packaging.
Em sex., 17 de jun. de 2022 07:27, Paul Reeves escreveu: > > We are currently doing this sort if thing in several batch files: > > if defined VS170COMNTOOLS () else > if defined VS160COMNTOOLS () else > if defined VS150COMNTOOLS () else > > which is very messy and hard to maintain. > > I've been working on fixing this and propose that we do this in > settenvar.bat: > > if not defined VSnnnCOMNTOOLS > if defined VS170COMNTOOLS set VSnnnCOMNTOOLS = VS170COMNTOOLS else > if defined VS160COMNTOOLS set VSnnnCOMNTOOLS = VS160COMNTOOLS else > if defined VS150COMNTOOLS set VSnnnCOMNTOOLS = VS150COMNTOOLS > > We then use VSnnnCOMNTOOLS to call the correct vcvarsall.bat. > I'd replace VSnnnCOMNTOOLS by FB_VSCOMNTOOLS. Adriano 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
On Thu, Jun 16, 2022 at 1:38 AM Dmitry Yemanov wrote: > > 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. > > The commit mentions no ticket nor also mentions that it causes a problem in standard usage with real pipe in Windows. This question has interesting links: https://stackoverflow.com/questions/40912072/git-for-windows-mintty-sys-stdout-isatty-returns-false I've tested the winpty approach in msys and it works correctly when ISQL does not consider pipe as tty. It also fixes the arrow keys problem. winpty is a library too and would be the easiest solution for that customer. There is also the Windows 10 ConPTY API and approaches with the additional hidden console wrapper (which seems to be what winpty is). Adriano 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
On Wed, Jun 15, 2022 at 6:31 PM Dimitry Sibiryakov wrote: > Adriano dos Santos Fernandes wrote 15.06.2022 21:56: > > Are there any reason for that? > >MSYS (and I suspect WSL as well) bash is using unnamed pipes to > redirect > input and output of applications into its window. Without considering > pipes as > console they are blind. >I know no way to distinguish CMD pipelining from any other stdout > redirection. > > Things work correctly with CMD, Windows Terminal and WSL. But has the problem you said with MSYS. But even unmodified ISQL is very problematic in MSYS when you input arrow keys. The cursor navigates through the screen and garbage is inputted. Looks like MSYS is implementing things incorrectly. If it does the same with the stdout, the CRT will buffer things differently than a standard console. The correct way must be with CreateConsoleScreenBuffer instead of pipes. Windows Terminal uses it. This article is about this subject: https://www.codeproject.com/Articles/16163/Real-Time-Console-Output-Redirection Since MSYS is not a standard tool in the Windows world and is already working very badly with ISQL, I think we should fix the problem. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] Stdin redirection with pipe in Windows ISQL
Hi! 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. Are there any reason for that? Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Record storage
On 09/06/2022 09:58, Dmitry Yemanov 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. 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. > 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 when page is cached that becomes more problematic. I think Jim's databases had record caches instead of only page caches, probably to avoid this type of problem. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Record storage
On 09/06/2022 09:29, Dimitry Sibiryakov wrote: > Adriano dos Santos Fernandes wrote 09.06.2022 14:16: >> What do you think and are there any active work in this regard? > > Using of record encoding instead of record compressing was suggested > years ago by Ann and Jim. Yes, but, suggesting things and doing no action during more than 10 years does not make users happy. > Self-descriptive record format which makes RDB$FORMATS obsolete and > solve problems with garbage collection etc was suggested by me (for > replication block buffer in the first place but storage can use it as > well). > Putting metadata bytes in each record is not efficient. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] Record storage
Hi! 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. 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. I believe that is not the way to go. 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. What do you think and are there any active work in this regard? Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Tablespaces proposal
On 02/06/2022 08:08, Dimitry Sibiryakov wrote: > Alex Peshkoff via Firebird-devel wrote 02.06.2022 13:01: >>> It is "ALTER TABLE" which must be executed on replica. Otherwise >>> you'll have problem with ALTER TABLE ADD FIELD. >> >> Is it impossible to analyze clauses of statement? > > Theoretically - yes. On practice it would require full Firebird SQL > parser which is hard. Simpler would be to downgrade the "missing > tablespace" error to a warning if the database is in replica mode or SQL > is performed by Applier. > Commands are replicated in text and executed in replica. The replica execution could ignore things when are running a replicated command. I see no difficulty there. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] event_t::event_count
Hi!
Isn't this thread unsafe?
struct event_t
{
SLONG event_count;
int pid;
pthread_mutex_t event_mutex[1];
pthread_cond_t event_cond[1];
};
int SharedMemoryBase::eventPost(event_t* event)
{
...
++event->event_count;
...
}
Should not event_count use a std::atomic?
Or is SharedMemoryBase::eventPost expected to not be thread safe to be
called concurrently?
Adriano
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Incorrect string right truncation errors in Firebird 5
On 17/05/2022 09:54, Mark Rotteveel wrote: > > Thanks! I will change Jaybird to switch to using blr_varying2/blr_text2 > for Jaybird 4.0.7 and Jaybird 5. > When you did it for test, did you used the charset number or CS_dynamic? I'm asking because the current code seems to have the same problem if test2/varying2 is used with CS_dynamic instead of the actual number. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Incorrect string right truncation errors in Firebird 5
On 17/05/2022 08:30, Mark Rotteveel wrote: >> Is this happening with fbclient library too? > > Good question: no it doesn't. Which suggests Jaybird is doing something > different. Jaybird uses blr_varying/blr_text, not blr_varying2/blr_text2 > when sending the BLR of the execute. Could that make a difference? > > Changing Jaybird to write blr_varying2/blr_text2 with the sub type > (character set) fixes the issue. > > However, I thought that using blr_varying/blr_text (judging by > MetadataFromBlr::MetadataFromBlr in > common\classes\InternalMessageBuffer.cpp, Firebird selects CS_dynamic, > which should use the connection character set (UTF8) in this case. Is it > possible that `CharSet* const fromCharSet = INTL_charset_lookup(tdbb, > from_cs);` doesn't handle CS_dynamic properly? Or that this happens in a > codepath where BLR decoding doesn't assign CS_dynamic, but it remains > zero (NONE)? > The engine is receiving the string using NONE charset, which makes things bad there. This happens due to this place: - // ASF: Older than 2.5 engine hasn't validating strings in DSQL. After this has been // implemented in 2.5, selecting a NONE column with UTF-8 attachment charset started // failing. The real problem is that the client encodes SQL_TEXT/SQL_VARYING using // blr_text/blr_varying (i.e. with the connection charset). I'm reseting the charset // here at the server as a way to make older (and not yet changed) client work // correctly. if (desc.isText() && desc.getTextType() == ttype_dynamic) desc.setTextType(ttype_none); - So it is happening for a long time and may make things incorrect in Jaybird. Anyway, it seems I'll need to change the fix for #7179 to take care of real NONE/BINARY source strings. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Incorrect string right truncation errors in Firebird 5
On 16/05/2022 12:07, Mark Rotteveel wrote: > On 16-05-2022 16:50, Mark Rotteveel wrote: >> I was running some tests against Firebird-5.0.0.494-0_x64 (latest >> snapshot, from last Saturday), and I notice that I get incorrect >> string right truncation errors with CHAR/VARCHAR. >> >> I currently cannot dive deeper into it, but as a datapoint, the error >> does not occur with Firebird-5.0.0.488-0_x64. >> >> Based solely on the commit message, maybe this commit is at fault? >> https://github.com/FirebirdSQL/firebird/commit/dd18a3b11b28c3ed8126a6f54b829989954bfa03 > > > The Jaybird test that triggers this is > org.firebirdsql.jdbc.TestFBPreparedStatementUTF8, specifically tests: > > - connectionUtf8_insertMultiByte_inWin1252_char_1_win1252_succeeds > (fails with "expected length 1, actual 2") > - connectionUtf8_insertMultiByte_notInWin1252_char_1_win1252_fails > (fails with "expected length 1, actual 2", should fail with "Cannot > transliterate character between character sets") > - connectionUtf8_insertMultipleInWin1252_char_5_win1252_succeeds (fails > with "expected length 5, actual 8") > - connectionUtf8_insertMultipleInWin1252_varchar_5_win1252_succeeds > (fails with "expected length 5, actual 8") > - > connectionUtf8_insertMultipleInWin1252_lessThanMax_varchar_5_win1252_succeeds > (fails with "expected length 5, actual 6") > -connectionUtf8_insertMultiByte_char_1_utf8_succeeds (fails with > "expected length 1, actual 2") > - connectionUtf8_insertMultiple_char_5_utf8_succeeds (fails with > "expected length 5, actual 8") > - connectionUtf8_insertMultiple_varchar_5_utf8_succeeds (fails with > "expected length 5, actual 8") > - connectionUtf8_insertMultiple_lessThanMax_varchar_5_utf8_succeeds > (fails with "expected length 5, actual 6") > > For CHAR, it seems to count 1 more character, for VARCHAR 2-3 more > characters. > Is this happening with fbclient library too? Is the error in insert or select? I'm failing to reproduce it in isql, for example for connectionUtf8_insertMultipleInWin1252_varchar_5_win1252_succeeds: - CREATE TABLE utf8table ( id INTEGER, char_1_none CHAR(1) CHARACTER SET NONE, char_1_utf8 CHAR(1) CHARACTER SET UTF8, char_1_win1252 CHAR(1) CHARACTER SET WIN1252, char_5_none CHAR(5) CHARACTER SET NONE, char_5_utf8 CHAR(5) CHARACTER SET UTF8, char_5_win1252 CHAR(5) CHARACTER SET WIN1252, varchar_5_none VARCHAR(5) CHARACTER SET NONE, varchar_5_utf8 VARCHAR(5) CHARACTER SET UTF8, varchar_5_win1252 VARCHAR(5) CHARACTER SET WIN1252 ); /* select unicode_char(0x00FE) || unicode_char(0x00A3) || 'a' || unicode_char(0x0160) || ',' from rdb$database; */ set bulk_insert INSERT INTO utf8table (id, char_5_win1252) VALUES (?, ?); (1, 'þ£aŠ,') stop ; select char_5_win1252 from utf8table where id = 1; - Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Refresh roles
On 11/05/2022 16:03, Roman Simakov wrote: > > SQL SECURITY replaces an effective user for some time. Can you use the > same trick while profiler initializing? > No. Profiler plugin creates tables and views and grant access to them to role PLG$PROFILER that is grated to users. Users directly query profiler tables and views, so SQL SECURITY helps nothing there. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Refresh roles
On 11/05/2022 14:47, Roman Simakov wrote: > > I was thinking the profiler should not touch profiled attachments at > all. It might influence its logic. > The profiler should work on its initial usage when it creates its metadata. This is priority for me than theoretical problems due to roles being refreshed. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Refresh roles
On 11/05/2022 11:55, Roman Simakov wrote: > Can you make up or describe a potential case of the situation? > Undoubtedly the feature would be useful but we've lived without it for > all the time. > In the profiler, I will create a role, the tables and views in another connection. I'll grant privileges to the role and default grant the role to public (currently not working due to #7178, so I'm testing with another user instead of public). But the user connection is already made and is going to use the profiler. Now I need to see what is its current_role, then "set role plg$profiler" (or any other role different than current), then "set role old_current_role" to refresh the current roles. > If a role is revoked from a user, it will affect the > next attachment only, won't it? I think this can be considered as a not well defined behavior and changeable, but anyway, if there is simple command to refresh the active roles instead of automatically, it's not going to be a problem. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] Refresh roles
Hi! When roles are default granted to an user, they are not immediately reflected in the attachments. Attachments need to issue a SET ROLE which should be different than currently used, then another SET ROLE to the correct one to refresh the roles. Or re-attach. I think we should improve that automatically refreshing roles via AST or creating some command like ALTER SESSION REFRESH ROLES. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] DEFAULT grants to PUBLIC
On 10/05/2022 05:21, Roman Simakov wrote: > It makes sense. Could you create a ticket? > https://github.com/FirebirdSQL/firebird/issues/7178 Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] DEFAULT grants to PUBLIC
Hi! A role can be default granted to PUBLIC. create role r1; grant default r1 to public; In this case I think the role should be act as a defaulted role to every user. But it's not working. The role privileges only works if user uses it explicitly. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Remove travis-ci?
On 28/04/2022 05:37, Mark Rotteveel wrote: > Our travis-ci builds haven't actually run for the past 10 months as we > ran out of credits. As no one has noticed this in that time, it looks > like the travis-ci builds serve no actual purpose. > > Any objections if I remove the travis-ci build definitions, and revoke > the travis-ci permissions from github.com/FirebirdSQL ? > No objections. They seem to had a security problem. I have been notified by GitHub that my account was used to "list my organizations" in a broader attack: https://github.blog/2022-04-15-security-alert-stolen-oauth-user-tokens/ Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Status of 3.0.10
On 13/04/2022 12:24, Alex Peshkoff via Firebird-devel wrote: > We have one test failing on linux - bugs/core_1885.fbt. Create collation > should fail with some option, but it succeeds. uppose due to upgrade of > ICU on linux test server. > In other aspects I suppose we are ready for release. > I have looked at https://github.com/FirebirdSQL/fbt-repository/blob/master/tests/bugs/core_1885.fbt and I have no idea about what this test is doing. It says to be for all platforms, but rejects things that always worked in Linux: CREATE COLLATION UNICODE_ENUS_CI_25X FOR UTF8 FROM UNICODE CASE INSENSITIVE 'LOCALE=en_US'; CREATE COLLATION UNICODE_ENUS_CI_3X FOR UTF8 FROM UNICODE CASE INSENSITIVE 'LOCALE=en_US'; It also says "-- Attribute 'LOCALE=en_US' is defined for charset = ISO8859_1 rather that for UTF8, see intl/fbintl.conf" - mention of EN_US in ISO8859_1 in fbintl.conf has no relation with ICU collations. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
On 16/03/2022 00:45, Dmitry Yemanov wrote: > 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 have part 4 only of SQL 2011. LTTs can be declared only in SQL-server modules, in our case, packages. They cannot be declared inside routines. Sure we may extrapolate, if we do things that we believe is not going to be incompatible with future standards. But standard LTTs seems to be very weird in an aspect: it works per requests. Here is some quotes. 5.b does not copy LTTs identities. 5.g.i should be PSQL routines. That means a package with a LTT cannot store data on a LTT and call another routine of the same package that reads the data. It is much more useful if data is shared in this case. - 5) Preserve the current SQL-session context CSC and create a new SQL-session context RSC derived from CSC as follows: b) The values of the current SQL-session identifier, the SQL-session user identifier, the identities of all instances of global temporary tables, the cursor instance descriptor of all open cursors accessible in the SQL-session, the current constraint mode for each integrity constraint, the current transaction access mode, the current transaction isolation level, the current condition area limit, the subject table restriction flag, and the restricted subject table name list are set to their values in CSC. g) Case: i) If R is an SQL routine, then the identities of all instances of created local temporary tables, declared local temporary tables that are defined by s that are contained in s, the cursor instance descriptors of all open cursors that are not global extended dynamic cursors, prepared statements that do not have global extended names, and SQL descriptor areas that do not have global extended names are removed from RSC. ii) Otherwise: 1) Remove from RSC the identities of all instances of created local temporary tables that are referenced in s that are not the of P, declared local temporary tables that are defined by s that are contained in s that are not the of P, and the cursor instance descriptors of all open cursors that are not global extended dynamic cursors and whose SQL-client module is not the SQL- client module of P. 2) It is implementation-defined whether the identities of all instances of created local temporary tables that are referenced in the of P, declared local tem- porary tables that are defined by s that are contained in the of P, the cursor instance descriptors of all open cursors that are not global extended dynamic cursors and whose SQL-client module is the SQL- client module of P, prepared statements that do not have global extended names, and SQL descriptor areas that do not have global extended names are removed from RSC. - Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
On 15/03/2022 15:20, Vlad Khorsun wrote: >> So CREATEd LTTs cannot be used in PSQL routines, only by DSQL? > > At first look I see two way's to go: > a) PSQL routines can't see CREATE'd LTT's, or > b) PSQL routines can see and use LTT definition that exists at the > moment of routine > definition\loading into metadata cache. Such LTT's definition should > be not changed > while dependend PSQL routine reside in metadata cache, or PSQL > routine should be > invalidated when LTT definition it depends on is changed. > > Of course, there could be something else, lets continue to think on it. > I think (a) is the best option. (b) seems not friendly for Firebird and for this usage GTT seems the way to go. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
On 15/03/2022 15:00, Adriano dos Santos Fernandes wrote:
>> Would it be possible to use some other word to distinguish, instead of
>> just leaving out "local" (which essentially loses the most important
>> aspect of what it really is)?
>>
>> Suggestions for thought:
>>
>> Internal?
>> Volatile?
>> Virtual?
>> Transient?
>>
>
> None of these words seems good for me to distinguish the difference.
>
I liked Sybase syntax:
http://dev.cs.ovgu.de/db/sybase9/help/dbrfen9/0356.htm
declare local temporary table (
...
) [ ON COMMIT { DELETE | PRESERVE } ROWS | NOT TRANSACTIONAL ]
Adriano
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
On 15/03/2022 14:07, Dmitry Yemanov wrote: > 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? > Like a standard variable, data will be shared with the autonomous transaction scope. Note that if we say that as the contrary, LT would be very similar to a LTT (with transaction control). And that would not be problematic, but... I also want packaged LT in the future, with would be very useful to work outside transaction control. >> 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). My understanding is that it is per session / package (SQL-client module), not request / routine. > "Declared" LTTs are defined inside the PSQL routine, Inside a package (SQL-client module) with data shared in the package / session. > 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. I also did not found any major DBMS that implemented declared LTTs, nor SQL-standard modules. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
On 15/03/2022 13:00, Dimitry Sibiryakov wrote: > Adriano dos Santos Fernandes wrote 15.03.2022 15:37: >> On 15/03/2022 11:23, Alex Peshkoff via Firebird-devel wrote: >>> Very interesting - but other name for them should be used. It's too easy >>> to loose difference between LT & LTT that are absolutely different >>> things. >>> >> Maybe just "DECLARE TABLE" then? > > Oracle uses term "collection". Why not to adapt their syntax? > The only similarity with LT is that data is not under transaction control. Oracle's collection variables are not integrated with SQL update commands. It's mostly a PL/SQL (not SQL) feature. LT, while defined in PSQL, works like a SQL table, except that it does not uses transaction control. Oracle's collection is integrated with standard tables. It's part of their object-relational integration. Collections only as variable feels as an incomplete feature for me. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
On 15/03/2022 14:02, Vlad Khorsun wrote:
>
>> 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.
>
> Could you specify, in short, what part of standard is hard to apply to
> the Firebird ?
>
Declared LTTs are defined in "SQL-client module"
SQL-client module seems to have a relation to packages.
They can group procedures.
So as I said, declared LTTs feat well in packages.
The standard does not define LTTs inside routines.
If they did, they could have defined it with different semantics than
what I propose, for example, it could use the routine only as a scope,
but with shared data between invocations. So my reluctance in define LTT
inside routines.
>> Declared LTTs uses syntax "DECLARE LOCAL TEMPORARY TABLE ... [ ON
>> COMMIT PRESERVE/DELETE ROWS ].
>
> "ON COMMIT" clause seems meaningless for LTT's DECLARED within PSQL
> routine,
Does it means ON COMMIT would be prohibited there inside routines?
> while could be useful for packaged LTT's.
>
But LT (non-transactional) is also useful in packages.
It then means in package not using ON COMMIT would mean a different
thing than absence of ON COMMIT in GTT. Not something we would want.
>> Declared LTTs could be done as part of packages. They would be like
>> GLOBAL TEMPORARY TABLES (GTT) but declared (created, updated) as part of
>> packages headers (public) or body (private). They would work under
>> transaction control and may be represented in metadata.
>
> Mostly agree - just not sure about metadata, it could be discussed later.
>
Sure.
>> LTs could also be added in packages. It's different feature than LTTs.
>
> What is a difference ?
>
In that scope, mostly about it not being transactional.
>> I propose syntax of LT:
>>
>> DECLARE LOCAL TABLE (
>> { }...
>> );
>
> This statement should be used as part of common DECLARE section of
> PSQL routine
Yes.
>> "DELETE FROM " would be optimized to use
>> blr_local_table_truncate.
>
> You mean - if without WHERE clause, correct ?
Sure.
> What about undo ?
> In general - how operations on LT[T] should be handled in case of PSQL
> exceptions ?
>
It will not have undo.
PSQL exceptions during multi-row changes would abort the operation
leaving already done changes.
>> Standard scope rules will be used. A LT may use the same name of a
>> schema-based table and will shadow it inside the routine.
>
> Also, packaged LT should hide "GLOBAL" name when used in routine of
> the same
> package, correct ?
>
Yes. But note that I propose packaged LTs for a later moment. We would
first need more useful and simple feature first implemented there:
packaged variables.
Adriano
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
On 15/03/2022 12:03, Kjell Rilbe wrote: > Den 2022-03-15 kl. 15:37, skrev Adriano dos Santos Fernandes: >> On 15/03/2022 11:23, Alex Peshkoff via Firebird-devel wrote: >>> Very interesting - but other name for them should be used. It's too easy >>> to loose difference between LT & LTT that are absolutely different >>> things. >>> >> Maybe just "DECLARE TABLE" then? >> >> We would refer to them as "declared tables". >> >> In routines they will be local declared tables. >> >> In the future, when they could also be in a package, a packaged declared >> table. > > From a linguistic point of view I find "declared" to be a nonsense > qualification of "table". Aren't all tables declared in a sense? > No, some are CREATEd. > The essence of these tables seems to be that they are local to > "something", but the term "local" seems to be "taken" by the things > called local temporary tables(?). > Yes. > Would it be possible to use some other word to distinguish, instead of > just leaving out "local" (which essentially loses the most important > aspect of what it really is)? > > Suggestions for thought: > > Internal? > Volatile? > Virtual? > Transient? > None of these words seems good for me to distinguish the difference. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
On 15/03/2022 14:39, Vlad Khorsun wrote: > 15.03.2022 19:27, Dmitry Yemanov wrote: >> 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). > > First, CREATE'd LTT's have the visibility scope and lifetime of the > attachment. > Its definition is not seen by other attachments and every attachment > could have > LTT with the same name and different definition. Second, attachment > could have > private part (instance) of metadata cache that will contains definitions > of every > CREATE'd LTT and every statement could use it without additional penalty. > > No need to pollute persistent schema with temporary objects and pay > runtime > cost for storing\erasing such definitions. > So CREATEd LTTs cannot be used in PSQL routines, only by DSQL? Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
On 15/03/2022 11:23, Alex Peshkoff via Firebird-devel wrote: > > Very interesting - but other name for them should be used. It's too easy > to loose difference between LT & LTT that are absolutely different things. > Maybe just "DECLARE TABLE" then? We would refer to them as "declared tables". In routines they will be local declared tables. In the future, when they could also be in a package, a packaged declared table. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] Local tables
Hi!
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.
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.
Declared LTTs uses syntax "DECLARE LOCAL TEMPORARY TABLE ... [ ON
COMMIT PRESERVE/DELETE ROWS ].
Declared LTTs could be done as part of packages. They would be like
GLOBAL TEMPORARY TABLES (GTT) but declared (created, updated) as part of
packages headers (public) or body (private). They would work under
transaction control and may be represented in metadata.
LTs could also be added in packages. It's different feature than LTTs.
I propose syntax of LT:
DECLARE LOCAL TABLE (
{ }...
);
It will work with common commands: DELETE, UPDATE, INSERT, MERGE, UPDATE
OR INSERT, SELECT.
"DELETE FROM " would be optimized to use
blr_local_table_truncate.
Standard scope rules will be used. A LT may use the same name of a
schema-based table and will shadow it inside the routine.
Future enhancements may be done (index usage, faster get of count of
records), but I don't want to go there in initial design/implementation.
Adriano
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
On 03/03/2022 14:19, Alex Peshkoff via Firebird-devel wrote: > On 2/28/22 20:30, Adriano dos Santos Fernandes wrote: >> On 28/02/2022 13:18, Alex Peshkoff via Firebird-devel wrote: >>> I suppose it's with mentoned 2Mb cache. >> Yes. But it's the same even with 100K as the test uses very few and >> small statements. > > Taking into an account that bigmost was 21k there are really few > statements :) > Did you try with smaller cache size? > No, only with it disabled. I still have some problems to detect statements size with more precision. > At the first glance that's changing index active/inactive This should be already invalidating cache, as it's a DDL statement. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] isc_arg_string in status vector
On 3/2/22 05:17, Jiří Činčura wrote: >> But before attachment is done that does not happen and strings are >> returned as is (for example, error about non-existent file in Windows >> ANSI encoding). >> >> To convert charsets, database must be opened currently. > > That's sad. I'm facing exactly that now with isc_create_database. :( > I was tracking this issue together with an intl refactor, but it's dormant for some time. I'll come back to it some time. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] Statement::verifyAccess of internal requests
Hi!
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;
...
}
Adriano
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Snapshot buuilds
On 01/03/2022 13:19, Karol Bieniaszewski wrote: > Hi > > > > Snapshots are not avilable > You may download non-official snapshot builds here (logged with github): https://github.com/FirebirdSQL/firebird/actions Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
On 28/02/2022 13:18, Alex Peshkoff via Firebird-devel wrote: > I suppose it's with mentoned 2Mb cache. Yes. But it's the same even with 100K as the test uses very few and small statements. > > One more question. Suppose some statement remains in a cache for very > long time cause it's reused again and again. That's fine - but with DB > grow optimal plan can change. Is it solved somehow? Suppose in first > implementation not, Correct. And it's a problem also now when application holds prepared statement manually, but that probably is not going to be changed. > but are there plans to solve it? > I think there are two possible ways: - Timeout of cached statement (counting from its first appearance in cache, not last usage) - When engine detects a condition which could change a plan, it may ask cache for invalidation. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
On 26/02/2022 22:05, Adriano dos Santos Fernandes wrote: > > I will report back some numbers (memory, prepare times). > Here is a performance test I did: https://pastebin.com/3UnB5BNU It's relative simple (not much indices) and common metadata. It's tested as whole (create database, create metadata, populate, run queries and get results, drop database). The last select has a statement size of 21.5KB. For reference, "select 1 from rdb$database" has size 6KB. Test timings: - Without statement cache: 19800ms - With statement cache: 16700ms Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] isc_arg_string in status vector
On 28/02/2022 07:17, Alex Peshkoff via Firebird-devel wrote: > On 2/28/22 13:10, Jiří Činčura wrote: >> Hi *, >> >> If I get pointer to a string in isc_arg_string in status vector the >> encoding is UTF8 if isc_dpb_utf8_filename was specified and >> ANSI/system charset when isc_dpb_utf8_filename was not specified? >> > > Data in status vector should be according to attachment charset and not > affected by isc_dpb_utf8_filename. > If that's not so then it should be a bug. > > ps.In some cases error may happen when trasliterating UTF8 line to > attachment charset. In that case strig willl be returned is UTF. > But before attachment is done that does not happen and strings are returned as is (for example, error about non-existent file in Windows ANSI encoding). To convert charsets, database must be opened currently. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
On 26/02/2022 14:43, Dmitry Yemanov wrote: > 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. > Procedure body does not count on this cache, as only the user issued statements counts, not recursively counting others called routines. But amount of parameters count. So as complex EXECUTE BLOCK. And EXECUTE BLOCK' sub routines. But memory size of statement is not always related to compile time, and cache is important for time-consuming compilation too. A situation I'd think here is when there are many indices to analyze. I have seem that very simple selects would have a size of few KBs. So my reason to think in 2M is that even a database with many connections (say 100) will not have a very memory-consuming cache if default is not changed. I will report back some numbers (memory, prepare times). Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
Hi! I do want to define default cache size. I'm thinking in 2M. Comments? Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Build for WASM
On 23/02/2022 06:40, Jiří Činčura wrote: >> You mean, running in the browser? > > Yes. Or any other place where WASM is/will be supported. > I had a (very) brief look at it once. Main difficulties should be: - Memory manager - no mmap - Shared libraries - File I/O - Lock manager All these points should have a workaround for a limited environment, but one must work on them - improving abstractions and much testing and fixes, I suppose. I believe it should be a long work. It would have a good usage: allow usage of Firebird directly in web pages, running in the client. That would be awesome for users wanting to try Firebird. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Build for WASM
Em qua., 23 de fev. de 2022 04:51, Jiří Činčura escreveu: > Hi *, > > Was there ever a discussion about providing Firebird build for WASM? I > mean, having Firebird Embedded in WASM would be sick. > You mean, running in the browser? Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] INT64 and index keys
On 15/02/2022 08:28, Dmitry Yemanov wrote: > > Any other comments? > Would not it be possible to use some form of varint encoding (VLQ) there? Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] JRD_reschedule
On 14/02/2022 10:43, Alex Peshkoff via Firebird-devel wrote:
>
> - AST (yes, 'A'-async is wrong tday, but I use traditional term) processing
That is the part I was missing, thanks.
BTW in thread_db::reschedule() we have:
-
void thread_db::reschedule()
{
// Somebody has kindly offered to relinquish
// control so that somebody else may run
checkCancelState();
StableAttachmentPart::Sync* sync =
this->getAttachment()->getStable()->getSync();
Database* dbb = this->getDatabase();
if (sync->hasContention())
{
FB_UINT64 cnt = sync->getLockCounter();
EngineCheckout cout(this, FB_FUNCTION);
Thread::yield();
while (sync->hasContention() && (sync->getLockCounter() == cnt))
Thread::sleep(1);
}
checkCancelState();
Monitoring::checkState(this);
if (tdbb_quantum <= 0)
tdbb_quantum = (tdbb_flags & TDBB_sweeper) ? SWEEP_QUANTUM :
QUANTUM;
}
-
Should not the checkCancelState() be better inside the "if
(sync->hasContention())" as the no contention case just checked it?
Adriano
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] JRD_reschedule
Hi! What is the importance of JRD_reschedule today? More specifically speaking, verifying contention and "checking out"? Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
On 10/02/2022 14:44, Dmitry Yemanov wrote: > 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. > Yes, but the impure space stores more allocations as execution is happening and this also serve as cache for later executions. So I think we have at least maintain a few ready instead of destroy all of them directly. And it seems we currently do not track later allocations stored in requests (for example vlu_string). And requests uses the statement pool. So maybe requests should have they own pool. In non-shared cache they could be sub-pool of the statement pool, but with shared cache they probably would need to be child of the attachment pool. >> 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. > Yes. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
On 10/02/2022 12:30, Vlad Khorsun wrote: > So, main benefit visible to end-user is to save prepare and check access > time, correct ? Yes. > Also, good written apps (that re-uses prepared statements) > will not see much changes - at least until impl of shared metadata cache. > I consider ORM written applications as good ones. And I would not consider them good if they cache statements in the client requiring extra efforts to avoid locks in DDL changes in another connections. Also good written applications uses connection pools and will benefit from the cache. It's like saying the current sharing of JRD statements from procedures/functions/triggers inside the attachment has no value because it's not shared. So I do not agree with your points. > >> Also reuse of cached statements reduces memory consumption of individual >> uncached identical statements. > > I.e. when application uses more than one instance of the same > statement in > the same connection ? Hard to imagine, but everything possible... > ORMs tends to execute parameterized queries that is always executed again and again in the course of the application execution. This is very common pattern. > The main idea is to allow to disable user cache but not system cache. > Are you going to move IRQ_REQUESTS and DYN_REQUESTS into such system cache, > or am I too optimistic ? > As I said in another answer, I consider this a different topic. >> If they are not, them when roles are different than one present in >> cached statement, a verifyAccess would need to be called on the >> statement get from the cache before it's usage is allowed. > > I ask because such restrictions could make stmt cache less useful. > > For example, we could keep list of already verified set of credentials > (user name + roles list) with cached stmt to not include it into key. > Yes, this is good. > I want to clarify - what happens when app prepares (or execute) two > identical stmts ? First instance could be taken from cache, ok. What > happens with second instance ? > First statement is prepared like before (parse, DSQL passes, GEN, JRD compile, JRD passes, DSQL messages) and then inserted into the cache. Will then create DSQL request with dsqlStatement->createRequest(). Second prepare will see the statement key in the cache and get DsqlStatement from it, check access and do dsqlStatement->createRequest(). Statement cache is basically a map>. DSqlStatement has the (Jrd)Statement inside it. So cached statements avoid all DSQL and JRD compilation passes. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
On 10/02/2022 14:13, Dimitry Sibiryakov wrote: > Dmitry Yemanov wrote 10.02.2022 18:07: >>> 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. > > On the other hand ACL verification used to be a known bottleneck in > the past so caching it may gain more that everything else in total. > It's why I said: *with verification cache*. ACL changes would them invalidate verification cache only. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
On 10/02/2022 13:13, Dmitry Yemanov wrote: > 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. > Answered in another topic. >> 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. Ok. > How are you going to calculate the memory consumption? > Size of the statement pool + impureSize? > I don't think the impureSize should be taken. It's related to request executions. 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. I would calculate size of statement as sum of DSQL statement pool + JRD statement pool after it is prepared. 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. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
On 10/02/2022 13:34, Dimitry Sibiryakov wrote:
> Dmitry Yemanov wrote 10.02.2022 17:28:
>>> 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?
>
> Yes, they can only cause miss but I'm not sure about literals as
> whole. Are they stored in execution tree already transliterated or with
> charset mark?
> Can the query "insert into t values ('абв')" sent from attachment with
> different charsets (win1251 and utf-8 for example) hit the cache at all?
> Should it?..
>
Please also note that transform queries to parameterized ones may be
problematic for future optimizer improvements (histograms).
Adriano
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
On 10/02/2022 13:28, Dmitry Yemanov wrote: > > In the ideal world, maybe. But this is *much* more complicated than it > seems at the first glance. > I'd even go further and say we should not slow down things to catch this. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
On 10/02/2022 13:21, Dimitry Sibiryakov wrote: > Dmitry Yemanov wrote 10.02.2022 17:13: >> 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. > > Only if such translation is made right. Remember charset introducers. > 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. > I think the main cache objective is not to lose time processing text and caching these cases, like ad hoc different queries may have. Only basic left/right trim would be more than enough. Applications uses queries and they do not create extra random spaces in statement text everytime they prepare/execute a query. Applications also tends to use the same character set. But main problem is because messages (both DSQL and JRD) are completely different when different charsets are used. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
On 10/02/2022 13:20, Dmitry Yemanov wrote: > 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? > Also, in the refactoring already made, the only change about Statement/jrd_req is that now Statement could be created without an initial jrd_req. Previously the internal API always created an initial jrd_req. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
On 10/02/2022 13:20, Dmitry Yemanov wrote: > 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? > I'm talking about compiled cache at DSQL level. We currently have one or two internal DSQL request, but I don't think it's a way to go now (or directly related to this) and expand it. I have some ideas for it which would replace GDML by something better, without preprocessor but also without lose type safety, but I think this is another topic. So in this thread I'm not considering changes in internal (GDML) requests. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
On 10/02/2022 12:43, Dmitry Yemanov wrote: > 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? > 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. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
On 10/02/2022 10:04, Dimitry Sibiryakov wrote: > Adriano dos Santos Fernandes wrote 10.02.2022 13:56: >> But it's not surprise that many applications are not well coded and >> sometimes does not do this. Competitor DBMSs has cache and works well in >> this case too. > > Such applications used to build the queries ad-hock with data as > literals. Some times yes, some times no (it's identical statement reprepared or executed). > The competitors has a way to forcefully parameterize such > queries and without it the cache is useless because every query is > different. > Yes, this is possible and some do it. But I do not see it in the scope of a first version. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
On 10/02/2022 07:36, Dimitry Sibiryakov wrote: > Alex Peshkoff via Firebird-devel wrote 10.02.2022 10:59: >>> If\when shared metadata will be implemented - will it be possible to >>> use cached compiled statement created in one attachment to use by >>> another >>> attachment ? >> >> If we need to take roles into an account - only for attachment with >> same USER. > > It depends on what exactly is going to be cached. If only execution > plan is cached - it is not affected any by current user, any by role. > Every DSQL structure (messages structures, for example) is cached. (Jrd)Statement is reused - new jrd_req are get from same statement. Things related with the DSQL request (message buffers) are created for each request. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
On 10/02/2022 06:59, Alex Peshkoff via Firebird-devel 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. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
On 10/02/2022 06:44, Vlad Khorsun wrote: > 08.02.2022 15:36, Adriano dos Santos Fernandes wrote: >> Hi! >> >> I have refactored DSQL statements/requests (continued worked of many >> time ago) to separate shared (statement) and specific (request) parts. >> >> It seems this ground work for compiled statement cache is complete and I >> have even did a (very very) initial version of a compiled statement >> cache working. > > First, please, describe - what goals\benefits of this cache ? Especially, > taking into account current per-attachment metadata. > In ideal world applications prepare their statement and execute them when that is going to be repeated. I would even consider a bug if they do not do that correctly in situations where an application process starts and do things in a loop. But it's not surprise that many applications are not well coded and sometimes does not do this. Competitor DBMSs has cache and works well in this case too. But more important to make bugged applications better, it's to make more well coded applications better. For example if ORM frameworks (you like it or not - they are used a lot) cache prepared statements for better performance, that will have side effects: - It cannot control server memory usage. - It locks not currently used objects preventing database changes. I have seem in test a not very complex statement having it's prepare time reduced by 50% when it's cached. Also reuse of cached statements reduces memory consumption of individual uncached identical statements. >> Now I think it's better to discuss its semantics. >> >> First what should be the statement key in the cache? > > All what affects statement compilation process, at least. > >> I've peek these: >> - statement text >> - clientDialect >> - isInternalRequest > > Why it is important ? Do we have internal DSQL requests now ? Yes. > Is it makes sense to have two caches - for internal and for user > statements ? > In long term I think yes, but it's not two cached, it's just a piece of the key. And not used cached internal requests would go out of cache. Anyway, it's very easy to disable it if it's considered as not important now. >> - current client charset (as external engines may change it) >> - active roles > > If\when shared metadata will be implemented - will it be possible to > use cached compiled statement created in one attachment to use by another > attachment ? It should be the easier part in the process of shared metadata changes. > If yes, does it means that another attachment should use same > client charset Yes. > and active roles to be able to use cached compiled > statement ? > About roles, they may not necessary be part of the cache key. If they are not, them when roles are different than one present in cached statement, a verifyAccess would need to be called on the statement get from the cache before it's usage is allowed. >> Do you see any thing more? >> >> 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 think we can start with 1. > > For me 2 is a must, 1 good to have but less important. > Ok. >> Should it be enabled by default? >> >> I think yes. > > Yes for internal requests. Not sure about user requests. > I would better put small max. memory usage for it than disable. It would impact a lot some usage patterns - for example - reports with sub-reports where sub-report always prepare its identical parameterized query. > And you not explained cache usage - when and how cached statement > should be used. > I'm not sure you want more additional information than one I replied here. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] Compiled statement cache
Hi! I have refactored DSQL statements/requests (continued worked of many time ago) to separate shared (statement) and specific (request) parts. It seems this ground work for compiled statement cache is complete and I have even did a (very very) initial version of a compiled statement cache working. Now I think it's better to discuss its semantics. 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) - active roles Do you see any thing more? 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 think we can start with 1. Should it be enabled by default? I think yes. And it may have per database configuration of the timeout value. Cache invalidation: Cached (and unused) statements should not lock objects preventing DDL changes. Assuming that cached statements never lock anything exclusively (there should be bug if that happens), then when someone tries to lock something exclusively it should ask caches to release the statements having that objects. Or, I think it's completely ok for an initial implementation, any try to exclusively lock an object may ask caches for complete invalidation. Comments? Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Parameter block and database/service name encoding
On 03/02/2022 12:39, Dimitry Sibiryakov wrote: > Hello All. > > Remember me please why isc_lc_ctype isn't used to determine encoding > of database name and DPB values? > BTW, service name during service attach isn't converted into UTF-8 at > all. > Character set discovery from name requires opening a database to match 100% what happens after database is open. There is aliases inside database (both iso8859_1 and iso88591 are valid). So chicken and egg problem. These creates others problems, for example to convert server codepage of exceptions to client. I plan to change some of these things in the future so all charset names could be discovered independent of a database connection. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
