Issue Details (XML | Word | Printable)

Key: CORE-4812
Type: Bug Bug
Status: Open Open
Priority: Critical Critical
Assignee: Unassigned
Reporter: Jiri Cincura
Votes: 0
Watchers: 5
Operations

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

Gbak cannot restore database with cyclic dependencies between views

Created: 26/May/15 06:39 AM   Updated: 27/May/15 10:38 AM
Component/s: Engine, GBAK
Affects Version/s: 2.5.4
Fix Version/s: None

Environment: Windows x64, Firebird x64


 Description  « Hide
Steps to reproduce:
*** 1:
CREATE OR ALTER VIEW CYCLE_A(
    COL)
AS
select RDB$RELATION_ID from rdb$database
;
*** 2:
CREATE OR ALTER VIEW CYCLE_B(
    COL)
AS
select RDB$RELATION_ID from rdb$database
;
*** 3:
CREATE OR ALTER VIEW CYCLE_A(
    COL)
AS
select RDB$RELATION_ID from rdb$database
union all
select col from cycle_b
;
*** 4:
CREATE OR ALTER VIEW CYCLE_B(
    COL)
AS
select RDB$RELATION_ID from rdb$database
union all
select col from cycle_a
;
*** 5: Backup database using gbak.
*** 6: Try restoring database using gbak. Fails with depth limit failure.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Sean Leyne added a comment - 26/May/15 06:54 PM
I wonder if the definition of circular views should be prevented?

{that's one way of solving this problem ;-]}

Jiri Cincura added a comment - 26/May/15 08:00 PM
As the i.e. FKs can have cyclic deps I believe this is valid scenario.

Sean Leyne added a comment - 26/May/15 08:09 PM
Understood but, FKs can't contain invalid references, and the restore of the FKs is agnostic to the circular'ness of the relationship.

The constraint are restored after the data has been restored and the index building doesn't re-check the constraint (it is checked when the FK is initially defined, so the data/values is guaranteed to be correct).

Further, with the introduction of recursive CTE's, the need for circular Views has been practically eliminated.

Adriano dos Santos Fernandes added a comment - 26/May/15 08:10 PM
Cyclic views should be disallowed. Views should be "expanded" in compile time, so that does not work.

Jiri Cincura added a comment - 27/May/15 05:01 AM
@Sean You can have cycles in computed fields (yes it's ugly). ;) That's more like the views.

Pavel Zotov added a comment - 27/May/15 07:00 AM
When we do:
SQL> create procedure sp_a as begin end;
SQL> create procedure sp_b as begin end;
SQL> set term ^;
SQL> alter procedure sp_a as begin execute procedure sp_b; end^
SQL> alter procedure sp_b as begin execute procedure sp_a; end^
SQL> set term ;^
SQL> exit;

-- then ISQL -X shows almost the same: first it displays procedures with empty bodies and after it fills them.
Perhaps, restore do the same but it doesn`t show details:
...
gbak:started transaction
gbak:restoring stored procedure SP_A
gbak:restoring stored procedure SP_B
...

What if restore first will create all view like this:

create view v_a as
-- temp., "dummy" columns:
select 1 as field_of_num_type, '' as field_of_text_type, current_date as field_of_date_type, ...
from rdb$database;

create view v_b as
-- temp., "dummy" columns:
select 1 as field_of_num_type, '' as field_of_text_type, current_date as field_of_date_type, ...
from rdb$database;

-- and after all such 'dummy' views with necessary columns are created, continue with:

alter view v_a as <stored "real" select for v_a>;
alter view v_b as <stored "real" select for v_b>;
etc.

- ?

Jiri Cincura added a comment - 27/May/15 07:06 AM
That's what I'm doing in a code I'm writing right now.

Adriano dos Santos Fernandes added a comment - 27/May/15 10:38 AM
> @Sean You can have cycles in computed fields (yes it's ugly). ;) That's more like the views.

Jiri, it just crashs. Conceptually, this type of cyclic does not and cannot work.