Issue Details (XML | Word | Printable)

Key: CORE-5746
Type: Sub-task Sub-task
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Rashid Abzalov
Reporter: Rashid Abzalov
Votes: 0
Watchers: 8
Operations

If you were logged in you would be able to see more operations.
Firebird Core
CORE-5612

Remove the restriction on create/delete, enable/disable the user indexes in system tables

Created: 11/Feb/18 03:47 PM   Updated: 11/May/18 10:46 AM
Component/s: Engine
Affects Version/s: 3.0.0, 3.0.1, 3.0.2, 3.0.3
Fix Version/s: 3.0.4, 4.0 Beta 1

File Attachments: 1. File core_4731.fbt (44 kB)
2. File test.7z (2.94 MB)


QA Status: Done successfully
Test Details:
todo: update test for CORE-4731 ("Prohibit an ability to issue DML or DDL statements on RDB$ tables") - no more restriction on creating user indices for system tables.


 Description  « Hide
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, we are can find a way around this restriction (check_gbak_cheating_insupd, check_gbak_cheating_delete), but why can not you provide a regular way?

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Sean Leyne added a comment - 28/Feb/18 05:30 PM
I don't believe that allowing developers to make changes to system indexes is necessary or advisable.

IMO, what you are seeing is the result some core FB deficiencies (1) the lack of some keys indexes for your use case (which would likely benefit all FB users and (2) some potential optimizations/changes that should be made to selected engine routines*


* Vlad Khorsun posted to the Firebird-Dev mailing list.

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.

Rashid Abzalov added a comment - 03/Mar/18 07:13 PM
Test case with database for 2.5 and it's backup. The database contains 300 existed views.
The bat-file creates 100 views in two ways (in a copy of the source database):
   1) with the disabled system indexes in RDB$DEPENDENCIES
   2) with the enabled system indexes in RDB$DEPENDENCIES
Disabled indices are RDB$INDEX_27 (RDB$DEPENDENT_NAME) and RDB$INDEX_28 (RDB$DEPENDED_ON_NAME).

Rashid Abzalov added a comment - 14/Mar/18 05:32 PM
The test case is not correct because it was tested on version 2.5.5.
On version 2.5.8 it not reproduced - the execution time is equally fast.

Vlad Khorsun added a comment - 05/Apr/18 08:11 AM
The pull requests implementing "enable to manage user indices on system tables" is merged, see
https://github.com/FirebirdSQL/firebird/pull/156 and
https://github.com/FirebirdSQL/firebird/pull/155

I offer to change this ticket description accordingly (user indices, not system!) and close it.

Artyom Smirnov added a comment - 09/Apr/18 02:15 PM
I noticed after those changes test bugs.core_4731 from fbt now hanging server (both 3 and 4). It quite complex test and I have not yet extracted exact testcase but it hang on query like "create descending index RDB_PROCEDURES on RDB$PROCEDURES(RDB$PROCEDURE_NAME);" which appears to be related to this ticket.

Rashid Abzalov added a comment - 09/Apr/18 06:00 PM
Where can I see the contents of this test?


Rashid Abzalov added a comment - 10/Apr/18 06:39 PM - edited
New test file with fixed hang is attached.

But probably in the test file still need to add a check to remove/disable the system and user indexes

Vlad Khorsun added a comment - 12/Apr/18 11:07 AM
The test is fixed, thanks !

Karol Bieniaszewski added a comment - 11/May/18 10:39 AM
Is this really fixed?
About what system tables is this ticket?

i try to create index on MON$ tables and it fail WI-V3.0.4.32954 Firebird 3.0
SQL Message : -607
This operation is not defined for system tables.

Engine Code : 335544351
Engine Message :
unsuccessful metadata update
feature is not supported




Vlad Khorsun added a comment - 11/May/18 10:46 AM
Karol,

yes, it was really fixed, i believe.

It is about "real" tables, not virtual. I.e. it is about RDB$ tables and not about MON$\SEC$.