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 1u4LBg-000txD-PD for pgsql-general@arkaria.postgresql.org; Mon, 14 Apr 2025 14:53:29 +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 1u4LBe-001clc-Ia for pgsql-general@arkaria.postgresql.org; Mon, 14 Apr 2025 14:53:27 +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 1u4LBe-001clU-7I for pgsql-general@lists.postgresql.org; Mon, 14 Apr 2025 14:53:27 +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 1u4LBb-0002zy-1u for pgsql-general@lists.postgresql.org; Mon, 14 Apr 2025 14:53:26 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmx.net; s=s31663417; t=1744642401; x=1745247201; i=jimis@gmx.net; bh=pp2TLYHRXiPIHx3cXVLha9mGl6rB15PHmQnJcBKRi3Y=; 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=HtB4SxeYxLplhH1TcDvhVSZP6BktLmQCyBQrTw5/ZHaVcQR/DRTp9WLXG4MHl+4d 6RjcREBWDsC4NgfxCYzQVgHRoLoyvSCZ8Kk5hmIOMOt+nH0pu5/NgDQq4U5YV43c+ BmS53oN14BenJNk0CPx9RbKQqL5OhE3aardDEOfzhxS8fjhaw5bwxyPilDNmNieKA rB8XXSc8s6Icoe6Q4jacBKAl9Ajd5ZYGwBe/A61GEDpcHJlO72RYBzm1B/8Z61u/t fyu5uUayksk7yFuqRr1eutkIbBIAFhQt2oib/QlicMPAlsnfksv0KJFRBl11nczLj orlPvrqUaFW7ebFhnQ== 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 1Mel81-1tTuRI1czl-00cGwK; Mon, 14 Apr 2025 16:53:21 +0200 Date: Mon, 14 Apr 2025 16:53:20 +0200 (CEST) From: Dimitrios Apostolou To: Tom Lane cc: pgsql-general@lists.postgresql.org Subject: Re: TRUNCATE ONLY with foreign keys and triggers disabled In-Reply-To: <1257959.1744641901@sss.pgh.pa.us> Message-ID: References: <1257959.1744641901@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII; format=flowed X-Provags-ID: V03:K1:FuxEHLmXBfZVKxA/xbE3x7lz5OxNw+jjLqmKmhuGgLlYN4pH2Js 411hzy/5kQSESpcadUJYht34XuVaWNlfYI0dIhxEfRPaZhgmUgG7eTsVl4aydmlaXHgjJHU 4jzut86mTL5JqCh8hnRDqyrx0HHFIQsRBF0PJgM9184rM0TaHxNe6PIoodA58Dv4c3xghr/ 28zhN+1+qZywRNLg+JQGg== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:kw0s0lHnIMk=;1420Q5qSXQl7odMu09rHvsLq6HB Fx8RK1gldwWEtNnbXhOsnGvbwB+sMYXQl4vmspMO1+sCpxRmf2Tca9K7qL1DMvF/kbYoBVukM vw/8rbs5OPX5Zxy1AVU+d098cpledza9RJmiA1OSYWGBrDO2vcQfuB9Bp7neC5+ffu6FZ0TMO u69N4EW21EJdkFYbOOqbs0gXQHebl05da5mkoZTMZJB4/jLkLmLCDu40MOjX8npb2Nz2m9aSl Nm+vAt2BrpphROgVnfogeasYVY3pwYal34J3irQ5CqEcHP6TtxvNDTH2N2/5AZRbvJ+GyF7Tl hvBjqMboftQlEhYqcMEHQjGo1iV4t4LvV+bq6Ba8CxaLAgYFhskS+vZAlLLKrV5PxJltxsxL6 o69flLGn79R4p4OH4KEhd63CYnl6jusInAOnF5yFTAtiBKRw1i/zKDqAkidXMtWWn27EIxP60 ZZW56iegFJ4Y0RaVoLSrFXCNHbDFsjtFamqDUBRMwvMRo2/57BbIk53UGVshvKzmD1+Ba9uVK thkpxR+znfbGpTlfQTsUXeRwAYD9d5/JVlVdcBlABcqd9kuV8Mghqy9DBm+C4yxOPPvqeOxB6 3+kSwkyoOUFzp5O694Uo+gR0mtHbEywv/0BeWR2Q24NbMt9I4apBrEwDM14oeewaN8LsOuM2M kRy1xk3QajgRlvXqJGczc7XstqFIG7SX9VUT/l6JJA3C2tL2sn9zfw+9h1nF9td6usnAQ0ml5 WlN29Ku8+Y9vmQyhvXTJsTkDACtIjo1JCjdQlmZWsm98TtdzpEf7/fnwVixwzzLgJnl3q/LHT xWFPf3cr6YQeOam3kzb3Pn5do75J4T4Z0ynbUqxJkCVFoGg5sCZnnWT7huBXDTvywyAHIJAhQ BolJHRc5BK7gk8Rctzft88NdA2i7bCTRUNXheCOztauf135rysAmvj0v3CS7ax0wT8F0wwvj2 76QRBFx+bOc1vbW96XM5IYbSk7kkhPCfaKyaXBMhD6KSy0OQNyQId319fYAz5ngjQG1RvuVry mxxhJ0X5DUX2Qz/zRqQWf8TZqzFWzpWLflYSNQAtSTQE23epLmhZJrS/c3a5cxqWIqnQEb7BN /euM8KaFKOdh7HclQ3Dwtn25FJ46+8ELruPoEIuMc4/rihqMU0penGFS50+qN0NTg+K9FqZ/U vGOU5NCB1FwnfsdZlReIuCKOA/9nCalo+ZP3KHPyCYencBWHnRAI7R3xmsJgXn6T8pJLvfv22 c0NkPEbYUjrfyCTibg08XrmJTFpYFBiQZuYS7IUO26Rhu4SG+bySSZWLJcmUIzanYMlwQvEJB p4omds1x6pyEhrW1FQR4JaeN+5F5E6o/DaDzoJdg1/dhMVhRyTOxsMnB/0UW/9TYIJq29it2q QiFe7xArG7G7s0K8QLi4I17mdygWke3jZJ2562Ih7QtRUq4O0S8IIbs3TlOiZvfxhYZ+e5V0V GY1rjKARhUz/nK7BgZ6ZXwfLbO1I= 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, Tom Lane wrote: > Dimitrios Apostolou writes: >> While doing TRUNCATE ONLY I get: >> ERROR: cannot truncate a table referenced in a foreign key constrain= t >> But in my case the table to be truncated is already empty, and the >> TRIGGERS are disabled in all tables. > > IIRC, it will let you do it if you truncate both the referenced and > referencing tables in the same command. The state of the triggers > is not material to this, since TRUNCATE doesn't fire them anyway. Thanks Tom, however that is not possible in my case, the referenced table is possibly just populated and potentially very big (partitioned table with many sub-partitions). Context is that I've modified pg_restore to accept --data-only --clean, and issues a TRUNCATE ONLY before each table's COPY, in the same transaction (thus avoiding WAL too). Will send an RFC patch to pgsql-hackers when I verify it works. Dimitris