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 1kmOEp-0006GH-NA for pgsql-general@arkaria.postgresql.org; Mon, 07 Dec 2020 21:40:07 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kmOEo-0004xE-MM for pgsql-general@arkaria.postgresql.org; Mon, 07 Dec 2020 21:40:06 +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 1kmOEo-0004x6-FD for pgsql-general@lists.postgresql.org; Mon, 07 Dec 2020 21:40:06 +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 1kmOEm-0001ji-GB for pgsql-general@lists.postgresql.org; Mon, 07 Dec 2020 21:40:06 +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 0B7Le22u3159382; Mon, 7 Dec 2020 16:40:02 -0500 From: Tom Lane To: Ron cc: pgsql-general@lists.postgresql.org Subject: Re: Deferrable FK not behaving as expected. In-reply-to: <9c844ed1-e9e3-57c9-3f34-3b09479078e6@gmail.com> References: <75fe0761-a291-86a9-c8d8-4906da077469@gmail.com> <3c32cc9c-1589-7f22-b9e3-f4bcccbaf87e@gmail.com> <3144564.1607369236@sss.pgh.pa.us> <9c844ed1-e9e3-57c9-3f34-3b09479078e6@gmail.com> Comments: In-reply-to Ron message dated "Mon, 07 Dec 2020 15:19:33 -0600" MIME-Version: 1.0 Content-Type: text/plain; charset="UTF-8" Content-ID: <3159380.1607377202.1@sss.pgh.pa.us> Content-Transfer-Encoding: 8bit Date: Mon, 07 Dec 2020 16:40:02 -0500 Message-ID: <3159381.1607377202@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Ron writes: > That works *sometimes*, but not when we *really* want it to work. > test=# begin transaction; > BEGIN > test=# ALTER TABLE sales_header DETACH PARTITION sales_header_202001; > ERROR:  removing partition "sales_header_202001" violates foreign key > constraint "sales_detail_cust_id_order_ts_fkey" That I think you're gonna have to live with. DETACH PARTITION isn't something we can defer to end of transaction. regards, tom lane