Re: Backend stuck in tirigger.c:afterTriggerInvokeEvents forever

Tom,

Thanks for that feedback.

I have tried adding a commit after the first insert but the problem
still persists so I don't think this problem is related to transaction
boundaries.

I am seeing this same problem with other table inserts as well.

My current hypothesis is that this bug happens when I am inserting
data that references rows in another table that I just inserted in the
same DO block. I don't know yet whether it only happens with the large
data sets I am processing.

I have also tried creating a procedure for the same code instead of
the DO block and the problem happens there as well.

This is a huge schema but I have included the definitions of the two
table in my examples. As you can see the second table references the
primary key of the first table (see constraint xe_auth_f5). There are
no deferred constraints.

--- first table-----
create table xe_patient_visit
(
accountid numeric(10) not null,
createtstamp timestamp not null,
creationuser varchar(60) not null,
modifiedtstamp timestamp not null,
modifieduser varchar(60) not null,
active varchar(1) default 'y'::character varying not null
constraint xe_patient_visit_active_check
check ((active)::text = ANY (ARRAY[('y'::character
varying)::text, ('n'::character varying)::text])),
iscmgmtpatient varchar(1) default 'n'::character varying not null
constraint xe_patient_visit_iscmgmtpatient_check
check ((iscmgmtpatient)::text = ANY (ARRAY[('y'::character
varying)::text, ('n'::character varying)::text])),
isinerror varchar(1) default 'n'::character varying not null
constraint xe_patient_visit_isinerror_check
check ((isinerror)::text = ANY (ARRAY[('y'::character
varying)::text, ('n'::character varying)::text])),
ismlc varchar(1) default 'n'::character varying not null
constraint xe_patient_visit_ismlc_check
check ((ismlc)::text = ANY (ARRAY[('y'::character
varying)::text, ('n'::character varying)::text])),
isreadmit varchar(1) default 'n'::character varying not null
constraint xe_patient_visit_isreadmit_check
check ((isreadmit)::text = ANY (ARRAY[('y'::character
varying)::text, ('n'::character varying)::text, ('u'::character
varying)::text])),
isvisitlocked varchar(1) default 'n'::character varying not null
constraint xe_patient_visit_isvisitlocked_check
check ((isvisitlocked)::text = ANY (ARRAY[('y'::character
varying)::text, ('n'::character varying)::text])),
iswalkin varchar(1) default 'n'::character varying not null
constraint xe_patient_visit_iswalkin_check
check ((iswalkin)::text = ANY (ARRAY[('y'::character
varying)::text, ('n'::character varying)::text])),
needsbed varchar(1) default 'n'::character varying not null
constraint xe_patient_visit_needsbed_check
check ((needsbed)::text = ANY (ARRAY[('y'::character
varying)::text, ('n'::character varying)::text])),
accidentdatetime timestamp,
accidentdatetimesrv timestamp,
authorizedadmitdate date,
clearancedatetime timestamp,
clearancedatetimesrv timestamp,
closedate timestamp,
closedatesrv timestamp,
enddate timestamp,
enddatesrv timestamp,
expectedenddate timestamp,
expectedenddatesrv timestamp,
expireattemptdatetime timestamp,
mergeverifieddate timestamp,
prioradmissiondate date,
referringletterdate date,
requestedadmitdate date,
startdate timestamp,
startdatesrv timestamp,
accidentdatetimeoff varchar(5),
accidentstate varchar(100),
accidenttypeid varchar(32)
constraint xe_patient_visit_f0
references xg_svc_code_catalog,
admitcategoryid varchar(16)
constraint xe_patient_visit_f1
references xe_admit_cat,
bedclassid varchar(32),
bedtext varchar(100),
clearancedatetimeoff varchar(5),
closecomment varchar(200),
closedateoff varchar(5),
closeusername varchar(60),
comments varchar(200),
dischargeinstruction varchar(2000),
dischargelocation varchar(100),
displayroombed varchar(50),
enddateoff varchar(5),
errordescription varchar(1024),
expectedenddateoff varchar(5),
expectedlengthofstay varchar(16),
expectedlengthofstayunits varchar(64)
constraint xe_patient_visit_f10
references xe_unit,
mlcdescription varchar(500),
mergecomments varchar(200),
mergeverifiedby varchar(60),
patientstatus varchar(16),
performlocation varchar(100),
priordiagnosisdesc varchar(200),
reasonfordischarge varchar(100),
reasonforvisit varchar(256),
referralsourceid varchar(20),
referraltype varchar(20),
referringhospitalname varchar(200),
referringhospitalpatientid varchar(100),
referringlettercontext varchar(200),
referringletterid varchar(200),
referringphysicianname varchar(200),
referringphysicianphoneno varchar(50),
sourcesystem varchar(100) default 'SYNERGY'::character varying not null,
startdateoff varchar(5),
status varchar(16)
constraint xe_patient_visit_f24
references xe_visit_status,
statusreasonid varchar(16)
constraint xe_patient_visit_f25
references xe_visit_status,
statusreasontext varchar(200),
transferstatus varchar(32),
visitid varchar(30) not null,
visittypeid varchar(16) not null
constraint xe_patient_visit_f27
references xe_visit_type,
bedid numeric(18),
clinicid numeric(18),
dischargedisposition numeric(18)
constraint xe_patient_visit_f5
references xg_clinical_ref_data,
dischargeorderowner numeric(18)
constraint xe_patient_visit_f6
references xe_resource,
dischargereasonid numeric(18),
dischargetounitid numeric(18),
dischargetype numeric(18)
constraint xe_patient_visit_f9
references xg_clinical_ref_data,
facilityid numeric(18),
hcu numeric(18),
ipid numeric(18) not null,
incomingconditionid numeric(18)
constraint xe_patient_visit_f14
references xg_clinical_ref_data,
locationid numeric(18)
constraint xe_patient_visit_f15
references xe_location,
mergesourceipid numeric(18),
orgunitid numeric(18),
parentid numeric(18),
patientmrdid numeric(18),
patienttypeid numeric(18),
previousivid numeric(18),
readmitdays numeric(3),
readmitivid numeric(18),
restrictvalue numeric(5) default '1'::numeric not null,
servicebookingid numeric(18),
servicecenterid numeric(18),
serviceid numeric(18)
constraint xe_patient_visit_f22
references xg_svc_base,
sourceofadmitid numeric(18)
constraint xe_patient_visit_f23
references xg_pos,
typeofcareid numeric(18)
constraint xe_patient_visit_f26
references xg_clinical_ref_data,
workid numeric(18),
ivid numeric(18) not null,
constraint pk_xe_patient_visit
primary key (ivid, accountid),
constraint xe_patient_visit_eu
unique (visitid, accountid),
constraint xe_patient_visit_f11
foreign key (facilityid, accountid) references xe_facility,
constraint xe_patient_visit_f12
foreign key (hcu, accountid) references xe_org_unit,
constraint xe_patient_visit_f13
foreign key (ipid, accountid) references xe_patient,
constraint xe_patient_visit_f16
foreign key (mergesourceipid, accountid) references xe_patient,
constraint xe_patient_visit_f17
foreign key (orgunitid, accountid) references xe_org_unit,
constraint xe_patient_visit_f18
foreign key (patientmrdid, accountid) references xe_patient_mrd,
constraint xe_patient_visit_f19
foreign key (patienttypeid, accountid) references xe_patient_type,
constraint xe_patient_visit_f2
foreign key (bedclassid, accountid) references xe_bed_class,
constraint xe_patient_visit_f20
foreign key (servicebookingid, accountid) references xe_srvc_booking,
constraint xe_patient_visit_f21
foreign key (servicecenterid, accountid) references xe_org_unit,
constraint xe_patient_visit_f28
foreign key (workid, accountid) references xe_workflow_inst,
constraint xe_patient_visit_f3
foreign key (bedid, accountid) references xe_bed,
constraint xe_patient_visit_f4
foreign key (clinicid, accountid) references xe_org_unit,
constraint xe_patient_visit_f7
foreign key (dischargereasonid, accountid) references
xe_discharge_reason,
constraint xe_patient_visit_f8
foreign key (dischargetounitid, accountid) references xe_org_unit
);

