Thread: User-friendliness for DROP RESTRICT/CASCADE
I have been reviewing Rod Taylor's pg_depend patch, which among other
things adds SQL-compliant DROP RESTRICT/CASCADE syntax and prevents
you from dropping things that other things depend on, as in ye olde
novice error of dropping a function used by a trigger.
As submitted, the patch gives elog(ERROR) as soon as it finds any
dependency, if you've specified (or defaulted to) DROP RESTRICT
behavior. This means you only find out about one randomly-chosen
dependency of the target object, and have no easy way to know what
else might get dropped if you say DROP CASCADE.
I am thinking of changing the behavior so that it reports *all* the
dependencies via NOTICEs before finally failing. So instead of this:
DROP TYPE widget RESTRICT; -- fail
ERROR: Drop Restricted as Operator <% Depends on Type widget
you might see this:
DROP TYPE widget RESTRICT; -- fail
NOTICE: operator <% depends on type widget
NOTICE: operator >% depends on type widget
NOTICE: operator >=% depends on type widget
ERROR: Cannot drop type widget because other objects depend on itUse DROP ... CASCADE to drop the dependent objects
too
Any objections?
Also, would it be a good idea to make it *recursively* report all
the indirect as well as direct dependencies? The output might get
a little bulky, but if you really want to know what DROP CASCADE
will get you into, seems like that is the only way to know.
To work recursively without getting into an infinite loop in the case of
circular dependencies, we'd need to make DROP actually drop each object
and CommandCounterIncrement, even in the RESTRICT case; it would rely on
rolling back the entire transaction when we finally elog(ERROR). This
might make things a tad slow, too, for something with many dependencies
... but I don't think we need to worry about making an error case fast.
Comments?
regards, tom lane
Tom Lane wrote: > > DROP TYPE widget RESTRICT; -- fail > NOTICE: operator <% depends on type widget > NOTICE: operator >% depends on type widget > NOTICE: operator >=% depends on type widget > ERROR: Cannot drop type widget because other objects depend on it > Use DROP ... CASCADE to drop the dependent objects too > > Any objections? > > Also, would it be a good idea to make it *recursively* report all > the indirect as well as direct dependencies? The output might get > a little bulky, but if you really want to know what DROP CASCADE > will get you into, seems like that is the only way to know. > > To work recursively without getting into an infinite loop in the case of > circular dependencies, we'd need to make DROP actually drop each object > and CommandCounterIncrement, even in the RESTRICT case; it would rely on > rolling back the entire transaction when we finally elog(ERROR). This > might make things a tad slow, too, for something with many dependencies > ... but I don't think we need to worry about making an error case fast. > > Comments? It would be nice if it is easy to do. -- 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
Tom Lane wrote: > Also, would it be a good idea to make it *recursively* report all > the indirect as well as direct dependencies? The output might get > a little bulky, but if you really want to know what DROP CASCADE > will get you into, seems like that is the only way to know. > > To work recursively without getting into an infinite loop in the case of > circular dependencies, we'd need to make DROP actually drop each object > and CommandCounterIncrement, even in the RESTRICT case; it would rely on > rolling back the entire transaction when we finally elog(ERROR). This > might make things a tad slow, too, for something with many dependencies > ... but I don't think we need to worry about making an error case fast. > > Comments? > Seems like the best approach to me. There's nothing more annoying than fixing errors one at a time, just to see what the next one is. It would be nice if the recursive dependency checking function was available as an end user function too, so you could analyze dependencies before even trying to drop something, or even just to understand a database schema you've inherited from someone else. Joe
Joe Conway <mail@joeconway.com> writes:
> It would be nice if the recursive dependency checking function was
> available as an end user function too, so you could analyze dependencies
> before even trying to drop something, or even just to understand a
> database schema you've inherited from someone else.
It'd be a pretty trivial exercise to build something that looks at the
pg_depend entries and generates whatever kind of display you want.
David Kaplan reminded me that there is another UI issue to be
considered: when we *are* doing a DROP CASCADE, should the dropped
dependent objects be reported somehow? As it stands, Rod's patch emits
elog(NOTICE) messages in this case, but I am wondering whether that will
be seen as useful or merely annoying chatter.
regards, tom lane
On Wed, 2002-06-26 at 22:30, Tom Lane wrote: > Joe Conway <mail@joeconway.com> writes: > > It would be nice if the recursive dependency checking function was > > available as an end user function too, so you could analyze dependencies > > before even trying to drop something, or even just to understand a > > database schema you've inherited from someone else. > > It'd be a pretty trivial exercise to build something that looks at the > pg_depend entries and generates whatever kind of display you want. > > David Kaplan reminded me that there is another UI issue to be > considered: when we *are* doing a DROP CASCADE, should the dropped > dependent objects be reported somehow? As it stands, Rod's patch emits > elog(NOTICE) messages in this case, but I am wondering whether that will > be seen as useful or merely annoying chatter. If the notices about implicit drops (triggers on tables, etc.) has been found to be useful in both creation and destruction then I would assume that this information would be wanted as well. If the above information has not been found to be useful in the past, then I would expect it to continue as chatter. Personally, I find it to be chatter and turn off NOTICES in general, but believe it to be consistent with similar messages in the past.
Rod Taylor wrote: > > David Kaplan reminded me that there is another UI issue to be > > considered: when we *are* doing a DROP CASCADE, should the dropped > > dependent objects be reported somehow? As it stands, Rod's patch emits > > elog(NOTICE) messages in this case, but I am wondering whether that will > > be seen as useful or merely annoying chatter. > > If the notices about implicit drops (triggers on tables, etc.) has been > found to be useful in both creation and destruction then I would assume > that this information would be wanted as well. > > If the above information has not been found to be useful in the past, > then I would expect it to continue as chatter. > > Personally, I find it to be chatter and turn off NOTICES in general, but > believe it to be consistent with similar messages in the past. Agreed. If you issue a single DROP that hits other objects, I think people would want to see that, but then again, if you drop the table, you would expect triggers and sequences to disappear with no mention. Tough one. -- 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
> DROP TYPE widget RESTRICT; -- fail > NOTICE: operator <% depends on type widget > NOTICE: operator >% depends on type widget > NOTICE: operator >=% depends on type widget > ERROR: Cannot drop type widget because other objects depend on it > Use DROP ... CASCADE to drop the dependent objects too > > Any objections? That looks pretty sweet to me... Chris