sql: create new indexes rather than a new table in TRUNCATE

[ajwerner]

Is your feature request related to a problem? Please describe.
Today the implementation of TRUNCATE creates a new table with the same structure as an old one. It then drops the old one and renames it to the new one. This has caused a number of problems and bugs. Worse yet, it means that a table ID is not a stable identifier, complicating how we store references to it in other tables.

Describe the solution you'd like

This was probably done because prior to 20.1, we required that every table have a primary index with ID 1. This is no longer the case since we implemented primary key changes. We should just drop all of the existing indexes and create new ones in truncate.

Describe alternatives you've considered
None

Additional context

This will make challenges related to renaming things which are referenced easier. See #10083 and #51090.

One problem here is that the index IDs might get quite high. This could be mitigated with #47981.

[blathers-crl]

Hi @ajwerner, I've guessed the C-ategory of your issue and suitably labeled it. Please re-label if inaccurate.

While you're here, please consider adding an A- label to help keep our repository tidy.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[knz]

One problem here is that the index IDs might get quite high.

Instead of a free list you can look at the minimum value used. Then when truncating, if all the indexes fit under the minimum, start from 1 again. This ensures indexes starting from 1 every 2 truncates.

[knz]

(I love this plan though.)

[petermattis]

This is a great idea!

[ajwerner]

Full credit goes to @lucy-zhang

[awoods187]

this is cool!

[thoszhang]

@rohany tentatively assigning to you, feel free to update/change as necessary.