Supports drop schema/database statement

[killme2008]

What problem does the new feature solve?

The greptimedb doesn't support drop [schema | database] [name] right now:

CREATE SCHEMA test;
Affected Rows: 1

DROP SCHEMA test;

Failed to execute, error: Datanode { code: 1001, msg: "Failed to execute sql, source: Failure during query execution, source: Cannot parse SQL, source: SQL statement is not supported:  DROP SCHEMA test;, keyword: SCHEMA" }

What does the feature do?

Drop schema(database) by sql statement.

Implementation challenges

No response

[TheR1sing3un]

Please assign it to me if you are willing to~

[MichaelScofield]

ping @TheR1sing3un , any updates?

[NiwakaDev]

Is this issue blocked until any issues are resolved? If not, I'd like to work on this issue.

[waynexia]

Appreciate it ❤️

[NiwakaDev]

Next I'll implement DropDatabaseProcedure. What do you think about this?

[NiwakaDev]

I have a question about the design of the lock_key return value in the DropDatabaseProcedure. Since the DropDatabaseProcedure deletes all tables on a schema, it needs to block all operations on the schema performed by other procedures. As far as I understand, I'd like to add the schema_name to the lock_key return value for CreateTableProcedure, AlterTableProcedure, and DropTableProcedure. Here's an example of adding schema_name to LockKey in CreateTableProcedure:

#[async_trait]
impl Procedure for CreateTableProcedure {
    //..
    fn lock_key(&self) -> LockKey {
        // We lock the whole table and the schema.
        let table_name = self.data.table_ref().to_string();
        LockKey::new(vec![table_name, self.request.schema_name.clone()])
    }
}

Similarly, for DropDatabaseProcedure, we also need to lock the schema_name, like so:

#[async_trait]
impl Procedure for DropDatabaseProcedure {
    //..

    fn lock_key(&self) -> LockKey {
        LockKey::new([
            self.request.schema_name.clone(),
            //..
        ])
    }
}

I think it is inefficient to lock the schema. Are there other effective approaches?

[evenyag]

I think it is inefficient to lock the schema.

Yes, we don't support hierarchy locking now. We could provide an intuitive implementation first:

• lists all tables under the schema
• locks tables listed
• drops those tables
• checks if new tables are added to the schema, if so, we abort the procedure and signal the procedure is failed

This means that a create table command aborts the drop database command. But users could fix this by re-submit a drop database command.

In the future, we can improve this by other approaches. There are some alternatives:

• maintains states for each database in memory and checks this state before creating/dropping tables
• support hierarchy locking

[evenyag]

We might choose another approach, see #1943 (comment)

[WenyXu]

@NiwakaDev After #3061, we allow the procedure to acquire a shared lock, which makes acquiring locks in the drop database procedure much more straightforward. I will refactor the locks of procedures recently to ensure we only need to acquire an exclusive lock on the database key during the drop database procedure.

[WenyXu]

@NiwakaDev, are there any updates? Would you like to continue working on this feature, Or may I take over the feature?

[tisonkun]

@WenyXu drop database is now tracked by #3516. Is it simliar to drop schema? I wonder if we can supersede this issue with other recent issues to keep the context updated.

[WenyXu]

@WenyXu drop database is now tracked by #3516. Is it simliar to drop schema? I wonder if we can supersede this issue with other recent issues to keep the context updated.

It's the same thing. Let's close it