Re: DROP CASCADE transitive dependencies

On Mon, Dec 3, 2018 at 1:26 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> C GG <cgg0007(at)gmail(dot)com> writes:
> > ...PostgreSQL 9.5...
> > `DROP SCHEMA blah;` reports all the dependent objects and advises to
> `DROP
> > SCHEMA blah CASCADE;` ...
>
> > Will DROP ... CASCADE traverse the entire dependency tree for each of the
> > dependent objects (potentially dropping something unintended), or will it
> > stop at the first level and balk at any new transitive dependencies?
>
> The former. However, the list of dependencies it's showing you as
> potentially dropped already includes transitive dependencies; there
> aren't going to be "new" ones unless somebody is adding things
> concurrently.
>

That's good news!

>
> If you're feeling paranoid, you could always do
>
> begin;
> drop ... cascade;
>
> and then look at the reported list of objects before deciding whether
> to commit or roll back.
>

Me, paranoid? Yes. Yes I am.

So I did that--

data=# begin;
BEGIN
data=# DROP SCHEMA blah CASCADE;
NOTICE: drop cascades to 278 other objects
DETAIL: drop cascades to type blah.timeclock_compute_hours_type
...
and 178 other objects (see server log for list)
data=# rollback;
ROLLBACK
data=#

and I can't see any of the other 178 objects in the server log. I did see
all the deadlock reports because I had left the transaction hanging open
while I went rubbernecking. ;) Maybe my log level isn't detailed enough.

Also-- it is interesting to note that the list that I was shown when I
executed `DROP SCHEMA blah;` is only 100 objects long. So that tells me
that there's 178 other entries I'm not seeing. Where's that tin-foil hat?

Any suggestions for getting the names of the other 178 dependent objects?

>
> regards, tom lane
>

Thanks Tom. I don't say it enough: I _really_ appreciate you and your
consistent excellent contributions to PostgreSQL and to the PostgreSQL
community.