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

[Busser, Jim]

On 2013-08-01, at 2:37 AM, Karsten Hilbert <address@hidden> wrote:

Re-sequencing your question & comments …

>> Is it possible that deleting the episode succeeded to
>> delete a row in the root item table (despite this was not
>> intended to be possible)?
> 
> That would mean several bugs in PostgreSQL which is
> extremely hard to believe.
> 
> For me, the clin.clin_root_item row (as well as the
> procedure row) both still exist despite the episode row
> clearly being gone and it having appeared in the audit
> table.

For me, the procedure row continued to exist (though I have since deleted it), 
and -- as shown in my last reply or two -- the episode row exists in the audit 
table, 

However there is a problem to find what should have been the associated 
clin.clin_root_item row.

>       gnumed_v18=> select * from clin.clin_root_item
>>      WHERE fk_episode IS NULL ;
>> 
>> yields no rows.
> 
> That is to be expected as there is a NOT NULL constraint on
> clin.clin_root_item.fk_episode.

Well, yes, but so too expected was a clin.clin_root_item to exist, hence the 
making sure …


> What does this show:
> 
>       \d clin.clin_root_item
>       \d clin.episode
>       \d clin.procedure

Separated thusly ==================


gnumed_v18=> \d clin.clin_root_item
                                           Table "clin.clin_root_item"
    Column     |           Type           |                               
Modifiers                               
---------------+--------------------------+-----------------------------------------------------------------------
 pk_audit      | integer                  | not null default 
nextval('audit.audit_fields_pk_audit_seq'::regclass)
 row_version   | integer                  | not null default 0
 modified_when | timestamp with time zone | not null default now()
 modified_by   | name                     | not null default "current_user"()
 pk_item       | integer                  | not null default 
nextval('clin.clin_root_item_pk_item_seq'::regclass)
 clin_when     | timestamp with time zone | not null default now()
 fk_encounter  | integer                  | not null
 fk_episode    | integer                  | not null
 narrative     | text                     | 
 soap_cat      | text                     | 
Indexes:
    "clin_root_item_pkey" PRIMARY KEY, btree (pk_item)
    "idx_cri_encounter" btree (fk_encounter)
    "idx_cri_episode" btree (fk_episode)
Check constraints:
    "clin_root_item_sane_soap_cat" CHECK (soap_cat IS NULL OR (lower(soap_cat) 
= ANY (ARRAY['s'::text, 'o'::text, 'a'::text, 'p'::text, 'u'::text])))
Foreign-key constraints:
    "clin_root_item_fk_encounter_fkey" FOREIGN KEY (fk_encounter) REFERENCES 
clin.encounter(pk) ON UPDATE CASCADE ON DELETE RESTRICT
    "clin_root_item_fk_episode_fkey" FOREIGN KEY (fk_episode) REFERENCES 
clin.episode(pk) ON UPDATE CASCADE ON DELETE RESTRICT
Rules:
    clin_ritem_no_del AS
    ON DELETE TO clin.clin_root_item DO INSTEAD  SELECT 
clin.f_protect_clin_root_item() AS f_protect_clin_root_item
    clin_ritem_no_ins AS
    ON INSERT TO clin.clin_root_item DO INSTEAD  SELECT 
clin.f_protect_clin_root_item() AS f_protect_clin_root_item
Triggers:
    tr_clin_item_mod AFTER INSERT OR DELETE OR UPDATE ON clin.clin_root_item 
FOR EACH ROW EXECUTE PROCEDURE clin.f_announce_clin_item_mod()
Inherits: audit.audit_fields

========================================================

gnumed_v18=> \d clin.episode
                                                          Table "clin.episode"
               Column                |           Type           |               
                Modifiers                               
-------------------------------------+--------------------------+-----------------------------------------------------------------------
 pk_audit                            | integer                  | not null 
default nextval('audit.audit_fields_pk_audit_seq'::regclass)
 row_version                         | integer                  | not null 
default 0
 modified_when                       | timestamp with time zone | not null 
default now()
 modified_by                         | name                     | not null 
default "current_user"()
 pk                                  | integer                  | not null 
default nextval('clin.episode_pk_seq'::regclass)
 fk_health_issue                     | integer                  | 
 description                         | text                     | not null
 is_open                             | boolean                  | default true
 fk_encounter                        | integer                  | not null
 diagnostic_certainty_classification | text                     | 
 summary                             | text                     | 
Indexes:
    "episode_pkey" PRIMARY KEY, btree (pk)
    "idx_uniq_open_epi_per_issue" UNIQUE, btree (is_open, fk_health_issue) 
WHERE fk_health_issue IS NOT NULL AND is_open
    "idx_episode_fk_encounter" btree (fk_encounter)
    "idx_episode_issue" btree (fk_health_issue)
    "idx_episode_modified_by" btree (modified_by)
    "idx_episode_with_issue" btree (fk_health_issue) WHERE fk_health_issue IS 
