Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jQu8V-0005Dg-IP for pgsql-bugs@arkaria.postgresql.org; Tue, 21 Apr 2020 14:44:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jQu8T-00083H-PY for pgsql-bugs@arkaria.postgresql.org; Tue, 21 Apr 2020 14:44:29 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jQu8T-00083A-Jj for pgsql-bugs@lists.postgresql.org; Tue, 21 Apr 2020 14:44:29 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jQu8R-0006Dn-DH for pgsql-bugs@lists.postgresql.org; Tue, 21 Apr 2020 14:44:28 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id 03LEiPWA026196; Tue, 21 Apr 2020 10:44:25 -0400 From: Tom Lane To: cbw cc: pgsql-bugs@lists.postgresql.org Subject: Re: Backend stuck in tirigger.c:afterTriggerInvokeEvents forever In-reply-to: References: Comments: In-reply-to cbw message dated "Mon, 20 Apr 2020 21:07:49 -0700" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <26194.1587480265.1@sss.pgh.pa.us> Date: Tue, 21 Apr 2020 10:44:25 -0400 Message-ID: <26195.1587480265@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk cbw 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