Thread: Proposal: TRUNCATE TABLE table RESTRICT
It seems the truncate command deletes all rows from a table even it is referenced by another tables. TRUNCATE is not in the standard any way, so I would not claim this is a bug. However, sometimes it would be helpful for a user to let him notice that the table about to be truncated is referenced by some tables. So I would propose to add "RESTRICT" option to the command. I mean if RESTRICT is specified, TRUNCATE will fail if the table is referenced. BTW, the keyword "RESTRICT" is inspired by the fact that DROP TABLE has the same option according to the standard. If a table is referenced by some tables and the drop table command has the RESTRICT option, it would fail. This seems to be a nice feature too. Comments? -- Tatsuo Ishii
At 09:24 PM 6/8/00 +0900, Tatsuo Ishii wrote: >It seems the truncate command deletes all rows from a table even it is >referenced by another tables. TRUNCATE is not in the standard any way, >so I would not claim this is a bug. However, sometimes it would be >helpful for a user to let him notice that the table about to be >truncated is referenced by some tables. So I would propose to add >"RESTRICT" option to the command. I mean if RESTRICT is specified, >TRUNCATE will fail if the table is referenced. Shouldn't it always fail if an explicit foreign key reference exists to the table, in much the way that delete of a referenced row does? If it doesn't now, I think it's a bug. If the references are implicit (no REFERENCE or FOREIGN KEY given to inform the db of the relationship) then a RESTRICT option to truncate does seem useful. - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Serviceand other goodies at http://donb.photo.net.
Don Baccus <dhogaza@pacifier.com> writes:
> If the references are implicit (no REFERENCE or FOREIGN KEY given
> to inform the db of the relationship) then a RESTRICT option to
> truncate does seem useful.
Uh, if the references are implicit, how would RESTRICT know they exist?
regards, tom lane
Don Baccus wrote: > > At 09:24 PM 6/8/00 +0900, Tatsuo Ishii wrote: > >It seems the truncate command deletes all rows from a table even it is > >referenced by another tables. TRUNCATE is not in the standard any way, > >so I would not claim this is a bug. However, sometimes it would be > >helpful for a user to let him notice that the table about to be > >truncated is referenced by some tables. So I would propose to add > >"RESTRICT" option to the command. I mean if RESTRICT is specified, > >TRUNCATE will fail if the table is referenced. > > Shouldn't it always fail if an explicit foreign key reference > exists to the table, in much the way that delete of a referenced > row does? If it doesn't now, I think it's a bug. > > If the references are implicit (no REFERENCE or FOREIGN KEY given > to inform the db of the relationship) then a RESTRICT option to > truncate does seem useful. > Just curious, Don. But could you check to see what Oracle's behavior is on this? That's the feature I was trying to mirror. At the time, RI wasn't integrated so I wasn't thinking about this issue. And the Oracle docs state that DML triggers aren't fired when a TRUNCATE is issued, so I didn't think there would be issues there. Could you check? Thanks, Mike Mascari > - Don Baccus, Portland OR <dhogaza@pacifier.com> > Nature photos, on-line guides, Pacific Northwest > Rare Bird Alert Service and other goodies at > http://donb.photo.net.
At 10:41 AM 6/8/00 -0400, Tom Lane wrote: >Don Baccus <dhogaza@pacifier.com> writes: >> If the references are implicit (no REFERENCE or FOREIGN KEY given >> to inform the db of the relationship) then a RESTRICT option to >> truncate does seem useful. > >Uh, if the references are implicit, how would RESTRICT know they exist? Duh, sorry, haven't had my coffee yet. I should know better than think about computers before coffee...got any? OK ... then I'd suggest that allowing truncate in the face of explicit foreign keys is a bug. Truncate should either refuse to do so in all cases, or follow RI rules (do ON DELETE CASCADE/SET NULL/SET DEFAULT or refuse to do it depending on the foreign key def). It would presumably do so by calling the RI trigger for each row just as delete does. TRUNCATE's documented as being a quick alternative to delete, so refusal is perhaps the best course. Or the documentation can say "it's a lot faster if there are no foreign keys referencing it, otherwise it's the same as DELETE FROM". But breaking RI by leaving "dangling references" is a bug, pure and simple. - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Serviceand other goodies at http://donb.photo.net.
At 10:43 AM 6/8/00 -0400, Mike Mascari wrote: >Just curious, Don. But could you check to see what Oracle's >behavior is on this? That's the feature I was trying to mirror. >At the time, RI wasn't integrated so I wasn't thinking about this >issue. Sure, I understand. > And the Oracle docs state that DML triggers aren't fired >when a TRUNCATE is issued, so I didn't think there would be >issues there. Could you check? It refuses to do the TRUNCATE, whether or not there's a "ON DELETE CASCADE" modifier to the references. That seems reasonable - it allows one to still say "truncate's really fast because it doesn't scan the rows in the table", and refuses to break RI constraints. All that needs doing is to check for the existence of at least one RI trigger on the table that's being truncated, and saying "no way, jose" if we want to mimic Oracle in this regard. TODO item? - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Serviceand other goodies at http://donb.photo.net.
> All that needs doing is to check for the existence of > at least one RI trigger on the table that's being truncated, > and saying "no way, jose" if we want to mimic Oracle in > this regard. > > TODO item? OK, added: * Prevent truncate on table acting as foreign key -- Bruce Momjian | http://www.op.net/~candle 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
Don Baccus wrote: > > At 10:43 AM 6/8/00 -0400, Mike Mascari wrote: ... > > And the Oracle docs state that DML triggers aren't fired > >when a TRUNCATE is issued, so I didn't think there would be > >issues there. Could you check? > > It refuses to do the TRUNCATE, whether or not there's a > "ON DELETE CASCADE" modifier to the references. > > That seems reasonable - it allows one to still say "truncate's > really fast because it doesn't scan the rows in the table", > and refuses to break RI constraints. > > All that needs doing is to check for the existence of > at least one RI trigger on the table that's being truncated, > and saying "no way, jose" if we want to mimic Oracle in > this regard. > > TODO item? Sounds like it to me. Rats... Mike Mascari > > - Don Baccus, Portland OR <dhogaza@pacifier.com> > Nature photos, on-line guides, Pacific Northwest > Rare Bird Alert Service and other goodies at > http://donb.photo.net.
At 11:47 AM 6/8/00 -0400, Bruce Momjian wrote: >OK, added: > > * Prevent truncate on table acting as foreign key How about this: Prevent truncate on table referenced by foreign key - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Serviceand other goodies at http://donb.photo.net.
> At 11:47 AM 6/8/00 -0400, Bruce Momjian wrote: > > >OK, added: > > > > * Prevent truncate on table acting as foreign key > > How about this: Prevent truncate on table referenced by foreign key > Actually, I made it: * Prevent truncate on table with a referential integrity trigger Is that good? -- Bruce Momjian | http://www.op.net/~candle 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
At 05:20 PM 6/8/00 -0400, Bruce Momjian wrote: > * Prevent truncate on table with a referential integrity trigger > >Is that good? It's beautiful! - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Serviceand other goodies at http://donb.photo.net.
> >It seems the truncate command deletes all rows from a table even it is > >referenced by another tables. TRUNCATE is not in the standard any way, > >so I would not claim this is a bug. However, sometimes it would be > >helpful for a user to let him notice that the table about to be > >truncated is referenced by some tables. So I would propose to add > >"RESTRICT" option to the command. I mean if RESTRICT is specified, > >TRUNCATE will fail if the table is referenced. > > Shouldn't it always fail if an explicit foreign key reference > exists to the table, in much the way that delete of a referenced > row does? If it doesn't now, I think it's a bug. That would be better. I am just wondering how the checkings hurt the speed of TRUNCATE (if TRUNCATE is that slow, why we need it:-). > If the references are implicit (no REFERENCE or FOREIGN KEY given > to inform the db of the relationship) then a RESTRICT option to > truncate does seem useful. Can you tell me what are the implicit references? BTW, what do you think about DROP TABLE RESTRICT? I think this is a nice feature and should be added to the TODO list... -- Tatsuo IShii
Tatsuo Ishii writes: > That would be better. I am just wondering how the checkings hurt the > speed of TRUNCATE (if TRUNCATE is that slow, why we need it:-). You can make any code arbitrarily fast if it doesn't have to behave correctly. :-) -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
At 08:08 PM 6/10/00 +0200, Peter Eisentraut wrote: >Tatsuo Ishii writes: > >> That would be better. I am just wondering how the checkings hurt the >> speed of TRUNCATE (if TRUNCATE is that slow, why we need it:-). > >You can make any code arbitrarily fast if it doesn't have to behave >correctly. :-) Checking for existence or absence of triggers will be fast. Jan suggested aborting TRUNCATE if any (user or system) triggers are on the table. If I understood his message correctly, that is. Oracle only aborts for foreign keys, executing TRUNCATE and ignoring user triggers if they exist. Any thoughts? Rather than abort TRUNCATE due to the mere existence of a referential integrity trigger on the table, we could be a bit more sophisicated and only abort if an RI trigger exists where the referring table is non-empty. If the referring table's empty, no foreign keys will be stored in it and you can safely TRUNCATE. - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Serviceand other goodies at http://donb.photo.net.
Don Baccus wrote: > > At 08:08 PM 6/10/00 +0200, Peter Eisentraut wrote: > >Tatsuo Ishii writes: > > > >> That would be better. I am just wondering how the checkings hurt the > >> speed of TRUNCATE (if TRUNCATE is that slow, why we need it:-). The major performance difference between TRUNCATE and DELETE is realized at VACUUM time. > > > >You can make any code arbitrarily fast if it doesn't have to behave > >correctly. :-) > > Checking for existence or absence of triggers will be fast. > > Jan suggested aborting TRUNCATE if any (user or system) triggers > are on the table. If I understood his message correctly, that is. > > Oracle only aborts for foreign keys, executing TRUNCATE and ignoring > user triggers if they exist. > > Any thoughts? I agree with this. > > Rather than abort TRUNCATE due to the mere existence of a referential > integrity trigger on the table, we could be a bit more sophisicated > and only abort if an RI trigger exists where the referring table is > non-empty. If the referring table's empty, no foreign keys will be > stored in it and you can safely TRUNCATE. Sorry to ask for another favor, but what does Oracle do here? If a referring table has 1,000,000 rows in it which have been deleted but not vacuumed, what would the performance implications be? Just curious, Mike Mascari
Mike Mascari wrote: > Sorry to ask for another favor, but what does Oracle do here? If > a referring table has 1,000,000 rows in it which have been > deleted but not vacuumed, what would the performance implications > be? Referential integrity has no performance impact on VACUUM. If that's what you aren't sure about. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Jan Wieck wrote: > > Mike Mascari wrote: > > Sorry to ask for another favor, but what does Oracle do here? If > > a referring table has 1,000,000 rows in it which have been > > deleted but not vacuumed, what would the performance implications > > be? > > Referential integrity has no performance impact on VACUUM. If > that's what you aren't sure about. > > Jan Actually, I was worried that if TRUNCATE were to vist all referring tables to determine whether or not it was empty, rather then just issuing an elog() at the first RI trigger encountered, that it might wind up scanning a 1,000,000 tuple relation (the referring relation) where all the rows have been marked as deleted before determining that its okay to perform the TRUNCATE. I was hoping that Oracle simply disallowed TRUNCATE on tables with referring relations, regardless of whether or not there was actually any data in them, so that PostgreSQL could do the same. :-) Mike Mascari
At 07:48 PM 6/12/00 -0400, Mike Mascari wrote: >Actually, I was worried that if TRUNCATE were to vist all >referring tables to determine whether or not it was empty, rather >then just issuing an elog() at the first RI trigger encountered, >that it might wind up scanning a 1,000,000 tuple relation (the >referring relation) where all the rows have been marked as >deleted before determining that its okay to perform the TRUNCATE. >I was hoping that Oracle simply disallowed TRUNCATE on tables >with referring relations, regardless of whether or not there was >actually any data in them, so that PostgreSQL could do the same. >:-) Well, I think we probably could do so regardless of what Oracle does. Proper use of "on delete cascade" and "on delete set null" etc would seem to make it more convenient to delete rows in a set of related tables via delete rather than running around trying to truncate them in the right order so that you end up with empty tables before you delete the one with the RI triggers on it. - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Serviceand other goodies at http://donb.photo.net.
Can someone comment on this? > It seems the truncate command deletes all rows from a table even it is > referenced by another tables. TRUNCATE is not in the standard any way, > so I would not claim this is a bug. However, sometimes it would be > helpful for a user to let him notice that the table about to be > truncated is referenced by some tables. So I would propose to add > "RESTRICT" option to the command. I mean if RESTRICT is specified, > TRUNCATE will fail if the table is referenced. > > BTW, the keyword "RESTRICT" is inspired by the fact that DROP TABLE > has the same option according to the standard. If a table is > referenced by some tables and the drop table command has the RESTRICT > option, it would fail. This seems to be a nice feature too. > > Comments? > -- > Tatsuo Ishii > -- 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
> At 09:24 PM 6/8/00 +0900, Tatsuo Ishii wrote: > >It seems the truncate command deletes all rows from a table even it is > >referenced by another tables. TRUNCATE is not in the standard any way, > >so I would not claim this is a bug. However, sometimes it would be > >helpful for a user to let him notice that the table about to be > >truncated is referenced by some tables. So I would propose to add > >"RESTRICT" option to the command. I mean if RESTRICT is specified, > >TRUNCATE will fail if the table is referenced. > > Shouldn't it always fail if an explicit foreign key reference > exists to the table, in much the way that delete of a referenced > row does? If it doesn't now, I think it's a bug. > > If the references are implicit (no REFERENCE or FOREIGN KEY given > to inform the db of the relationship) then a RESTRICT option to > truncate does seem useful. TODO updated: * Prevent truncate on table with a referential integrity trigger (RESTRICT) -- 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