Re: DROP CASCADE transitive dependencies

po 3. 12. 2018 v 20:07 odesílatel C GG <cgg0007(at)gmail(dot)com> napsal:

>
>
> 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?
>

you can use some of mentioned queries
https://wiki.postgresql.org/wiki/Pg_depend_display
https://dba.stackexchange.com/questions/78301/postgresql-dependencies-on-a-schema

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.
>
>