public inbox for [email protected]
help / color / mirror / Atom feedRe: TRUNCATE ONLY with foreign keys and triggers disabled
4+ messages / 3 participants
[nested] [flat]
* Re: TRUNCATE ONLY with foreign keys and triggers disabled
@ 2025-04-14 14:53 Dimitrios Apostolou <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Dimitrios Apostolou @ 2025-04-14 14:53 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: [email protected]
On Mon, 14 Apr 2025, Tom Lane wrote:
> Dimitrios Apostolou <[email protected]> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: TRUNCATE ONLY with foreign keys and triggers disabled
@ 2025-04-14 15:05 Dimitrios Apostolou <[email protected]>
parent: Dimitrios Apostolou <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Dimitrios Apostolou @ 2025-04-14 15:05 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: [email protected]
On Mon, 14 Apr 2025, Dimitrios Apostolou wrote:
> On Mon, 14 Apr 2025, Tom Lane wrote:
>
>> Dimitrios Apostolou <[email protected]> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: TRUNCATE ONLY with foreign keys and triggers disabled
@ 2025-04-14 15:07 Laurenz Albe <[email protected]>
parent: Dimitrios Apostolou <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Laurenz Albe @ 2025-04-14 15:07 UTC (permalink / raw)
To: Dimitrios Apostolou <[email protected]>; Tom Lane <[email protected]>; +Cc: [email protected]
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: TRUNCATE ONLY with foreign keys and triggers disabled
@ 2025-04-14 15:13 Adrian Klaver <[email protected]>
parent: Laurenz Albe <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Adrian Klaver @ 2025-04-14 15:13 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; Dimitrios Apostolou <[email protected]>; Tom Lane <[email protected]>; +Cc: [email protected]
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
[email protected]
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-04-14 15:13 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-04-14 14:53 Re: TRUNCATE ONLY with foreign keys and triggers disabled Dimitrios Apostolou <[email protected]>
2025-04-14 15:05 ` Dimitrios Apostolou <[email protected]>
2025-04-14 15:07 ` Laurenz Albe <[email protected]>
2025-04-14 15:13 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox