Re: [Firebird-devel] Build master branch with VS 2019
19.08.2022 15:04, Gabor Boros wrote: 2022.08.19. 12:31 keltezéssel, Vlad Khorsun írta: ... At last, ensure your .bat files contains Windows-style EOL's (crlf), not Unix style. (I used "Code/Download ZIP" at GitHub to fetch the source.) Build works after bought an unix2dos lifetime license. :-) Thank You! Happy testing ;) In output_x64_release directory I have common_test.exe and engine_test.exe. Is it normal? I cannot check the snapshot build because "Connection timed out" (http://web.firebirdsql.org/download/snapshot_builds/win/5.0). Yes, it is normal. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Build master branch with VS 2019
19.08.2022 13:36, Mark Rotteveel wrote: On 19-08-2022 12:31, Vlad Khorsun wrote: At last, ensure your .bat files contains Windows-style EOL's (crlf), not Unix style. The repository should contain a .gitattributes file that configures this. I was a bit surprised to see use of unix2dos in the GitHub actions workflow to ensure this for the batch files. Me too ;) Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Build master branch with VS 2019
19.08.2022 13:15, Gabor Boros wrote: 2022.08.19. 10:54 keltezéssel, Vlad Khorsun írta: It shows that preprocessing was failed. Look for the following line at console: Preprocessing the source files needed to build gpre and isql... Few lines after there should be few blocks like: Processing burp/backup.epp Calling GPRE for burp/backup.epp 1 file(s) moved. Show it here, please. Preprocessing the source files needed to build gpre and isql... The file: "C:\Program Files (x86)\Microsoft Visual Studio\2019\Community\Common7\Tools\\..\..\VC\Auxiliary\Build\vcvarsall.bat" AMD64 has already been executed. Setting Environment Variables thus... vs_ver=msvc15 FB_VSCOMNTOOLS=C:\Program Files (x86)\Microsoft Visual Studio\2019\Community\Common7\Tools\ platform=x64 msvc_version=15 db_path=R:/FIREBI~1 root_path=R:\FIREBI~1 (End of setenvvar.bat) ... Processing yvalve/blob.epp Calling GPRE for yvalve/blob.epp 1 file(s) moved. Here is missed lines about preprocessing of dsql/metd.epp, dsql/DdlNodes.epp and dsql/PackageNodes.epp Check, please, presence of src/dsql/*.epp files Also, ensure your preprocess.bat contains following line: ::=== :BOOT_PROCESS @echo. @set GPRE=%FB_BOOT_BIN_DIR%\gpre_boot -lang_internal @for %%i in (backup, restore, OdsDetection) do @call :PREPROCESS burp %%i -ocxx -m @for %%i in (extract, isql, show) do @call :PREPROCESS isql %%i -ocxx @for %%i in (dba) do @call :PREPROCESS utilities/gstat %%i @set GPRE=%FB_BOOT_BIN_DIR%\gpre_boot @for %%i in (alice_meta) do @call :PREPROCESS alice %%i @for %%i in (array, blob) do @call :PREPROCESS yvalve %%i @for %%i in (metd, DdlNodes, PackageNodes) do @call :PREPROCESS dsql %%i -gds_cxx <<< HERE @for %%i in (gpre_meta) do @call :PREPROCESS gpre/std %%i At last, ensure your .bat files contains Windows-style EOL's (crlf), not Unix style. Processing gpre/std/gpre_meta.epp Calling GPRE for gpre/std/gpre_meta.epp 1 file(s) moved. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Build master branch with VS 2019
03.07.2022 13:43, Gabor Boros wrote: Hi All, I tried to build master branch with VS 2019 Community 2019 16.11.16 on an up to date Windows 10 Pro 64bit. At make_boot got fatal errors in isql_x64.log: WorkerAttachment.cpp lock.cpp gsec.cpp ppg.cpp nbackup.cpp The command exited with code 2. Done executing task "CL" -- FAILED. 10>Done building target "ClCompile" in project "engine.vcxproj" -- FAILED. 10>Done Building Project "R:\firebird-master\builds\win32\msvc15\engine.vcxproj" (default targets) -- FAILED. 3>Done executing task "MSBuild" -- FAILED. 3>Done building target "Build" in project "engine.vcxproj.metaproj" -- FAILED. 3>Done Building Project "R:\firebird-master\builds\win32\msvc15\engine.vcxproj.metaproj" (default targets) -- FAILED. 2>Done executing task "MSBuild" -- FAILED. 2>Done building target "Build" in project "isql.vcxproj.metaproj" -- FAILED. 2>Done Building Project "R:\firebird-master\builds\win32\msvc15\isql.vcxproj.metaproj" (default targets) -- FAILED. 1>Done executing task "MSBuild" -- FAILED. 1>Done building target "isql" in project "Firebird.sln" -- FAILED. 1>Done Building Project "R:\firebird-master\builds\win32\msvc15\Firebird.sln" (isql target(s)) -- FAILED. Build FAILED. ... "R:\firebird-master\builds\win32\msvc15\Firebird.sln" (isql target) (1) -> "R:\firebird-master\builds\win32\msvc15\isql.vcxproj.metaproj" (default target) (2) -> "R:\firebird-master\builds\win32\msvc15\engine.vcxproj.metaproj" (default target) (3) -> "R:\firebird-master\builds\win32\msvc15\engine.vcxproj" (default target) (10) -> (ClCompile target) -> c1xx : fatal error C1083: Cannot open source file: '..\..\..\gen\dsql\DdlNodes.cpp': No such file or directory [R:\firebird-master\builds\win32\msvc15\engine.vcxproj] c1xx : fatal error C1083: Cannot open source file: '..\..\..\gen\dsql\metd.cpp': No such file or directory [R:\firebird-master\builds\win32\msvc15\engine.vcxproj] c1xx : fatal error C1083: Cannot open source file: '..\..\..\gen\dsql\PackageNodes.cpp': No such file or directory [R:\firebird-master\builds\win32\msvc15\engine.vcxproj] It shows that preprocessing was failed. Look for the following line at console: Preprocessing the source files needed to build gpre and isql... Few lines after there should be few blocks like: Processing burp/backup.epp Calling GPRE for burp/backup.epp 1 file(s) moved. Show it here, please. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Current value for parallel workers
16.08.2022 12:26, Jiří Činčura wrote: Hi *, Is there a way to get current value of parallel workers (from i.e. monitoring tables)? Not yet. How would you like to see it ? Monitoring table, session context variable, database_info item ? All above ? ;) Should user session be allowed to change it ? Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] op_que_events and database shutdown
16.08.2022 11:26, Jiří Činčura wrote: I just want to know there's nothing on protocol level I can use to help this situation. No response. So I suppose nothing? I didn't found anything suitable. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] New statement: EXECUTE SQL
15.08.2022 20:42, Vlad Khorsun wrote: Note, semicolon usually mark "client" named parameters Colon, of course, not semicolon. Sorry, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] New statement: EXECUTE SQL
14.08.2022 2:28, Adriano dos Santos Fernandes wrote: 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. 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 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. Therefore I suggest to use one kind of parameters. I prefer declared ones, i.e. without direct params. Query above will look like: execute sql (p1 integer = :p1, p2 varchar(255) = :p2) do select * from t where t.id = :p1 and t.name = :p2 and after preprocessing: execute sql (p1 integer = ?, p2 varchar(255) = ?) do select * from t where t.id = :p1 and t.name = :p2 i.e. only header part between 'sql' and 'do' (or 'declare') should be preprocessed by client app\access components. Also, I don't like 'sql' word, especially after 'execute statement' and 'execute block'. Too much, as for me :) Syntax with 'with' instead of 'execute sql' looks much better to me, but it is already used in CTE's, thus it seems as not the best choice :( Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Parallel workers in isc_action_svc_repair + isc_spb_rpr_sweep_db
12.08.2022 16:45, Mark Rotteveel wrote: On 12-08-2022 15:13, Vlad Khorsun wrote: 12.08.2022 15:31, Jiří Činčura wrote: Hi *, does the isc_dpb_parallel_workers apply when running sweep via isc_action_svc_repair + isc_spb_rpr_sweep_db? Sure. There is no isc_spb_parallel_workers, so I would think it doesn't apply to service actions. What am I missing? There is isc_spb_rpr_par_workers. I should have been more careful reading the question, sorry. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Status of isc_dpb_parallel_workers
12.08.2022 14:25, Jiří Činčura WROTE: Is there any prospect for more parallel operations in near future? Especially around i.e. query processing, basically general database operations. The reason I'm asking is whether to implement it in .NET provider on connection string level or only for "gbak" and "gfix". Disregard this question. I somewhat missed the index creation feature. It looks like connection string level looks like a better fit. Agree. BTW the linked document mentions only gfix and index. But doc/README.gbak covers also gbak. Maybe good idea to update the doc/README.parallel_features as well? README.parallel_features describes engine, while parallel data load\read is implemented mostly at gbak, not at the engine side. Though gbak uses some new "helper" features of engine, such as ability to share database snapshot or new built-in function MAKE_DBKEY(), it not depends on engine's parallelism. So, I don't see what should be added to the README.parallel_features regarding gbak. Do you have good idea ? Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Status of isc_dpb_parallel_workers
08.08.2022 9:57, Jiří Činčura wrote: Is there any prospect for more parallel operations in near future? Especially around i.e. query processing, basically general database operations. The reason I'm asking is whether to implement it in .NET provider on connection string level or only for "gbak" and "gfix". I don't expect more parallel features in v5 than already committed. And, yes, it should be implemented for regular conn str as any other DPB tag. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Parallel workers in isc_action_svc_repair + isc_spb_rpr_sweep_db
12.08.2022 15:31, Jiří Činčura wrote: Hi *, does the isc_dpb_parallel_workers apply when running sweep via isc_action_svc_repair + isc_spb_rpr_sweep_db? Sure. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] BLOB_APPEND and NULL
11.08.2022 19:31, Mark Rotteveel wrote: On 11-08-2022 18:04, Vlad Khorsun wrote: 11.08.2022 17:46, Jiří Činčura wrote: I was thinking the same when reading the discussion on GH. There was a LOT of time to write something at that discussion. Nobody asked about NULL's there, while it was documented since a very beginning. Unfortunately I don't always have time or energy to monitor all GitHub updates, and there was no previous discussion on this list about BLOB_APPEND, so I simply had not noticed its existence before. I replied to Jiří who definitely read that discussion ;) It is always very sad to get such comments two month after the code was merged and release is almost out. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] BLOB_APPEND and returned SUB_TYPE
11.08.2022 17:40, Mark Rotteveel wrote: I noticed that BLOB_APPEND always returns a blob of SUB_TYPE TEXT, even if the first blob is binary or other type of blob. Is that expected? No, the intention was to use type\charset of first arg (if not NULL). I'll check and fix it, thanks. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] BLOB_APPEND and NULL
11.08.2022 17:46, Jiří Činčura wrote: I was thinking the same when reading the discussion on GH. There was a LOT of time to write something at that discussion. Nobody asked about NULL's there, while it was documented since a very beginning. I believe it's not late to make correction (hey, we all make mistakes) and have consistent behavior. So far I don't see it as mistake. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] BLOB_APPEND and NULL
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. I had hoped for something more solid, so I could add that as an explanation when documenting it in the language reference. 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. PS You mailed this to me privately instead of to the list. Sorry, hope you are not suffer too much because of it ;) Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] op_que_events and database shutdown
05.08.2022 12:40, Dimitry Sibiryakov wrote: Vlad Khorsun wrote 05.08.2022 11:17: It could be compared with last known counters before re-connect, if necessary. Bad idea. If database was reloaded counters are reset. If there was just connection (not database) shutdown, counters will not be reset. In any case - this is up to app dev to decide how to handle re-connection in own code. The main point still the same - no events will be missed. Another way could be to call callback with NULL events and 0 length but I'm not sure existing apps is ready to handle such signal. They are ready because it is normal reaction to isc_cancel_events. Good. Probably it is the way to go. While I'm not sure we really need such changes. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] op_que_events and database shutdown
03.08.2022 16:35, Jiří Činčura wrote: Hi *, op_que_events and waiting for events, database goes into shutdown. What to do next? Good question The socket is kept open, no luck there. And the "database shutdown" error is returned only after some next operation. Yes. This is done to allow application to get isc_shutdown error instead of isc_network_error. But as I'm waiting for the events, I kind of don't have next operation. Seems so. Unless the developer does something (which might happen way later and events might be missed). What events might be missing ? Connection is shutdown and can't be resurrected. New connection will queue new events and can't miss one. Remember, first "queue events" in connection immediately receives most current counters. It could be compared with last known counters before re-connect, if necessary. Is this something we have solution for. Or is this developer's responsibility? Dev responsibility is to react on isc_shutdown: by disconnecting - this will stop events listener for given connection. In theory, engine could call events callback with isc_shutdown in status-vector to signal about shutdown. But there is no status-vector in callback routine, unfortunately :( Another way could be to call callback with NULL events and 0 length but I'm not sure existing apps is ready to handle such signal. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Status of isc_dpb_parallel_workers
04.08.2022 15:58, Jiří Činčura wrote: Hi *, What's the status of isc_dpb_parallel_workers? The only reference I can find is the slide deck from 2019 from Firebird Conference. But I can't find anything in code. Was this implemented? Dropped? Renamed? Postponed? It is implemented in Firebird 5 and documented there, see doc\README.parallel_features Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] SIO_LOOPBACK_FAST_PATH deprecated
10.07.2022 11:58, Mark Rotteveel wrote: On 02-02-2021 00:45, Vlad Khorsun wrote: 02.02.2021 1:35, Leyne, Sean wrote: https://www.gitmemory.com/issue/grpc/grpc/18057/486312183 There is no mention of SIO_LOOPBACK_FAST_PATH in this link Read carefully, please. https://support.microsoft.com/en-us/topic/stop-error-0xd1-when-you-set- the-sio-loopback-fast-path-flag-in-windows-8-or-windows-server-2012- 14399334-f3a8-b731-3799-12899a79bf35 The HotFix is from 2012, with the last update in 2015. I think that the underlying issue has been resolved/mitigated. We can only guess. Therefore I don't offer to remove the feature, but consider to deactivate it by default. I want to revisit this discussion. I think that given Microsoft deprecated SIO_LOOPBACK_FAST_PATH and warns against it, we should either remove this entirely from Firebird 5.0, or at minimum disable it by default. Thoughts? Disable in 4.0.2 and disable (or remove) in 5.0, I think. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] GBAK problem after parallel backups changes
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. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] GBAK problem after parallel backups changes
26.06.2022 1:31, Adriano dos Santos Fernandes wrote: 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 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. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] GBAK problem after parallel backups changes
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 ? Regards, Vlad 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.
17.06.2022 13:33, Adriano dos Santos Fernandes wrote: 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. +1 Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] WITH CALLER PRIVILEGES propagation
21.04.2022 13:38, Jiří Činčura wrote: Hi, Can propagate the privileges down into the call stack when using WITH CALLER PRIVILEGES? For example: CREATE TABLE T_TEST (ID INTEGER NOT NULL, CONSTRAINT PK_TEST PRIMARY KEY (ID)); /* Package header: PKG_TEST, Owner: SYSDBA */ CREATE PACKAGE PKG_TEST AS begin procedure test returns (i int); end^ /* Package header: PKG_TEST_LIMITED, Owner: SYSDBA */ CREATE PACKAGE PKG_TEST_LIMITED AS begin procedure test returns (i int); end^ /* Package body: PKG_TEST, Owner: SYSDBA */ CREATE PACKAGE BODY PKG_TEST AS begin procedure test returns (i int) as begin for select id from t_test into :i do begin suspend; end end end^ /* Package body: PKG_TEST_LIMITED, Owner: SYSDBA */ CREATE PACKAGE BODY PKG_TEST_LIMITED AS begin procedure test returns (i int) as begin for execute statement 'select i from pkg_test.test' with caller privileges into :i do begin suspend; end end end^ /* Grant permissions for this database */ GRANT SELECT ON T_TEST TO PACKAGE PKG_TEST_LIMITED; GRANT EXECUTE ON PACKAGE PKG_TEST_LIMITED TO USER LIMITED; Now if I do, under LIMITED user, `select * from pkg_test_limited.test;` is will end up with `no permission for SELECT access to TABLE T_TEST`. Here user LIMITED executes PKG_TEST_LIMITED.TEST (which it have explicit grant to do, see 2nd GRANT statement) and than going to execute procedure from package PKG_TEST which nor user LIMITED nor package PKG_TEST_LIMITED is not granted to do. Error message is misleading here, btw. If you GRANT SELECT ON T_TEST TO PACKAGE PKG_TEST and run select * from pkg_test_limited.test then you'll see more correct error: no permission for EXECUTE access to PACKAGE PKG_TEST Then add missing GRANT EXECUTE ON PACKAGE PKG_TEST TO PACKAGE PKG_TEST_LIMITED and query will run successfully. But if I change the execute statement into `for execute statement 'select id t_test' with caller privileges into :i do` everything is fine. Sure, because package PKG_TEST_LIMITED granted to do it (your 1st GRANS statement) and caller privileges is effective. I guess the "caller privileges" is propagated only into `pkg_test_limited.test` when calling, but not further into `t_test`. > > Can I somewhat make it work/propagate? Or did I misunderstood the feature? Hope it is clear now. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] trace_dsql_prepare and statement encoding
04.04.2022 19:06, Dimitry Sibiryakov wrote: Hello. I've found that for versions 3 and 4 in trace_dsql_prepare() statement->getText() returning SQL statement text converted into UTF-8 and getTextUTF8() - returning it converted twice. ITraceSQLStatement::getTextUTF8() is not used by trace plugin since v3 as engine already converts SQL statement text into UTF8 encoding. Therefore second conversion was not noticed so far. BTW, in v2.5 this method was used for regexp matching only (include\exclude_filter). Note, engine convert statement text into UTF8 after successful parsing only, therefore statement text reported by failed prepare could be not converted and put into trace\audit log as it was supplied by client. It is reproducible even with standard trace plugin (audit log as seen in ANSI mode): Since v3 trace\audit log considered to be in UTF8. Statement 32: --- select * from "абв" ^^^ Select Expression -> Table "абв" Full Scan 1 ms Here conversion from UTF8 back to connection charset happens at OPT_get_plan() that call RecordSource::printName(). Looks like we should find a way to disable such conversion when it is not needed. Is it really supposed to work this way? We trying to do as much as possible to put UTF8 data into trace\audit log. If some parts are not converted - it should be found and fixed. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] ITraceConnection::getCharSet() result TTL
04.04.2022 17:50, Dimitry Sibiryakov wrote: Hello. How long lives the pointer returned by ITraceConnection::getCharSet()? This is implemenation details that could be changed without notice. Can I store it or must copy content into local storage in the instance of ITracePlugin that is created for this connection? I'd recommend to make a copy of all necessary bits of info obtained from trace objects. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Security vulnerability in zlib library
31.03.2022 11:11, Mark Rotteveel wrote: A security vulnerability was found in zlib: https://nakedsecurity.sophos.com/2022/03/29/zlib-data-compressor-fixes-17-year-old-security-bug-patch-errr-now/ Will we include an updated version in the next release? I'll take care about Windows builds Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
15.03.2022 20:14, Adriano dos Santos Fernandes wrote: 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. Yes. But we can think of "standalone" PSQL routine as of member of implicit package, if needed. 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. It could be discussed and I see no big problem implementing both scope's. I.e. data of DECLARE'd LTT could shared or not for recursive invocations of PSQL routine with declaration. Also, we should define access rules for sub-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? Prohibited or ignored, to be decided. 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. It is also should be discussed properly. BTW, why do you want LT to be non-transactional ? Because of implementation difficulties or by another reason ? For me, transactional LT[T] is a must have feature, while non-transactional could be an extension. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
15.03.2022 20:50, Adriano dos Santos Fernandes write: 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. At current state of things I tend to agree with you. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
15.03.2022 19:41, Dmitry Yemanov wrote: Anyway, IMO "declared" LTTs are much more useful than "created" LTTs, so the latter ones may be deferred. Or we may live without them at all ;-) CREATE'd LTT's could be very useful to store\materialize intermediate results of complex evaluations. Think about it as of temp variables used in complex expressions. Obviously, GTT's are much less useful in such scenarios. *Relational* DBMS should be able to operate with *relation*-based expressions, including intermediate steps\results, isn't is ? ;) Yes, we have CTE's which allows to reduce complexity of many queries, but it is not an universal solution. And our optimizer is not smart enough, unfortunately to make CTE's more powerful. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
15.03.2022 19:56, Adriano dos Santos Fernandes wrote: 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? 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. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
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. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
15.03.2022 19:07, Dmitry Yemanov 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. I'd just adapt their definitions to our realities. +1 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. It would be good to have ability to query schema for such objects, though. "Declared" LTTs are defined inside the PSQL routine, with data isolated the same way. In fact, what you priorly define as LT is IMO "declared" LTT. +1. And don't forget about packages. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
15.03.2022 18: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? Is it standard ? Could "collection" be joined with regular tables ? Isn't it is better to implement standard SET\ARRAY ? If there is strong demand to have Oracle's collection - could it be implemented later based on LT[T] ? Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
15.03.2022 16:14, Adriano dos Santos Fernandes wrote:
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.
Ok.
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 uses syntax "DECLARE LOCAL TEMPORARY TABLE ... [ ON
COMMIT PRESERVE/DELETE ROWS ].
"ON COMMIT" clause seems meaningless for LTT's DECLARED within PSQL routine,
while could be useful for packaged LTT's.
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.
LTs could also be added in packages. It's different feature than LTTs.
What is a difference ?
I propose syntax of LT:
DECLARE LOCAL TABLE (
{ }...
);
This statement should be used as part of common DECLARE section of PSQL
routine
or any place of package definition (but before usage), correct ?
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.
You mean - if without WHERE clause, correct ? What about undo ?
In general - how operations on LT[T] should be handled in case of PSQL
exceptions ?
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 ?
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.
I'd say indices (at least one) is a must. Probably we could allow only inplace
index definition (i.e. as part of DECLARE LOCAL TABLE only). But, of course, it
could be postponed for a while.
So far I see no need to introduce non-standard LT and would like to see LTT's.
But I, probably, not see whole picture.
Regards,
Vlad
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Knowing whether fbclient supports isc_dpb_utf8_filename
01.03.2022 14:15, Jiří Činčura wrote: Sorry for disappointing you, but they not gonna be any helpful, as they always return version "6.3" for InterBase compatibility ;-) Yeah. And looks like no function returns FB_MAJOR_VER. Hope somebody has a nice trick in sleeves. isc_get_client_version() returns string with FB_BUILD_SUFFIX as last part. It have value "Firebird 2.1" for FB 2.1, "Firebird 4.0" for FB 4 and so on. Hope it helps, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] INT64 and index keys
16.02.2022 11:45, Alex Peshkoff via Firebird-devel wrote:
On 2/15/22 18:20, Vlad Khorsun wrote:
I'd vote to remove idx_numeric2 in favour of idx_decimal
Appears to be good idea.
and look how to improve
Decimal128::makeIndexKey() performance. For example, it stores every 3 decimal
digits into 10 bits using relatively complex (computationally) algorithm with
shifts and multiplications.
To be precise - 9 digits into ULONG. No shifts (BTW what a problem with them, CPUs have appropriate fast support since first pentium
or even more). There is one division of small integer (range from 0 to 33). It can be easily replaced with table lookup but I'm not
sure is it worth doing or not - small numbers division if fast enough.
I refers to this piece of code:
// compress coeff - 3 decimal digits (999) per 10 bits (1023)
unsigned char* p = coeff;
for (ShiftTable* t = table; p < end; p += 3)
{
USHORT val = p[0] * 100 + p[1] * 10 + p[2];
fb_assert(val < 1000); // 1024, 10 bit
*k |= (val >> t->rshift);
++k;
*k = (val << t->lshift);
if (!t->lshift)
{
++k;
*k = 0;
t = table;
}
else
++t;
}
For Decimal34 use of packed BCD will cause growth of index key (+1 DWORD). I doubt that can make overall result better. May be we
can move 2 digits in the end of exponent's DWORD, in that case we do not loose in key size. But is that faster or slower compared
with current should be checked.
For Decimal16 I see no such problems - packed BCD can be used w/o problems.
Storing every digit in 4 bits will use 2 bits per 3
digits more but should save come CPU circles, I believe.
There is also extracting of BCD from internal decfloat representation. Also not
too fast process.
Yes, I have same concerns. We could extract packed BCD from decFloat, btw,
but it
seems as not very convenient for us (see decDoubleToPacked\decQuadToPacked):
pack receives DECDOUBLE_Pmax packed decimal digits (one digit per four-bit
nibble)
followed by a sign nibble and prefixed (for decDouble and decQuad only) with an
extra
pad nibble (which is 0).
Regards,
Vlad
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] INT64 and index keys
16.02.2022 11:19, Mark Rotteveel wrote: On 2022-02-16 09:56, Vlad Khorsun wrote: 16.02.2022 10:35, Mark Rotteveel wrote: On 2022-02-15 20:08, Vlad Khorsun wrote: 15.02.2022 20:32, Mark Rotteveel wrote: On 2022-02-15 16:20, Vlad Khorsun wrote: I'd vote to remove idx_numeric2 in favour of idx_decimal and look how to improve Decimal128::makeIndexKey() performance. For example, it stores every 3 decimal digits into 10 bits using relatively complex (computationally) algorithm with shifts and multiplications. Storing every digit in 4 bits will use 2 bits per 3 digits more but should save come CPU circles, I believe. But that is the storage format of Decimal128 (DECFLOAT(34)). No. It looks more like packed BCD. DECFLOAT uses a more complex internals, it is not BCD inside. Decimal128 uses densely packed BCD (with some oddities to encode sign, exponent and first digit). It is not BCD and definitely not what uses Decimal128::makeIndexKey(). See http://speleotrove.com/decimal/dbspec.html and http://speleotrove.com/decimal/DPDecimal.html Also, note, Decimal encodings is not directly sortable. From that first link under 'coefficient continuation': "Each 10-bit group represents three decimal digits, using Densely Packed Decimal encoding." Sure. And it is not BCD, nor packed BCD ;) and at the footnote (and the top of your second link): "Chen-Ho encoding is a lossless compression of three Binary Coded Decimal digits into 10 bits using an algorithm which can be applied or reversed using only simple Boolean operations". In any case - encoding used by Decimal128::makeIndexKey() is not the same encoding as used by Decimal internally. However, that was not my main point. My main point was that it sounds like an index format that was created for supporting DECFLOAT(34), and that it is not suitable for the full range of INT128 and NUMERIC/DECIMAL backed by INT128 (for the same reasons the DOUBLE PRECISION format is not suitable for BIGINT and NUMERIC/DECIMAL backed by BIGINT). And I never object it. Note, I offer to remove idx_numeric2 (used for INT64). Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] INT64 and index keys
16.02.2022 10:35, Mark Rotteveel wrote: On 2022-02-15 20:08, Vlad Khorsun wrote: 15.02.2022 20:32, Mark Rotteveel wrote: On 2022-02-15 16:20, Vlad Khorsun wrote: I'd vote to remove idx_numeric2 in favour of idx_decimal and look how to improve Decimal128::makeIndexKey() performance. For example, it stores every 3 decimal digits into 10 bits using relatively complex (computationally) algorithm with shifts and multiplications. Storing every digit in 4 bits will use 2 bits per 3 digits more but should save come CPU circles, I believe. But that is the storage format of Decimal128 (DECFLOAT(34)). No. It looks more like packed BCD. DECFLOAT uses a more complex internals, it is not BCD inside. Decimal128 uses densely packed BCD (with some oddities to encode sign, exponent and first digit). It is not BCD and definitely not what uses Decimal128::makeIndexKey(). See http://speleotrove.com/decimal/dbspec.html and http://speleotrove.com/decimal/DPDecimal.html Also, note, Decimal encodings is not directly sortable. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] INT64 and index keys
15.02.2022 20:32, Mark Rotteveel wrote: On 2022-02-15 16:20, Vlad Khorsun wrote: I'd vote to remove idx_numeric2 in favour of idx_decimal and look how to improve Decimal128::makeIndexKey() performance. For example, it stores every 3 decimal digits into 10 bits using relatively complex (computationally) algorithm with shifts and multiplications. Storing every digit in 4 bits will use 2 bits per 3 digits more but should save come CPU circles, I believe. But that is the storage format of Decimal128 (DECFLOAT(34)). No. It looks more like packed BCD. DECFLOAT uses a more complex internals, it is not BCD inside. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] INT64 and index keys
15.02.2022 13:28, Dmitry Yemanov wrote: All, Historically, we store most numerics as double precision inside index keys. It makes stored keys independent of the declared scale and allows both prefix and suffix compression. However, int64 keys (BIGINT and largish NUMERICs/DECIMALs) do not fit the double format without risk of rounding/truncation, so they're stored differently -- normalized to the maximum value that fits into double and then re-scaled. The problem is that this format is not well-compressable. ... Maybe we couldn't do any better before FB4, but now we have INT128 and DECFLOAT which use more effective index key format, so I asked myself whether it makes sense to utilize it for int64 values too. Below is the stats if BIGINT would be stored as idx_decimal in the index: T_BIGINT2 (134) Index I_BIGINT2_ID (0) Root page: 37193, depth: 3, leaf buckets: 739, nodes: 100 Average node length: 5.96, total dup: 0, max dup: 0 Average key length: 3.10, compression ratio: 1.59 Average prefix length: 3.87, average data length: 1.05 Index I_BIGINT2_COL (1) Root page: 37215, depth: 2, leaf buckets: 697, nodes: 100 Average node length: 5.61, total dup: 367845, max dup: 8 Average key length: 2.76, compression ratio: 1.79 Average prefix length: 4.23, average data length: 0.70 Storage surely looks good, but internal conversions to Decimal128 are not free. I've done a few artificial tests (index scan in the loop with many iterations, CPU bound as tables fit the page cache), here are the results: (1) Wide range scan: T_INT = 3.53s T_BIGINT = 3.82s T_INT128 = 4.36s T_BIGINT2 = 3.72s More or less what's expected: different number of pages are accessed, so T_BIGINT2 wins as compared to T_BIGINT. Interesting that T_INT128 has the same index size, but is noticeably slower. Due to a bigger computational overhead? (2) Narrow range scan: T_INT = 2.74s T_BIGINT = 2.79s T_INT128 = 2.83s T_BIGINT2 = 2.76s Almost the same results for both BIGINT compressions. (3) Unique scan: T_INT = 1.83s T_BIGINT = 1.89s T_INT128 = 2.09s T_BIGINT2 = 2.05s This is the worst case for T_BIGINT2, it loses ~10% to T_BIGINT. In cases (2) and (3) index size does not matter, only index depth matters. So the stats reflects CPU overhead of btr.cpp::compress() and data type conversions. It's also likely that if BIGINT is used as PK and currently its index is 4 levels depth, the new storage will shrink it down to 3 levels and this could noticeably improve both lookup and (especially) join performance. Does anyone think we should investigate this possibility more closely? Any other comments? I'd vote to remove idx_numeric2 in favour of idx_decimal and look how to improve Decimal128::makeIndexKey() performance. For example, it stores every 3 decimal digits into 10 bits using relatively complex (computationally) algorithm with shifts and multiplications. Storing every digit in 4 bits will use 2 bits per 3 digits more but should save come CPU circles, I believe. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] INT64 and index keys
15.02.2022 15:20, Dmitry Yemanov wrote: ... I can think of two ways to proceed: 1) Keep rebuilding the index every time the type is changed. Cast all column values into the new format before storing keys into the index. Forget about being scale-independent, pick the scale from the latest table format (and store BIGINTs or all exact numerics as integers without scale, as you suggest). 2) Prohibit decreasing the scale. Avoid rebuilding the indices unless really necessary (idx_itype is changed). Consider it a good thing (tm). Maybe there may be other options, I didn't think deeply about this. 2+) Rebuild index only if scale was decreased. Add optional clause at SQL syntax level to explicitly [dis]allow this. If scale was not decreased - leave index unchanged. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
10.02.2022 17:35, Dimitry Sibiryakov wrote: Vlad Khorsun wrote 10.02.2022 16:30: More, I already implemented simple cache of prepared statements near 5 years ago and I know when it is useful :) It was not ported into Firebird because of limited usage and its simplicity. But customer which uses it, was very happy. Isn't it inside of EXECUTE STATEMENT implementation? No. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
10.02.2022 14:56, Adriano dos Santos Fernandes wrote: 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. So, main benefit visible to end-user is to save prepare and check access time, correct ? Also, good written apps (that re-uses prepared statements) will not see much changes - at least until impl of shared metadata cache. I'm not against of caching statements by the engine, I just want to explore both sides of coin. More, I already implemented simple cache of prepared statements near 5 years ago and I know when it is useful :) It was not ported into Firebird because of limited usage and its simplicity. But customer which uses it, was very happy. 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... 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. 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 ? - 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. 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. ... 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. 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 ? Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
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. 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 ? Is it makes sense to have two caches - for internal and for user statements ? - 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 ? If yes, does it means that another attachment should use same client charset and active roles to be able to use cached compiled statement ? 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. Should it be enabled by default? I think yes. Yes for internal requests. Not sure about user requests. > And it may have per database configuration of the timeout > value. Sure. 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. Enough for start, IMHO. And you not explained cache usage - when and how cached statement should be used. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Database detach after shutdown
22.01.2022 14:42, Dimitry Sibiryakov wrote: Adriano dos Santos Fernandes wrote 22.01.2022 13:37: After calling IDispatch::shutdown() an attempt to call IAttachment::detach() in embedded mode returns error "Database shutdown". But... why? Isn't IDispatch::shutdown already causes all databases to be disconnected? In this case the second disconnect attempt should be successful no-op + release(), no?.. No. In this case there is a bug in application that will not be found. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Commit (un)certainity
05.01.2022 15:03, Dimitry Sibiryakov wrote: Hello All. It is documented that successful return from function send() for TCP doesn't mean successful delivery of data to the target host, mere put them into socket buffer. If op_commit is sent but network error appear during waiting for response there can be two cases: 1) op_commit packet is lost on its way to server; 2) op_response is lost on its way to client. In the first case the transaction on server is rolled back in the latter - committed successfully. Is there a way to handle such situation? 2PC protocol was developed for this kind of problems. Using of two round-trips cannot solve the problem, only shift point of uncertainty. 2PC allows to know trasaction state and fix half-done commit. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] UDR for reading server configuration for Firebird QA
13.12.2021 12:45, Dimitry Sibiryakov пишет: Vlad Khorsun wrote 13.12.2021 10:32: Our IFirebirdConf implementation is not generic enough to provide way to get value of any known setting as string. IMHO it is ok for such UDR to return only subset of known parameters converting them into strings by hand. Before make such useful suggestion, please, look at the code that was referred to. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] UDR for reading server configuration for Firebird QA
11.12.2021 23:01, Alex Peshkoff via Firebird-devel wrote: I see no big use in full support of v.3. Requested UDR is trivial but I highly displike requirement of keeping it in std distro of firebird. I see no problem to add such UDR into samples but, unfortunately, it can't be implemented without additional support by the engine. Our IFirebirdConf implementation is not generic enough to provide way to get value of any known setting as string. Also, it does not to distinguish server-global setting from database-specific. I refactored config code in v4 to support RDB$CONFIG. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Visual Studio version for v5
27.10.2021 21:48, Adriano dos Santos Fernandes wrote: Hi! Do we plan to update Visual Studio to 2019 for v5? I have no such a plan but see nothing wrong with it. If yes, will the VS 2017 build be still maintained? I suppose - yes. If yes for above question, would we create duplicate files as before? I see no problem with it. Especially if we'll support just two VS versions. Or is there a better way to make the same set of files easily usable both in VS 2017 and 2019? It highly depends on what is "usable". I don't know about "better" way as current one satisfy my needs as active developer and I don't look for something else. For my builds in VS 2019, I set WindowsTargetPlatformVersion just to "10.0" instead of a specific point release. Maybe this also works for VS 2017. There is no such setting in VS 2017. There is "TargetPlatform" (read-only) and "Windows SDK Version". The values of the latter one depends on "Platform Toolset". For PlatformToolset, is there a way to set it to some value that gets the "default" (v141 for VS 2017, v142 for VS 2019)? There is a problem with "Windows SDK Version" value in VS 2017, as different developers could have installed different versions of SDK and VS 2017 can't just use most\any "suitable". It was promised to be fixed in VS 2019. I didn't check it by myself. As for "PlatformToolset" - i don't know, but not see it as a problem if we support two set of project files. I'm testing the creation of a docker image for Firebird build, and it seems impossible to create a scripted install using a specific version of VS 2017. It always installs the latest version. Why it is a problem for us ? For VS 2019 it's possible to install any version downloading a specific vs_buildtools installer. Adriano PS: Soon there will also be VS 2022... I hope we will release FB5 before VS 2022 became stable. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Curly brackets in config files
14.10.2021 18:48, Dimitry Sibiryakov wrote:
Hello All,
Is there a way to workaround "illegal line" error if curly brackets are part
of database GUID used as a key name:
{F5B90BDE-6DA3-4095-A9E1-6D26AE34519F} = target.fdb
fbtrace.conf:
# To enter curvy brackets { } somewhere in a configuration dup them: {{ }}.
Hope it helps,
Vlad
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Tablespaces proposal
13.10.2021 15:44, Dimitry Sibiryakov wrote: Vlad Khorsun wrote 13.10.2021 14:39: Due to this, I see no reason to replicate physical layout of master database to the replica. I.e. TABLESPACE-related statements should not be replicated at all, IMO. Other opinions ? I fully agree with not replicated "CREATE/ALTER/DROP TABLESPACE" but what to do with "CREATE TABLE ... IN TABLESPACE"? I see two ways to handle it a) corresponding part of statement should be omitted when replicating, or b) on replica side it could be handled by looking for target tablespace by name and replacing it by another one (pre-defined by DBA), if not found. I.e. if on master we have TABLESPACE TS1 and on replica it is absent, table will be created in PRIMARY TABLESPACE, if other was not specified by DBA for replica. Of course, some way to specify that non-mathced TABLESPACE is necessary. Perhaps not at the initial version of code. We also might combine (a) and (b) by introducing some setting for replication on master side. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Tablespaces proposal
13.10.2021 13:14, Dimitry Sibiryakov wrote: About moving of tablespaces and objects between tablespaces: on primary site it succeeded, on synchronous replica it failed (and vice versa). How such situation is going to be handled? Tablespace is a concept about physical layout of data. It is closely bound to the database host. Due to this, I see no reason to replicate physical layout of master database to the replica. I.e. TABLESPACE-related statements should not be replicated at all, IMO. Other opinions ? Regards, Vlad PS does anyone know how it is handled by other DBMS ? Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Tablespaces proposal
12.10.2021 13:57, Kjell Rilbe wrote:
Den 2021-10-12 kl. 08:09, skrev Roman Simakov:
пн, 11 окт. 2021 г. в 23:03, Vlad Khorsun mailto:hv...@optima.com.ua>>:
11.10.2021 21:23, Roman Simakov wrote:
> пн, 11 окт. 2021 г. в 18:42, Vlad Khorsun mailto:hv...@optima.com.ua> <mailto:hv...@optima.com.ua
<mailto:hv...@optima.com.ua>>>:
>
> 11.10.2021 15:17, Roman Simakov wrote:
> > SYNTAX
> > ===
> >
> > Note: *MAIN* - is a name of the basic database file.
>
> Please, use *DEFAULT* for default (main) tablespace at
"main" database file.
> It is much more consistent with SQL and allows to avoid new
unnecessary keyword.
>
>
> I'd be happy to agree. Actually we took a look at Oracle syntax.
The fact is that DEFAULT means different things. For example,
> DEFAULT tablespace for indices is the tablespace of its table.
That's why DEFAULT is not such an obvious name as we want it to be.
This is matter of documentation, IMHO. BTW, why you don't like
ORACLE's way ?
It looks logical for me. If you want to avoid ambiguity we could
introduce
special syntax for the table's sub-objects (blob fields, indices,
constraints),
say use keyword TABLE or PARENT as tablespace name, for ex:
CREATE INDEX … AT TABLESPACE { | DEFAULT | TABLE}, or
more natural
CREATE INDEX … AT DEFAULT | TABLE TABLESPACE
CREATE INDEX … AT TABLESPACE
I had such an idea but didn't want to make up our own way.
If we go Oracle way and use DEFAULT we won't be able to move index data to the main database for indices for a table at;) a
tablespace. I.e. we can move either to a named tablespace or to a default (table's) tablespace.
It seems Oracle uses the name SYSTEM for the main database. Do you like it? Anyway the main database tablespace has to have a
name. The question is what name?
MAIN
PRIMARY
SYSTEM
DATABASE TABLESPACE
DATABASE
but definitely it could not be DEFAULT because DEFAULT meaning depends on the
context.
I think PRIMARY is good because it's already a reserved word and has an
appropriate meaning.
Ok, seems we have wide agreement on it.
// let me use AT until we agreed to use IN ;)
I'd like to get an answer from native speakers, but I think it's like a
database or file (in a database, in a file).
I'm not a native speaker but I consider myself to be pretty good at English,
and I'm pretty sure IN is the best word here.
Let it be IN then, thanks.
Regards,
Vlad
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Tablespaces proposal
12.10.2021 17:53, Roman Simakov wrote: вт, 12 окт. 2021 г. в 13:11, Vlad Khorsun : 12.10.2021 9:09, Roman Simakov wrote: пн, 11 окт. 2021 г. в 23:03, Vlad Khorsun mailto:hv...@optima.com.ua>>: ... > But MAIN exactly specifies the database itself. We especially have removed DEFAULT from the new version of the proposal because it's > better to explicitly require a tablespace name in the beginning. Later we can add defaults. I hope you don't require to use TABLESPACE clause every time ? If yes, you should define defaults anyway ;) Definitely not. Hmm... when object is creating and tablespace was not specified, we must use something (by default). Obvious choice is to use 'SYSTEM' tablespace, correct ? For tablespace yes. For tables, perhaps ? For indices the default tablespace is a tablespace of its table. Sure. I meant tables (and other "independent" objects, if any). ... After DY's statement re. tablespace per partition, we should consider ability to create much more tablespaces. I see no problem with increasing the limit. I see problems with reducing it (someone may use them). So let's start from a small number 63. When we implement partitions we increase it more consciously. I speak about data type used in ODS for tablespace ID. It seems INT should be used, not SMALLINT. You suggest extending it in the PR or we can put it off? In the PR. It costs nothing but allows to avoid additional ODS changes. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Tablespaces proposal
12.10.2021 12:02, Mark Rotteveel wrote: On 11-10-2021 17:41, Vlad Khorsun wrote: 11.10.2021 15:17, Roman Simakov wrote: Note: *MAIN* - is a name of the basic database file. Please, use *DEFAULT* for default (main) tablespace at "main" database file. It is much more consistent with SQL and allows to avoid new unnecessary keyword. Technically, I think MAIN doesn't have to be a keyword, it can be the object name of the tablespace that is the first database file. Agree. I just don't like word MAIN for this purpose :) It will require some extra handling though, because you can't alter MAIN opposed to other tablespaces. Sure. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Tablespaces proposal
12.10.2021 9:09, Roman Simakov wrote:
пн, 11 окт. 2021 г. в 23:03, Vlad Khorsun mailto:hv...@optima.com.ua>>:
11.10.2021 21:23, Roman Simakov wrote:
> пн, 11 окт. 2021 г. в 18:42, Vlad Khorsun mailto:hv...@optima.com.ua> <mailto:hv...@optima.com.ua
<mailto:hv...@optima.com.ua>>>:
>
> 11.10.2021 15:17, Roman Simakov wrote:
> > SYNTAX
> > ===
> >
> > Note: *MAIN* - is a name of the basic database file.
>
> Please, use *DEFAULT* for default (main) tablespace at "main"
database file.
> It is much more consistent with SQL and allows to avoid new
unnecessary keyword.
>
>
> I'd be happy to agree. Actually we took a look at Oracle syntax. The
fact is that DEFAULT means different things. For example,
> DEFAULT tablespace for indices is the tablespace of its table. That's
why DEFAULT is not such an obvious name as we want it
to be.
This is matter of documentation, IMHO. BTW, why you don't like ORACLE's
way ?
It looks logical for me. If you want to avoid ambiguity we could introduce
special syntax for the table's sub-objects (blob fields, indices,
constraints),
say use keyword TABLE or PARENT as tablespace name, for ex:
CREATE INDEX … AT TABLESPACE { | DEFAULT | TABLE}, or more
natural
CREATE INDEX … AT DEFAULT | TABLE TABLESPACE
CREATE INDEX … AT TABLESPACE
I had such an idea but didn't want to make up our own way.
If we go Oracle way and use DEFAULT we won't be able to move index data to the main database for indices for a table at;) a
tablespace. I.e. we can move either to a named tablespace or to a default (table's) tablespace.
Now I understand you better, thanks. But I still against word MAIN :)
It seems Oracle uses the name SYSTEM for the main database. Do you like it? Anyway the main database tablespace has to have a name.
'SYSTEM' is good choice. All system relations is here. So, engine will always
create
tablespace with name 'SYSTEM', and put all system relations and TIP here,
correct ?
'SYSTEM' tablespace can't be renamed and could (should?) be marked as system
one.
The question is what name?
MAIN
PRIMARY
SYSTEM
DATABASE TABLESPACE
DATABASE
SYSTEM (best) or PRIMARY, imho.
but definitely it could not be DEFAULT because DEFAULT meaning depends on the
context.
Ok.
in this case, when table's table space is changed, all dependent object
should
be changed accordingly
What do you mean saying "changed"? Now we explicitly set the tablespace name for an index and when a table is moving leave the index
where it was. So subobjects are not bind to the parent. So does Oracle. Do you suggest moving all dependent objects implicitly? So
the question is to bind or not to bind?
Yes. I assumed sub-objects placed in the same tablespace as object itself
should be
moved all together (i.e. bound). But now I think there should be option to
[not]move
sub-objects when object moved into new tablespace.
// let me use AT until we agreed to use IN ;)
I'd like to get an answer from native speakers, but I think it's like a
database or file (in a database, in a file).
No problem :)
> But MAIN exactly specifies the database itself. We especially have
removed DEFAULT from the new version of the proposal
because it's
> better to explicitly require a tablespace name in the beginning. Later
we can add defaults.
I hope you don't require to use TABLESPACE clause every time ? If yes,
you
should define defaults anyway ;)
Definitely not.
Hmm... when object is creating and tablespace was not specified, we must use
something
(by default). Obvious choice is to use 'SYSTEM' tablespace, correct ?
The point is that we cannot use DEFAULT as a name for the main database. If so I decided not to introduce DEFAULT
keyword at all. We can add it when we understand how it works and what defaults are useful.
Ok. So, we should remove all mentions of MAIN in your next version of
proposal, correct ?
If one need to place\move object into main database file (tablespace) name
'SYSTEM' should
be used explicitly (so far).
...
After DY's statement re. tablespace per partition, we should consider
ability to create much more tablespaces.
I see no problem with increasing the limit. I see problems with reducing it (someone may use them). So let's start from a small
number 63. When we implement partitions we increase it more consciously.
I speak about data type used in ODS for tablespace ID. It seems INT should be
used,
not SMALLINT.
Regards,
Vlad
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Tablespaces proposal
11.10.2021 21:23, Roman Simakov wrote:
пн, 11 окт. 2021 г. в 18:42, Vlad Khorsun mailto:hv...@optima.com.ua>>:
11.10.2021 15:17, Roman Simakov wrote:
> SYNTAX
> ===
>
> Note: *MAIN* - is a name of the basic database file.
Please, use *DEFAULT* for default (main) tablespace at "main" database
file.
It is much more consistent with SQL and allows to avoid new unnecessary
keyword.
I'd be happy to agree. Actually we took a look at Oracle syntax. The fact is that DEFAULT means different things. For example,
DEFAULT tablespace for indices is the tablespace of its table. That's why DEFAULT is not such an obvious name as we want it to be.
This is matter of documentation, IMHO. BTW, why you don't like ORACLE's way ?
It looks logical for me. If you want to avoid ambiguity we could introduce
special syntax for the table's sub-objects (blob fields, indices, constraints),
say use keyword TABLE or PARENT as tablespace name, for ex:
CREATE INDEX … AT TABLESPACE { | DEFAULT | TABLE}, or more natural
CREATE INDEX … AT DEFAULT | TABLE TABLESPACE
CREATE INDEX … AT TABLESPACE
in this case, when table's table space is changed, all dependent object should
be changed accordingly
// let me use AT until we agreed to use IN ;)
But MAIN exactly specifies the database itself. We especially have removed DEFAULT from the new version of the proposal because it's
better to explicitly require a tablespace name in the beginning. Later we can add defaults.
I hope you don't require to use TABLESPACE clause every time ? If yes, you
should define defaults anyway ;)
But if you have a good idea how to resolve this issue we will be happy to use
it in the proposal.
See above.
> 1. *CREATE TABLESPACE FILE '/path/to/file'*
>
> 2. *ALTER TABLESPACE FILE '/path/to/file'*
In DDL, ALTER usually combined with ADD | SET | DROP, so let follow this
convention. I.e. ALTER TABLESPACE SET FILE '/path/to/file'
Oracle syntax:
ALTER TABLESPACE users
RENAME DATAFILE '/u02/oracle/rbdb1/user1.dbf',
'/u02/oracle/rbdb1/user2.dbf'
TO '/u02/oracle/rbdb1/users01.dbf',
'/u02/oracle/rbdb1/users02.dbf';
I don't like it and we have only one data file actually and have no preferences
here. We can make [SET] optional.
ALTER somethings SET property TO value - looks as natural way to speak.
Yes, we may introduce RENAME and so on for every property of every altering
object, but it looks too noisy for me.
> 4. CREATE TABLE ...
> (
> ...
> FIELD TYPE CONSTRAINT ... USING INDEX ... *TABLESPACE { |
MAIN}* -- field constraint tablespace
> ...
> CONSTRAINT ... USING INDEX ... *TABLESPACE { | MAIN}* -- table
constraint tablespace
> ...
> )
> *TABLESPACE *
Should we consider optional AT before TABLESPACE in all CREATE
[AT] TABLESPACE
statements ? And, perhaps, optional TO in ALTER SET TABLESPACE
[TO] .
Below I'll use both AT and TO, but not insist on it.
Oracle doesn't use prepositions here.
I already said that I'm not insist on it. BTW, in your case for (3) ORACLE
uses RENAME ... TO :)
...
> SOME DETAILS
> =
...
> It's possible to create up to 253 tablespaces.
Two ID's is reserved for DEFAULT and TEMPORARY tablespaces, correct ?
Currently it's like
const USHORT DB_PAGE_SPACE = 1;
// .. here all tablespace IDs. Keep TRANS_PAGE_SPACE rightafter DB_PAGE_SPACE
const USHORT TRANS_PAGE_SPACE = 255; // is not used for tablespace id
const USHORT TEMP_PAGE_SPACE= 256;
This code was no relation with ODS. And it could be changed as required.
I would reserve some more ID's for future system usage. I don't see it as
limitation for end users.
I see no problem with it. At least for the first version we can use 63
tablespaces for example (64 including the main database) .
We add another constant for the border of the available IDs.
After DY's statement re. tablespace per partition, we should consider
ability to create much more tablespaces.
Regards,
Vlad
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Tablespaces proposal
11.10.2021 19:24, Dmitry Yemanov wrote:
11.10.2021 18:41, Vlad Khorsun wrote:
2. *ALTER TABLESPACE FILE '/path/to/file'*
In DDL, ALTER usually combined with ADD | SET | DROP, so let follow this
convention. I.e. ALTER TABLESPACE SET FILE '/path/to/file'
I'm not so sure about "usually", e.g. ALTER INDEX INACTIVE, ALTER DOMAIN TYPE X, etc. As for me, SET is better for cases where DROP
may also be applied. But in fact we have both kinds of SET usage in the grammar and I cannot say which is better.
I said about regular usage, when some property of object is changed.
Of course there is some special cases with special syntax. Many of them
was introduced long time ago, btw ;)
Should we consider optional AT before TABLESPACE in all CREATE [AT]
TABLESPACE statements ?
I'd rather consider IN instead of AT, but also not insisting.
Question for native speaker ;)
5. ALTER TABLE *ALTER TABLESPACE { | MAIN}*
Data of the table will be moved to the specified tablespace or the main
database.
This statement not alters tablespace itself, i.e. there should be SET (and
DEFAULT):
ALTER TABLE SET TABLESPACE [TO] | DEFAULT
+1
ALTER TABLE ALTER COLUMN ... SET TABLESPACE [TO] { | DEFAULT}
Do you mean blobs or vertical partitioning here?
Blobs, as discussed earlier. I'm not consider partitioning, so far.
It's possible to create up to 253 tablespaces.
Two ID's is reserved for DEFAULT and TEMPORARY tablespaces, correct ?
I would reserve some more ID's for future system usage. I don't see it as
limitation for end users.
For regular tablespaces (created explicitly) - sure. But if we think about automatically created partitions, even 253 tablespaces
may become a sad limit.
I see no reason to create tablespace for every partition, but if we going to
support such scenario, we should use larger type for tablespace ID, of course.
nbackup support is postponed.
At this stage I agree, but this must be 1st goal after initial
implementation, IMO.
The first PR may come without it, but I'm against releasing it until nbackup is
supported.
Sure.
Page size is identical for every tablespace and the main database.
Support for many page sizes requires changes in page cache management and should
be considered together. I don't see it as "must have" feature, btw.
Me neither.
Good.
Regards,
Vlad
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Tablespaces proposal
11.10.2021 15:17, Roman Simakov wrote:
Here is the second version of the proposal. It's taken into account all agreements we made during discussion and we'll do it in this
way if there are no objections.
PROPOSAL==
GOALS
==
1) Extend the current limits on database size
2) Keep non active parts of a database on slow disks (having big volume)
3) Split indices from the database
etc
SYNTAX
===
Note: *MAIN* - is a name of the basic database file.
Please, use *DEFAULT* for default (main) tablespace at "main" database file.
It is much more consistent with SQL and allows to avoid new unnecessary keyword.
1. *CREATE TABLESPACE FILE '/path/to/file'*
2. *ALTER TABLESPACE FILE '/path/to/file'*
In DDL, ALTER usually combined with ADD | SET | DROP, so let follow this
convention. I.e. ALTER TABLESPACE SET FILE '/path/to/file'
FILE can contain either an absolute path or a relative to the main database
file.
3. *DROP TABLESPACE [INCLUDING CONTENTS]*
If the tablespace contains some database objects the behaviour depends
on INCLUDING CONTENTS clause. if it is specified all database objects
in the tablespace will be dropped as well. Otherwise there will be an
error.
4. CREATE TABLE ...
(
...
FIELD TYPE CONSTRAINT ... USING INDEX ... *TABLESPACE { | MAIN}* --
field constraint tablespace
...
CONSTRAINT ... USING INDEX ... *TABLESPACE { | MAIN}* -- table
constraint tablespace
...
)
*TABLESPACE *
Should we consider optional AT before TABLESPACE in all CREATE [AT]
TABLESPACE
statements ? And, perhaps, optional TO in ALTER SET TABLESPACE [TO]
.
Below I'll use both AT and TO, but not insist on it.
if the tablespace name is not specified *MAIN* will be used as default
tablespace for the table data.
DEFAULT, not MAIN, please.
A constraint will use the tablespace of the table if *TABLESPACE* is omitted.
5. ALTER TABLE *ALTER TABLESPACE { | MAIN}*
Data of the table will be moved to the specified tablespace or the main
database.
This statement not alters tablespace itself, i.e. there should be SET (and
DEFAULT):
ALTER TABLE SET TABLESPACE [TO] | DEFAULT
6. CREATE INDEX … *TABLESPACE { | MAIN}*
The index will be created in the specified tablespace or the main database.
If tablespace is omitted the index will be created in the tablespace of the
table.
CREATE INDEX … [AT] TABLESPACE { | DEFAULT}
7. ALTER INDEX *ALTER TABLESPACE { | MAIN}*
Data of the index will be moved to the specified tablespace or the main
database.
ALTER INDEX SET TABLESPACE [TO] { | DEFAULT}
It seems we missed
ALTER TABLE ADD CONSTRAINT ... [AT] TABLESPACE { | DEFAULT}, and
ALTER TABLE ALTER COLUMN ... SET TABLESPACE [TO] { | DEFAULT}
ODS CHANGES
=
A new table RDB$TABLESPACES:
RDB$TABLESPACE_ID - SMALLINT
RDB$TABLESPACE_NAME - CHAR (63)
RDB$SECURITY_CLASS - CHAR (63)
RDB$DESCRIPTION - BLOB SUBTYPE TEXT SEGMENT SIZE 80
RDB$OWNER_NAME - CHAR (63)
RDB$FILE_NAME - VARCHAR (255)
A new field in RDB$INDICES:
RDB$TABLESPACE_NAME - CHAR (63)
A new field in RDB$RELATION_FIELDS:
RDB$TABLESPACE_NAME - CHAR (63)
New fields in RDB$RELATIONS:
RDB$TABLESPACE_NAME - CHAR (63)
RDB$POINTER_PAGE - INTEGER
RDB$ROOT_PAGE - INTEGER
Add page space id to page number in ods.h:index_root_page.
UTILITIES
Logical backup
gbak -b works as usual for now. It gets data from a database
transparently working with tablespaces.
Logical restore
gbak -c -TABLESPACE_MAP(PING_FILE) /path/to/file/map/tablespaces
The option TABLESPACE_MAP(PING_FILE) specifies a path to the file
which maps tablespace names on file names. For example,
TS1 /path/to/tablespace1
TS2 /path/to/tablespace2
or directly in the command line
gbak -c -ts =/path/to/tablespace1 -ts =...
It allows you to restore tablespace contents to new places.
If the option is not specified gbak will use old locations for every tablespace.
The initial implementation requires explicitly specifying all tablespace files. Later we can add merging tablespaces or default
original paths.
SOME DETAILS
=
pag_header in every tablespace is reserved and may be replaced by a
new page type.
You mean page zero, which is currently always pag_header. I see no reason
to change this, so far. Header page uses to describe properties of database
and could be extended to describe pagespace when necessary.
pag_scns and pag_pip are located in every tablespace
pag_root is located in the tablespace where a table is located
It's possible to create up to 253 tablespaces.
Two ID's is reserved for DEFAULT and TEMPORARY tablespaces, correct ?
I would reserve some more ID's for future system usage. I don't see it as
limitation for end users.
nbackup support is postponed.
At this stage I agree, but this must be 1st goal after initial
implementation, IMO.
Location of BLOB fields is postponed.
Less import
Re: [Firebird-devel] Unhandled exception in Why
27.09.2021 12:20, Jiří Činčura wrote: I'm almost sure it is it. Could you confirm database have expression index which depends on PSQL function that uses UDR ? As far as I can tell there isn't any such index. Looking closer - index expression uses some table that have computed field, that uses PSQL function, that uses UDR. This table loaded into metadata cache and engine loaded all its direct dependencies. If you wish, I could prepare build with this patch for you. That would be great. I'll do my best to test it ASAP. Sent off-list Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Unhandled exception in Why
27.09.2021 10:33, Jiří Činčura wrote: You may, if you wish. It should fix the crash but there is another (less visible) issues. See discussion at https://github.com/FirebirdSQL/firebird/pull/6844. Sadly the artifacts on GitHub are expired. Is there a way to confirm I'm hitting same issue? Would memory dump help? I'm almost sure it is it. Could you confirm database have expression index which depends on PSQL function that uses UDR ? If you wish, I could prepare build with this patch for you. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Unhandled exception in Why
26.09.2021 18:16, Jiří Činčura wrote: Yes, that looks very similar. Is it worth trying the build with https://github.com/FirebirdSQL/firebird/commit/2b0dc05f529c13a0dd070c9fde6d048bc7fdccf4 commit? You may, if you wish. It should fix the crash but there is another (less visible) issues. See discussion at https://github.com/FirebirdSQL/firebird/pull/6844. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Unhandled exception in Why
26.09.2021 17:05, Jiří Činčura пишет: Hi, I can, although in pretty elaborate setup, trigger unhandled exception in Why (see below). It's 99% related to something that external engine is doing, because removing one external engine function call from PSQL function makes it not crash. What would be a best way to pin point what's happening? Or at least some pointers what to look for? Looks like https://github.com/FirebirdSQL/firebird/issues/6843 In this case Garbage Collector handle some expression index that call P-SQL Function that call External Function. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird 4 - MON$ / Trace API changes?
13.09.2021 16:59, Maya Opperman wrote:
Another possibility is to set coercion rules for new data type for your
connection, see
https://firebirdsql.org/file/documentation/release_notes/html/en/4_0/rlsnotes40.html#rnfb4-msql-set-bind-native-to-legacy-coercion-rules
Thanks Vlad! In this example from this link:
SET BIND OF DECFLOAT TO DOUBLE PRECISION;
SELECT CAST('123.45' AS DECFLOAT(16)) FROM RDB$DATABASE;--double
Does this need to be done within every transaction that is started, or per
connection/session, or once off per database?
The coercion rule (binding) become active immediately after corresponding
statement (SET BIND OF) is executed and stay active until disconnect or new
"SET BIND OF" is executed for the same data type. The scope of the rule is
all subsequent statements in the connection. Other connections are not
affected.
See also common description of session management statement at the start
of the same chapter,
Hope it helps,
Vlad
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird 4 - MON$ / Trace API changes?
Hello Thomas, 13.09.2021 11:43, Thomas Steinmaurer wrote: Hello, congratulations to the Firebird 4 release! How do I best find out what has been changed in the area of MON$ tables and especially Firebird Trace API. MON$ tables get a bit of mentioning in the Release Notes, e.g. what fields have been added etc. resp. I could extract the DDL for all MON$ tables and textually compare it the output for Firebird 3 MON$ tables. What might have been added in the Firebird 4 Trace API interesting for our FB TraceManager product? Any new trace event types (aka EXECUTE_STATEMENT_FINISH etc ...). Would thi be covered in the Release Notes? Sure. There was not mush changes in Trace visible to the end users: - new system privilege to trace attachments of other users - session management statements now traced - trace record for COMMIT/ROLLBACK RETAINING was extended to show old/new transaction IDs Some other features that developed before release was backported into v3 so it is not new in v4. And, of course, some bugs was fixed. Many thanks. This helps. It seems MON$ tables are now using the timestamp with time zone data type. Yes, look also for changes of CURRENT_TIME[STAMP] To do a simple "SELECT * ..." query on e.g. MON$DATABASE, I guess either the client library needs to support the new data type or I need to explicitly CAST to TIMESTAMP each affected column, right? Another possibility is to set coercion rules for new data type for your connection, see https://firebirdsql.org/file/documentation/release_notes/html/en/4_0/rlsnotes40.html#rnfb4-msql-set-bind-native-to-legacy-coercion-rules and https://firebirdsql.org/file/documentation/release_notes/html/en/4_0/rlsnotes40.html#rnfb40-compat Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird 4 - MON$ / Trace API changes?
13.09.2021 11:43, Thomas Steinmaurer wrote: Hello, congratulations to the Firebird 4 release! How do I best find out what has been changed in the area of MON$ tables and especially Firebird Trace API. MON$ tables get a bit of mentioning in the Release Notes, e.g. what fields have been added etc. resp. I could extract the DDL for all MON$ tables and textually compare it the output for Firebird 3 MON$ tables. What might have been added in the Firebird 4 Trace API interesting for our FB TraceManager product? Any new trace event types (aka EXECUTE_STATEMENT_FINISH etc ...). Would thi be covered in the Release Notes? Sure. There was not mush changes in Trace visible to the end users: - new system privilege to trace attachments of other users - session management statements now traced - trace record for COMMIT/ROLLBACK RETAINING was extended to show old/new transaction IDs Some other features that developed before release was backported into v3 so it is not new in v4. And, of course, some bugs was fixed. Again, congrats to Firebird 4. With native replication and others, seems like a really cool new major release. Thanks, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Timezone in fb_info_creation_timestamp_tz
09.09.2021 12:36, Jiří Činčura wrote: I'm trying to read response for `fb_info_creation_timestamp_tz`. Timezone piece. But I'm getting some weird data. The whole buffer is `139, 12, 0, 8, 232, 0, 0, 10, 71, 173, 16, 216, 103, 147, 0, 1`, thus if I'm counting correctly the `216, 103, 147, 0` should be timezone data, which is weird. Time zone should be at bytes 8 - 11 (counting from zero), i.e. '71, 173, 16, 216', AFAIU I think: 139 = fb_info_creation_timestamp_tz 12, 0 = length 12 8, 232, 0, 0 = 59400 = 05.07.2021 10, 71, 173, 16 = 279791370 = 07:46:19.137 And the time zone and 1 as end. Yes, you are correct of course. I thought you show "data" part of the response, not whole response :) The code you mention is buggy: The buffer you got contains garbage, if I'm right. OK, I'll create an issue. Please, do Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Timezone in fb_info_creation_timestamp_tz
09.09.2021 11:51, Jiří Činčura wrote: Hi, I'm trying to read response for `fb_info_creation_timestamp_tz`. Timezone piece. But I'm getting some weird data. The whole buffer is `139, 12, 0, 8, 232, 0, 0, 10, 71, 173, 16, 216, 103, 147, 0, 1`, thus if I'm counting correctly the `216, 103, 147, 0` should be timezone data, which is weird. Time zone should be at bytes 8 - 11 (counting from zero), i.e. '71, 173, 16, 216', AFAIU The `dbb->dbb_creation_date.time_zone` from [inf.cpp](https://github.com/FirebirdSQL/firebird/blob/master/src/jrd/inf.cpp#L557) is `ISC_USHORT`, hence `USHORT`, hence `unsigned short`. The `INF_convert` would use `put_vax_long` and eventually `memcpy(p, &value, sizeof(SLONG));`. The code you mention is buggy: case fb_info_creation_timestamp_tz: length = INF_convert(dbb->dbb_creation_date.utc_timestamp.timestamp_date, p); p += length; length += INF_convert(dbb->dbb_creation_date.utc_timestamp.timestamp_time, p); p += length; length += INF_convert(dbb->dbb_creation_date.time_zone, p); break; Second "p += length" is wrong as "length" here is not a length of just added value (timestamp_time) but sum of length of both added values (timestamp_date and timestamp_time). Thus, correct "time_zone" bytes (8-11) contains some garbage. It should be something like: case fb_info_creation_timestamp_tz: length = INF_convert(dbb->dbb_creation_date.utc_timestamp.timestamp_date, p); length += INF_convert(dbb->dbb_creation_date.utc_timestamp.timestamp_time, p + length); length += INF_convert(dbb->dbb_creation_date.time_zone, p + length); p += length; break; > > But how can `ISC_USHORT` end up in `216, 103, 147, 0`? The buffer you got contains garbage, if I'm right. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Dialect 3 inconsistent round/trunc - configurable calculation method needed
31.08.2021 23:53, Mark Rotteveel wrote: The only debatable feature of dialect 3 division is the fact the calculation stops (equivalent to floor rounding), while reduction of scale through assignment or cast applies half-up rounding, on the other hand, this behaviour is consistent with integer division, otherwise NUMERIC(18,0) division and BIGINT division would have to behave differently (e.g. 14/3 = 5 for NUMERIC(18,0) vs = 4 for BIGINT), which would be confusing as hell, though technically you could solve that by applying the "The precision and scale of the result of division are implementation-defined." (there is no requirement that BIGINT/BIGINT is BIGINT or NUMERIC/DECIMAL with a scale of 0). Below is comment from code ArithmeticNode::divide2() at src\dsql\ExprNodes.cpp: * In the SQL standard, the precision and scale of the quotient of exact * numeric dividend and divisor are implementation-defined: we have defined * the precision as 18 (in other words, an SINT64), and the scale as the * sum of the scales of the two operands. To make this work, we have to * multiply by pow(10, -2* (scale of divisor)). ... * To maximize the amount of information in the result, we scale up * the dividend as far as we can without causing overflow, then we perform * the division, then do any additional required scaling. So, 127.13 / 3.4618 evaluates in a following way: 127.13 is NUMERIC(5, 2) with value 12713 and scale -2 3.4618 is NUMERIC(5, 4) with value 34618 and scale -4 12713 * 10^8 / 34618 = 36723669 (integer division, no rounding) Result is NUMERIC(18, 6) with value 36723669 and scale -6, or 36.723669 Hope it helps, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] isc_info_sql_stmt_type/isc_info_sql_stmt_flags
19.08.2021 17:57, Adriano dos Santos Fernandes wrote: On 19/08/2021 11:43, Dmitry Yemanov wrote: Perhaps, although I'm not sure why the client application would need to know the statement type at all ;-) Before isc_info_sql_stmt_flags, I think that was the way to know if there is cursor or not. Correct. Therefore we should continue to support this "feature". I.e. any statement with resultset should return isc_info_sql_stmt_select. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Trace session and choice of events
13.08.2021 19:02, Dimitry Sibiryakov wrote: Hello All, As I see in TraceManager code every trace session is receiving not only events it is subscribed for with trace_needs() but also everything that previous sessions were subscribed for. For me it looks like a rather strange design. What is the purpose of that? I'm not an original author of the trace subsystem, so below is just how I remember and understand original impl and my own conclusions about it. At initial impl there was nor interfaces nor objects, just plain C. Trace plugin fills vector with implemented "hooks entrypoints" and passes it to the engine. I.e. plugin could set some entrypoint to NULL and engine should not pass corresponding events into such plugin instance. But this was never used - trace plugin (the only one existing) implements all known entrypoints. Maybe it was planned to be used in next versions of C-style plugin when engine and plugin knows different set of entrypoints. But this compatibility task is solved by another way in Firebird plugin API. Currently, bitmask "trace_needs" always have all bits set and useless. Yes, it could be used as kind of optimization to allow engine to not pass unneeded events into given trace plugin instance. It requires to move trace_needs() from ITraceFactory to ITracePlugin, btw. TraceManager also requires some changes. Regards, Vlad PS it was expected that such questions arise few years ago when Trace API was introduced, but there was no interest from 3rd party devs, unfortunately. Thus, currently we have what we have. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Windows installer and ICU files
14.08.2021 15:54, Carlos H. Cantu wrote: Hi! I see Firebird 4.0 Windows Installer does not copy the ICU dlls when doing a client+tools or minimum client install. Speaking with Adriano, it seems that having tzdata files are not enough to assure that all time zones conversions will be handled correctly. For that, ICU dlls are needed too. IIRC, ICU needed to fbclient only to correctly display tz names. I.e. not for conversions or something really important. If client app requires ICU for own needs, it is not our deal. So, I think installer should install them during client installs. Comments? Only by explicit user request, off by default. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local temporary tables (internals)
29.07.2021 22:06, Adriano dos Santos Fernandes wrote: Hi! As discussed in #6815 and have atomic RETURNING (records are first changed, then returned) I'd want to implement local temporary tables, initially only in BLR level. I don't want to discuss user visible complete local temporary tables feature here, that will make #6815 stuck. It will be necessary to add: blr_dcl_local_table - declares a local table. It will have a blr version for future improvements, an id and list of fields and they types. blr_local_table_id - references a local table by its id blr_local_table_truncate - truncate a local table blr_local_table_id should be supported at least in blr_rse and blr_store* Hmm... LTT is good to have, but... is it necessary here ? Is it possible to use RecordBuffer (and some kind of BufferedStream) for temporary storage ? Data will be placed in temp space. Sure. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Thread about Jaybird / Firebird Embedded crash on firebird-java
23.07.2021 10:50, Mark Rotteveel wrote: Hi, Last week a question was asked on firebird-java regarding a JVM crash on Linux when Firebird Embedded is used in combination with a UDF, but to be honest, I have no clue what to look for. I have been able to reproduce the crash (or possibly even two different crashes) with the application and basic UDF provided by the OP. Can someone look at https://groups.google.com/g/firebird-java/c/22G7zPGNnkY? Since you are able to reproduce the crash - could you provide us with stack backtrace ? Also, I saw no mention of messages in firebird.log - could you check it also ? Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Retrieving statement BLR for debug and test
16.07.2021 15:22, Dimitry Sibiryakov wrote: 16.07.2021 14:19, Adriano dos Santos Fernandes wrote: Instead of isc_info_sql_blr_bytes/isc_info_sql_blr_text, we can add single top level info code with sub code for raw/blr / dsql/jrd parse trees. Info request cannot have subcodes, it'll break its design. Learn how isc_info_sql_describe_vars work. Regards. Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Retrieving statement BLR for debug and test
16.07.2021 13:03, Dimitry Sibiryakov wrote: 16.07.2021 11:52, Vlad Khorsun wrote: We need a way to know when application uses BLR API directly. Direct BLR compilation won't have DSQL prepare event with the same statement ID. Isn't it enough for this topic's purpose?.. There are another purposes. Security is most important. I.e. trace_blr_compile is very different from trace_generated_blr from securty POV (at least). But sent data is exactly the same so what's the difference? Difference is huge - origin of event. Some apps could try to hide suspicious operations from DB audit using BLR API. Currently it could be tracked using trace_blr_XXX events. Usually these events is absent or very rare. If we start to generate such events for every DSQL statement it will make task of auditor much harder. Also, it will make trace/audit logs size much larger and could lower overall system performance. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Retrieving statement BLR for debug and test
16.07.2021 12:26, Dimitry Sibiryakov wrote: 16.07.2021 11:21, Vlad Khorsun wrote: Second, we need separate trace event to show generated BLR. Isn't trace_blr_compile enough? We need a way to know when application uses BLR API directly. I.e. trace_blr_compile is very different from trace_generated_blr from securty POV (at least). Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Retrieving statement BLR for debug and test
15.07.2021 22:34, Adriano dos Santos Fernandes wrote:
Hi!
Currently it's possible to set TraceDSQL config parameter in dev build
to have formatted statement BLR in firebird.log, but that is not
sufficient for automated tests.
I want to add facility to make it possible to applications request
individual statements BLR for debug and test purposes (also in prod build).
Here is the design I would go:
interface Statement
{
const uint PREPARE_PRESERVE_BLR = 0x80;
}
#define isc_info_sql_blr_bytes 31
#define isc_info_sql_blr_text 32
Statements prepared with PREPARE_PRESERVE_BLR would be usable with
getInfo and isc_info_sql_blr_*.
isc_info_sql_blr_bytes retrieves raw BLR bytes and isc_info_sql_blr_text
retrieves formatted BLR as the BLOB filter/TraceDSQL.
ISQL will have SET BLR_DISPLAY OFF/ON/ONLY. ONLY will make only
prepare/display without execution.
ISQL would use isc_info_sql_blr_text and retrieves already formatted
data ready for display.
That would be for master and v4.
Comments?
I like it. Not sure how it will handle non-DML statements, but it is
tool for those who understand how and why to use it.
Regards,
Vlad
PS same way to get dsql\jrd parse tree ? :)
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Retrieving statement BLR for debug and test
15.07.2021 22:56, Dimitry Sibiryakov wrote: 15.07.2021 21:34, Adriano dos Santos Fernandes wrote: Comments? ... It would be simpler to raise trace BLR event from SQL prepare and let anyone interested to watch generated BLR using existing (currently unused) trace parameter. First, existing BLR-related trace events are not unused. Second, we need separate trace event to show generated BLR. Next, there is no generated BLR for many (most) non-DML statements. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] IPluginModule::doClean() call point
14.07.2021 16:16, Dimitry Sibiryakov wrote: 14.07.2021 15:08, Vlad Khorsun wrote: And this is correct. What is not good for you is that it is called not because of module unload (by Plugin Manager), AFAIU. For me it is irrelevant because I don't set any logic on this Hmm... so what we speak about in this thread ? Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] IPluginModule::doClean() call point
14.07.2021 15:34, Dimitry Sibiryakov wrote: And here is a call stack from module destructor which in this case workd as UnloadDetector: #1 0x768f4921 in __GI_abort () at abort.c:79 #2 0x70a5ff89 in PluginModule::~PluginModule (this=0x70f971b0 , __in_chrg=) at /home/sd/ibptrace/plugin/main.cpp:248 #3 0x768f7161 in __run_exit_handlers (status=0, listp=0x76c9f718 <__exit_funcs>, run_list_atexit=run_list_atexit@entry=true, run_dtors=run_dtors@entry=true) at exit.c:108 #4 0x768f725a in __GI_exit (status=) at exit.c:139 #5 0x768d5bfe in __libc_start_main (main=0x555756e0 , argc=2, argv=0x7fffe378, init=, fini=, rtld_fini=, stack_end=0x7fffe368) at ../csu/libc-start.c:344 #6 0x555760fa in _start () Obviously library's onexit handler that destruct global variables is called before application's one in glibc 2.27. And this is correct. What is not good for you is that it is called not because of module unload (by Plugin Manager), AFAIU. I.e. you need something like UnloadDetectorHelper to corectly unregister your plugin. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] IPluginModule::doClean() call point
14.07.2021 15:29, Dimitry Sibiryakov wrote: 14.07.2021 14:25, Vlad Khorsun wrote: Are you sure fb_shutdown() was called before exit() ? isql calls fb_shutdown() in atexit() handler so it is surely called after exit(). Yes, indeed. In isql fb_shutdown() is called as part of exit(), to be more precise. I.e. before OS loader forces unload of still loaded modules (dlls\shared objects). I don't know for sure when globals of loaded modules is destoyed by Linux loader. In Windows case it happens when module is unloading. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] IPluginModule::doClean() call point
14.07.2021 15:14, Dimitry Sibiryakov wrote: 14.07.2021 13:06, Alex Peshkoff via Firebird-devel wrote: Looks like it is not thread races or I did call gettid() wrongly. But if all that chain was called from fb_shutdown() it does not matter - UnloadDetector is not needed. Can you show the stack trace when doClean is called? Here it is: ... #35 0x74d805dc in (anonymous namespace)::allClean () at /home/sd/firebird/src/common/classes/init.cpp:132 #36 (anonymous namespace)::Cleanup::~Cleanup (this=, __in_chrg=) at /home/sd/firebird/src/common/classes/init.cpp:172 #37 0x768f7161 in __run_exit_handlers (status=0, listp=0x76c9f718 <__exit_funcs>, run_list_atexit=run_list_atexit@entry=true, run_dtors=run_dtors@entry=true) at exit.c:108 #38 0x768f725a in __GI_exit (status=) at exit.c:139 #39 0x768d5bfe in __libc_start_main (main=0x555756e0 , argc=2, argv=0x7fffe378, init=, fini=, rtld_fini=, stack_end=0x7fffe368) at ../csu/libc-start.c:344 #40 0x555760fa in _start () Are you sure fb_shutdown() was called before exit() ? Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] IPluginModule::doClean() call point
14.07.2021 14:16, Dimitry Sibiryakov пишет: 14.07.2021 13:12, Vlad Khorsun wrote: Good. In this case the scenario is: - app call fb_shutdown() - fb_shutdown() call doClean() of every loaded plugin module and unload dll module - windows loader call DllMain of unloading module (actually it is not DllMain but some other entrypoint of run-time) - run-time entrypoint call dtors of global objects in dll module What problem from FB side do you see here ? That's exactly what I'm trying to find out because doClean() is called AFTER destructors as you can see from the log. In your log I don't see what class doClean() belongs to. Probably it is method of some global object that have no relation to Plugin Manager. Waiting for more useful info. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] IPluginModule::doClean() call point
14.07.2021 13:22, Dimitry Sibiryakov wrote: 14.07.2021 12:13, Vlad Khorsun wrote: Why do you think it does ? Do you have a stack trace ? Yes. Functions after global destructors are called from fb_shutdown(). Does your embedded app calls fb_shutdown() before exit() ? My "embedded app" is isql so - yes, it calls fb_shutdown() from atexit(). Good. In this case the scenario is: - app call fb_shutdown() - fb_shutdown() call doClean() of every loaded plugin module and unload dll module - windows loader call DllMain of unloading module (actually it is not DllMain but some other entrypoint of run-time) - run-time entrypoint call dtors of global objects in dll module What problem from FB side do you see here ? Does your dll module conains code that detects "unxpected" unload (as UnloadDetectorHelper do) ? No because my doClean() does nothing useful and I saw no point in cleanup. This is wrong, see above. Does your dll module run cleanup code in this case ? Which exactly "cleanup code" do you have on mind? See above. Calling of unregisterModule() could prevent call of doClean(), but not TraceFactory::getOwner()/release() which happen from PluginManager::releasePlugin(). Show stack trace, pls Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] IPluginModule::doClean() call point
14.07.2021 12:38, Dimitry Sibiryakov wrote: 14.07.2021 11:27, Alex Peshkoff via Firebird-devel wrote: I'll do a couple more experiments to determine if the problem is with thread races or library unloading order by OS because I suspect that on exit they are unloaded in reverse order As far as I know yes - reverse. so trace plugin is unloaded before UnloadDetectorHelper in the engine is fired. In this case the engine's UnloadDetector must not call routines from other libraries. Why do you think it does ? Do you have a stack trace ? Does your embedded app calls fb_shutdown() before exit() ? Does your dll module conains code that detects "unxpected" unload (as UnloadDetectorHelper do) ? Does your dll module run cleanup code in this case ? Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Slow DDL execution with 4.0 and 3.0
12.07.2021 7:15, Gabor Boros wrote: 2021.05.20. 10:36 keltezéssel, Gabor Boros írta: Hi All, I try to find why the real life application's internal database version changer much slower with 4.0. One slow step is the procedure altering. Vlad suggested and index privately: RDB$DEPENDENCIES (RDB$DEPENDENT_NAME, RDB$DEPENDED_ON_NAME, RDB$FIELD_NAME). I've spend a lot of time looking at case Gabor sent to me privately. The case content is more than 2200 "CREATE OR ALTER PROCEDURE" statements. The main reasons for slower execution I found are: - fb25 update records in RDB$PROCEDURE_PARAMETERS, while fb3 do delete\insert records in RDB$PROCEDURE_PARAMETERS - with records in RDB$PROCEDURE_PARAMETERS fb3 delete\insert related records in RDB$FIELDS, RDB$USER_PRIVILEGES and RDB$SECURITY_CLASSES - indices on RDB$DEPENDENCIES was changed in ODS12 before ODS 12: CREATE INDEX RDB$INDEX_27 ON RDB$DEPENDENCIES (RDB$DEPENDENT_NAME); CREATE INDEX RDB$INDEX_28 ON RDB$DEPENDENCIES (RDB$DEPENDED_ON_NAME); ODS 12: CREATE INDEX RDB$INDEX_27 ON RDB$DEPENDENCIES (RDB$DEPENDENT_NAME, RDB$DEPENDENT_TYPE); CREATE INDEX RDB$INDEX_28 ON RDB$DEPENDENCIES (RDB$DEPENDED_ON_NAME, RDB$DEPENDED_ON_TYPE, RDB$FIELD_NAME); Some (many) internal requests uses both RDB$INDEX_27 and RDB$INDEX_28 with bad selectivity on RDB$DEPENDENT_NAME and RDB$DEPENDENT_ON_NAME, while combined index on (RDB$DEPENDENT_NAME, RDB$DEPENDENT_ON_NAME) have much better selectivity. I recommend to add such index into next ODS. BTW, fb25 also have very visible perf boost with such index. - metadata names is significantly larger in ODS13: ODS12: CHAR(31) CHARACTER SET UNICODE_FSS 93 bytes ODS13: CHAR(63) CHARACTER SET UTF8 252 bytes Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Memory leak with external engine plugin
05.07.2021 10:17, Jiří Činčura wrote: You may try next build I can confirm the leak does not happen in https://github.com/FirebirdSQL/firebird/actions/runs/999413719 build. Good job! Will all the fixes be ported back to 3.0.8? Done, hope I did it right. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Memory leak with external engine plugin
02.07.2021 16:43, Vlad Khorsun wrote: 02.07.2021 16:35, Jiří Činčura wrote: Any progress here? It's starting to be bit ridiculous. There's memory leak that's taking down about 15 servers I care about daily and I have to constantly beg for response. I found and fixed few more places but not all of them. Still looking... You may try next build Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Memory leak with external engine plugin
02.07.2021 16:35, Jiří Činčura wrote: Any progress here? It's starting to be bit ridiculous. There's memory leak that's taking down about 15 servers I care about daily and I have to constantly beg for response. I found and fixed few more places but not all of them. Still looking... Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Memory leak with external engine plugin
21.06.2021 21:55, Adriano dos Santos Fernandes wrote:
I'm testing with the example function:
create function sum_args (
n1 integer,
n2 integer,
n3 integer
) returns integer
external name 'udrcpp_example!sum_args'
engine udr;
select sum_args(1, 2, 3) from rdb$database where 1 = 0;
I put breakpoint in UdrEngine.cpp here:
~SharedFunction()
{
engine->deleteChildren(children);
}
And engine calls it.
Can you do the same test using sum_args?
I just tried it with v3 and it doesn't call nor ~SharedFunction(), nor
ExtEngineManager::Function::~Function(), nor Jrd::Routine::~Routine().
Than I found that it was fixed in v4 with PR168 but never backported into v3.
What was not fixed and still leaks memory in v4 - is few metadata instances
incorrectly counted at ExtEngineManager::makeFunction() and
ExtEngineManager::makeProcedure().
Fix is committed into v4.0-release. Jiří could you try next build and report
here ?
Regards,
Vlad
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Trace plugins instance's usage
23.06.2021 12:13, Dimitry Sibiryakov wrote: 22.06.2021 16:47, Vlad Khorsun wrote: Can engine reuse an instance of ITracePlugin for different attachment or services (interchangeable)? No And when backup service establish a new connection to a database, this connection will have its own TracePlugin, right? Only if there is active trace session(s). In this case both service and database connections could create own instance of ITracePlugin per trace session. Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Trace plugins instance's usage
22.06.2021 17:15, Dimitry Sibiryakov wrote: Hello All. Can engine reuse an instance of ITracePlugin for different attachment or services (interchangeable)? No Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] IBlob::putSegment
02.06.2021 20:39, Jiří Činčura wrote: IXpbBuilder* pb = utl->getXpbBuilder(&status, IXpbBuilder::BPB, NULL, 0); Looks like FB3 does not have BPB (https://github.com/FirebirdSQL/firebird/blob/R3_0_7/src/include/firebird/IdlFbInterfaces.h#L3827). > > Is it only in FB4? Seems yes, IXpbBuilder in FB3 supports not all kinds of parameter blocks. But you still may use traditional way to build BPB, as described in IB6 docs. Hope it helps, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] CALL statement
31.05.2021 16: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.
As I see, all problems above is related with RETURNING_VALUES clause, correct
?
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.
If "CALL" is not accepted (because of non-standard syntax), why not add
"correct"
RETURNING clause into EXECUTE PROCEDURE ? Leave old RETURNING_VALUES clause as
is for
compatibility and allow to use new good RETURNING clause instead.
Just my 0.02 uah
Regards,
Vlad
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] DefaultTimeZone config
10.05.2021 17:52, Adriano dos Santos Fernandes wrote: On 10/05/2021 11:45, Vlad Khorsun wrote: People wanting a functionality should not be worried about a DLL (which in 99% of cases will be there already). I can agree about 99% of non-Windows cases, but on Windows it is wrong assumption. I mean 99% of people has 0% necessity of not deploy ICU DLLs with their clients. Ok, understand. I could agree with above, if you wrote "...0% necessity of deploy ICU..." :) And Firebird in Windows comes with that DLLs. It have nothing common with what people deploy with own applications (hint: fbclient.dll only). Many of people may really not know they could be needed in the client, but as soon a non-default setting is used and they have an error explaining that, they will know and deploy or will avoid the setting. Correct me if I'm wrong: when client config is present and contains non-commented out setting DefaultTimeZone - with some value, other than "Auto" such value is passed via DPB and validated on server, using server-side ICU - with value "Auto" fbclient asks ICU about local client time zone name and put it into DPB, if ICU is not found - error is raised (with some explanation, not just OS error) If there is no client config or DefaultTimeZone is not set, nothing is added into DPB. Correct ? Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] DefaultTimeZone config
10.05.2021 17:12, Adriano dos Santos Fernandes wrote: On 10/05/2021 11:03, Dimitry Sibiryakov wrote: 10.05.2021 14:55, Adriano dos Santos Fernandes wrote: May be explicit DefaultTimeZone=Auto can turn on passing implicit client time zone? Not to say I treat it as absoluely needed, just possible solution. It should be possible, but requiring ICU in the client when that setting is used. Should this part be deferred? May be it would be enough to use system-specific timezone functions and translate their results to ICU names/ids?.. Do you know such "translator" ? At least for Windows. It's what ICU does and I see no need to replicate it. People wanting a functionality should not be worried about a DLL (which in 99% of cases will be there already). I can agree about 99% of non-Windows cases, but on Windows it is wrong assumption. Despite of presence of ICU in some recent versions of Win10, the non-standard non- predictable way of its deployment not allows us to make use of it. According to https://docs.microsoft.com/en-us/windows/win32/intl/international-components-for-unicode--icu- a) Win10 before build 1703 - no integrated ICU b) Win10 1703 : standard, expected deployment of ICU (icuuc.dll, icuin.dll) c) Win10 1903 : non-standard deployment using "common" library icu.dll Yes, we could add Windows-specific code to try load icu.dll, but I don't know what MS will change in next release and by what reason. Thus I don't think we could rely on presence of system ICU in Win10, unfortunately. And, note, we still support Win7+ where ICU is far not guaranteed to be present. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Plugin used by trace session
03.05.2021 21:57, Dimitry Sibiryakov пишет: 03.05.2021 20:22, Vlad Khorsun wrote: Trace session can't choose trace plugin, yes. Thus, all loaded plugins receives notifications from the engine. Does it mean that output for this session will be a mess from all plugins at once? No mess, every plugin could (or could not) put own message into trace output. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
