sql: implement the DROP TYPE ... CASCADE command

[rohany]

We don't currently support the CASCADE suffix.

Jira issue: CRDB-4040
Epic link: CRDB-13664

[solongordon]

Probably won't be completed for 20.2. Per Rohan this is difficult to implement with the current schema change framework because the type cannot be dropped until all objects which use it have been dropped. Rollback is also quite tricky.

[simplenotezy]

Currently using TypeORM with cockroachdb as a driver; is there a workaround on this? The app simply won't boot:

[Nest] 85679  - 06/20/2022, 8:47:09 PM     LOG [RedisModule] default: Connected successfully to the server
query: CREATE EXTENSION IF NOT EXISTS "uuid-ossp"
query: CREATE EXTENSION IF NOT EXISTS "postgis"
query: SHOW server_version;
query: START TRANSACTION
query: SHOW SERVER_VERSION
query: SELECT 'DROP VIEW IF EXISTS "' || schemaname || '"."' || viewname || '" CASCADE;' as "query" FROM "pg_views" WHERE "schemaname" IN (current_schema()) AND "viewname" NOT IN ('geography_columns', 'geometry_columns', 'raster_columns', 'raster_overviews')
query: SELECT 'DROP MATERIALIZED VIEW IF EXISTS "' || schemaname || '"."' || matviewname || '" CASCADE;' as "query" FROM "pg_matviews" WHERE "schemaname" IN (current_schema())
query: SELECT 'DROP TABLE IF EXISTS "' || schemaname || '"."' || tablename || '" CASCADE;' as "query" FROM "pg_tables" WHERE "schemaname" IN (current_schema()) AND "tablename" NOT IN ('spatial_ref_sys')
query: SELECT 'DROP TYPE IF EXISTS "' || n.nspname || '"."' || t.typname || '" CASCADE;' as "query" FROM "pg_type" "t" INNER JOIN "pg_enum" "e" ON "e"."enumtypid" = "t"."oid" INNER JOIN "pg_namespace" "n" ON "n"."oid" = "t"."typnamespace" WHERE "n"."nspname" IN (current_schema()) GROUP BY "n"."nspname", "t"."typname"
query: DROP TYPE IF EXISTS "public"."gender_enum" CASCADE;
query: DROP TYPE IF EXISTS "public"."sexual_orientations_enum" CASCADE;
query failed: DROP TYPE IF EXISTS "public"."gender_enum" CASCADE;
error: error: unimplemented: DROP TYPE CASCADE is not yet supported
    at Parser.parseErrorMessage (/Users/me/Projects/project/backend/node_modules/pg-protocol/src/parser.ts:369:69)
    at Parser.handlePacket (/Users/me/Projects/project/backend/node_modules/pg-protocol/src/parser.ts:188:21)
    at Parser.parse (/Users/me/Projects/project/backend/node_modules/pg-protocol/src/parser.ts:103:30)
    at Socket.<anonymous> (/Users/me/Projects/project/backend/node_modules/pg-protocol/src/index.ts:7:48)
    at Socket.emit (node:events:526:28)
    at Socket.emit (node:domain:475:12)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at Socket.Readable.push (node:internal/streams/readable:228:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  length: 213,
  severity: 'ERROR',
  code: '0A000',
  detail: undefined,
  hint: 'You have attempted to use a feature that is not yet implemented.\n' +
    'See: https://go.crdb.dev/issue-v/51480/v22.1',
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'drop_type.go',
  line: '53',
  routine: 'DropType'
}
query: ROLLBACK
query failed: DROP TYPE IF EXISTS "public"."sexual_orientations_enum" CASCADE;
error: error: current transaction is aborted, commands ignored until end of transaction block
    at Parser.parseErrorMessage (/Users/me/Projects/project/backend/node_modules/pg-protocol/src/parser.ts:369:69)
    at Parser.handlePacket (/Users/me/Projects/project/backend/node_modules/pg-protocol/src/parser.ts:188:21)
    at Parser.parse (/Users/me/Projects/project/backend/node_modules/pg-protocol/src/parser.ts:103:30)
    at Socket.<anonymous> (/Users/me/Projects/project/backend/node_modules/pg-protocol/src/index.ts:7:48)
    at Socket.emit (node:events:526:28)
    at Socket.emit (node:domain:475:12)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at Socket.Readable.push (node:internal/streams/readable:228:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  length: 143,
  severity: 'ERROR',
  code: '25P02',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'errors.go',
  line: '39',
  routine: 'NewTransactionAbortedError'
}

[postamar]

This will be trivial to implement in the declarative schema changer once DROP COLUMN support is added. This is required because in postgres, if you have a table with a column with a user-defined type, and you DROP TYPE CASCADE that type, this drops the column from the table.

[kris-lwks]

We run into this issue when testing migrating a mysql database into CockroachDB.

[rafiss]

This has been implemented under the new schema changer. We do not have plans to address this in the legacy schema changer. https://www.cockroachlabs.com/docs/stable/online-schema-changes#declarative-schema-changer