Re: Backend stuck in tirigger.c:afterTriggerInvokeEvents forever - Mailing list pgsql-bugs

This turned out to be the result of outdated table statistics.

We were adding a large number of rows to a table then adding a large
number of rows to a table that referenced the first table. Because the
table statistics were so wrong we ended up with triggers, etc using
catastrophically bad execution plans.

The fix was to add an 'analyze first_table' statement after adding the
rows to the first table.

Many thanks to Christophe Pettus of PGExperts for solving this issue.

On Mon, Apr 20, 2020 at 9:07 PM cbw <cbwhitebu@gmail.com> wrote:
>
> 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.
>
> I have tried this in version 11.7 and 12.2 (on both Linux and Windows).
>
> I grabbed the source from git and did a Linux build (REL_12_STABLE)
> and can see that the method in question just keeps looping through
> 'chunks' and 'events'. I disabled the user triggers on the table in
> question and the 'events' seem to be various foreign key references
> and other constraints.
>
> I bumped the log level up to debug5 and can see rows in the first
> table getting inserted. Sometimes I see a few rows for the second
> table and sometimes I don't. After that there are no additional log
> entries from the backend process. I have let the process run overnight
> (the inserts take about 12 minutes to complete when run
> independently).
>
> Any tips about how to go about debugging this would be appreciated. I
> am struggling to see exactly what the events are so maybe so
> suggestions on the best place to add some logging?
>
> Here is the DO block:
>
> DO
> $$
>     BEGIN
>         PERFORM XeP_set_identifier('xi_batch_user');
>
>         alter table xe_patient_visit disable trigger USER;
>         alter table xe_auth disable trigger USER;
>
>         RAISE NOTICE '% : inserting visits...', clock_timestamp();
>
>         INSERT INTO
>             xe_patient_visit
>         (
>             createtstamp,
>             creationuser,
>             modifiedtstamp,
>             modifieduser,
>             active,
>             visitid,
>             sourcesystem,
>             status,
>             visittypeid,
>             ipid,
>             accountid,
>             ivid
>         )
>         SELECT (now() AT TIME ZONE 'utc') AS createtstamp,
>                'xi_batch_user' AS creationuser,
>                (now() AT TIME ZONE 'utc') AS modifiedtstamp,
>                'xi_batch_user' AS modifieduser,
>                'y' AS active,
>                authorizationid AS visitid,
>                'staging' AS sourcesystem,
>                a.status,
>                'AUTH' AS visittypeid,
>                p.ipid,
>                e.accountid,
>                nextval('seq_xe_patient_visit') AS ivid
>         FROM (SELECT authorizationid,
>                      memberid,
>                      CASE
>                          WHEN authorizationstatus = 'Fully Approved'
> THEN 'AUTH_APPROVED'
>                          WHEN authorizationstatus = 'Partially
> Approved' THEN 'AUTH_REDUCED'
>                          WHEN authorizationstatus = 'Voided' THEN
> 'AUTH_COMPLETED'
>                          WHEN authorizationstatus = 'Incomplete' THEN
> 'AUTH_PEND'
>                          WHEN authorizationstatus = 'Pending Decision'
> THEN 'AUTH_PEND'
>                          WHEN authorizationstatus = 'Denied' THEN
> 'AUTH_DENIED' END
>
>                         AS status,
>                      row_number() OVER (PARTITION BY authorizationid
> ORDER BY authorizationid) AS rownum
>               FROM staging."authorization") a
>                  JOIN xe_patient p ON p.patientid = a.memberid
>                  JOIN xe_enterprise_data e ON e.accountid =
> p.accountid AND e.enterpriseid = 'staging'
>         WHERE rownum = 1
>         ON CONFLICT (accountid, visitid)
>             DO NOTHING;
>
>
>         RAISE NOTICE '% : inserting auths...', clock_timestamp();
>         INSERT INTO
>             xe_auth
>         (
>             createtstamp,
>             creationuser,
>             modifiedtstamp,
>             modifieduser,
>             active,
>             accountid,
>             receiveddate,
>             authnum,
>             authtypeid,
>             authsubtypeid,
>             umurgencyid,
>             ivid
>         )
>         SELECT (now() AT TIME ZONE 'utc') AS createtstamp,
>                'xi_batch_user' AS creationuser,
>                (now() AT TIME ZONE 'utc') AS modifiedtstamp,
>                'xi_batch_user' AS modifieduser,
>                'y' AS active,
>                ed.accountid,
>                receiveddate,
>                authnum,
>                a.authtypeid,
>                at.authtypeid,
>                umurgencyid,
>                ivid
>         FROM (
>                  SELECT cast(receiveddate AS timestamp) AS receiveddate,
>                         authorizationid AS authnum,
>                         CASE
>                             WHEN authorizationcategory = 'Inpatient'
>                                 THEN 'AUTH_IPA'
>                             ELSE 'AUTH_SVC' END AS authtypeid,
>                         authorizationtype,
>                         CASE
>                             WHEN authorizationurgency = 'ROUTINE' THEN 'STD'
>                             WHEN authorizationurgency = 'EXPEDITED' THEN 'EXP'
>                             END
>                                                         AS umurgencyid
>                  FROM staging."authorization"
>              ) a
>                  JOIN xe_patient_visit v ON v.visitid = a.authnum
>                  JOIN xe_enterprise_data ed ON ed.accountid =
> v.accountid AND ed.enterpriseid = 'staging'
>                  JOIN xe_auth_type at ON at.name = a.authorizationtype
>         ON CONFLICT (authnum, accountid)
>             DO NOTHING;
>
>     END
> $$