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 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?
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 tooAny 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, Pennsylvania 19026
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, Pennsylvania 19026
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 tooAny objections?
That looks pretty sweet to me...
Chris