POSTGRES - Risk of dropping foreign data wrapper/schema

Question

We had one of the devs create a foreign data wrapper with these commands:

CREATE SERVER serverName FOREIGN DATA WRAPPER postgres_fdw OPTIONS (xxxx);
CREATE USER MAPPING FOR user SERVER foreign_db OPTIONS (user 'xxxx', password 'xxxx');
CREATE SCHEMA foreign_db;
IMPORT FOREIGN SCHEMA public FROM SERVER serverName INTO foreign_db;

To drop this schema the suggestion was to run:

DROP SCHEMA if exists foreign_db cascade;
DROP USER mapping if exists for user server foreign_db;
DROP SERVER if exists serverName;

In the spec I see this for CASCADE:

Automatically drop objects (tables, functions, etc.) that are contained in the schema, and in turn all objects that depend on those objects

what concerns me is this line:

and in turn all objects that depend on those objects

My question is there a possibility of dropping anything outside of foreign_db schema and if yes, how can I check it?

Thank you.

klin · Accepted Answer · 2019-01-11 17:03:30Z

3

It is possible that the command drops something outside the schema. Consider this:

create schema example;
create table example.my_table (id int);
create view public.my_view as select * from example.my_table;

If the schema is dropped with the cascade option, public.my_view will also be dropped. However, the behavior is logical and desirable.

You can check this executing these commands one by one:

begin;
drop schema example cascade;
rollback;

The schema will not be dropped and after drop... you should get something like this:

NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to table example.my_tabledrop cascades to view my_view

Alternatively, you can use the system catalog pg_depend, see this answer How to list tables affected by cascading delete.

edited Jan 11, 2019 at 17:03

answered Jan 11, 2019 at 16:57

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

POSTGRES - Risk of dropping foreign data wrapper/schema

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related