Re: [Gnumed-devel] Inadvertent deletion of Procedure when Deleting episode

[Karsten Hilbert]

I found the explanation of why it was possible to delete the
clin.episode row with the clin.procedure /
clin.clin_root_item rows staying in place. Under Inheritance
the PG Manual says:

        All check constraints and not-null constraints on a
        parent table are automatically inherited by its
        children. Other types of constraints (unique, primary
        key, and foreign key constraints) are not inherited.

I'm pretty sure I tested that back in the days but
apparently got it wrong (or got the test wrong or tested a
PG version which did something different).

It never bit us (knowingly) because GNUmed code tries to be
extremely careful when deleting things (such as not to raise
an exception) by checking beforehand. Deleting an episode
did not check procedures, however, it seems.

In order for everyone to check the amount of dangling items
I would suggest running the following queries:

        SELECT * from clin.v_pat_items c_vpi where not exists (
                select 1 from clin.episode c_e where c_e.pk = c_vpi.fk_episode
        );

        SELECT * from clin.v_pat_items c_vpi where not exists (
                select 1 from clin.encounter c_e where c_e.pk = 
c_vpi.fk_encounter
        );

I will write some code which will (re)add explicit foreign
key definitions on .fk_encounter and .fk_episode to each
clin.clin_root_item child on each database upgrade/fixup.

Karsten
-- 
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346