Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5746) Remove the restriction on create/delete, enable/disable the system indexes in system tables
10.04.2018 21:51, Rashid Abzalov wrote: The implemented feature allows changing user indexes to any users. It allows to change user indices by the OWNERS and users with ALTER TABLE privilege granted, see below: fb30\temp\x64\debug\firebird>isql s:\Temp\A.30.FDB Database: s:\Temp\A.30.FDB, User: SYSDBA SQL> show users; Users in the database 1 #SYSDBA 0 VLAD SQL> SQL> create index idx1 on rdb$dependencies(RDB$DEPENDENT_NAME); SQL> exit; fb30\temp\x64\debug\firebird>isql s:\Temp\A.30.FDB -user vlad Database: s:\Temp\A.30.FDB, User: VLAD SQL> create index idx1 on rdb$dependencies(RDB$DEPENDENT_NAME); Statement failed, SQLSTATE = 28000 unsuccessful metadata update -CREATE INDEX IDX1 failed -no permission for ALTER access to TABLE RDB$DEPENDENCIES SQL> drop index idx1; Statement failed, SQLSTATE = 28000 unsuccessful metadata update -DROP INDEX IDX1 failed -no permission for ALTER access to TABLE RDB$DEPENDENCIES SQL> exit; fb30\temp\x64\debug\firebird>isql s:\Temp\A.30.FDB Database: s:\Temp\A.30.FDB, User: SYSDBA SQL> grant alter any table to user vlad; SQL> exit; F:\FB2\fb30\temp\x64\debug\firebird>isql s:\Temp\A.30.FDB -user vlad Database: s:\Temp\A.30.FDB, User: VLAD SQL> create index idx1 on rdb$dependencies(RDB$DEPENDENT_NAME); Statement failed, SQLSTATE = 42S11 unsuccessful metadata update -CREATE INDEX IDX1 failed -Index IDX1 already exists SQL> drop index idx1; SQL> exit; Regards, Vlad -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5746) Remove the restriction on create/delete, enable/disable the system indexes in system tables
The implemented feature allows changing user indexes to any users. Perhaps it makes sense to allow this only for users who are members of the RDB$ADMIN role? Or is it better to check by analogy with database triggers - SCL_check_database(tdbb, SCL_alter)? -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5746) Remove the restriction on create/delete, enable/disable the system indexes in system tables
I was not entirely right. The above tests were carried out on version 2.5.5, where the result was obtained in 30 minutes. After upgrading to version 2.5.8, the test shows the same results regardless of the processor ~ 1 min. Also checked for version 3.0, with additional indexes created in RDB$DEPENDENCIES - everything works similarly fast. Thus, to solve the issues CORE-5612 and CORE-5746 it is enough to create the following indexes in RDB$DEPENDENCIES: not unique - RDB$DEPENDENT_NAME,RDB$DEPENDENT_TYPE unique for 2.5 - RDB$DEPENDENT_NAME, RDB$DEPENDED_ON_NAME, RDB$FIELD_NAME, RDB$DEPENDENT_TYPE, RDB$DEPENDED_ON_TYPE for 3.0 and above - RDB$DEPENDENT_NAME, RDB$DEPENDED_ON_NAME, RDB$FIELD_NAME, RDB$DEPENDENT_TYPE, RDB$DEPENDED_ON_TYPE, RDB$PACKAGE_NAME Should I send pull requests myself in B3_0_Release, and the master branches? -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5746) Remove the restriction on create/delete, enable/disable the system indexes in system tables
27.02.2018 17:19, Rashid Abzalov wrote: And what about enable/disable the regular system indexes? Without this capability, system queries will continue to use regular system indexes, instead of more suitable (for performance reasons in specific cases) user indexes. It's up to the optimizer to decide what indices should be used in every particular case, it doesn't distinguish between system and user-defined indices. If your index matches better, it will be used. Also, most internal queries are cached after prepare, so (1) indices already used cannot be disabled and (2) plans cannot change after some index is enabled. So I don't see much practical sense in allowing disable/enable for system indices. Dmitry -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5746) Remove the restriction on create/delete, enable/disable the system indexes in system tables
How much I understood, there is no way to specify what index should be used at system inquiries from *.epp files? I have the same understanding It seems to me that the solution of this problem would be of much more use. Then you could just create additional system indexes and use those or other in specific system queries. Is it worth it to me to search in this direction (is there a chance in implementation or it can break a lot)? If not, is it possible to enable the create/delete, enable/disable indexes on system tables, including standard system indexes? I would try to implement this if the approach is approved. I have no objection to allow to create\alter\drop\set_stats for user index on system tables. Note, such indices will not be put into gbak backup and should be re-created after restore. Of course, it also could be improved, if necessary. And what about enable/disable the regular system indexes? Without this capability, system queries will continue to use regular system indexes, instead of more suitable (for performance reasons in specific cases) user indexes. Yes, I also think that there is no need to restore non-system objects created in system entities. Those if you do not want to allow such actions publicly, you could be allowed to do it from external utilities similar to GBACK (dpb.insertString(isc_dpb_gbak_attach, FB_VERSION, fb_strlen(FB_VERSION))), but then need to remove the check for the creator of database (attachment->att_flags & ATT_creator). I could be wrong, but: to make it properly, one should look as SCL_check_relation(..., SCL_control) calls at VIO_erase\VIO_modify\VIO_store near the "case rel_indices" and pass "false" into last parameter (protectSys) for user indices. The same should be done at checkPermission() method of CreateIndexNode, AlterIndexNode, DropIndexNode, and SetStatisticsNode classes. Well thank you. Is this just in case we take the approach with the transfer of a special flag so that user applications can act like gbak? -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5746) Remove the restriction on create/delete, enable/disable the system indexes in system tables
20.02.2018 14:23, Rashid Abzalov wrote: The background of this task in CORE-5612, it contains research details and conclusions. How much I understood, there is no way to specify what index should be used at system inquiries from *.epp files? I have the same understanding If not, is it possible to enable the create/delete, enable/disable indexes on system tables, including standard system indexes? I would try to implement this if the approach is approved. I have no objection to allow to create\alter\drop\set_stats for user index on system tables. Note, such indices will not be put into gbak backup and should be re-created after restore. Of course, it also could be improved, if necessary. In Firebird 2.5, we get around the problem described in CORE-5612 by creating 2 indexes and turning off the indices RDB$INDEX_27 and RDB$INDEX_28 at the right time (then turn it back on). But we can not act in the same way in Firebird 3.0 because these actions are only allowed by GBACK (attachment->isGbak) and only during the creation of the database (attachment->att_flags & ATT_creator). Those if you do not want to allow such actions publicly, you could be allowed to do it from external utilities similar to GBACK (dpb.insertString(isc_dpb_gbak_attach, FB_VERSION, fb_strlen(FB_VERSION))), but then need to remove the check for the creator of database (attachment->att_flags & ATT_creator). I could be wrong, but: to make it properly, one should look as SCL_check_relation(..., SCL_control) calls at VIO_erase\VIO_modify\VIO_store near the "case rel_indices" and pass "false" into last parameter (protectSys) for user indices. The same should be done at checkPermission() method of CreateIndexNode, AlterIndexNode, DropIndexNode, and SetStatisticsNode classes. Regards, Vlad -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5746) Remove the restriction on create/delete, enable/disable the system indexes in system tables
Hello. The background of this task in CORE-5612, it contains research details and conclusions. How much I understood, there is no way to specify what index should be used at system inquiries from *.epp files? If not, is it possible to enable the create/delete, enable/disable indexes on system tables, including standard system indexes? I would try to implement this if the approach is approved. In Firebird 2.5, we get around the problem described in CORE-5612 by creating 2 indexes and turning off the indices RDB$INDEX_27 and RDB$INDEX_28 at the right time (then turn it back on). But we can not act in the same way in Firebird 3.0 because these actions are only allowed by GBACK (attachment->isGbak) and only during the creation of the database (attachment->att_flags & ATT_creator). Those if you do not want to allow such actions publicly, you could be allowed to do it from external utilities similar to GBACK (dpb.insertString(isc_dpb_gbak_attach, FB_VERSION, fb_strlen(FB_VERSION))), but then need to remove the check for the creator of database (attachment->att_flags & ATT_creator). -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-5746) Remove the restriction on create/delete, enable/disable the system indexes in system tables
Remove the restriction on create/delete, enable/disable the system indexes in system tables --- Key: CORE-5746 URL: http://tracker.firebirdsql.org/browse/CORE-5746 Project: Firebird Core Issue Type: Sub-task Components: Engine Affects Versions: 3.0.3, 3.0.2, 3.0.1, 3.0.0 Reporter: Rashid Abzalov Proceeding from the fact that it is necessary to apply adaptive mechanics, and from the fact that in the source code is not used anywhere specifying the plan (perhaps there are reasons for it) - there is hardly a chance that you implement the use of "correct" system indexes in the right places. Therefore, please remove the restriction on creating indexes for system tables, and disabling/enabling system indexes. We ourselves will manipulate them at the right time. In the end, what can be a bad from modification of system indexes, given that DB developers understand what they are doing? For example, in Oracle there are no restrictions in modifying the system tables - even insert into dual table, not to mention the creation of indexes. Of course, you can find a way around this restriction (check_gbak_cheating_insupd, check_gbak_cheating_delete), but why can not you provide a regular way? -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
