From: Karsten Hilbert <Karsten.Hilbert@gmx.net>
To: pgsql-general@lists.postgresql.org
Subject: Re: backend crash on DELETE, reproducible locally
Date: Thu, 1 Nov 2018 16:41:13 +0100
Message-ID: <20181101154113.GH2954@hermes.hilbert.loc> (raw)
In-Reply-To: <20181101152723.zq2dtcgntmcmr4br@alvherre.pgsql>
References: <20181101152115.GF2954@hermes.hilbert.loc>
<20181101152723.zq2dtcgntmcmr4br@alvherre.pgsql>
On Thu, Nov 01, 2018 at 12:27:23PM -0300, Alvaro Herrera wrote:
> Ah, now this is interesting. Can you please supply the definition of
> the table?
Attached.
> I'm wondering if there is a partitioned table with an FK to
> this one.
There is. Both ref.auto_hint and clin.suppressed_hint are
using inheritance (from audit.audit_fields). However, GNUmed
does not use inheritance for explicit partitioning but rather
similar to how classes and subclasses are used in OO languages.
> I'm not quite seeing how come 'tup' is NULL there. Can you
> 'print trigdata' in frame 2?
Sure, how ? :-)
(I can surely type "print trigdata" but does that already
auto-select from "frame 2" ?)
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Ausgabeformat ist »wrapped«.
Tabelle »audit.audit_fields«
Spalte | Typ | Sortierfolge | NULL erlaubt? | Vorgabewert | Speicherung | Statistikziel | Beschreibung
---------------+--------------------------+--------------+---------------+------------------------------------------------------+-------------+---------------+--------------------------------------------------------
pk_audit | integer | | not null | nextval('audit.audit_fields_pk_audit_seq'::regclass) | plain | |
row_version | integer | | not null | 0 | plain | | the version of the row; mainly just a count
modified_when | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | when has this row been committed (created/modified)
modified_by | name | | not null | CURRENT_USER | plain | | by whom has this row been committed (created/modified)
Indexe:
"audit_fields_pkey" PRIMARY KEY, btree (pk_audit)
Check-Constraints:
"audit_audit_fields_sane_modified_when" CHECK ((modified_when <= clock_timestamp()) IS TRUE)
Regeln:
audit_fields_no_del AS
ON DELETE TO audit.audit_fields DO INSTEAD NOTHING
audit_fields_no_ins AS
ON INSERT TO audit.audit_fields DO INSTEAD NOTHING
audit_fields_no_upd AS
ON UPDATE TO audit.audit_fields DO INSTEAD NOTHING
Kindtabellen: bill.bill,
bill.bill_item,
blobs.doc_desc,
blobs.doc_med,
blobs.lnk_doc2hospital_stay,
blobs.lnk_doc_med2episode,
cfg.report_query,
clin.allergy_state,
clin.clin_diag,
clin.clin_item_type,
clin.clin_root_item,
clin.encounter,
clin.episode,
clin.external_care,
clin.fhx_relation_type,
clin.form_data,
clin.health_issue,
clin.incoming_data_unmatchable,
clin.incoming_data_unmatched,
clin.lnk_code2item_root,
clin.lnk_constraint2vacc_course,
clin.lnk_pat2vaccination_course,
clin.lnk_substance2episode,
clin.lnk_tst2norm,
clin.lnk_type2item,
clin.lnk_vaccination_course2schedule,
clin.lnk_vaccine2inds,
clin.patient,
clin.review_root,
clin.suppressed_hint,
clin.test_org,
clin.test_panel,
clin.test_type,
clin.vaccination_course,
clin.vaccination_course_constraint,
clin.vaccination_definition,
clin.vaccination_schedule,
clin.vacc_indication,
clin.vaccine,
clin.vaccine_batches,
clin.vacc_route,
clin.waiting_list,
de_de.beh_fall_typ,
de_de.lab_test_gnr,
de_de.prax_geb_paid,
dem.address,
dem.gender_label,
dem.identity,
dem.identity_tag,
dem.inbox_item_category,
dem.inbox_item_type,
dem.lnk_identity2ext_id,
dem.lnk_job2person,
dem.lnk_org_unit2comm,
dem.lnk_org_unit2ext_id,
dem.lnk_person2relative,
dem.message_inbox,
dem.occupation,
dem.org,
dem.org_unit,
dem.praxis_branch,
dem.region,
dem.relation_types,
dem.staff,
dem.street,
dem.urb,
gm.access_log,
ref.auto_hint,
ref.branded_drug,
ref.consumable_substance,
ref.data_source,
ref.lnk_substance2brand,
ref.paperwork_templates,
ref.tag_image
Ausgabeformat ist »wrapped«.
Tabelle »ref.auto_hint«
Spalte | Typ | Sortierfolge | NULL erlaubt? | Vorgabewert | Speicherung | Statistikziel | Beschreibung
----------------------+--------------------------+--------------+---------------+------------------------------------------------------+-------------+---------------+------------------------------------------------------------------------
pk_audit | integer | | not null | nextval('audit.audit_fields_pk_audit_seq'::regclass) | plain | |
row_version | integer | | not null | 0 | plain | |
modified_when | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | |
modified_by | name | | not null | CURRENT_USER | plain | |
pk | integer | | not null | nextval('ref.auto_hint_pk_seq'::regclass) | plain | |
query | text | | | | extended | | This query is run against the database.
title | text | | | | extended | | A short title to summarize and identify the hint.
hint | text | | | | extended | | When the query returns true this is the hint that should be displayed.
url | text | | | | extended | | An URL relevant to the hint.
is_active | boolean | | not null | true | plain | | Whether or not this query/hint is active.
source | text | | | | extended | | Who provided query and hint.
lang | text | | | | extended | | The language the hint is written in.
recommendation_query | text | | | | extended | |
Indexe:
"auto_hint_pkey" PRIMARY KEY, btree (pk)
"ref_auto_hint_uniq_query" UNIQUE CONSTRAINT, btree (query)
"ref_auto_hint_uniq_title" UNIQUE CONSTRAINT, btree (title)
Check-Constraints:
"audit_audit_fields_sane_modified_when" CHECK ((modified_when <= clock_timestamp()) IS TRUE)
"ref_auto_hint_sane_hint" CHECK (gm.is_null_or_blank_string(hint) IS FALSE)
"ref_auto_hint_sane_lang" CHECK (gm.is_null_or_blank_string(lang) IS FALSE)
"ref_auto_hint_sane_query" CHECK (gm.is_null_or_blank_string(query) IS FALSE)
"ref_auto_hint_sane_rec_query" CHECK (gm.is_null_or_non_empty_string(recommendation_query))
"ref_auto_hint_sane_source" CHECK (gm.is_null_or_blank_string(source) IS FALSE)
"ref_auto_hint_sane_title" CHECK (gm.is_null_or_blank_string(title) IS FALSE)
"ref_auto_hint_sane_url" CHECK (gm.is_null_or_non_empty_string(url))
Fremdschlüsselverweise von:
TABLE "clin.suppressed_hint" CONSTRAINT "fk_clin_suppressed_hint_fk_hint" FOREIGN KEY (fk_hint) REFERENCES ref.auto_hint(pk) ON UPDATE RESTRICT ON DELETE CASCADE
Trigger:
zt_del_auto_hint BEFORE DELETE ON ref.auto_hint FOR EACH ROW EXECUTE PROCEDURE audit.ft_del_auto_hint()
zt_ins_auto_hint BEFORE INSERT ON ref.auto_hint FOR EACH ROW EXECUTE PROCEDURE audit.ft_ins_auto_hint()
zt_upd_auto_hint BEFORE UPDATE ON ref.auto_hint FOR EACH ROW EXECUTE PROCEDURE audit.ft_upd_auto_hint()
Erbt von: audit.audit_fields
Ausgabeformat ist »wrapped«.
Tabelle »clin.suppressed_hint«
Spalte | Typ | Sortierfolge | NULL erlaubt? | Vorgabewert | Speicherung | Statistikziel | Beschreibung
-----------------+--------------------------+--------------+---------------+------------------------------------------------------+-------------+---------------+-----------------------------------------------------------
pk_audit | integer | | not null | nextval('audit.audit_fields_pk_audit_seq'::regclass) | plain | |
row_version | integer | | not null | 0 | plain | |
modified_when | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | |
modified_by | name | | not null | CURRENT_USER | plain | |
pk | integer | | not null | nextval('clin.suppressed_hint_pk_seq'::regclass) | plain | |
fk_encounter | integer | | not null | | plain | | the encounter during which this hint was first suppressed
fk_hint | integer | | not null | | plain | | the hint that is suppressed
suppressed_by | name | | not null | CURRENT_USER | plain | | who suppressed this hint
suppressed_when | timestamp with time zone | | not null | statement_timestamp() | plain | | when was this hint suppressed
rationale | text | | | | extended | | rationale on why this hint is suppressed in this patient
md5_sum | text | | | | extended | | md5 of relevant fields of this hint
Indexe:
"suppressed_hint_pkey" PRIMARY KEY, btree (pk)
"idx_suppressed_hint_fk_encounter" btree (fk_encounter)
"idx_suppressed_hint_fk_hint" btree (fk_hint)
Check-Constraints:
"audit_audit_fields_sane_modified_when" CHECK ((modified_when <= clock_timestamp()) IS TRUE)
"clin_suppressed_hint_sane_by" CHECK (length(suppressed_by::text) > 0)
"clin_suppressed_hint_sane_md5" CHECK (gm.is_null_or_blank_string(md5_sum) IS FALSE)
"clin_suppressed_hint_sane_rationale" CHECK (gm.is_null_or_blank_string(rationale) IS FALSE)
Fremdschlüssel-Constraints:
"fk_clin_suppressed_hint_fk_encounter" FOREIGN KEY (fk_encounter) REFERENCES clin.encounter(pk) ON UPDATE RESTRICT ON DELETE RESTRICT
"fk_clin_suppressed_hint_fk_hint" FOREIGN KEY (fk_hint) REFERENCES ref.auto_hint(pk) ON UPDATE RESTRICT ON DELETE CASCADE
Trigger:
tr_sanity_check_uniq_hint_per_pat_ins_upd AFTER INSERT OR UPDATE ON clin.suppressed_hint DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE clin.trf_sanity_check_uniq_hint_per_pat_ins_upd()
zt_del_suppressed_hint BEFORE DELETE ON clin.suppressed_hint FOR EACH ROW EXECUTE PROCEDURE audit.ft_del_suppressed_hint()
zt_ins_suppressed_hint BEFORE INSERT ON clin.suppressed_hint FOR EACH ROW EXECUTE PROCEDURE audit.ft_ins_suppressed_hint()
zt_upd_suppressed_hint BEFORE UPDATE ON clin.suppressed_hint FOR EACH ROW EXECUTE PROCEDURE audit.ft_upd_suppressed_hint()
zzz_tr_announce_clin_suppressed_hint_del AFTER DELETE ON clin.suppressed_hint DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE gm.trf_announce_table_del('operation=DELETE::table=clin.suppressed_hint::PK name=pk', 'select $1.pk', 'select fk_patient from clin.encounter where pk = $1.fk_encounter limit 1')
zzz_tr_announce_clin_suppressed_hint_ins_upd AFTER INSERT OR UPDATE ON clin.suppressed_hint DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE gm.trf_announce_table_ins_upd('table=clin.suppressed_hint::PK name=pk', 'select $1.pk', 'select fk_patient from clin.encounter where pk = $1.fk_encounter limit 1')
Erbt von: audit.audit_fields
Attachments:
[text/plain] audit.audit_fields.txt (4.2K, ../20181101154113.GH2954@hermes.hilbert.loc/2-audit.audit_fields.txt)
download | inline:
Ausgabeformat ist »wrapped«.
Tabelle »audit.audit_fields«
Spalte | Typ | Sortierfolge | NULL erlaubt? | Vorgabewert | Speicherung | Statistikziel | Beschreibung
---------------+--------------------------+--------------+---------------+------------------------------------------------------+-------------+---------------+--------------------------------------------------------
pk_audit | integer | | not null | nextval('audit.audit_fields_pk_audit_seq'::regclass) | plain | |
row_version | integer | | not null | 0 | plain | | the version of the row; mainly just a count
modified_when | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | when has this row been committed (created/modified)
modified_by | name | | not null | CURRENT_USER | plain | | by whom has this row been committed (created/modified)
Indexe:
"audit_fields_pkey" PRIMARY KEY, btree (pk_audit)
Check-Constraints:
"audit_audit_fields_sane_modified_when" CHECK ((modified_when <= clock_timestamp()) IS TRUE)
Regeln:
audit_fields_no_del AS
ON DELETE TO audit.audit_fields DO INSTEAD NOTHING
audit_fields_no_ins AS
ON INSERT TO audit.audit_fields DO INSTEAD NOTHING
audit_fields_no_upd AS
ON UPDATE TO audit.audit_fields DO INSTEAD NOTHING
Kindtabellen: bill.bill,
bill.bill_item,
blobs.doc_desc,
blobs.doc_med,
blobs.lnk_doc2hospital_stay,
blobs.lnk_doc_med2episode,
cfg.report_query,
clin.allergy_state,
clin.clin_diag,
clin.clin_item_type,
clin.clin_root_item,
clin.encounter,
clin.episode,
clin.external_care,
clin.fhx_relation_type,
clin.form_data,
clin.health_issue,
clin.incoming_data_unmatchable,
clin.incoming_data_unmatched,
clin.lnk_code2item_root,
clin.lnk_constraint2vacc_course,
clin.lnk_pat2vaccination_course,
clin.lnk_substance2episode,
clin.lnk_tst2norm,
clin.lnk_type2item,
clin.lnk_vaccination_course2schedule,
clin.lnk_vaccine2inds,
clin.patient,
clin.review_root,
clin.suppressed_hint,
clin.test_org,
clin.test_panel,
clin.test_type,
clin.vaccination_course,
clin.vaccination_course_constraint,
clin.vaccination_definition,
clin.vaccination_schedule,
clin.vacc_indication,
clin.vaccine,
clin.vaccine_batches,
clin.vacc_route,
clin.waiting_list,
de_de.beh_fall_typ,
de_de.lab_test_gnr,
de_de.prax_geb_paid,
dem.address,
dem.gender_label,
dem.identity,
dem.identity_tag,
dem.inbox_item_category,
dem.inbox_item_type,
dem.lnk_identity2ext_id,
dem.lnk_job2person,
dem.lnk_org_unit2comm,
dem.lnk_org_unit2ext_id,
dem.lnk_person2relative,
dem.message_inbox,
dem.occupation,
dem.org,
dem.org_unit,
dem.praxis_branch,
dem.region,
dem.relation_types,
dem.staff,
dem.street,
dem.urb,
gm.access_log,
ref.auto_hint,
ref.branded_drug,
ref.consumable_substance,
ref.data_source,
ref.lnk_substance2brand,
ref.paperwork_templates,
ref.tag_image
[text/plain] ref.auto_hint.txt (4.4K, ../20181101154113.GH2954@hermes.hilbert.loc/3-ref.auto_hint.txt)
download | inline:
Ausgabeformat ist »wrapped«.
Tabelle »ref.auto_hint«
Spalte | Typ | Sortierfolge | NULL erlaubt? | Vorgabewert | Speicherung | Statistikziel | Beschreibung
----------------------+--------------------------+--------------+---------------+------------------------------------------------------+-------------+---------------+------------------------------------------------------------------------
pk_audit | integer | | not null | nextval('audit.audit_fields_pk_audit_seq'::regclass) | plain | |
row_version | integer | | not null | 0 | plain | |
modified_when | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | |
modified_by | name | | not null | CURRENT_USER | plain | |
pk | integer | | not null | nextval('ref.auto_hint_pk_seq'::regclass) | plain | |
query | text | | | | extended | | This query is run against the database.
title | text | | | | extended | | A short title to summarize and identify the hint.
hint | text | | | | extended | | When the query returns true this is the hint that should be displayed.
url | text | | | | extended | | An URL relevant to the hint.
is_active | boolean | | not null | true | plain | | Whether or not this query/hint is active.
source | text | | | | extended | | Who provided query and hint.
lang | text | | | | extended | | The language the hint is written in.
recommendation_query | text | | | | extended | |
Indexe:
"auto_hint_pkey" PRIMARY KEY, btree (pk)
"ref_auto_hint_uniq_query" UNIQUE CONSTRAINT, btree (query)
"ref_auto_hint_uniq_title" UNIQUE CONSTRAINT, btree (title)
Check-Constraints:
"audit_audit_fields_sane_modified_when" CHECK ((modified_when <= clock_timestamp()) IS TRUE)
"ref_auto_hint_sane_hint" CHECK (gm.is_null_or_blank_string(hint) IS FALSE)
"ref_auto_hint_sane_lang" CHECK (gm.is_null_or_blank_string(lang) IS FALSE)
"ref_auto_hint_sane_query" CHECK (gm.is_null_or_blank_string(query) IS FALSE)
"ref_auto_hint_sane_rec_query" CHECK (gm.is_null_or_non_empty_string(recommendation_query))
"ref_auto_hint_sane_source" CHECK (gm.is_null_or_blank_string(source) IS FALSE)
"ref_auto_hint_sane_title" CHECK (gm.is_null_or_blank_string(title) IS FALSE)
"ref_auto_hint_sane_url" CHECK (gm.is_null_or_non_empty_string(url))
Fremdschlüsselverweise von:
TABLE "clin.suppressed_hint" CONSTRAINT "fk_clin_suppressed_hint_fk_hint" FOREIGN KEY (fk_hint) REFERENCES ref.auto_hint(pk) ON UPDATE RESTRICT ON DELETE CASCADE
Trigger:
zt_del_auto_hint BEFORE DELETE ON ref.auto_hint FOR EACH ROW EXECUTE PROCEDURE audit.ft_del_auto_hint()
zt_ins_auto_hint BEFORE INSERT ON ref.auto_hint FOR EACH ROW EXECUTE PROCEDURE audit.ft_ins_auto_hint()
zt_upd_auto_hint BEFORE UPDATE ON ref.auto_hint FOR EACH ROW EXECUTE PROCEDURE audit.ft_upd_auto_hint()
Erbt von: audit.audit_fields
[text/plain] clin.suppressed_hint.txt (4.6K, ../20181101154113.GH2954@hermes.hilbert.loc/4-clin.suppressed_hint.txt)
download | inline:
Ausgabeformat ist »wrapped«.
Tabelle »clin.suppressed_hint«
Spalte | Typ | Sortierfolge | NULL erlaubt? | Vorgabewert | Speicherung | Statistikziel | Beschreibung
-----------------+--------------------------+--------------+---------------+------------------------------------------------------+-------------+---------------+-----------------------------------------------------------
pk_audit | integer | | not null | nextval('audit.audit_fields_pk_audit_seq'::regclass) | plain | |
row_version | integer | | not null | 0 | plain | |
modified_when | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | |
modified_by | name | | not null | CURRENT_USER | plain | |
pk | integer | | not null | nextval('clin.suppressed_hint_pk_seq'::regclass) | plain | |
fk_encounter | integer | | not null | | plain | | the encounter during which this hint was first suppressed
fk_hint | integer | | not null | | plain | | the hint that is suppressed
suppressed_by | name | | not null | CURRENT_USER | plain | | who suppressed this hint
suppressed_when | timestamp with time zone | | not null | statement_timestamp() | plain | | when was this hint suppressed
rationale | text | | | | extended | | rationale on why this hint is suppressed in this patient
md5_sum | text | | | | extended | | md5 of relevant fields of this hint
Indexe:
"suppressed_hint_pkey" PRIMARY KEY, btree (pk)
"idx_suppressed_hint_fk_encounter" btree (fk_encounter)
"idx_suppressed_hint_fk_hint" btree (fk_hint)
Check-Constraints:
"audit_audit_fields_sane_modified_when" CHECK ((modified_when <= clock_timestamp()) IS TRUE)
"clin_suppressed_hint_sane_by" CHECK (length(suppressed_by::text) > 0)
"clin_suppressed_hint_sane_md5" CHECK (gm.is_null_or_blank_string(md5_sum) IS FALSE)
"clin_suppressed_hint_sane_rationale" CHECK (gm.is_null_or_blank_string(rationale) IS FALSE)
Fremdschlüssel-Constraints:
"fk_clin_suppressed_hint_fk_encounter" FOREIGN KEY (fk_encounter) REFERENCES clin.encounter(pk) ON UPDATE RESTRICT ON DELETE RESTRICT
"fk_clin_suppressed_hint_fk_hint" FOREIGN KEY (fk_hint) REFERENCES ref.auto_hint(pk) ON UPDATE RESTRICT ON DELETE CASCADE
Trigger:
tr_sanity_check_uniq_hint_per_pat_ins_upd AFTER INSERT OR UPDATE ON clin.suppressed_hint DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE clin.trf_sanity_check_uniq_hint_per_pat_ins_upd()
zt_del_suppressed_hint BEFORE DELETE ON clin.suppressed_hint FOR EACH ROW EXECUTE PROCEDURE audit.ft_del_suppressed_hint()
zt_ins_suppressed_hint BEFORE INSERT ON clin.suppressed_hint FOR EACH ROW EXECUTE PROCEDURE audit.ft_ins_suppressed_hint()
zt_upd_suppressed_hint BEFORE UPDATE ON clin.suppressed_hint FOR EACH ROW EXECUTE PROCEDURE audit.ft_upd_suppressed_hint()
zzz_tr_announce_clin_suppressed_hint_del AFTER DELETE ON clin.suppressed_hint DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE gm.trf_announce_table_del('operation=DELETE::table=clin.suppressed_hint::PK name=pk', 'select $1.pk', 'select fk_patient from clin.encounter where pk = $1.fk_encounter limit 1')
zzz_tr_announce_clin_suppressed_hint_ins_upd AFTER INSERT OR UPDATE ON clin.suppressed_hint DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE gm.trf_announce_table_ins_upd('table=clin.suppressed_hint::PK name=pk', 'select $1.pk', 'select fk_patient from clin.encounter where pk = $1.fk_encounter limit 1')
Erbt von: audit.audit_fields
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: Karsten.Hilbert@gmx.net, pgsql-general@lists.postgresql.org
Subject: Re: backend crash on DELETE, reproducible locally
In-Reply-To: <20181101154113.GH2954@hermes.hilbert.loc>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox