Skip site navigation (1) Skip section navigation (2)

Re: TRUNCATE question

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Mike Mascari <mascarm(at)mascari(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>, Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: TRUNCATE question
Date: 2001-09-06 17:13:50
Message-ID: 200109061713.f86HDog04120@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Is there a TODO item here?


> 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
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman(at)candle(dot)pha(dot)pa(dot)us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

In response to

Responses

pgsql-hackers by date

Next:From: Marko KreenDate: 2001-09-06 17:39:49
Subject: Re: Bytea string operator support
Previous:From: Mikheev, VadimDate: 2001-09-06 17:05:52
Subject: Re: Problems starting up postgres

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group