Closed
Description
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.
root@127.0.0.1:53338/movr> CREATE DATABASE db;
CREATE DATABASE
Time: 2.743ms
root@127.0.0.1:53338/movr> CREATE SEQUENCE db.seq;
CREATE SEQUENCE
Time: 3.191ms
root@127.0.0.1:53338/movr> DROP DATABASE db CASCADE;
DROP DATABASE
Time: 45.877ms
root@127.0.0.1:53338/movr> INSERT INTO t(b) VALUES(2);
ERROR: nextval(): relation "db.seq" does not exist
SQLSTATE: 42P01
root@127.0.0.1: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
SERIALhave 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.