alter table xe_patient_visit owner to xowner;

create index xe_patient_visit_12
on xe_patient_visit (startdate, accountid, status, visittypeid);

create index xe_patient_visit_13
on xe_patient_visit (accountid, status, visittypeid, ivid, enddate);

create index xe_patient_visit_14
on xe_patient_visit (enddate, hcu, accountid);

create index xe_patient_visit_15
on xe_patient_visit (servicebookingid, accountid);

create index xe_patient_visit_16
on xe_patient_visit (parentid, accountid, ivid);

create index xe_patient_visit_i1
on xe_patient_visit (ipid, accountid);

create trigger xe_patient_visit_ht
after insert or update or delete
on xe_patient_visit
for each row
execute procedure xep_xe_patient_visit();

create trigger xe_patient_visit_xe_auth_te
after insert or update
on xe_patient_visit
for each row
execute procedure xep_xe_patient_visit_xe_auth_tef();

create trigger xe_patient_visit_xe_patient_issue_te
after insert or update
on xe_patient_visit
for each row
execute procedure xep_xe_patient_visit_xe_patient_issue_tef();

create trigger xe_patient_visit_xe_visit_case_mgmt_pgm_te
after insert or update
on xe_patient_visit
for each row
execute procedure xep_xe_patient_visit_xe_visit_case_mgmt_pgm_tef();

