sql: schema updates have problematic shortcomings

[knz]

This is a meta issue that aims to collect and structure the various UX issues around schema changes.

• Issues arising from performing schema updates inside a transaction:

  • sql: schema changes in SQL transactions don't make sense sql: schema changes in SQL transactions don't make sense #14280
  • sql: Can't drop and replace a table within a transaction sql: Can't drop and replace a table within a transaction #12123
  • sql: index creation fails if issued just after create table in same txn sql: index creation fails if issued just after create table in same txn #13505
  • sql: retry errors report the wrong key if the table was created in the same txn sql: retry errors report the wrong key if the table was created in the same txn #14282

• Issues in our use of table leases:

  • sql: a txn can obtain a lease on an arbitrarily-old descriptor version sql: a txn can obtain a lease on an arbitrarily-old descriptor version #6774
  • sql: a txn might use a table descriptor lease from the future sql: a txn might use a table descriptor lease from the future #6773
  • sql: use the same version of table descriptor for the lifetime of a transaction sql: use the same version of table descriptor for the lifetime of a transaction #2948

• Issues arising from asynchronous schema updates outside of a transaction:

  • Can't use table descriptors while async change is ongoing (improper locking in leasemgr?)

    • sql: all inspection queries hang during table dropping sql: all inspection queries hang during table dropping #13460
    • sql: hang during prepared query within a transaction sql: hang during prepared query within a transaction #13468

  • Can't use table name after it's been dropped:

    • sql: dropping a table prevents recreating a table with the same name sql: dropping a table prevents recreating a table with the same name #13700
    • sql: drop table and recreate fail sql: drop table and recreate fail #7348

  • Other/misc

    • sql: DROP takes a loooooong time sql: DROP DATABASE takes a looooong time #14279
    • sql: DROP hangs after server restart sql: DROP hangs after server restart #14246
    • sql: non-recoverable errors during schema changes do not cause schema change to abort sql: non-recoverable errors during schema changes do not cause schema change to abort #13872
    • sql: adding column hangs and leaves server in bad state sql: adding column hangs and leaves server in bad state #13380
    • sql: index backfill not terminating distsql: avoid dead nodes #13655
    • sql: give users a way to wait for their schema changes to fully apply and expose errors sql: give users a way to wait for their schema changes to fully apply and expose errors #4646

• Improper handling of schema change semantics:

  • sql: can define invalid column default sql: can define improperly typed column default #13873
  • sql: tables with an interleaved child cannot be DROPed without CASCADE sql: tables with an interleaved child cannot be DROPed without CASCADE #8036
  • sql: Problems with DROP COLUMN and DROP CONSTRAINT behavior sql: Problems with DROP COLUMN and DROP CONSTRAINT behavior #10122
  • sql: DROP INDEX...RESTRICT fails to prevent dropping indexes with dependent constraints sql: DROP INDEX...RESTRICT fails to prevent dropping indexes with dependent constraints #12620
  • sql: TRUNCATE's CASCADE clause unsupported sql: TRUNCATE's CASCADE clause unsupported #8502

Slightly related wishlist, listed here for reference but not targeted by the meta-issue

• sql: support adding constraints or columns with constraints sql: support adding constraints or columns with constraints #8773
• sql: Support ALTER TABLE __ ADD CONSTRAINT __ FOREIGN KEY __ statements sql: Support ALTER TABLE __ ADD CONSTRAINT __ FOREIGN KEY __ statements #8855

cc @vivekmenezes

[vivekmenezes]

closing this issue because I don't want us to be tracking meta issues. Thanks for documenting this.

[vivekmenezes]

reopening since folks find it useful

[vivekmenezes]

closing in favor of https://github.com/cockroachdb/cockroach/projects/4

Thanks

[knz]

Nice initiative!