NOT NULL
    "idx_episode_without_issue" btree (fk_health_issue) WHERE fk_health_issue 
IS NULL
Check constraints:
    "sane_description" CHECK (gm.is_null_or_blank_string(description) IS FALSE)
    "valid_diagnostic_certainty_classification" CHECK 
(diagnostic_certainty_classification = ANY (ARRAY['A'::text, 'B'::text, 
'C'::text, 'D'::text, NULL::text]))
Foreign-key constraints:
    "episode_fk_encounter_fkey" FOREIGN KEY (fk_encounter) REFERENCES 
clin.encounter(pk) ON UPDATE CASCADE ON DELETE RESTRICT
    "episode_fk_health_issue_fkey" FOREIGN KEY (fk_health_issue) REFERENCES 
clin.health_issue(pk) ON UPDATE CASCADE ON DELETE RESTRICT
Referenced by:
    TABLE "clin.allergy" CONSTRAINT "allergy_fk_episode_fkey" FOREIGN KEY 
(fk_episode) REFERENCES clin.episode(pk) ON UPDATE CASCADE ON DELETE RESTRICT
    TABLE "clin.clin_aux_note" CONSTRAINT "clin_aux_note_fk_episode_fkey" 
FOREIGN KEY (fk_episode) REFERENCES clin.episode(pk) ON UPDATE CASCADE ON 
DELETE RESTRICT
    TABLE "clin.family_history" CONSTRAINT "clin_hx_family_fk_episode_fkey" 
FOREIGN KEY (fk_episode) REFERENCES clin.episode(pk) ON UPDATE CASCADE ON 
DELETE RESTRICT
    TABLE "clin.clin_narrative" CONSTRAINT "clin_narrative_fk_episode_fkey" 
FOREIGN KEY (fk_episode) REFERENCES clin.episode(pk) ON UPDATE CASCADE ON 
DELETE RESTRICT
    TABLE "clin.clin_root_item" CONSTRAINT "clin_root_item_fk_episode_fkey" 
FOREIGN KEY (fk_episode) REFERENCES clin.episode(pk) ON UPDATE CASCADE ON 
DELETE RESTRICT
    TABLE "blobs.doc_med" CONSTRAINT "doc_med_fk_episode_fkey" FOREIGN KEY 
(fk_episode) REFERENCES clin.episode(pk) ON UPDATE CASCADE ON DELETE RESTRICT
    TABLE "clin.form_instances" CONSTRAINT "form_instances_fk_episode_fkey" 
FOREIGN KEY (fk_episode) REFERENCES clin.episode(pk) ON UPDATE CASCADE ON 
DELETE RESTRICT
    TABLE "clin.lab_request" CONSTRAINT "lab_request_fk_episode_fkey" FOREIGN 
KEY (fk_episode) REFERENCES clin.episode(pk) ON UPDATE CASCADE ON DELETE 
RESTRICT
    TABLE "clin.lnk_code2episode" CONSTRAINT "lnk_code2episode_fk_item_fkey" 
FOREIGN KEY (fk_item) REFERENCES clin.episode(pk) ON UPDATE CASCADE ON DELETE 
CASCADE
    TABLE "blobs.lnk_doc_med2episode" CONSTRAINT 
"lnk_doc_med2episode_fk_episode_fkey" FOREIGN KEY (fk_episode) REFERENCES 
clin.episode(pk) ON UPDATE CASCADE ON DELETE RESTRICT
    TABLE "clin.lnk_substance2episode" CONSTRAINT 
"lnk_substance2episode_fk_episode_fkey" FOREIGN KEY (fk_episode) REFERENCES 
clin.episode(pk) ON UPDATE CASCADE ON DELETE RESTRICT
    TABLE "au.referral" CONSTRAINT "referral_fk_episode_fkey" FOREIGN KEY 
(fk_episode) REFERENCES clin.episode(pk) ON UPDATE CASCADE ON DELETE RESTRICT
    TABLE "clin.test_result" CONSTRAINT "test_result_fk_episode_fkey" FOREIGN 
KEY (fk_episode) REFERENCES clin.episode(pk) ON UPDATE CASCADE ON DELETE 
RESTRICT
    TABLE "clin.vaccination" CONSTRAINT "vaccination_fk_episode_fkey" FOREIGN 
KEY (fk_episode) REFERENCES clin.episode(pk) ON UPDATE CASCADE ON DELETE 
RESTRICT
Triggers:
    tr_episode_mod AFTER INSERT OR DELETE OR UPDATE ON clin.episode DEFERRABLE 
INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE 
clin.trf_announce_episode_mod()
    tr_sanity_check_enc_vs_issue_on_epi BEFORE INSERT OR UPDATE ON clin.episode 
