Certain types of schema changes don't work with CockroachDB v22.2

[rafiss]

Bug description

CockroachDB had the following change as part of v22.2:

Changed the default value of the enable_implicit_transaction_for_batch_statements to true. This means that a batch of statements sent in one string separated by semicolons is treated as an implicit transaction. #76834

See the release notes. This change was made in order to be compatible with the PostgreSQL behavior documented at multiple statements in a single query.

This has an unfortunate interaction with how some schema changes work in CockroachDB. Specifically, trying to drop and add a column does not work.

This can be resolved in one of two ways:

• Prisma can send the schema changes as separate statements, rather than one large batch statement.
• Prisma can set enable_implicit_transaction_for_batch_statements to false when running these schema changes.

How to reproduce

1. Clone this repo, modified from the Cockroach Labs example Prisma app: AndrewSouthpaw/crdb-prisma-migration-repro-2022-12-12@e69b1e5
2. Start a v22.2 CockroachDB cluster
3. Run prisma migrate dev
4. See the following error

$ node_modules/.bin/prisma migrate dev
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": CockroachDB database "undefined", schema "public" at "localhost:26257"

Error: P3006

Migration `20221212204348_breaking_migration` failed to apply cleanly to the shadow database. 
Error:
db error: ERROR: column "balance" being dropped, try again later
   0: sql_migration_connector::validate_migrations
           with namespaces=None
             at migration-engine/connectors/sql-migration-connector/src/lib.rs:294
   1: migration_core::state::DevDiagnostic
             at migration-engine/core/src/state.rs:251

Expected behavior

The migration should work.

Prisma information

See above repo for schema.

Environment & setup

• Database: CockroachDB v22.2

Prisma Version

3.14

[tomhoule]

Testing on cockroachdb 22.2 has been on our agenda for a while, but we haven't had time to do it yet. This should be tackled in combination.

Of the two suggestions,

Prisma can send the schema changes as separate statements, rather than one large batch statement.

That would require an architecture change, but

Prisma can set enable_implicit_transaction_for_batch_statements to false when running these schema changes.

sounds like an easy change.

Thanks for opening the issue!

[AndrewSouthpaw]

BTW my repro repo was using 3.14 at the time, but I've also tested this against the latest 4.7 and had the same problem, and have updated the repo to reflect it.

[Jolg42]

Look like a similar issue to #17055