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?

Here are the rules for Oracle:

1. The table must be in your schema (i.e., you're the table owner)
or you have been granted the DELETE ANY TABLE System Privilege. We
need System Privileges, BTW.

2. The table cannot be truncated if it is the parent of a
referential integrity constraint. The exception is that if the
integrity constraint is entirely self-referencing.

3. If the table has ON DELETE triggers, the TRUNCATE does not fire
those triggers nor does Oracle prohibit you from TRUNCATE-ing a
table with ON DELETE triggers.

4. The TRUNCATE command generates no rollback information.

5. Like all Oracle DDL statements, TRUNCATE implicitly commits and
begins a new transaction.

I'd like to see PostgreSQL do all but #5; its been two years, but
now I'm a believer ;-).

Mike Mascari
mascarm(at)mascari(dot)com

>
> regards, tom lane