prisma: prisma migrate meta issue

[otan]

This issue captures known issues with using prisma migrate by running the test suite.

---

• CockroachDB branch: https://github.com/cockroachdb/cockroach/pull/72782 Setup:

  CREATE USER prisma;
  GRANT admin TO prisma;
  SET CLUSTER SETTING sql.defaults.default_int_size = 4;
  SET CLUSTER SETTING sql.defaults.serial_normalization = 'sql_sequence';
  SET CLUSTER SETTING schemachanger.backfiller.buffer_increment = '128 KiB';
  SET CLUSTER SETTING sql.defaults.experimental_alter_column_type.enabled = true;

• Prisma branch: https://github.com/prisma/prisma-engines/compare/master...otan-cockroach:migration_stuff?expand=1
• Raw output failures (cargo test -p migration-engine-tests 2>&1 | tee ~/prisma-migrate-log): https://gist.github.com/otan/2158e17ab21596968553c6ae15550a40

---

Blocking issues:

• [ ] (prisma issue) is_cockroach check does not work here: https://github.com/prisma/prisma-engines/blob/a72b5e6e3c931bab4f2c2f3e04e4ac893c79e132/libs/sql-schema-describer/src/postgres.rs#L156
• [ ] (prisma can get around this, at the heart a cockroachdb issue) add pseudo support for CREATE EXTENSION uuid-ossp: https://github.com/prisma/prisma-engines/commit/8e5838c0c35ff98430462bf711503e629b15541b

Somewhat ignorable issues (we can probably skip these tests if we don't think they matter)

• [ ] (cockroachdb issue) a lot of ERROR: column "field" being dropped, try again later related errors applying prisma schema changes. investigate and separate txn on prisma side if possible.
• [ ] (cockroachdb/prisma issue) Prisma drops a PRIMARY KEY and sets another set of fields as PK on PG. This setup needs to use ALTER PRIMARY KEY in CockroachDB (apparently we support this if a PK is being dropped and added in the same txn, need to confirm the prisma side). There may be some tests just dropping the PK for fun - we should skip those.
• [ ] (cockroachdb issue) ALTER COLUMN TYPE cannot be used in combination with other ALTER TABLE commands
• [ ] (cockroachdb issue) unimplemented: ALTER COLUMN TYPE is not supported inside a transaction
• [ ] (cockroachdb issue) ALTER COLUMN TYPE requiring rewrite of on-disk data is currently not supported for columns that are part of an index

Ignorable issues (probably will skip tests):

• [ ] (cockroachdb issue) XML is not supported (a few of these)
• [ ] (cockroachdb issue) ERROR: unimplemented: extension "citext" is not yet supported (citext_to_text_and_back_works)
• [ ] (cockroachdb issue) CockroachDB does not support JSON and translates everything to JSONB (database_level_json_defaults_can_be_defined)
• [ ] (cockroachdb issue) ALTER COLUMN TYPE is heavily used, but we have still marked it as experimental
• [ ] (cockroachdb issue) money, point types not supported

Epic CRDB-12077

Jira issue: CRDB-11438

[ajwerner]

(cockroachdb/prisma issue) Prisma drops a PRIMARY KEY and sets another set of fields as PK on PG. This setup needs to use ALTER PRIMARY KEY in CockroachDB.

We support this syntax within a transaction.

[otan]

We support this syntax within a transaction.

guess prisma doesn't do it within a txn then, hehe! (haven't looked deep enough to know, but they recalled it being a problem and i see some error messages related to it)

edit: looks like they drop PKs, do schema changes, then sometimes readd it