Re: Undocumented behavior od DROP SCHEMA ... CASCADE

On Fri, Aug 12, 2016 at 6:01 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> writes:
> > Tom Lane wrote:
> >> There's plenty of discoverability already. The documentation suggests
> >>
> >> (If you want to check what <command>DROP ... CASCADE</> will do,
> >> run <command>DROP</> without <literal>CASCADE</> and read the
> >> <literal>DETAIL</> output.)
>
> > True, but the DETAIL is capped at 100 objects (per
> > reportDependentObjects);
>
> True, though I've heard no complaints about that (and in the extreme,
> you can look into the server log).
>
> > also, object locks need to be acquired, which
> > can be very troublesome if you discover that some frequently-used object
> > is in the set to be dropped, by some unfortunate accident.
>
> You'd need the object locks in any case, to be sure things hold still long
> enough for their dependencies to be examined. It's possible a weaker lock
> type would suffice, but I'm not sure; we generally don't require exclusive
> lock on an object to add or remove dependencies on it.
>
> I'm not necessarily against adding a function to report the dependencies
> as a table rather than NOTICE output. But things have been like this
> for quite a few years and I can count the number of requests for such a
> function without running out of thumbs. Doesn't seem very high priority.
>

​FWIW I recently got bit hard by the fact that types will cascade drop
individual columns from existing tables...

If we are looking to improve things here I'd at least consider having the
default cascade to be safe and not drop persisted data (I suppose that
could functions linked to functional indexes...) and have a separate flag
that would also be permitted to destroy data. Having such a dependency
listing query distinguish between data-loss and other would be a good
intermediate step.

I haven't thought this out in depth, and its probably an unlikely area for
improvement, but having recently spent a couple hours re-learning this
(then confirming its working as intended via the docs) at least makes me
want to bring it up.

David J.