Thread: TRUNCATE question
Is TRUNCATE supposed to be equivalent to DELETE FROM blah? Because I notice that DELETE triggers are not called when you truncate a table... Isn't that a bad thing? Chris
Christopher Kings-Lynne wrote: > > Is TRUNCATE supposed to be equivalent to DELETE FROM blah? > > Because I notice that DELETE triggers are not called when you truncate a > table... Isn't that a bad thing? It's supposed to work that way - same as Oracle. Mike Mascari mascarm@mascari.com
Makes for a real pain when the nice and safe foreign keys aren't really nice and safe anymore. -- Rod Taylor Your eyes are weary from staring at the CRT. You feel sleepy. Notice how restful it is to watch the cursor blink. Close your eyes. The opinions stated above are yours. You cannot imagine why you ever felt otherwise. ----- Original Message ----- From: "Mike Mascari" <mascarm@mascari.com> To: "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> Cc: "Hackers" <pgsql-hackers@postgresql.org> Sent: Thursday, August 02, 2001 9:56 PM Subject: Re: [HACKERS] TRUNCATE question > Christopher Kings-Lynne wrote: > > > > Is TRUNCATE supposed to be equivalent to DELETE FROM blah? > > > > Because I notice that DELETE triggers are not called when you truncate a > > table... Isn't that a bad thing? > > It's supposed to work that way - same as Oracle. > > Mike Mascari > mascarm@mascari.com > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
Rod Taylor wrote: > > Makes for a real pain when the nice and safe foreign keys aren't > really nice and safe anymore. > > > > It's supposed to work that way - same as Oracle. TRUNCATE TABLE is essentially short-hand for DROP/CREATE, but preserves GRANT permissions, associations from its oid in functions, views, etc. Oracle disallows TRUNCATE on a table involved in a referential integrity relationship, but doesn't disallow the behavior for a normal ON DELETE trigger. According to previous discussions, PostgreSQL should behave similarly. If it does not, its a bug. I haven't checked the status since 7.1.0, so I don't know. Accordingly, as of 7.1.0, nothing stops you in PostgreSQL from performing a DROP/CREATE on a table involved in a referential integrity relationship. Now your foreign keys are completely gone. I haven't checked that behavior in later versions, however. Oracle requires DROP TABLE <table> CASCADE CONSTRAINTS to force a DROP of a table involved in a primary/foreign key relationship. Mike Mascari mascarm@mascari.com
I agree it matches the description. That said, it rather surprised me when Triggers and things didn't go off. Primarily due to the 'Works like a Delete *'. The description has changed since I first discovered it though. -- Rod Taylor Your eyes are weary from staring at the CRT. You feel sleepy. Notice how restful it is to watch the cursor blink. Close your eyes. The opinions stated above are yours. You cannot imagine why you ever felt otherwise. ----- Original Message ----- From: "Mike Mascari" <mascarm@mascari.com> To: "Rod Taylor" <rbt@barchord.com> Cc: "Christopher Kings-Lynne" <chriskl@familyhealth.com.au>; "Hackers" <pgsql-hackers@postgresql.org> Sent: Thursday, August 02, 2001 10:40 PM Subject: Re: [HACKERS] TRUNCATE question > Rod Taylor wrote: > > > > Makes for a real pain when the nice and safe foreign keys aren't > > really nice and safe anymore. > > > > > > It's supposed to work that way - same as Oracle. > > TRUNCATE TABLE is essentially short-hand for DROP/CREATE, but preserves > GRANT permissions, associations from its oid in functions, views, etc. > Oracle disallows TRUNCATE on a table involved in a referential integrity > relationship, but doesn't disallow the behavior for a normal ON DELETE > trigger. According to previous discussions, PostgreSQL should behave > similarly. If it does not, its a bug. I haven't checked the status since > 7.1.0, so I don't know. > > Accordingly, as of 7.1.0, nothing stops you in PostgreSQL from > performing a DROP/CREATE on a table involved in a referential integrity > relationship. Now your foreign keys are completely gone. I haven't > checked that behavior in later versions, however. Oracle requires DROP > TABLE <table> CASCADE CONSTRAINTS to force a DROP of a table involved in > a primary/foreign key relationship. > > Mike Mascari > mascarm@mascari.com >
Mike Mascari <mascarm@mascari.com> writes:
> Christopher Kings-Lynne wrote:
>> Is TRUNCATE supposed to be equivalent to DELETE FROM blah?
>>
>> Because I notice that DELETE triggers are not called when you truncate a
>> table... Isn't that a bad thing?
> It's supposed to work that way - same as Oracle.
AFAICT, the whole point of TRUNCATE is to skip all the fancy stuff like
delete triggers and just zero the table. Irreversibly. If you don't
like it, don't use it...
Perhaps TRUNCATE should require superuser privilege, just to protect
people from themselves? Not that the DBAs are necessarily any smarter
than anyone else, but at least they're supposed to know what they're
doing.
regards, tom lane
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?
regards, tom lane
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@mascari.com > > regards, tom lane
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."
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@mascari.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@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian wrote: > > Is there a TODO item here? Yes. It should read: "Disallow TRUNCATE TABLE on tables that are parents of a referential integrity constraint" In PostgreSQL current sources I can do: CREATE TABLE employees (employeeid INTEGER PRIMARY KEY NOT NULL ); CREATE TABLE salaries ( employeeid INTEGER NOT NULL REFERENCES employees(employeeid),salary FLOAT NOT NULL ); INSERT INTO employees VALUES (1); INSERT INTO salaries VALUES (1, 45000); TRUNCATE TABLE employees; SELECT * FROM salaries; employeeid | salary ------------+-------- 1 | 45000 (1 row) In Oracle, the following occurs: CREATE TABLE employees (employeeid INTEGER NOT NULL PRIMARY KEY ); CREATE TABLE salaries (employeeid INTEGER NOT NULL REFERENCES employees(employeeid),salary FLOAT NOT NULL ); INSERT INTO employees VALUES (1); INSERT INTO salaries VALUES (1, 40000); TRUNCATE TABLE employees; TRUNCATE TABLE employees; * ERROR at line 1: ORA-02266: unique/primary keys in table referenced by enabled foreign keys Mike Mascari mascarm@mascari.com
Added to TODO. Thanks. > Bruce Momjian wrote: > > > > Is there a TODO item here? > > Yes. It should read: > > "Disallow TRUNCATE TABLE on tables that are parents of a referential > integrity constraint" > > In PostgreSQL current sources I can do: > > CREATE TABLE employees ( > employeeid INTEGER PRIMARY KEY NOT NULL > ); > > CREATE TABLE salaries ( > employeeid INTEGER NOT NULL REFERENCES employees(employeeid), > salary FLOAT NOT NULL > ); > > INSERT INTO employees VALUES (1); > > INSERT INTO salaries VALUES (1, 45000); > > TRUNCATE TABLE employees; > > SELECT * FROM salaries; > > employeeid | salary > ------------+-------- > 1 | 45000 > (1 row) > > In Oracle, the following occurs: > > CREATE TABLE employees ( > employeeid INTEGER NOT NULL PRIMARY KEY > ); > > CREATE TABLE salaries ( > employeeid INTEGER NOT NULL REFERENCES employees(employeeid), > salary FLOAT NOT NULL > ); > > INSERT INTO employees VALUES (1); > > INSERT INTO salaries VALUES (1, 40000); > > TRUNCATE TABLE employees; > > TRUNCATE TABLE employees; > * > ERROR at line 1: > ORA-02266: unique/primary keys in table referenced by enabled > foreign keys > > Mike Mascari > mascarm@mascari.com > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026