Re: TRUNCATE question

Tom Lane wrote:

> I wrote:
> > Perhaps TRUNCATE should require superuser privilege, just to protect
> > people from themselves?
>
> Alternative possibilities came to mind just after I hit "send" ...
>
> 1. Refuse TRUNCATE if the table has any DELETE triggers. (Are there
> any other conditions to check for?)
>
> 2. If the table has DELETE triggers, allow TRUNCATE only to the
> superuser.
>
> Our current behavior is to allow TRUNCATE only to the table owner,
> which seems to miss the point from a purely semantic point of view.
> Anyone with DELETE privileges can do a universal DELETE, so why
> shouldn't the faster alternative be available to them?
>
> Does Oracle have any special permission checks for TRUNCATE?

TRUNCATE is a scary ass command. It is the 800 pound gorilla of delete.
Here's what Oracle docs has to say about it:

"To remove all rows from a table or cluster and reset the STORAGE
parameters to the values when the table or cluster was created.

Deleting rows with the TRUNCATE statement can be more efficient than
dropping and re-creating a table. Dropping and re-creating a table
invalidates the table's dependent objects, requires you to regrant object
privileges on the table, and requires you to re-create the table's indexes,
integrity constraint, and triggers and respecify its storage parameters.
Truncating has none of these effects. "

The "Oracle 8 Complete Reference" says:

"The TRUNCATE command is faster than a delete command because it generates
no rollback information, does not fire any DELETE triggers (and therefore
must be used with caution), and does not record any information in the
snapshot log. In addition, using TRUNCATE does not invalidate the objects
depending on the deleted rows or the privileges on the table. You cannot
roll back a TRUNCATE statement."

Neither reference any special privileges or conditions for the statement,
but they are littered with sentences like: "You should be SURE you really
want to TRUNCATE before doing it."