Re: Dropping dependent tables

[Tiffany Thang]

On Thu, Jun 3, 2021 at 10:18 AM Tom Lane  wrote:

> Tiffany Thang  writes:
> > I would like to write a SQL statement to drop all the tables owned by me
> > but a problem I’m struggling with is with referential integrity. The
> > statement I have now to generate the drop statements is
>
> > select 'drop table '||tablename||' cascade;' from pg_tables where
> > tableowner='';
>
> > The generated SQLs above might attempt to drop the parent tables first
> > before the child and to be able to drop all the tables, I had to run the
> > SQL script in multiple iterations. Not very clean.
>
> Uh ... it's not clear to me why that wouldn't work.  CASCADE should
> be able to take care of foreign keys:
>
> postgres=# create table t1 (f1 int primary key);
> CREATE TABLE
> postgres=# create table t2 (f2 int references t1);
> CREATE TABLE
> postgres=# drop table t1;
> ERROR:  cannot drop table t1 because other objects depend on it
> DETAIL:  constraint t2_f2_fkey on table t2 depends on table t1
> HINT:  Use DROP ... CASCADE to drop the dependent objects too.
> postgres=# drop table t1 cascade;
> NOTICE:  drop cascades to constraint t2_f2_fkey on table t2
> DROP TABLE
>
> Could you enlarge on what problem you saw, specifically?
>
> regards, tom lane
>

Thanks Tom for your response. I tried it again and I was not able to
reproduce the issue.

Obviously I did something incorrectly previously.

Thanks.

Tiff

Re: Dropping dependent tables

[Tom Lane]

Tiffany Thang  writes:
> I would like to write a SQL statement to drop all the tables owned by me
> but a problem I’m struggling with is with referential integrity. The
> statement I have now to generate the drop statements is

> select 'drop table '||tablename||' cascade;' from pg_tables where
> tableowner='';

> The generated SQLs above might attempt to drop the parent tables first
> before the child and to be able to drop all the tables, I had to run the
> SQL script in multiple iterations. Not very clean.

Uh ... it's not clear to me why that wouldn't work.  CASCADE should
be able to take care of foreign keys:

postgres=# create table t1 (f1 int primary key);
CREATE TABLE
postgres=# create table t2 (f2 int references t1);
CREATE TABLE
postgres=# drop table t1;
ERROR:  cannot drop table t1 because other objects depend on it
DETAIL:  constraint t2_f2_fkey on table t2 depends on table t1
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
postgres=# drop table t1 cascade;
NOTICE:  drop cascades to constraint t2_f2_fkey on table t2
DROP TABLE

Could you enlarge on what problem you saw, specifically?

regards, tom lane

Dropping dependent tables

[Tiffany Thang]

 Hi,
I would like to write a SQL statement to drop all the tables owned by me
but a problem I’m struggling with is with referential integrity. The
statement I have now to generate the drop statements is

select 'drop table '||tablename||' cascade;' from pg_tables where
tableowner='';

The generated SQLs above might attempt to drop the parent tables first
before the child and to be able to drop all the tables, I had to run the
SQL script in multiple iterations. Not very clean.

Can someone advise how I could formulate the SQL to check for table
dependencies to generate a SQL script that drops the child tables first
before the parent? Or are there any better alternatives?

Thank you.

Tiff


Virus-free.
www.avast.com

<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>