create trigger xe_patient_visit_xe_visit_case_mgmt_te
after insert or update
on xe_patient_visit
for each row
execute procedure xep_xe_patient_visit_xe_visit_case_mgmt_tef();

--- second table ---
create table xe_auth
(
accountid numeric(10) not null,
createtstamp timestamp not null,
creationuser varchar(60) not null,
modifiedtstamp timestamp not null,
modifieduser varchar(60) not null,
active varchar(1) default 'y'::character varying not null
constraint xe_auth_active_check
check ((active)::text = ANY (ARRAY[('y'::character
varying)::text, ('n'::character varying)::text])),
additionalcomments varchar(4000),
isappeal varchar(1) default 'n'::character varying not null
constraint xe_auth_isappeal_check
check ((isappeal)::text = ANY (ARRAY[('y'::character
varying)::text, ('n'::character varying)::text])),
isextended varchar(1) default 'n'::character varying not null
constraint xe_auth_isextended_check
check ((isextended)::text = ANY (ARRAY[('y'::character
varying)::text, ('n'::character varying)::text])),
ispatientreq varchar(1) default 'n'::character varying not null
constraint xe_auth_ispatientreq_check
check ((ispatientreq)::text = ANY (ARRAY[('y'::character
varying)::text, ('n'::character varying)::text])),
ispreauth varchar(1) default 'n'::character varying not null
constraint xe_auth_ispreauth_check
check ((ispreauth)::text = ANY (ARRAY[('y'::character
varying)::text, ('n'::character varying)::text])),
isreopen varchar(1) default 'n'::character varying not null
constraint xe_auth_isreopen_check
check ((isreopen)::text = ANY (ARRAY[('y'::character
varying)::text, ('n'::character varying)::text])),
isselfreferral varchar(1) default 'n'::character varying not null
constraint xe_auth_isselfreferral_check
check ((isselfreferral)::text = ANY (ARRAY[('y'::character
varying)::text, ('n'::character varying)::text])),
medicalnecessity varchar(4000),
needsccr varchar(1) default 'y'::character varying not null
constraint xe_auth_needsccr_check
check ((needsccr)::text = ANY (ARRAY[('y'::character
varying)::text, ('n'::character varying)::text])),
approveddate timestamp,
cancelleddate timestamp,
compliancedate timestamp,
denieddate timestamp,
duedate timestamp,
expectedbydate timestamp,
expirationdate timestamp,
nextreviewdate timestamp,
outboundqueuedtime timestamp,
receiveddate timestamp,
authnum varchar(9) not null,
authsubtypeid varchar(30)
constraint xe_auth_f0
references xe_auth_type,
authtypeid varchar(30) not null
constraint xe_auth_f1
references xe_auth_type,
denialcode varchar(60),
deniedbyusername varchar(60),
guidelines varchar(200),
pricingnote varchar(100),
primaryusername varchar(60),
umurgencyid varchar(32)
constraint xe_auth_f13
references xg_um_urgency,
allowedlos numeric(3),
authorizedlos numeric(3),
denialreasonid numeric(18)
constraint xe_auth_f2
references xg_clinical_ref_data,
expediterequestortypeid numeric(18)
constraint xe_auth_f4
references xg_clinical_ref_data,
owner numeric(18),
requestedlos numeric(3),
requestedposid numeric(18)
constraint xe_auth_f8
references xg_pos,
restrictvalue numeric(5) default '1'::numeric not null,
servicetypeid numeric(18)
constraint xe_auth_f9
references xg_clinical_ref_data,
sourceofadmitid numeric(18)
constraint xe_auth_f10
references xg_pos,
treatmentcategoryid numeric(18)
constraint xe_auth_f11
references xg_clinical_ref_data,
typeofcareid numeric(18)
constraint xe_auth_f12
references xg_clinical_ref_data,
ivid numeric(18) not null,
constraint pk_xe_auth
primary key (ivid, accountid),
constraint xe_auth_eu
unique (authnum, accountid),
constraint xe_auth_f3
foreign key (deniedbyusername, accountid) references xe_user_data,
constraint xe_auth_f5
foreign key (ivid, accountid) references xe_patient_visit,
constraint xe_auth_f6
foreign key (owner, accountid) references xe_staff,
constraint xe_auth_f7
foreign key (primaryusername, accountid) references xe_user_data
);

