Thread: TRUNCATE ONLY with foreign keys and triggers disabled
Hello list, While doing TRUNCATE ONLY I get: ERROR: cannot truncate a table referenced in a foreign key constraint But in my case the table to be truncated is already empty, and the TRIGGERS are disabled in all tables. I wonder, is there a deeper reason for this error, or is it just an implementation shortcoming? In other words, how easy would it be to write a patch that allows TRUNCATE even with foreign keys when triggers are disabled or the table is empty? Thanks in advance, Dimitris
Dimitrios Apostolou <jimis@gmx.net> writes:
> While doing TRUNCATE ONLY I get:
> ERROR: cannot truncate a table referenced in a foreign key constraint
> 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.
regards, tom lane
On Mon, 14 Apr 2025, Tom Lane wrote: > Dimitrios Apostolou <jimis@gmx.net> writes: >> While doing TRUNCATE ONLY I get: >> ERROR: cannot truncate a table referenced in a foreign key constraint >> 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
On Mon, 14 Apr 2025, Dimitrios Apostolou wrote: > On Mon, 14 Apr 2025, Tom Lane wrote: > >> Dimitrios Apostolou <jimis@gmx.net> writes: >>> While doing TRUNCATE ONLY I get: >>> ERROR: cannot truncate a table referenced in a foreign key constraint >>> 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). Terminology correction: I meant the *referencing* table has just been populated. I'm trying to delete the *referenced* table and I get the error. Dimitris
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. Yours, Laurenz Albe
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 follow correctly and possibly running a foul of; https://www.postgresql.org/docs/current/sql-altertable.html " Disabling or enabling internally generated constraint triggers requires 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." > > Yours, > Laurenz Albe > > -- Adrian Klaver adrian.klaver@aklaver.com
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 follow > correctly and possibly running a foul of; > > https://www.postgresql.org/docs/current/sql-altertable.html > > " Disabling or enabling internally generated constraint triggers requires > 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
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: >>> 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 >> follow >> correctly and possibly running a foul of; >> >> https://www.postgresql.org/docs/current/sql-altertable.html >> >> " Disabling or enabling internally generated constraint triggers requires >> 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 code changes you have done there is really no way to determine what the exact issue is. > > > Dimitris > -- Adrian Klaver adrian.klaver@aklaver.com
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: >>>> 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 >>> follow >>> correctly and possibly running a foul of; >>> >>> https://www.postgresql.org/docs/current/sql-altertable.html >>> >>> " Disabling or enabling internally generated constraint triggers requires >>> 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 code > 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-6cb2e681ac0a%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