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 1kmMAN-0000ic-8c for pgsql-general@arkaria.postgresql.org; Mon, 07 Dec 2020 19:27:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kmMAM-0004Zm-7T for pgsql-general@arkaria.postgresql.org; Mon, 07 Dec 2020 19:27:22 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kmMAL-0004ZS-W8 for pgsql-general@lists.postgresql.org; Mon, 07 Dec 2020 19:27:21 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kmMAI-0000fK-PY for pgsql-general@lists.postgresql.org; Mon, 07 Dec 2020 19:27:21 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 0B7JRG663144565; Mon, 7 Dec 2020 14:27:16 -0500 From: Tom Lane To: Ron cc: pgsql-general@lists.postgresql.org Subject: Re: Deferrable FK not behaving as expected. In-reply-to: <3c32cc9c-1589-7f22-b9e3-f4bcccbaf87e@gmail.com> References: <75fe0761-a291-86a9-c8d8-4906da077469@gmail.com> <3c32cc9c-1589-7f22-b9e3-f4bcccbaf87e@gmail.com> Comments: In-reply-to Ron message dated "Mon, 07 Dec 2020 12:30:48 -0600" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <3144563.1607369236.1@sss.pgh.pa.us> Date: Mon, 07 Dec 2020 14:27:16 -0500 Message-ID: <3144564.1607369236@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Ron writes: > Neither technique worked. > test=# ALTER TABLE sales_detail ALTER CONSTRAINT > fk_sales_detail_sales_header DEFERRABLE INITIALLY DEFERRED; > ALTER TABLE Hmm, reproduced here. There seems to be some kind of bug in ALTER CONSTRAINT --- it looks like it's updated all the child FKs successfully, but it actually hasn't. If you drop the FK constraint altogether, and recreate it having DEFERRABLE INITIALLY DEFERRED from the start, it should work --- at least it does for me. regards, tom lane