sql: optimize DROP and TRUNCATE

[mberhault]

TRUNCATE currently deletes all rows in the table before returning, and DROP just calls TRUNCATE followed by table descriptor deletions.

Instead, we should introduce a deleted field for tables causing the following:

• best-effort data deletion
• hidden from all sql statements
• not included in accounting (well, we don't have accounting yet, so that's easy).

DROP would just mark the table as deleted, remove the name -> ID entry and exit. TRUNCATE would call DROP and create a new table with the same name.

[petermattis]

I was thinking about this as well, though it is obviously lower priority than getting various bits of functionality working first. It would be great if we could figure out how to perform the deletion as part of normal compactions.

[mberhault]

We don't have priorities, or I would have put it as "get around to it when someone's bored".

[petermattis]

We can make our own priority labels.

[tbg]

so the idea here is to make data deletion asynchronous? There's probably value in not making it too asynchronous. If I drop a table, I'd at the very least want the storage stats to update quickly, and to do that you might as well delete it all as soon as you can.

[mberhault]

We haven't started doing accounting for structured data, but we'll probably need per-table and per-database stats, so we'll need some logic for aggregation.
My suggestion would be that marking a table a deleted (and therefore unreachable to the user) would remove that table from the accounting aggregation. From the user's point of view, this would provide the proper behavior: the table is no longer visible or accessible, accounting is decremented, and the name is reusable.

[vivekmenezes]

related to

#3274

[vivekmenezes]

Implementing Truncate, one thought is to use a new table ID and delete all the keys from the old table ID. Unfortunately that's complicated by the fact that we have cross table references from foreign keys and interleaved tables that use the table ID as the reference, and we would also need to update those. I think it's best to just disable the table from reads/writes, truncate it and reenable it. I'll try that option for now.