Thread: Undocumented behavior od DROP SCHEMA ... CASCADE

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/9.5/static/sql-dropschema.html
Description:

DROP SCHEMA ... CASCADE has a behavior that has not been documented.
According to the documentation: "CASCADE - Automatically drop objects
(tables, functions, etc.) that are contained in the schema.".
Well, besides this, DROP SCHEMA ... CASCADE also drops all the views which
depends on the schema that is being dropped. It does not matter if the views
are in the public schema or another schema than the one being dropped, they
are deleted.

This is an IMPORTANT thing to know, especially for system designed to use
multiple schemas. Please document this behavior!

P.S.: If by any chance this is a bug and you plan on fixing it, please let
me know.

Thank you!

On 8/5/16 6:48 AM, denisa.cirstescu@asentinel.com wrote:
> Page: https://www.postgresql.org/docs/9.5/static/sql-dropschema.html
> Description:
>
> DROP SCHEMA ... CASCADE has a behavior that has not been documented.
> According to the documentation: "CASCADE - Automatically drop objects
> (tables, functions, etc.) that are contained in the schema.".
> Well, besides this, DROP SCHEMA ... CASCADE also drops all the views which
> depends on the schema that is being dropped. It does not matter if the views
> are in the public schema or another schema than the one being dropped, they
> are deleted.

Can you give an example?

--
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 12/08/16 14:47, Peter Eisentraut wrote:
> On 8/5/16 6:48 AM, denisa.cirstescu@asentinel.com wrote:
>> Page: https://www.postgresql.org/docs/9.5/static/sql-dropschema.html
>> Description:
>>
>> DROP SCHEMA ... CASCADE has a behavior that has not been documented.
>> According to the documentation: "CASCADE - Automatically drop objects
>> (tables, functions, etc.) that are contained in the schema.".
>> Well, besides this, DROP SCHEMA ... CASCADE also drops all the views which
>> depends on the schema that is being dropped. It does not matter if the views
>> are in the public schema or another schema than the one being dropped, they
>> are deleted.
>
> Can you give an example?

vik=# create schema a create table t (id int);
CREATE SCHEMA
vik=# create schema b create view v as select * from a.t;
CREATE SCHEMA
vik=# drop schema a cascade;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to table a.t
drop cascades to view b.v
DROP SCHEMA

I think the OP is complaining that cascading to b.v is not sufficiently
documented.  It seems logical to me that this would be the correct
behavior, but since at least one person got confused enough about it to
come and tell us, we should probably add some kind of warning or something.
--
Vik Fearing                                          +33 6 46 75 15 36
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Fri, Aug 12, 2016 at 8:54 AM, Vik Fearing <vik@2ndquadrant.fr> wrote:
>> I think the OP is complaining that cascading to b.v is not sufficiently
>> documented.  It seems logical to me that this would be the correct
>> behavior, but since at least one person got confused enough about it to
>> come and tell us, we should probably add some kind of warning or something.

> ​Yeah, it should probably be modified to something like:

> Automatically drop objects (tables, functions, etc...) that are contained
> in the schema.  Each object dropped is also done with the CASCADE option.
> This means that others schemas will be affected if they depend on objects
> in the named schema.

If we're going to add a warning about CASCADE being recursive, it would
logically need to be added to every last DROP command that has a CASCADE
option, which is most of them.  I don't necessarily object to that, but
we'd need a more boiler-plate, copy-and-pasteable phrasing.

A quick survey says that DROP SCHEMA is just about the only such command
that isn't documented with wording along the lines of "Automatically drop
objects that depend on the <whatever>", often with a parenthetical "such
as" to illustrate what sorts of objects might be dependent.  I think the
variant wording for DROP SCHEMA was intentional, with the thought that it
was easy to specify exactly which objects "depend on" a schema --- but is
that decision contributing to the confusion?

I'm inclined to suggest that maybe the generic phrasing could be
"Automatically drop objects that depend on the <whatever> [(such as ...)],
and in turn all objects that depend on those objects".

            regards, tom lane

I wrote:
> If we're going to add a warning about CASCADE being recursive, it would
> logically need to be added to every last DROP command that has a CASCADE
> option, which is most of them.  I don't necessarily object to that, but
> we'd need a more boiler-plate, copy-and-pasteable phrasing.
> ...
> I'm inclined to suggest that maybe the generic phrasing could be
> "Automatically drop objects that depend on the <whatever> [(such as ...)],
> and in turn all objects that depend on those objects".

Not hearing any alternative suggestions, I'm going to go do something
like the above.

            regards, tom lane

Tom Lane wrote:

> I'm inclined to suggest that maybe the generic phrasing could be
> "Automatically drop objects that depend on the <whatever> [(such as ...)],
> and in turn all objects that depend on those objects".

I think it'd be a good idea to add some more discoverability: what would
be deleted if an object X were to be deleted?  I suppose this is just a
recursive query on pg_depend, but expecting users to handle that on
their own is preposterous.  We could offer a simple function, to which
you pass classid, objid, objsubid, returns setof same plus obj_description.

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

Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> I think it'd be a good idea to add some more discoverability: what would
> be deleted if an object X were to be deleted?

There's plenty of discoverability already.  The documentation suggests

   (If you want to check what <command>DROP ... CASCADE</> will do,
   run <command>DROP</> without <literal>CASCADE</> and read the
   <literal>DETAIL</> output.)