FOR EACH ROW EXECUTE PROCEDURE clin.trf_sanity_check_enc_vs_issue_on_epi()
    zt_del_episode BEFORE DELETE ON clin.episode FOR EACH ROW EXECUTE PROCEDURE 
audit.ft_del_episode()
    zt_ins_episode BEFORE INSERT ON clin.episode FOR EACH ROW EXECUTE PROCEDURE 
audit.ft_ins_episode()
    zt_upd_episode BEFORE UPDATE ON clin.episode FOR EACH ROW EXECUTE PROCEDURE 
audit.ft_upd_episode()
Inherits: audit.audit_fields

========================================================

gnumed_v18-> \d clin.procedure
                                               Table "clin.procedure"
      Column      |           Type           |                               
Modifiers                               
------------------+--------------------------+-----------------------------------------------------------------------
 pk_audit         | integer                  | not null default 
nextval('audit.audit_fields_pk_audit_seq'::regclass)
 row_version      | integer                  | not null default 0
 modified_when    | timestamp with time zone | not null default now()
 modified_by      | name                     | not null default "current_user"()
 pk_item          | integer                  | not null default 
nextval('clin.clin_root_item_pk_item_seq'::regclass)
 clin_when        | timestamp with time zone | not null default now()
 fk_encounter     | integer                  | not null
 fk_episode       | integer                  | not null
 narrative        | text                     | 
 soap_cat         | text                     | default 'p'::text
 pk               | integer                  | not null default 
nextval('clin.procedure_pk_seq'::regclass)
 clin_where       | text                     | 
 fk_hospital_stay | integer                  | 
 clin_end         | timestamp with time zone | 
 is_ongoing       | boolean                  | not null default false
Indexes:
    "procedure_pkey" PRIMARY KEY, btree (pk)
Check constraints:
    "clin_root_item_sane_soap_cat" CHECK (soap_cat IS NULL OR (lower(soap_cat) 
= ANY (ARRAY['s'::text, 'o'::text, 'a'::text, 'p'::text, 'u'::text])))
    "procedure_sane_end" CHECK (clin_end IS NULL OR clin_end >= clin_when)
    "procedure_sane_ongoing" CHECK (is_ongoing IS FALSE OR clin_end IS NULL OR 
clin_end > now())
    "sane_location" CHECK (gm.is_null_or_non_empty_string(clin_where))
    "sane_procedure" CHECK (gm.is_null_or_blank_string(narrative) IS FALSE)
    "sane_soap_cat" CHECK (soap_cat = ANY (ARRAY['a'::text, 'p'::text]))
    "single_location_definition" CHECK (fk_hospital_stay IS NULL AND clin_where 
IS NOT NULL OR fk_hospital_stay IS NOT NULL AND clin_where IS NULL)
Foreign-key constraints:
    "procedure_fk_hospital_stay_fkey" FOREIGN KEY (fk_hospital_stay) REFERENCES 
clin.hospital_stay(pk)
Referenced by:
    TABLE "clin.lnk_code2procedure" CONSTRAINT 
"lnk_code2procedure_fk_item_fkey" FOREIGN KEY (fk_item) REFERENCES 
clin.procedure(pk) ON UPDATE CASCADE ON DELETE CASCADE
Triggers:
    tr_narrative_mod AFTER INSERT OR DELETE OR UPDATE ON clin.procedure 
DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE 
clin.trf_announce_narrative_mod()
    tr_normalize_proc_is_ongoing BEFORE UPDATE ON clin.procedure FOR EACH ROW 
EXECUTE PROCEDURE clin.trf_normalize_proc_is_ongoing()
    tr_procedure_mod AFTER INSERT OR DELETE OR UPDATE ON clin.procedure 
DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE 
clin.trf_announce_procedure_mod()
    tr_sanity_check_enc_epi_insert BEFORE INSERT ON clin.procedure FOR EACH ROW 
EXECUTE PROCEDURE clin.trf_sanity_check_enc_epi_insert()
    tr_sanity_check_procedure_episode BEFORE INSERT OR UPDATE ON clin.procedure 
FOR EACH ROW EXECUTE PROCEDURE clin.trf_sanity_check_procedure_episode()
    zt_del_procedure BEFORE DELETE ON clin.procedure FOR EACH ROW EXECUTE 
PROCEDURE audit.ft_del_procedure()
    zt_ins_procedure BEFORE INSERT ON clin.procedure FOR EACH ROW EXECUTE 
PROCEDURE audit.ft_ins_procedure()
    zt_upd_procedure BEFORE UPDATE ON clin.procedure FOR EACH ROW EXECUTE 
PROCEDURE audit.ft_upd_procedure()
Inherits: clin.clin_root_item

gnumed_v18-> 

-- Jim