sql: DROP DATABASE/SCHEMA CASCADE may break sequences that have cross schema references

[arulajmani]

Describe the problem

Currently, CRDB doesn't implement DROP SEQUENCE CASCADE. We simply disallow dropping sequences that have usages by tables when a user tries to drop a sequence. This check is not performed during a DROP DATABASE though -- so if a sequence is used by a table in a different database, we end up having dropped a sequence that a table still relies on. This leads to a corrupted default expression.

To Reproduce

What did you do? Describe in your own words.

[email protected]:53338/movr> CREATE DATABASE db;
CREATE DATABASE

Time: 2.743ms

[email protected]:53338/movr> CREATE SEQUENCE db.seq;
CREATE SEQUENCE

Time: 3.191ms


[email protected]:53338/movr> DROP DATABASE db CASCADE;
DROP DATABASE

Time: 45.877ms

[email protected]:53338/movr> INSERT INTO t(b) VALUES(2);
ERROR: nextval(): relation "db.seq" does not exist
SQLSTATE: 42P01
[email protected]:53338/movr>

Expected behavior
There shouldn't be a default expression after the database is dropped. Possible solutions:

• Disallow cross database usages completely (though this must be done keeping sql: sequences created by SERIAL have weird semantics with regards to database #51090 in mind, else serial sequences for tables created in a different database won't work).
• Actually implement drop database cascade and use CASCADE as the drop behavior when dropping a sequence.

[vy-ton]

Should we do the same as #59020 here to prevent this problematic state?

@solongordon or @jordanlewis for 21.1 prioritization

[ajwerner]

We should definitely do something. I think that @the-ericwang35's work should fix this for new sequences but it won't fix old ones.

[ajwerner]

Postgres drops the default value from the column that depends on it. We could do that. Lower priority.