From: Tom Lane <tgl@sss.pgh.pa.us>
To: cbw <cbwhitebu@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Backend stuck in tirigger.c:afterTriggerInvokeEvents forever
Date: Tue, 21 Apr 2020 10:44:25 -0400
Message-ID: <26195.1587480265@sss.pgh.pa.us> (raw)
In-Reply-To: <CANM0TiRMfrA-5Rqa3gR_CEgrRjAf8v-6QjW1V7r2mh0hu4dWKg@mail.gmail.com>
References: <CANM0TiRMfrA-5Rqa3gR_CEgrRjAf8v-6QjW1V7r2mh0hu4dWKg@mail.gmail.com>
cbw <cbwhitebu@gmail.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
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-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, cbwhitebu@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Backend stuck in tirigger.c:afterTriggerInvokeEvents forever
In-Reply-To: <26195.1587480265@sss.pgh.pa.us>
* 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