Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tuueD-00DrkI-Li for pgsql-performance@arkaria.postgresql.org; Wed, 19 Mar 2025 14:43:57 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tuueC-0060CK-48 for pgsql-performance@arkaria.postgresql.org; Wed, 19 Mar 2025 14:43:56 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tuueB-0060Bh-Bw for pgsql-performance@lists.postgresql.org; Wed, 19 Mar 2025 14:43:55 +0000 Received: from fhigh-b1-smtp.messagingengine.com ([202.12.124.152]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1tuue7-003mIZ-0B for pgsql-performance@postgresql.org; Wed, 19 Mar 2025 14:43:55 +0000 Received: from phl-compute-05.internal (phl-compute-05.phl.internal [10.202.2.45]) by mailfhigh.stl.internal (Postfix) with ESMTP id 8E1CA25401AC; Wed, 19 Mar 2025 10:43:49 -0400 (EDT) Received: from phl-mailfrontend-01 ([10.202.2.162]) by phl-compute-05.internal (MEProxy); Wed, 19 Mar 2025 10:43:49 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-transfer-encoding :content-type:content-type:date:date:feedback-id:feedback-id :from:from:in-reply-to:in-reply-to:message-id:mime-version :reply-to:subject:subject:to:to:x-me-proxy:x-me-sender :x-me-sender:x-sasl-enc; s=fm1; t=1742395429; x=1742481829; bh=D kkUC0QWZFvR+hJGkvjZAFll1IAfCrCt6V+9dSA0mDI=; b=paKaqAIVBnYEsk99n eJjeGhYHLWBSJZgcctTmKUDo0++XFq2hYnVQIVsWlseRJ2EcSl/7Gj7gQ9hTKJyR 3yqr1FOZhVmA/HMkns/VVBWKzqw/DnaqQkj7QqLOgFceW4sbDN5F0o5JrqgAhBOo NP7enQftGLLNBxlg2HIsF4VHkbhOq2gdJ0/lz1+nAWriZj0R4JjPQIq2m+MN+gNy lX08TeQ+YeLFOh00ERhUGvh479VLHmZDKGZKJcfxV+yw/PR0rck8ad4AXIqTfMhT sAYBB7uxs++Vk7CPWCSIG11ZZ9JBwvSzdZZ9K6MDUmVFBVyWQNygJAvlYXvlvOHE LZQWA== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgeefvddrtddtgddugeehieduucetufdoteggodetrf dotffvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdggtfgfnhhsuhgsshgtrhhisggv pdfurfetoffkrfgpnffqhgenuceurghilhhouhhtmecufedttdenucesvcftvggtihhpih gvnhhtshculddquddttddmnecujfgurhepfffhvfevuffkgggtugfgjgesthekredttddt jeenucfhrhhomheplmhlvhgrrhhoucfjvghrrhgvrhgruceorghlvhhhvghrrhgvsegrlh hvhhdrnhhoqdhiphdrohhrgheqnecuggftrfgrthhtvghrnhepheejteegjefgfeelfeek jedvtdfhieeuieffjedvjeekteefledtueffjedtieeinecuffhomhgrihhnpegvnhhtvg hrphhrihhsvggusgdrtghomhdpphhoshhtghhrrdgvshenucevlhhushhtvghrufhiiigv pedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpegrlhhvhhgvrhhrvgesrghlvhhhrdhnoh dqihhprdhorhhgpdhnsggprhgtphhtthhopedvpdhmohguvgepshhmthhpohhuthdprhgt phhtthhopegrkhhumhgrrhgrnhgthhgrthhhsehlihhvvgdrtghomhdprhgtphhtthhope hpghhsqhhlqdhpvghrfhhorhhmrghntggvsehpohhsthhgrhgvshhqlhdrohhrgh X-ME-Proxy: Feedback-ID: ia2694551:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Wed, 19 Mar 2025 10:43:48 -0400 (EDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=alvh.no-ip.org; s=schmee; t=1742395425; bh=YP2CEPdEl8SqOBAEW3O65txRDHUjFD++IDI4OKSSIy8=; h=Date:From:To:Cc:Subject:In-Reply-To:From; b=FOMK4M/PfckFBd8zUKburRfP8Fpg4RF8AUfUrgqttmrR+/nfzXvPVYRiAV/4VanBA i1JwmNvajnh2fOt1Bq5Kc6kyuurKKrE4HE93lfsD6pRVUBtwcis4cvzsY0nRNqvj4t YUcvTNnQzB4qr1CSEGawje/ySiI3EXxs9z7j1EunCxtgSDPlQ7kTfmaY96lmN8//16 ub2cEY7kqxhnCpWg0ediArg6nnh0xnnqUCOmD55AaNd0kum/A1+rGKrJV3iubthbE0 3pUgcq2REHeRUzZC+XMcqAjVAYKWS7jDXlXXeF7/eK2QZBma3a7krrVmEkpdJy9/TJ IgsBJx9r+RTjQ== Received: by schmee.alvh.no-ip.org (Postfix, from userid 1000) id 2172587; Wed, 19 Mar 2025 15:43:45 +0100 (CET) Date: Wed, 19 Mar 2025 15:43:45 +0100 From: =?utf-8?Q?=C3=81lvaro?= Herrera To: Adithya Kumaranchath Cc: "pgsql-performance@postgresql.org" Subject: Re: PostgreSQL 16 - Detach partition with FK - Error Message-ID: <202503191443.3q6lcqhexjp2@alvherre.pgsql> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 2025-Mar-19, Adithya Kumaranchath wrote: > --Scenario 1: Detach parent partition > alter table table1 detach partition table1_202402 > > ERROR: Key (parnt_id, archive_dt)=(a6955e39-22eb-48e2-bd59-cad9650a4f6b, 2024-02-11) is still referenced from table "table1_child_202402".removing partition "table1_202402" violates foreign key constraint "table1_child_202402_parnt_id_archive_dt_fkey1" ERROR: removing partition "table1_202402" violates foreign key constraint "table1_child_202402_parnt_id_archive_dt_fkey1" SQL state: 23503 Detail: Key (parnt_id, archive_dt)=(a6955e39-22eb-48e2-bd59-cad9650a4f6b, 2024-02-11) is still referenced from table "table1_child_202402". If I understand the example correctly, this is the expected behavior. Here you're detaching a partition from the referenced table, which contains rows that are still referenced from the constrained table. If we allowed this detach to continue, you would have rows in table1_child that do not have corresponding rows in table1, in other words you would have created an invalid primary key. The fact that your 15 install did not throw an error is probably a bug. I do get an error in the latest 15, though, so perhaps this is one of those that was fixed along the way. What exact 15.x version were you running? -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/ "The ability of users to misuse tools is, of course, legendary" (David Steele) https://postgr.es/m/11b38a96-6ded-4668-b772-40f992132797@pgmasters.net