feature(DDL): drop schema cascade

[st1page]

Is your feature request related to a problem? Please describe.

https://www.postgresql.org/docs/current/sql-dropschema.html

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

Describe the solution you'd like

No response

Describe alternatives you've considered

No response

Additional context

No response

[adevday]

Dropping schema with cascade mode is a requirement to pass the dbt adapter tests.
#7577

[st1page]

Dropping schema with cascade mode is a requirement to pass the dbt adapter tests. #7577

I can transfer the assignee if anyone is interested in it, will you do this issue? 👀

[adevday]

Dropping schema with cascade mode is a requirement to pass the dbt adapter tests. #7577

I can transfer the assignee if anyone is interested in it, will you do this issue? 👀

I'm not sure. I will first figure out missing features.

[lmatz]

done by #11250 ?

[st1page]

#11250

[yezizp2012]

#11250 only supported drop cascade for relations including tables, mviews, views, indexes, sources and sinks, but not schema. To support that it still requires drop cascade for function and connection. But the dependency info for function is not maintained yet. Let's reopen it to see if anyone can help to do it. 🥵

[neverchanje]

DROP SCHEMA IF EXISTS "test16919875632139641546_test_basic" CASCADE:
Feature is not yet implemented: DROP CASCADE

Required by dbt's basic tests. cc @chenzl25

[chenzl25]

DROP SCHEMA IF EXISTS "test16919875632139641546_test_basic" CASCADE:
Feature is not yet implemented: DROP CASCADE

Required by dbt's basic tests. cc @chenzl25

How did you run these tests? I think dbt does not rely on this feature. BTW, risingwave current dropping mechanism is not easy to support this feature. We can delay this feature until it becomes urgent.

[fuyufjh]

closed #11250

[james-johnston-thumbtack]

@fuyufjh @yezizp2012 Could this issue be reopened? This would be a convenient way for dropping sandbox schemas created by dbt in a dev environment. It would also be a convenient way for cleaning up schemas if we choose to go with a blue/green deployment model at a schema level of granularity (similar to https://materialize.com/docs/manage/blue-green/ ).

It is not urgent (yet) for me (not necessary for initial RW deployment at a small scale), but it would be nice if the issue can be reopened and tracked for development.

[chenzl25]

#11250 only supported drop cascade for relations including tables, mviews, views, indexes, sources and sinks, but not schema. To support that it still requires drop cascade for function and connection. But the dependency info for function is not maintained yet. Let's reopen it to see if anyone can help to do it. 🥵

Reopen that issue, but as @yezizp2012 said, some issues need to be resolved before we can support drop schema cascade.

[BugenZhao]

Another use case is for cleaning up in end-to-end tests. Say that we create a complex dependency topology between tables and materialized views, we can organize them within a schema and simply execute DROP SCHEMA CASCADE to clean them up, eliminating the need to write individual DROP statements for each streaming job.

[xxchan]

Another requirement: dbeaver/dbeaver#23001 (#15685)

Another use case is for cleaning up in end-to-end tests.

+1 This can greatly simplify:

risingwave/e2e_test/source/basic/kafka.slt

Lines 827 to 918 in 532134e

	statement ok
	drop materialized view source_mv1
	statement ok
	drop materialized view source_mv2
	statement ok
	drop materialized view source_mv3
	statement ok
	drop table s6
	statement ok
	drop table s8
	statement ok
	drop table s8_no_schema_field
	statement ok
	drop table s9
	statement ok
	drop table s10
	statement ok
	drop table s11
	statement ok
	drop table s12
	statement ok
	drop table s13
	statement ok
	drop table s14
	statement ok
	drop table s15
	statement ok
	drop table s16
	statement ok
	drop source s17
	statement ok
	drop source s18
	statement ok
	drop table s20
	statement ok
	drop table s21
	statement ok
	drop source s22
	statement ok
	drop source s23
	statement ok
	drop source s24
	statement ok
	drop table s27
	# statement ok
	# drop table s28
	statement ok
	drop table s29
	statement ok
	DROP TABLE mongo_customers;
	statement ok
	DROP TABLE mongo_customers_no_schema_field;
	statement ok
	DROP TABLE upsert_students;
	statement ok
	DROP TABLE upsert_students_default_key;
	statement ok
	drop table dbz_ignore_case_json;
	statement ok
	drop table source_with_rdkafka_props;
	statement ok
	drop table debezium_ignore_key;

I have used DROP SOURCE CASCADE, which already greatly simplified the cleanup, and CREATE SCHEMA would be even better.

[xxchan]

But the dependency info for function is not maintained yet.

IIRC functions aren't namespaced yet (all functions are global) 🤡 #12422

[yezizp2012]

But the dependency info for function is not maintained yet.

IIRC functions aren't namespaced yet (all functions are global) 🤡 #12422

All UDFs are already namespaced but search paths are ignored at binding step. 🥵

[neverchanje]

This feature is also required by atlasgo.

./atlas schema clean -u 'postgresql://root@localhost:4566/dev?sslmode=disable'

It provides a clean sub-command that helps conveniently drop the entire schema.

[justinjoseph89]

any plans to support this anytime soon? This feature is needed in sqlmesh as well if we want to invalidate an environment crated by the developers. Sqlmesh command to do the invalidate will return success as risingwave is not considering it as an error.

[yezizp2012]

Since the dependency information of UDF is now resolved and managed, I will work on it this week.