Feedback-ID: i76984098:Fastmail
Message-ID: <3b2467c2-3af2-438a-87e6-81e6653ffd0e@aklaver.com>
Date: Mon, 14 Apr 2025 08:32:22 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: TRUNCATE ONLY with foreign keys and triggers disabled
To: Dimitrios Apostolou <jimis@gmx.net>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>, Tom Lane <tgl@sss.pgh.pa.us>,
 pgsql-general@lists.postgresql.org
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>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <376367c8-1baf-3e64-9363-15fa24265b6c@gmx.net>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/3b2467c2-3af2-438a-87e6-81e6653ffd0e%40aklaver.com>
Precedence: bulk

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