Inconsistent state of master-detail occurs after RE-connect + 'SET AUTODDL OFF' + 'drop <FK>' which is ROLLED BACK

[pavel-zotov]

Following script was tested during re-implementing test for CORE-4212.
Please note on lines marked as [1] and [2]

set list on;
shell del g:\temp\tmp4test.fdb 2>nul;
create database 'localhost:g:\temp\tmp4test.fdb';
 
create table persistent_main (
    id int not null,
    primary key (id)
);
 
create table persistent_detl (id int);
 
alter table persistent_detl add constraint tdetl_fk foreign key (id) references persistent_main (id);
commit;
 
insert into persistent_detl(id) values(1);
commit;
 
connect 'localhost:g:\temp\tmp4test.fdb'; -------------------------------------------------------- [ !!! 1 !!! ] 
 
set autoddl off;
commit;
 
alter table persistent_detl drop constraint tdetl_fk;

rollback; -------------------------------------------------------- [ !!! 2 !!! ] 
 
set echo on;
show table persistent_detl;
commit;
 
insert into persistent_detl(id) values(2);
 
select d.id as orphan_child_id
from persistent_detl d
where not exists(select * from persistent_main m where m.id = d.id);

Of course, first attempt to insert row in detail table (while master is empty) will fail:
insert into persistent_detl(id) values(1);
-- produces: SQLSTATE = 23000 / -Foreign key reference target does not exist / -Problematic key value is ("ID" = 1))

But let this script go further.
You can see that there was statement to DROP FK but it was rolled back (see line marked as [ !!! 2 !!! ]).

Final output of script will end with:

show table persistent_detl; -- Still exists: "CONSTRAINT TDETL_FK: Foreign key (ID) References
ID                              INTEGER Nullable
CONSTRAINT TDETL_FK:
  Foreign key (ID)    References PERSISTENT_MAIN (ID)
commit;

insert into persistent_detl(id) values(2); -- NO error here! Why ?

select d.id as orphan_child_id
from persistent_detl d
where not exists(select * from persistent_main m where m.id = d.id);

ORPHAN_CHILD_ID                 2

What is most interesting: such outcome (presence of 'orphan' record in the detailed table) will NOT occur if we comment out line marked as [ !!! 1 !!! ]

[hvlad]

The issue is related with inconsistency in relation metadata in memory.
Right after (re)connect only basic relation's metadata is loaded into metadata cache. Some parts is delayed until really required. In this case information about partners (PK\FK references) is not loaded from disk.
When FK is dropped, engine delete corresponding rows in system tables and in this process load missing metadata - but too late, after rows in RDB$RELATION_CONSTRAINTS was deleted. Thus relation have incomplete metadata in memory.
If transaction rolled back, relation still missing information about its partners and didn't check FK on DELETE.

[hvlad]

The offered fix ensures that relation's metadata is loaded before record in RDB$RELATION_CONSTRAINTS is deleted.

[hvlad]

Re-opened for backporting

[hvlad]

Re-opened for backporting

[hvlad]

Will be ported into v5 later

[pavel-zotov]

QA issue: test currently is skipped for FB 5.x. Waiting for port.