sql: refactor drop table cascade to reduce potential for bugs going forward

[arulajmani]

Is your feature request related to a problem? Please describe.

This is in context to #51813 to track follow up work that makes future bugs with DROP TABLE CASCADE less likely. The main motivation here is #51782 and #50997.

It isn't ideal that we do a recursive accumulation of objects that will be dropped in the preparation phase of DROP DATABASE CASCADE because this implicitly happens when we execute drops on individual objects. This is currently unavoidable because:

• We allow cross database references, so simply scanning the namespace table doesn't provide the full picture -- we need to recursively use the "dependsOn" field on the descriptor.

• We use this list of objects to filter out dependent objects which will be cascade deleted. This is more than just an optimization, as the individual object drop implementations expect dependent descriptors to not be dropped.

• The drop database job is responsible for schema changes on all dropped objects (instead of individual objects having their own jobs). If the job doesn't know of all the objects that are being dropped, it will leave behind dangling namespace/descriptor entries and data.

Describe the solution you'd like

• Change dropTableImpl,dropSequenceImpl and dropViewImpl to return a list of objects dropped instead of a list of cascading views. This would include the object itself in addition to any owned sequences and dependant views.

• When we implement DROP TYPE CASCADE this list should also include dropped types as well. Types for the most part will play well with DROP DATABASE CASCASDE as we don't allow cross-database type references. But the problem is pushed one level down to DROP SCHEMA instead, as we do allow cross-schema type references, and presumably DROP SCHEMA would have a similar structure to the one DROP DATABASE does today.

• Instead of pre-fetching descriptors during the planning phase , we should restrict the planning phase to getting entries from the namespace table and let execution get descriptors as and when we are about to call drop on an object. This allows us to check for dropped descriptors (which are dropped because of a cascade delete) and simply skip over them. This is much cleaner than special casing for dropped descriptors in individual object drop implementations, which can get a bit confusing

Jira issue: CRDB-3996

[arulajmani]

@ajwerner typed this up, would love to get some thoughts here!

[jordanlewis]

cc @ajwerner