Or you could do BEGIN; DROP ... CASCADE; ROLLBACK.

            regards, tom lane

Tom Lane wrote:
> Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> > I think it'd be a good idea to add some more discoverability: what would
> > be deleted if an object X were to be deleted?
>
> There's plenty of discoverability already.  The documentation suggests
>
>    (If you want to check what <command>DROP ... CASCADE</> will do,
>    run <command>DROP</> without <literal>CASCADE</> and read the
>    <literal>DETAIL</> output.)
>
> Or you could do BEGIN; DROP ... CASCADE; ROLLBACK.

True, but the DETAIL is capped at 100 objects (per
reportDependentObjects); also, object locks need to be acquired, which
can be very troublesome if you discover that some frequently-used object
is in the set to be dropped, by some unfortunate accident.

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

Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> Tom Lane wrote:
>> There's plenty of discoverability already.  The documentation suggests
>>
>> (If you want to check what <command>DROP ... CASCADE</> will do,
>> run <command>DROP</> without <literal>CASCADE</> and read the
>> <literal>DETAIL</> output.)

> True, but the DETAIL is capped at 100 objects (per
> reportDependentObjects);

True, though I've heard no complaints about that (and in the extreme,
you can look into the server log).

> also, object locks need to be acquired, which
> can be very troublesome if you discover that some frequently-used object
> is in the set to be dropped, by some unfortunate accident.

You'd need the object locks in any case, to be sure things hold still long
enough for their dependencies to be examined.  It's possible a weaker lock
type would suffice, but I'm not sure; we generally don't require exclusive
lock on an object to add or remove dependencies on it.

I'm not necessarily against adding a function to report the dependencies
as a table rather than NOTICE output.  But things have been like this
for quite a few years and I can count the number of requests for such a
function without running out of thumbs.  Doesn't seem very high priority.

            regards, tom lane

Tom Lane wrote:

> > also, object locks need to be acquired, which
> > can be very troublesome if you discover that some frequently-used object
> > is in the set to be dropped, by some unfortunate accident.
>
> You'd need the object locks in any case, to be sure things hold still long
> enough for their dependencies to be examined.  It's possible a weaker lock
> type would suffice, but I'm not sure; we generally don't require exclusive
> lock on an object to add or remove dependencies on it.

Of course, using a weak lock could run afoul of somebody changing the
dependencies underneath.  But even using a stronger lock is unlikely to
give any actual protection: in UI programs (be it GUI admin programs or
psql), more likely than not many users are going to run a check in one
transaction, then run the actual drop in a different transaction.

> I'm not necessarily against adding a function to report the dependencies
> as a table rather than NOTICE output.  But things have been like this
> for quite a few years and I can count the number of requests for such a
> function without running out of thumbs.  Doesn't seem very high priority.

Sure.  Perhaps this'd be a good item for a novice hacker.

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

David G. Johnston wrote:

> If we are looking to improve things here I'd at least consider having the
> default cascade to be safe and not drop persisted data (I suppose that
> could functions linked to functional indexes...) and have a separate flag
> that would also be permitted to destroy data.  Having such a dependency
> listing query distinguish between data-loss and other would be a good
> intermediate step.

Well, if you happen to drop a view for which you no longer have the
definition, you may be similarly screwed.  I prefer the approach that we
consider all drops as potentially dangerous.

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

Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> David G. Johnston wrote:
>> If we are looking to improve things here I'd at least consider having the
>> default cascade to be safe and not drop persisted data (I suppose that
>> could functions linked to functional indexes...) and have a separate flag
>> that would also be permitted to destroy data.  Having such a dependency
>> listing query distinguish between data-loss and other would be a good
>> intermediate step.

> Well, if you happen to drop a view for which you no longer have the
> definition, you may be similarly screwed.  I prefer the approach that we
> consider all drops as potentially dangerous.

There's also the minor problem that the SQL standard is quite clear about
what DROP CASCADE means, and it ain't that.

            regards, tom lane

Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> Tom Lane wrote:
>> You'd need the object locks in any case, to be sure things hold still long
>> enough for their dependencies to be examined.  It's possible a weaker lock
>> type would suffice, but I'm not sure; we generally don't require exclusive
>> lock on an object to add or remove dependencies on it.

> Of course, using a weak lock could run afoul of somebody changing the
> dependencies underneath.  But even using a stronger lock is unlikely to
> give any actual protection: in UI programs (be it GUI admin programs or
> psql), more likely than not many users are going to run a check in one
> transaction, then run the actual drop in a different transaction.

Well, obviously the output would not be authoritative about what might get
dropped in a later drop attempt.  But I'm thinking just about not getting
"cache lookup failed" or similar failures in the function itself.

Also, assuming that we did acquire full-strength locks, that would mean
that if you do
    begin;
    select pg_drop_cascades_to(foo);
    drop foo cascade;
    commit;
then in fact the function output WOULD be authoritative about what would
get dropped in the second step.  That seems like a useful property to
have, even if there are lots of production scenarios where you'd not want
to hold the locks long enough for a human to eyeball the list.  You
could imagine for instance an application quickly verifying that the
SELECT result matches a previously vetted list before barging ahead
with the DROP.  (Right now, you can approximate that sort of "safe
drop" by looking at the NOTICE output before committing --- but as you
noted, that only works up to 100 dependencies.)

            regards, tom lane