DROP CASCADE transitive dependencies

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

DROP CASCADE transitive dependencies

C GG
...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? 

All the dependent objects listed by `DROP SCHEMA blah` are fair game to be dropped, but if I add CASCADE I don't necessarily want to have it go beyond that initial list without my say-so.

The alternative is to drop each one in the list individually without CASCADE-- a tedious task I'd rather short-cut, but in a non-reckless manner ;) ...

CG

Reply | Threaded
Open this post in threaded view
|

Re: DROP CASCADE transitive dependencies

Tom Lane-2
C GG <[hidden email]> 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.

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.

                        regards, tom lane

Reply | Threaded
Open this post in threaded view
|

Re: DROP CASCADE transitive dependencies

C GG


On Mon, Dec 3, 2018 at 1:26 PM Tom Lane <[hidden email]> wrote:
C GG <[hidden email]> 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.
 
Reply | Threaded
Open this post in threaded view
|

Re: DROP CASCADE transitive dependencies

Pavel Stehule


po 3. 12. 2018 v 20:07 odesílatel C GG <[hidden email]> napsal:


On Mon, Dec 3, 2018 at 1:26 PM Tom Lane <[hidden email]> wrote:
C GG <[hidden email]> 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

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.
 
Reply | Threaded
Open this post in threaded view
|

Re: DROP CASCADE transitive dependencies

Alvaro Herrera-9
In reply to this post by C GG
On 2018-Dec-03, C GG wrote:

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

If you do DROP without cascade, the objects are going to be listed
in the DETAIL field of the ERROR, so you need to have
log_error_verbosity set to verbose or default; if you have it as terse,
they won't appear in the server log.

If you DROP with CASCADE, the objects would appear in a NOTICE message,
which may be below your log_min_messages.  Try with
SET LOCAL log_min_messages TO notice;
in the same transaction, just before the drop.

--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services