Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gIF62-0000Os-6r for pgsql-general@arkaria.postgresql.org; Thu, 01 Nov 2018 15:41:22 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gIF60-0002uR-6u for pgsql-general@arkaria.postgresql.org; Thu, 01 Nov 2018 15:41:20 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gIF5z-0002uK-TU for pgsql-general@lists.postgresql.org; Thu, 01 Nov 2018 15:41:19 +0000 Received: from mout.gmx.net ([212.227.17.20]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gIF5x-0003K7-Nz for pgsql-general@lists.postgresql.org; Thu, 01 Nov 2018 15:41:19 +0000 Received: from hermes ([84.165.111.43]) by mail.gmx.com (mrgmx103 [212.227.17.168]) with ESMTPSA (Nemesis) id 0LhwAY-1fn2wj06q2-00n6s5 for ; Thu, 01 Nov 2018 16:41:15 +0100 Received: from hermes ([84.165.111.43]) by mail.gmx.com (mrgmx103 [212.227.17.168]) with ESMTPSA (Nemesis) id 0LhwAY-1fn2wj06q2-00n6s5 for ; Thu, 01 Nov 2018 16:41:15 +0100 Received: from ncq by hermes with local (Exim 4.91) (envelope-from ) id 1gIF5t-0006CP-L1 for pgsql-general@lists.postgresql.org; Thu, 01 Nov 2018 16:41:13 +0100 Date: Thu, 1 Nov 2018 16:41:13 +0100 From: Karsten Hilbert To: pgsql-general@lists.postgresql.org Subject: Re: backend crash on DELETE, reproducible locally Message-ID: <20181101154113.GH2954@hermes.hilbert.loc> References: <20181101152115.GF2954@hermes.hilbert.loc> <20181101152723.zq2dtcgntmcmr4br@alvherre.pgsql> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="32u276st3Jlj2kUU" Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <20181101152723.zq2dtcgntmcmr4br@alvherre.pgsql> Ma_X_il-Followup-to: d Re_X_turn-receipt-to: Karsten.Hilbert@gmx.net Di_X_sposition-Notification-To: Karsten.Hilbert@gmx.net X-Confi_X_rm-Reading-To: Karsten.Hilbert@gmx.net X-Pri_X_ority: 2 (High) User-Agent: Mutt/1.10.1 (2018-07-13) Sender: X-Provags-ID: V03:K1:sjS4ozhpW1JWbXTWWURFmycwvLYcseVKjIYBS0KMklmVbn9uBSH kP38rG64IUDBub3epEKuNKArnOsHp7sZgfQgR9AVafQ575OlSx/5ZYiBg/zF48x3ICqNtl0 8qhatdsRa8DnU5/Z60GawBwwa5fvUOWi6v84M3Uxle9jJx32rQCQkkpk3mTefPRr7MaKb4e Eo0fpzyCIRHRWsvR8U4Vg== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V01:K0:dBJ7Hu/9mRg=:WqOJ0N8+nNORhoDEbcExPt TtrizQhRoHe/gjGB1zelNb27H25x3HrZ/BR7NUSU4EQuR6OHx1m3JKXqtt2biVmKykmhgSgxV GbqaAv9t8huUvgDIbAfz8ihYqI3hOBdPrBMN46Q6wd/UHcGGpx6CeTb7fSa6unlnjIMRK8KhP I4LtN9PD2c5u/zSJXFvnxR6nVfRBgaffkAjJYIuUr3LqQJ0WlSfo3wSnz9Mw5C34irmCjrmAa +VJeiObbpncpojDSuP8WJ/1UpuzQkU6phICov2MX3Q1YS8qUwGFP1pyK0kRLtQhMzWYP8cZ7/ lvPxGF2cJtzdj3PGSq4EcHjHxbCBsJbvyc7KHYEGlj/3pDLj+K8WJSG2CdTF+BOcc711pioJa EejotUlvXFMz8XWUTqi/+jqjTpC4jaJBYb4WEsT9z2+4MazyE+6wsuf1C2nO75pa9xW5EQqPH apdHfnDTCP6o/iNkQe7mupdVa3wxPoAMNSAQ+x/GRW+LOyDXnNa5aMmxozNKZ32og8aUPKhm1 MV6iTYxomJ5d8UesVJdZQZcy1O7ZTxTU4X58IozylCMwsNcVH3mOqA4fXX6dR9v2Dr+3T8jHh JfCFHlH5uYtFivBWb8RsPSBJnyMPcyo6fUY3fMKUyqr03mOD7lZvgS3OwSY/NHQ3IAkW1HpBf vpmsy1dNCbU12uSIg+SsjE+n+8tAgQJYH1tG03tdCIu6f5z+OzFLxj59xwclODboZKiojEtvy Zf9a7Mecx2k1RqvB6zssillIxnAQADPuU+VyIHQbW39sdrTrX58Y2qwkYVEhURPX2S+sTEA+u DnVlPWgTmk/LMI0KvcoGv+QVN6ANTYCz0kAyzoCv0rSfN2Gh2LgggxUECWe2FApO71vFQeXgg YVukk+tUIcIU5BDzd0H4KYQYpzE5GSPg9XNBCeSEW/h4nDBtIlRYwAp7rP1j/a List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --32u276st3Jlj2kUU Content-Type: text/plain; charset=us-ascii Content-Disposition: inline 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 --32u276st3Jlj2kUU Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: attachment; filename="audit.audit_fields.txt" Content-Transfer-Encoding: 8bit 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 --32u276st3Jlj2kUU Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: attachment; filename="ref.auto_hint.txt" Content-Transfer-Encoding: 8bit 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 --32u276st3Jlj2kUU Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: attachment; filename="clin.suppressed_hint.txt" Content-Transfer-Encoding: 8bit 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 --32u276st3Jlj2kUU--