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?
dependency is stored in pg_depend query - so you just to iterate over this table.
Regards
Pavel
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.
