Обсуждение: DROP CASCADE transitive dependencies
...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
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;BEGINdata=# DROP SCHEMA blah CASCADE;NOTICE: drop cascades to 278 other objectsDETAIL: drop cascades to type blah.timeclock_compute_hours_type...and 178 other objects (see server log for list)data=# rollback;ROLLBACKdata=#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 laneThanks Tom. I don't say it enough: I _really_ appreciate you and your consistent excellent contributions to PostgreSQL and to the PostgreSQL community.
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