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 1u4LPo-000wX9-9U for pgsql-general@arkaria.postgresql.org; Mon, 14 Apr 2025 15:08:04 +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 1u4LPm-001vQN-76 for pgsql-general@arkaria.postgresql.org; Mon, 14 Apr 2025 15:08:03 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u4LN9-001mw9-61 for pgsql-general@lists.postgresql.org; Mon, 14 Apr 2025 15:05:20 +0000 Received: from mout.gmx.net ([212.227.15.18]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1u4LN7-0002Nt-20 for pgsql-general@lists.postgresql.org; Mon, 14 Apr 2025 15:05:19 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmx.net; s=s31663417; t=1744643115; x=1745247915; i=jimis@gmx.net; bh=FXUwNQjd50K0Krt1pJPuxI8K6CXhLrknQSSKOobLrso=; 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=Q+NfxEin58EgMutUDrG9IAS7NdkJ+Fq3MEM7c0KWDyvI48A6WkTCQk26TTI0l7zT SyK3WWEilIjmQ4uSSNip3Ul0utdLxJYBs6ybKq4a9rCm+Di9sTG4Anp1biHtwL4Jn 581oOYyQ5yV7pJfFOe38Z9w4ZN8Y4w6eqgEzExRdlLt5qcEl85WYttxGs1YH+2bR7 CD4QH1ME3b7YpnZVw47SPw/yBfFeHeS6LDKuKSExLlVDL8jZiYblIGg04ob7rOuwk Svmv2iKkvu5wc9+WXpyInT+VCBpCXI3VeQt7eih98CjLtOmv0S3AWHNrDTsJDAds1 ZMtag7B6R1QirWfy7A== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from [10.9.70.81] ([185.55.106.54]) by mail.gmx.net (mrgmx005 [212.227.17.190]) with ESMTPSA (Nemesis) id 1N4zAs-1t3oeV26OC-00t75z; Mon, 14 Apr 2025 17:05:15 +0200 Date: Mon, 14 Apr 2025 17:05:12 +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: 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:fWoisCsAaMuHffJ0BUYeMrqpTiiOe+hSI2y+vaonDuYibk+KktI HqCZpIWojsrMhUMZWGTgFaoTX0QJBSEp7R98yibQykl5Fa6wPLertP8+ysdd6mtdNC6TdyT sYzRgbdRosZTRT0FLv4B6GGqE/Ybu8+hNpCj4hZi5t+8TWXT67ovdjGNCXpe3fsfsT0fuPh FuP1gIDn4u87qtUKFt1fg== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:jHSB6inQT2Y=;GKj0p8IWXLYKMohsKay3WdxqEc2 JCX5dZJHw7LoXsTh1gcU7JCtIpj3CY2DIN1xA3mQyXF0IJ9gqIGThG5LSQgYsUNWS884QaUGG SbG+SKtWNj7Hny3v//mhNDinOO/UfVMq/68kx1k387x6aYXavBoHUenDXiXl9kvbRWOQcEAzZ f9dkvUZjQRNz9mUGPN88xdh3ir+cx6FmFM1BY3zRQZvaZyzgImRL/gC26SscpcVI01GW7GJKz +PWCmkaJui4dnLxAMU7joMRnmYcO6HQ/HRyYtbznzej7cDPvTMQuozMH8a4dX6a+EIxkQYsRd +h0/z+9A5O52c2dzrrqS20FI+RH1QI/12N2OcWXs2M6tDy5UV7Xrxr8SeicSXxOYaOOriR2JG aT9onsVblJ89kgQVxJ9HNaeLDWq9RlwK+iS7u2OUR9T8dmXL8FIOc+d0ISsJ9eskbC6dTi3z4 PchQMXgoteUv06Xvph2wby9LR4cJz1Eq3kGCrxYVTGzJyI4wBunb3uWT8YmfIinaTtop6L7G7 5ewy8TnhPsowKTEKaylGLMEEwrCQqOWUnmMM4ZclXNqqUCOqO0BVz8OpH8BKsN7MdH+PrVdIB h3wCWpBZ0QqZL+uq/1CsDLaxllyNuVsuhHwKYpZIMwyAo7feaMMylXrpmBjE4TEPEz2RGXKBs Hc2Vl0M6v/MPc/iJIlHIgkimQtANGYY1Y0aG6MWlfmAfntOWVR1GHrFngeiSm4+Lh0bsCg2NS +qGBDxMcDP++IbWfr2AwqXOGBqTvb7ZfuMjbTHJNh7PE6LuzfXHsZBx9NP7giltivrPiYtFA+ Ci8swOjUvBcDA8BHVm9+swT4XtinGKVi+fWKf1w3ulFZ6u+0zYFL3sIli45gTGw7+/AQsNW2H vAqzmjYI2RLTJiP7qYXEJMPAgdmgf1A82GHXvmHE3pRuped5N+ncAXxJWMId3OwdlVogOwrMY 0WNS1IygI9WvNx69CfspoMroNyW87pP6BYnE6AUIorw1glwNZS/tj9ue0yiErbXepkZIjtzRz AjW7rGrhBPc5tK9qj5zR7ZMOoVc6EY1PAx8qt7bYnlE1wuwhzzCugj+HJeFBRPMjR0HTpVHwB w7237TNiQKHWPW2iNrHn55AlYPv9uHasa6Q/UFIRTfsI3n+vbLB1TcVRVs/yWDAY9cNP7ezoj 9CYeMFHBAUEzMnwTGahX/BPfPGgnp1dgWEUgVvCWU9naKEWRdYZC9Iri698U/RqSVSLSmHYPs kjsLnDqs7OOsWLX3cNvznQeWCM3HF6keQsHFmOydybHF7NIdSxAhOER4xAeIT1n4E0oN5evKl kFA2Wc3O2MW3M8gJM693jlyy6EYL3BFeVhvMN61YnyVaMwb6Wj1XtEOaWnsh+aeniCyRfkKvu cgqW6Jsj7CqFhuRE8y4jh6hT44fJlrrREc3gxQyWsd2O46849syqzboZaoxGOnxFRjQeuKFhS 9iBt/m33sguR9YbeSBnZ4WYAYCeE= 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, Dimitrios Apostolou wrote: > 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 constra= int >>> 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 tabl= e is=20 > possibly just populated and potentially very big (partitioned table with= many=20 > sub-partitions). Terminology correction: I meant the *referencing* table has just been=20 populated. I'm trying to delete the *referenced* table and I get the=20 error. Dimitris