Those operations do different things, sure. But from a roles/privilege framework perspective, why would you want to give certain users the DELETE privilege whereas others you want to give them only the TRUNCATE privilege?
Are we saying to a user that "You need a different level of privilege because you are about to cause a MVCC-unsafe operation?". Or is the privilege framework simply ruling "Do different things, have different permissions"? Marcelo. On Sat, Jan 17, 2026 at 5:46 AM Adrian Klaver <[email protected]> wrote: > > 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]
