[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: [Gnumed-devel] Inadvertent deletion of Procedure when Deleting episo
From: |
Busser, Jim |
Subject: |
Re: [Gnumed-devel] Inadvertent deletion of Procedure when Deleting episode |
Date: |
Thu, 1 Aug 2013 15:37:31 +0000 |
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
- Re: [Gnumed-devel] Inadvertent deletion of Procedure when Deleting episode, (continued)
- Re: [Gnumed-devel] Inadvertent deletion of Procedure when Deleting episode, Karsten Hilbert, 2013/08/02
- Re: [Gnumed-devel] Inadvertent deletion of Procedure when Deleting episode, Busser, Jim, 2013/08/02
- Re: [Gnumed-devel] Inadvertent deletion of Procedure when Deleting episode, Karsten Hilbert, 2013/08/02
- Re: [Gnumed-devel] Inadvertent deletion of Procedure when Deleting episode, Karsten Hilbert, 2013/08/02
- Re: [Gnumed-devel] Inadvertent deletion of Procedure when Deleting episode, Karsten Hilbert, 2013/08/01
- Re: [Gnumed-devel] Inadvertent deletion of Procedure when Deleting episode, Karsten Hilbert, 2013/08/02
- Re: [Gnumed-devel] Inadvertent deletion of Procedure when Deleting episode, Karsten Hilbert, 2013/08/02
- Re: [Gnumed-devel] Inadvertent deletion of Procedure when Deleting episode, Busser, Jim, 2013/08/02
- Re: [Gnumed-devel] Inadvertent deletion of Procedure when Deleting episode,
Busser, Jim <=
- Re: [Gnumed-devel] Inadvertent deletion of Procedure when Deleting episode, Busser, Jim, 2013/08/01
- Re: [Gnumed-devel] Inadvertent deletion of Procedure when Deleting episode, Karsten Hilbert, 2013/08/01
Re: [Gnumed-devel] Inadvertent deletion of Procedure when Deleting episode, Karsten Hilbert, 2013/08/01