public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adrian Klaver <[email protected]>
To: Dominique Devienne <[email protected]>
To: Marcelo Fernandes <[email protected]>
Cc: [email protected]
Subject: Re: Why does TRUNCATE require a special privilege?
Date: Fri, 16 Jan 2026 08:46:56 -0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAFCRh-9HTzAU7ia--Uad2-KnZv=zXeFaG0iT2ut0KXGEZhsHuw@mail.gmail.com>
References: <CAM2F1VNkovvL_56K_6OJiYU8toUdEG1Jk7ABRQ426Geh8LMB+g@mail.gmail.com>
	<CAFCRh-9HTzAU7ia--Uad2-KnZv=zXeFaG0iT2ut0KXGEZhsHuw@mail.gmail.com>

On 1/16/26 02:32, Dominique Devienne wrote:
> On Fri, Jan 16, 2026 at 10:13 AM Marcelo Fernandes <[email protected]> wrote:
>>  From the documentation:
>>> TRUNCATE quickly removes all rows from a set of tables. It has the same
>>> effect as an unqualified DELETE on each table, but since it does not actually
>>> scan the tables it is faster.
>>> (...)
>>> You must have the TRUNCATE privilege on a table to truncate it.
>>
>> Granted that TRUNCATE and DELETE are different operations under the hood, but
>> why would the TRUNCATE operation require its own specific privilege rather than
>> say, use the same privilege as the DELETE operation?
> 
> It's kinda obvious, when you read the notes.
> 
> 1) Not MVCC-safe.
> 2) Do not fire TRIGGERs, thus breaking data-integrity

It will not fire ON DELETE triggers, it will fire ON TRUNCATE triggers.

> 3) "Viral" in the presence of FKs, i.e. related tables must also be TRUNCATEd

Only if you add the CASCADE option, or TRUNCATE them in the same 
command. Otherwise it will fail.

> 
> Just these 3 are HUGE departures from a DELETE. --DD

I would add from:

https://www.postgresql.org/docs/current/sql-truncate.html

"TRUNCATE acquires an ACCESS EXCLUSIVE lock on each table it operates 
on, which blocks all other concurrent operations on the table. When 
RESTART IDENTITY is specified, any sequences that are to be restarted 
are likewise locked exclusively. If concurrent access to a table is 
required, then the DELETE command should be used instead."

and

""
When RESTART IDENTITY is specified, the implied ALTER SEQUENCE RESTART 
operations are also done transactionally; that is, they will be rolled 
back if the surrounding transaction does not commit. Be aware that if 
any additional sequence operations are done on the restarted sequences 
before the transaction rolls back, the effects of these operations on 
the sequences will be rolled back, but not their effects on currval(); 
that is, after the transaction currval() will continue to reflect the 
last sequence value obtained inside the failed transaction, even though 
the sequence itself may no longer be consistent with that. This is 
similar to the usual behavior of currval() after a failed transaction."


> 
> 


-- 
Adrian Klaver
[email protected]

view thread (5+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Why does TRUNCATE require a special privilege?
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox