Re: DROP CASCADE transitive dependencies

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

C GG <cgg0007@gmail.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.