sql: create an admin owned trash database for dropped tables

[benesch]

If you forget to change a table's GC TTL before you drop it, e.g via.

ALTER TABLE foo EXPERIMENTAL CONFIGURE ZONE '{gc: {ttlseconds: 30}}'

you won't ever be able to change it after DROP TABLE foo. You just can't name the table.

ALTER TABLE foo EXPERIMENTAL CONFIGURE ZONE '{gc: {ttlseconds: 30}}'
pq: relation "foo" does not exist

🤦‍♂️

I think @knz might have plans to fix this by allowing you to target tables by ID in DDL statements. Regardless, we'll need to do some work even after we have the syntax to make sure ALTER TABLE ... EXPERIMENTAL CONFIGURE ZONE doesn't panic if it loads a dropped table desc.

Jira issue: CRDB-5008

[bdarnell]

As an alternative to targeting by ID in ALTER TABLE, we could have an undelete/undrop command to bring the table back from its "trash" state and give it a name again.

Also, in either case, we'll need to make sure there's some way to discover the ID to use.

[vivekmenezes]

good catch @benesch . The TestDropTableDeleteData test uses

if _, err := sqlDB.Exec(`INSERT INTO system.zones VALUES ($1, $2)`, descs[i].ID, buf); err != nil {
			t.Fatal(err)
		}

but that facility is only available to root.

[knz]

My personal preference would be to have DROP of anything actually move the thing to a root-owned "trash" database, where it can be renamed from, until the data deletion actually completes (then the descriptor can be removed).

[knz]

Filed #26543 for per-ID access.

[vivekmenezes]

The goal here is to change DROP to rename the table to trash/id where id is the descriptor id of the table. The table will inherit the zone config of the original table. A DROP DATABASE will move all the underlying tables to trash without the database. A TRUNCATE will leave the original table as empty with the data under trash/id

An admin can use ALTER TABLE DROP|RENAME to drop or bring back a table.

Once this is complete, we will stop allowing AS OF SYSTEM TIME queries over the original table before the DROP timestamp.

[postamar]

Some of what's covered by this longstanding issue came to the forefront in a recent incident. As a user, I should be able to change the GC TTL of a table after it's dropped, but I can't. We need to fix this.

[ajwerner]

The proposal of the moment is to create a vtable which discovers all dropped descriptors which can hold zone configs (tables, materialized views, sequences, databases), and it lists

• ID
• names (at the time, unqualified)
• parent IDs
• drop time
• current TTL.

Then, additionally, we create a builtin which can be used by an admin to inject a new TTL for a given ID which is dropped.

Taken together, this will allow users to find and update the TTL for dropped things.

---

In terms of implementation details, the code which goes to write the zone config entry should make sure that the descriptor exists and is dropped in the same transaction that writes the new zone config entry.