sql: zone configuration created directly before drop may not be applied

[stevendanna]

Describe the problem

If we create a database and a table:

demo@127.0.0.1:26257/movr> CREATE DATABASE baz;
CREATE DATABASE

Time: 3ms total (execution 3ms / network 0ms)

demo@127.0.0.1:26257/movr> CREATE TABLE baz.bat (pk int primary key);                                                                                                                                                                                                                                                                                            CREATE TABLE

Time: 3ms total (execution 2ms / network 0ms)

We shortly after see a span configuration for the new table:

> SELECT crdb_internal.pretty_key(start_key, 0), crdb_internal.pb_to_json('cockroach.roachpb.SpanConfig', config) FROM system.span_configurations WHERE crdb_internal.pretty_key(start_key, 0) LIKE '%114';
  crdb_internal.pretty_key |                                             crdb_internal.pb_to_json
---------------------------+--------------------------------------------------------------------------------------------------------------------
  /114                     | {"gcPolicy": {"ttlSeconds": 90000}, "numReplicas": 1, "rangeMaxBytes": "536870912", "rangeMinBytes": "134217728"}
(1 row)

If I modify the baz database's zone configuration, I expect that the span configuration for the span starting at /114 will be updated. However, if I create this zone configuration and then quickly drop the table and the database:

ALTER DATABASE baz CONFIGURE ZONE USING gc.ttlseconds = 1; DROP TABLE baz.bat; DROP DATABASE baz;

The zone configuration update is sometimes never reflected in the span configuration. I believe that most proximately this is the result of the fact that in DROP DATABASE, we delete the zone configuration immediately if the database does not have any tables:

https://github.com/cockroachdb/cockroach/blob/70e868e6546df5e0d29b40489a0416c7c67f6b9d/pkg/sql/schema_changer.go#L2695-L2710

This isn't a big usability issue, but it does mean that one of backup/restore's attempt to make sure some cleanups happen quickly don't work correctly:

https://github.com/cockroachdb/cockroach/blob/master/pkg/ccl/backupccl/restore_job.go#L2730-L2737

Jira issue: CRDB-19588

[ajwerner]

This relates to https://github.com/cockroachdb/cockroach/issues/26476. More generally, our story around GC and dynamic ttls is largely non-sensical.

[postamar]

I've assigned this to myself for the purpose of figuring out what the broader problem is, here, and bringing together various stakeholders to fix it.

First of all, there's the problem that zone configs are defined hierarchically and inherit from their ancestors, but the hierarchy's lifecycle isn't coordinated; even worse the zone config values themselves also determine this lifecyle. This issue is one instance of that: the db's zone config disappears despite there being zombie tables that depend on it, so things go awry. So far I see three potential kind of fixes, but there might be more:

1. When dropping the DB, don't erase the zone config eagerly but fire up a GC job in all cases. The GC job must walk down the whole zone config hierarchy to find the max TTL among its descendants because it can't do anything until that expires.
2. When dropping the DB, GC TTLs in the descendant zone configs get forcibly upserted.
3. Zone configs are always fully materialized, no more walking the hierarchy to build them upon request. Every time a zone config changes all of its dependencies need to be refreshed.

Then, there's the problem that how tables are GC'ed is the result of an accident of history: the GC job relies on the table descriptor and the zone config instead of its behaviour (TTL etc) being defined solely by its own job details.

Somewhat related in https://github.com/cockroachdb/cockroach/issues/26476 there's a legitimate need to soft-delete tables and whatnot by moving them to some trash schema or db. How do we deal with zone configs if we want to do that? Do we want to do that?

Going even further one may wonder what is the purpose at all of a SQL-level GC job, just DelRangeing the table data and Deleting the table metadata at the end of the DROP schema change should be enough, now that DelRange exists (it's MVCC compatible, right? that was the whole point IIRC).

cc @devadvocado: zone configs are one of those things that SQL Schema owns but which are mostly used outside of the team. Unsurprisingly the expected behaviour is poorly defined, especially as it appears between how the zone configs change over time and how the associated schema objects change over time.

[postamar]

I've unassigned myself considering that I'm very unlikely to close this before going on leave.

[postamar]

This might relate to the proposed concept of a "root descriptor" which is parent to all DBs. cc @ajwerner