Date: Mon, 14 Apr 2025 18:09:30 +0200 (CEST)
From: Dimitrios Apostolou <jimis@gmx.net>
To: Adrian Klaver <adrian.klaver@aklaver.com>
cc: Laurenz Albe <laurenz.albe@cybertec.at>, Tom Lane <tgl@sss.pgh.pa.us>, 
    pgsql-general@lists.postgresql.org
Subject: Re: TRUNCATE ONLY with foreign keys and triggers disabled
In-Reply-To: <3b2467c2-3af2-438a-87e6-81e6653ffd0e@aklaver.com>
Message-ID: <40e61dac-9903-2d45-0dd5-4d848106e7a0@gmx.net>
References: <edf978ea-6cf8-b2c1-4866-3fa7ae160836@gmx.net> <1257959.1744641901@sss.pgh.pa.us> <e5909bae-6782-9c61-fb86-cdf3398e3c9f@gmx.net> <c691b848-7649-e425-b066-c1116147de55@gmx.net> <6776dc7beaf591a684d089db4c77407b7a2866c4.camel@cybertec.at>
 <ca7e26fa-de54-4b7c-b3b6-c2967ec92f78@aklaver.com> <376367c8-1baf-3e64-9363-15fa24265b6c@gmx.net> <3b2467c2-3af2-438a-87e6-81e6653ffd0e@aklaver.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="0-516259503-1744646971=:483477"
UI-OutboundReport: notjunk:1;M01:P0:NUUvyQrDXyM=;oI+J7XHVZFx04fuEOjCf0i65NOw
 QsBJgQvs6rM2BbCNeKzm5jY8Nn2d4Kq2+T/+oQNiVX60zv+epHimPi20/khvC7GM1riCHe74s
 PcfoK6kC8y06GGBMR4UchjrzIBAyPjmWau8ZBtIGw+FbRwvaoBmKkFTN/bbP5j7mKR7v5GaGV
 38rNXGD0VlzuuOCrqzz/RuE4OQM7CInDcnCgX527a4T1tVnpbiwlJddgayBtN/i0GDWhkX3Cf
 CqVSj0ZEw/if8v/WSGBp2DlL4Kx13ONHnBBS1c8GotbtvQ3nwflkQT8DNYEM6xwpFSeNS1x85
 6Yt6Cpsu0romutdujANRG6E79NZs7/lx/gorqjyB9EYDf8s5Z6z+RzMvJ5cgTRnBEva1B+srR
 caXj3LsVGofRLSovWiAH7Fz7h9RgLxtvTqkiHNrp/ZFrrlt+aTajqowYxB56KH+wMKBSCuGqJ
 mHKqTa3m/7NB5JHFVmGI0R4aW/Aw0Kp/bXwaf4dQhHbKyc7fiiVsE+ayEsVc1WvRoDlQrXgDw
 HhGrcWWnQ/Pg1enshIZnmP3E/m8CHud6EZ8jaa4L3Xjz0hxDagHhHkR26DCkjtmJt0c5dQK3C
 b6gWwWO5s3GUSwciRoVuYaCr7RaQTXdLf+9rciDnsjkDnMZLtGm9Dg12OQ2ZjJtfEFzMhbzvW
 xOE9LadymKaHjMFx3+snD65tni2kzQIU0WQ1t67C7uLW4p899MGjTueE7djUHGYeyL/TzuKNB
 MlIhrz1q25mWqQ/zOF8LWeAz291q07oq6jr5FbwAmP5RPMTJv4exlX2/oL2W1MWRvYbqvvCAB
 4JblGwpxVgfzji1XaIwuqCY3E+K00irXVobJ38vp5FJjjccmbBxoDep50lkLyXNiGIVhqs9h+
 gT3/N84XnCvf3AHIdWOXGv246wGyJiWN+tINhPbtQL/EIuphTix5DZRNuy5J2aIxHrkZl5TkP
 msFz1xWIh4yHhhIjFpxA1I8Z59KgoxerlltHFOi0EOP1lRpTHxlUCfYYWIOVuqO4qYLxeupWo
 VFl4Rmf4QTPunf30kpdyTpNBHA1xv+KuZR7skOOHy2Zweo/IBQKfzdwM3kPwFfzzgctKd11eR
 Aa+jeYjBX7er51YWD+zZEnR/oJGiOVuJTMPuAWlf8C0yiIlM6bhbQj18nCyvksSnuhz5xTo/t
 L6FlbwkVEv5ogAJUDRvO89iq6yx3EW/Rt6qap/Pfyieqmnp5Jfmvdf55GfPQ90w0s+2oX0x4T
 +ufLw+6KZdVzuSjTYC6KCECR+yqGqapoa4BZXAElbflRlIU9oO+za4ZYm9Dvm/zihoVv4bxR0
 aoUCKVqGgpt04UhySGwYJeJvprebKUcbXKMDw7imZNHNXl8S7RoxyJ1LHKZEAbSsGKmddtX7X
 1IfdTw01M9PHZEQwsnxc/iz7vzUio1V0t8F5NtH/K2eVF5JpbWFU+Dc7J2vFTMTVq7XfKguXm
 eLoXHcVjK0VhfpgaYi8Jkfek04rY=
Archived-At: <https://www.postgresql.org/message-id/40e61dac-9903-2d45-0dd5-4d848106e7a0%40gmx.net>
Precedence: bulk

  This message is in MIME format.  The first part should be readable text,
  while the remaining parts are likely unreadable without MIME-aware tools.

--0-516259503-1744646971=:483477
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: quoted-printable

On Mon, 14 Apr 2025, Adrian Klaver wrote:

> On 4/14/25 08:20, Dimitrios Apostolou wrote:
>>  On Mon, 14 Apr 2025, Adrian Klaver wrote:
>>
>>>  On 4/14/25 08:07, Laurenz Albe wrote:
>>>>  =C2=A0On Mon, 2025-04-14 at 17:05 +0200, Dimitrios Apostolou wrote:
>>>>>  =C2=A0I meant the *referencing* table has just been
>>>>>  =C2=A0populated. I'm trying to delete the *referenced* table and I =
get the
>>>>>  =C2=A0error.
>>>>
>>>>  =C2=A0That would break the foreign key constraint, right?
>>>>  =C2=A0PostgreSQL cannot allow that.
>>>
>>>  I believe the OP is disabling all triggers including system ones if I
>>>  follow
>>>  correctly and possibly running a foul of;
>>>
>>>  https://www.postgresql.org/docs/current/sql-altertable.html
>>>
>>>  " Disabling or enabling internally generated constraint triggers requ=
ires
>>>  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.
>
> I'm going to say the depends on order of execution. Without seeing the c=
ode
> changes you have done there is really no way to determine what the exact
> issue is.

Sent patch here:

https://www.postgresql.org/message-id/flat/4589087c-ec6f-4407-1f82-6cb2e68=
1ac0a%40gmx.net

The reason I first sent here (pgsql-general) is that I wondered if the
error is just an implementation shortcoming without deeper reasons, thus
it would be achievable to try to fix it myself.


Dimitris


--0-516259503-1744646971=:483477--