alter table xe_auth owner to xowner;

create trigger xe_auth_ht
after insert or update or delete
on xe_auth
for each row
execute procedure xep_xe_auth();

create trigger xe_auth_xe_patient_visit_te
after insert or update
on xe_auth
for each row
execute procedure xep_xe_auth_xe_patient_visit_tef();

On Tue, Apr 21, 2020 at 7:44 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> cbw <cbwhitebu(at)gmail(dot)com> writes:
> > I have a DO block that has a couple of inserts (copying large amounts
> > of data from staging tables) that I am passing to the server using
> > JDBC,
> > When I execute the inserts independently, they work fine. But when I
> > submit them as part of the do block, the backend goes into
> > trigger.c:afterTriggerInvokeEvents and never returns. This happens
> > after the second insert starts running.
>
> Have you got deferred uniqueness or exclusion constraints on the
> target table?
>
> If so, perhaps a plausible theory is that when you submit the queries
> separately, the unique_key_recheck trigger is never fired at all --- but
> if they are in the same transaction, then recheck events get queued
> because the index can't tell whether the earlier row should be treated
> as committed. This requires some assumptions about the table schema
> (which you haven't shown us) but it's a bit hard to see why the second
> query would act differently in the two contexts otherwise.
>
> A variant of that theory is that foreign key trigger firings are being
> skipped in one case but not the other; but offhand I think those
> optimizations only apply to update/delete cases not inserts. Anyway
> that still requires some assumptions about moving parts that you
> haven't shown us.
>
> The short answer very likely is going to be that you need to perform
> the queries as separate transactions, or the second one drowns in
> trigger overhead. Tracing down exactly why might not be worth a
> lot of trouble.
>
> regards, tom lane