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 1u4Lc6-000ycU-RW for pgsql-general@arkaria.postgresql.org; Mon, 14 Apr 2025 15:20:47 +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 1u4Lc5-0027HX-0R for pgsql-general@arkaria.postgresql.org; Mon, 14 Apr 2025 15:20:45 +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 1u4Lc4-0027HP-LI for pgsql-general@lists.postgresql.org; Mon, 14 Apr 2025 15:20:45 +0000 Received: from mout.gmx.net ([212.227.17.21]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1u4Lc1-0003Jr-37 for pgsql-general@lists.postgresql.org; Mon, 14 Apr 2025 15:20:45 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmx.net; s=s31663417; t=1744644037; x=1745248837; i=jimis@gmx.net; bh=Moi1uFmgRAUO17mpW41qYzJo+ainDaU4FF1kFx49Yvo=; h=X-UI-Sender-Class:Date:From:To:cc:Subject:In-Reply-To:Message-ID: References:MIME-Version:Content-Type:Content-Transfer-Encoding:cc: content-transfer-encoding:content-type:date:from:message-id: mime-version:reply-to:subject:to; b=FbQQVwilpBXtSe7ipehoIF64MqEROeJPeeLxz9wyG5NNaeY3ykgMUpYu2faLuMKP 0uj0yO+y1VWBYb3ql5Lyrjr6FzpqGpSI2+qVJwO7ItZDC2Ezhra1lo+71Dh55a2/T hQLl+EMJ7HGPOUYHKFOOFFNbEKeuw9u7asv92yWpxSpqkiqOc50xGWsY8VcPuN5/t EwV1D0iv/TFFATQeHhf0c4KUpJr22yB22tWeBINt1Jg4ndaa5GorK7gjFmq0oy87v Jc/xGcBnvNvGdmDEWwzXAp2nFfFhK+NDmzUWxDKDEgFKGoPhj0mG33qYYYVj7XNeL ouR6fBds9dMv76RmMg== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from [10.9.70.81] ([185.55.106.54]) by mail.gmx.net (mrgmx104 [212.227.17.168]) with ESMTPSA (Nemesis) id 1M1HZi-1u2hih19as-001GxS; Mon, 14 Apr 2025 17:20:37 +0200 Date: Mon, 14 Apr 2025 17:20:36 +0200 (CEST) From: Dimitrios Apostolou To: Adrian Klaver cc: Laurenz Albe , Tom Lane , pgsql-general@lists.postgresql.org Subject: Re: TRUNCATE ONLY with foreign keys and triggers disabled In-Reply-To: Message-ID: <376367c8-1baf-3e64-9363-15fa24265b6c@gmx.net> References: <1257959.1744641901@sss.pgh.pa.us> <6776dc7beaf591a684d089db4c77407b7a2866c4.camel@cybertec.at> MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII; format=flowed X-Provags-ID: V03:K1:GyIkQ6wOBy6SsyqDXHF1Rkfx112POWOGUxwyfWD4vV4XzYquum4 2ytiujwXqwAUSoxsgWEqT5xNdJwBqSf8gKsLhsFD8WtnfrK4u4xwF4FVreTqyT/GM9zY/b7 wsbKRsN+Gw24u9SBSPXKVGgs81VnxDjQSUmCc3bODwNb1ucXSp84l4KFYafDYUM12o6Fn6W 533yPHYuD1HY9Nn1K4L0Q== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:lu8rHcfj8WU=;mnUHgIInMqxC16ZoPGcxvYPN45k U9MTtzMk/5MJfLsXIqZDdM/Bq7oWQCPQ6wTBWJYvYKaBf1AktuBtrAVNGuYwblSeyFE32tZGy mEtVvh96MLVvxXVoiogDjjf7ybjGzPpsfgH8XY+WCBKTXW4R1Amg3x/Eh8u5T++ZqlAjTV/FM BgZHQr1iKZfKV3bBwTRRdGYkT30iYlG7XGYZOlIt1uX+sFDsCsXAOAVFTWTe131yTlBEUqR+V ysLPoF/f44IGQx8Htnt4Y/clPAbkKHEqKJTEUG0l07Xc/U3K0Uvk+EbpkGoCwmOjnvtCrf9K1 BSc84zUIuEjnxKmabrRCJzFmXZY0tNkm6WWKZjyLxcMNM2yvC25XzE+JjNn3LNsuWcO3w/bn+ jZcjZNAEir3QWQO9rzOejicPdZxyzfb/hmFa0ZAFIePK8px210vo5kToBynkdirGt4H0LFaUs F3hPY3JPZd2LwF6Mi272R416Jlng2bv31pYL+/DOLyMVRlBsk37ED0RYV6Q1wTndHua4IK01S +jHK0cEzL/MCrIHxA/mIRWh3i5TCan7r6rzSCNObt2JrZtblqCmoeq4Dn/snvXVCMXzpcJH+d A6PGvXPs8yQp9HF8anUIiQzVs4qXsVOpuTLW6jN4LAcXuEQEIgRdkwaTSip44KmIrBXJV3K+o IbuFGq4q7sciHrwh3oPqXH2TCamgKE78r+cbSMHgOAU7TY35AdbM3yC/34vuUIUQSqDXL5W86 fjpR7uwDzeq4l4GEpd8ZlKfX6gDaWizk8JzB18IZcf7mfwuy52YFyMRCqLVFszA4398FeQXhy bFja8qQAHybANFXJNvv2QQ/QEZhHJ66Ef2XmvyuDpzRF032ve541h29DaK1vedDm7oicXcE87 27S7YoijBSAB31ZJQ07qaFIoPpYXbOH8BhIhpK42h8LDwiEWvN0S6p7+YmKUniagqSq9xbGmo vqRTQExi5593N16ZZsJnHDnOWJ9snvlaPMGa3AulaQEd8CvsS6Cjf7Tc2iLhGj96ajnydNLHN JqsdUejWDM3+0PehfQsqudJUf8JkHhsd2ZP6f8rCwEFTEsoFVarfuNcmwDybwSsJVfIYErXwJ rTD6MNl4OBFyHojowsLZFwHdqpcFy40NZmIjYxttPunYd+RmIkmvvqkdtFIgdHj94BbrPhmX5 chlAKN6GqFCyCpZN262ggBF68TwKKpy6AoQXXsA6VR+qy3LFYMZn9i1ShYYRRht3ikG7kIJBP Hag5f/KHCLC+VTmVHs8KC7GC8SM/XgKZOtqAsLOAZQcBdnfqgiUo2gb0QRDkwPMAE49VBD26a SFYcZPORGIJ8zKg4LKpWo9jpiVZZ6l9eq32Z8dp+xWPEbeH3vTGB4AWIK79MXxQ0J4so640C1 yQ6RvURnC8CJahrkIcRqUoBluwScoIVcYBjBnjdj+is6iLR7okhIxOD3QBJCBUBDnPf7Ap4EX 0iVG/ikwlaPM9/X6jh8nOhMcnd+o= Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Mon, 14 Apr 2025, Adrian Klaver wrote: > On 4/14/25 08:07, Laurenz Albe wrote: >> On Mon, 2025-04-14 at 17:05 +0200, Dimitrios Apostolou wrote: >>> I meant the *referencing* table has just been >>> populated. I'm trying to delete the *referenced* table and I get the >>> error. >> >> That would break the foreign key constraint, right? >> PostgreSQL cannot allow that. > > I believe the OP is disabling all triggers including system ones if I fo= llow > correctly and possibly running a foul of; > > https://www.postgresql.org/docs/current/sql-altertable.html > > " Disabling or enabling internally generated constraint triggers require= s > superuser privileges; it should be done with caution since of course the > integrity of the constraint cannot be guaranteed if the triggers are not > executed." Exactly that. I learned this from pg_restore --disable-triggers, as a way to speed-up insertion. Since triggers are disabled, I assumed that postgresql shouldn't care about referential integrity in TRUNCATE. Dimitris