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

[Karsten Hilbert]

On Thu, Aug 01, 2013 at 11:55:18AM +0200, Karsten Hilbert wrote:

> 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.

The (v18 onwards) bootstrapper now checks -- and creates if
necessary -- the relevant foreign keys on upgrade or fixup
runs. A corresponding log snippet showing a failure to add
the FK due to an orphaned row in clin.procedure goes like
this:

2013-08-02 12:01:32  INFO      gm.bootstrapper 
(./bootstrap_gm_db_system.py::bootstrap_notifications() #1116): 
clin.clin_root_item child tables:
2013-08-02 12:01:32  INFO      gm.bootstrapper 
(./bootstrap_gm_db_system.py::bootstrap_notifications() #1118): 
clin.form_instances
2013-08-02 12:01:32  INFO      gm.bootstrapper 
(./bootstrap_gm_db_system.py::bootstrap_notifications() #1118): au.referral
2013-08-02 12:01:32  INFO      gm.bootstrapper 
(./bootstrap_gm_db_system.py::bootstrap_notifications() #1118): 
clin.family_history
2013-08-02 12:01:32  INFO      gm.bootstrapper 
(./bootstrap_gm_db_system.py::bootstrap_notifications() #1118): 
clin.clin_aux_note
2013-08-02 12:01:32  INFO      gm.bootstrapper 
(./bootstrap_gm_db_system.py::bootstrap_notifications() #1118): clin.vaccination
2013-08-02 12:01:32  INFO      gm.bootstrapper 
(./bootstrap_gm_db_system.py::bootstrap_notifications() #1118): clin.lab_request
2013-08-02 12:01:32  INFO      gm.bootstrapper 
(./bootstrap_gm_db_system.py::bootstrap_notifications() #1118): clin.test_result
2013-08-02 12:01:32  INFO      gm.bootstrapper 
(./bootstrap_gm_db_system.py::bootstrap_notifications() #1118): 
clin.clin_narrative
2013-08-02 12:01:32  INFO      gm.bootstrapper 
(./bootstrap_gm_db_system.py::bootstrap_notifications() #1118): clin.allergy
2013-08-02 12:01:32  INFO      gm.bootstrapper 
(./bootstrap_gm_db_system.py::bootstrap_notifications() #1118): 
clin.substance_intake
2013-08-02 12:01:32  INFO      gm.bootstrapper 
(./bootstrap_gm_db_system.py::bootstrap_notifications() #1118): clin.procedure
2013-08-02 12:01:32  INFO      gm.bootstrapper 
(./bootstrap_gm_db_system.py::bootstrap_notifications() #1118): 
clin.hospital_stay
2013-08-02 12:01:32  INFO      gm.bootstrapper 
(./bootstrap_gm_db_system.py::bootstrap_notifications() #1131): FK(s) exist: 
[#1 = clin.form_instances_fk_episode_fkey] clin.form_instances.fk_episode -> 
clin.episode.pk
2013-08-02 12:01:32  INFO      gm.bootstrapper 
(./bootstrap_gm_db_system.py::bootstrap_notifications() #1155): FK(s) exist: 
[#1 = clin.form_instances_fk_encounter_fkey] clin.form_instances.fk_encounter 
-> clin.encounter.pk
2013-08-02 12:01:32  INFO      gm.bootstrapper 
(./bootstrap_gm_db_system.py::bootstrap_notifications() #1131): FK(s) exist: 
[#1 = au.referral_fk_episode_fkey] au.referral.fk_episode -> clin.episode.pk
2013-08-02 12:01:32  INFO      gm.bootstrapper 
(./bootstrap_gm_db_system.py::bootstrap_notifications() #1155): FK(s) exist: 
[#1 = au.referral_fk_encounter_fkey] au.referral.fk_encounter -> 
clin.encounter.pk
2013-08-02 12:01:32  INFO      gm.bootstrapper 
(./bootstrap_gm_db_system.py::bootstrap_notifications() #1131): FK(s) exist: 
[#1 = clin.clin_hx_family_fk_episode_fkey] clin.family_history.fk_episode -> 
clin.episode.pk
2013-08-02 12:01:32  INFO      gm.bootstrapper 
(./bootstrap_gm_db_system.py::bootstrap_notifications() #1155): FK(s) exist: 
[#1 = clin.clin_hx_family_fk_encounter_fkey] clin.family_history.fk_encounter 
-> clin.encounter.pk
2013-08-02 12:01:32  INFO      gm.bootstrapper 
(./bootstrap_gm_db_system.py::bootstrap_notifications() #1131): FK(s) exist: 
[#1 = clin.clin_aux_note_fk_episode_fkey] clin.clin_aux_note.fk_episode -> 
clin.episode.pk
2013-08-02 12:01:32  INFO      gm.bootstrapper 
(./bootstrap_gm_db_system.py::bootstrap_notifications() #1155): FK(s) exist: 
[#1 = clin.clin_aux_note_fk_encounter_fkey] clin.clin_aux_note.fk_encounter -> 
clin.encounter.pk
2013-08-02 12:01:32  INFO      gm.bootstrapper 
(./bootstrap_gm_db_system.py::bootstrap_notifications() #1131): FK(s) exist: 
[#1 = clin.vaccination_fk_episode_fkey] clin.vaccination.fk_episode -> 
clin.episode.pk
2013-08-02 12:01:32  INFO      gm.bootstrapper 
(./bootstrap_gm_db_system.py::bootstrap_notifications() #1155): FK(s) exist: 
[#1 = clin.vaccination_fk_encounter_fkey] clin.vaccination.fk_encounter -> 
clin.encounter.pk
2013-08-02 12:01:32  INFO      gm.bootstrapper 
(./bootstrap_gm_db_system.py::bootstrap_notifications() #1131): FK(s) exist: 
[#1 = clin.lab_request_fk_episode_fkey] clin.lab_request.fk_episode -> 
clin.episode.pk
2013-08-02 12:01:32  INFO      gm.bootstrapper 
(./bootstrap_gm_db_system.py::bootstrap_notifications() #1155): FK(s) exist: 
[#1 = clin.lab_request_fk_encounter_fkey] clin.lab_request.fk_encounter -> 
clin.encounter.pk
2013-08-02 12:01:32  INFO      gm.bootstrapper 
(./bootstrap_gm_db_system.py::bootstrap_notifications() #1131): FK(s) exist: 
[#1 = clin.test_result_fk_episode_fkey] clin.test_result.fk_episode -> 
clin.episode.pk
2013-08-02 12:01:32  INFO      gm.bootstrapper 
(./bootstrap_gm_db_system.py::bootstrap_notifications() #1155): FK(s) exist: 
[#1 = clin.test_result_fk_encounter_fkey] clin.test_result.fk_encounter -> 
clin.encounter.pk
2013-08-02 12:01:32  INFO      gm.bootstrapper 
(./bootstrap_gm_db_system.py::bootstrap_notifications() #1131): FK(s) exist: 
[#1 = clin.clin_narrative_fk_episode_fkey] clin.clin_narrative.fk_episode -> 
clin.episode.pk
2013-08-02 12:01:32  INFO      gm.bootstrapper 
(./bootstrap_gm_db_system.py::bootstrap_notifications() #1155): FK(s) exist: 
[#1 = clin.clin_narrative_fk_encounter_fkey] clin.clin_narrative.fk_encounter 
-> clin.encounter.pk
2013-08-02 12:01:32  INFO      gm.bootstrapper 
(./bootstrap_gm_db_system.py::bootstrap_notifications() #1131): FK(s) exist: 
[#1 = clin.allergy_fk_episode_fkey] clin.allergy.fk_episode -> clin.episode.pk
2013-08-02 12:01:32  INFO      gm.bootstrapper 
(./bootstrap_gm_db_system.py::bootstrap_notifications() #1155): FK(s) exist: 
[#1 = clin.allergy_fk_encounter_fkey] clin.allergy.fk_encounter -> 
clin.encounter.pk
2013-08-02 12:01:32  INFO      gm.bootstrapper 
(./bootstrap_gm_db_system.py::bootstrap_notifications() #1131): FK(s) exist: 
[#1 = clin.substance_intake_fk_episode_fkey] clin.substance_intake.fk_episode 
-> clin.episode.pk
2013-08-02 12:01:32  INFO      gm.bootstrapper 
(./bootstrap_gm_db_system.py::bootstrap_notifications() #1155): FK(s) exist: 
[#1 = clin.substance_intake_fk_encounter_fkey] 
clin.substance_intake.fk_encounter -> clin.encounter.pk
2013-08-02 12:01:32  INFO      gm.bootstrapper 
(./bootstrap_gm_db_system.py::bootstrap_notifications() #1133): adding FK: 
clin.procedure.fk_episode -> clin.episode.pk
2013-08-02 12:01:32  ERROR     gm.db 
(/home/ncq/Projekte/gm-git/gnumed/gnumed/Gnumed/pycommon/gmPG2.py::run_rw_queries()
 #1251): RW query failed: [
ALTER TABLE clin.procedure
        ADD FOREIGN KEY (fk_episode)
                REFERENCES clin.episode(pk)
                ON UPDATE CASCADE
                ON DELETE RESTRICT
;]
2013-08-02 12:01:32  ERROR     gm.db 
(/home/ncq/Projekte/gm-git/gnumed/gnumed/Gnumed/pycommon/gmPG2.py::run_rw_queries()
 #1254): PG error code: 23503
2013-08-02 12:01:32  ERROR     gm.db 
(/home/ncq/Projekte/gm-git/gnumed/gnumed/Gnumed/pycommon/gmPG2.py::run_rw_queries()
 #1256): PG error message: ERROR:  insert or update on table "procedure" 
violates foreign key constraint "procedure_fk_episode_fkey"
DETAIL:  Key (fk_episode)=(6) is not present in table "episode".
2013-08-02 12:01:32  ERROR     gm.bootstrapper 
(./bootstrap_gm_db_system.py::<module>() #1594): unhandled exception caught
Traceback (most recent call last):
  File "./bootstrap_gm_db_system.py", line 1587, in <module>
    main()
  File "./bootstrap_gm_db_system.py", line 1542, in main
    handle_cfg()
  File "./bootstrap_gm_db_system.py", line 1501, in handle_cfg
    if not bootstrap_notifications():
  File "./bootstrap_gm_db_system.py", line 1303, in bootstrap_notifications
    if not db.bootstrap_notifications():
  File "./bootstrap_gm_db_system.py", line 1142, in bootstrap_notifications
    gmPG2.run_rw_queries(link_obj = self.conn, queries = [{'cmd': cmd}])
  File "/home/ncq/Projekte/gm-git/gnumed/gnumed/Gnumed/pycommon/gmPG2.py", line 
1249, in run_rw_queries
    curs.execute(query['cmd'], args)
  File "/usr/lib/python2.7/dist-packages/psycopg2/extras.py", line 123, in 
execute
    return _cursor.execute(self, query, vars)
IntegrityError: insert or update on table "procedure" violates foreign key 
constraint "procedure_fk_episode_fkey"
DETAIL:  Key (fk_episode)=(6) is not present in table "episode".

2013-08-02 12:01:32  ERROR     gm.bootstrapper 
(./bootstrap_gm_db_system.py::exit_with_msg() #1407): Bootstrapping failed: 
unhandled exception occurred


To remedy this situation one will have to track down and fix
any orphaned rows.

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