DROP CASCADE transitive dependencies

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.

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

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.

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

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?

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

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