Run a migration by hand and update the migration table manually

[kevinsimper]

Problem

Adding indexes should be done concurrently in postgres

Prisma generates indexes in the migration.

Indexes concurrently can't be added in a transaction

I run the migration manually by hand and comment out the add indexes while running prisma migrate deploy

Afterwards Prisma deploy will say

WARNING The following migrations have been modified since they were applied:

Suggested solution

One solution could be:

Output the checksum so that I can update it manually

[janpio]

Adding indexes should be done concurrently in postgres

Prisma generates indexes in the migration.

Indexes concurrently can't be added in a transaction

Can you please elaborate a bit what you mean by that exactly? What does "should be done concurrently" mean?

[kevinsimper]

Hi @janpio 😄

What does "should be done concurrently" mean?

If you add a index to a tabel it will lock writes to it. Postgres supports writing a index CONCURRENTLY. It takes longer, but does not lock the table. https://www.postgresql.org/docs/9.1/sql-createindex.html

a standard index build locks out writes (but not reads) on the table until it's done.

But a index CONCURRENTLY can not be added in a transaction block.

Adding a column to a table with milions of rows took for me 5 mins adding it concurrently. Adding it without concurrent would have been quicker, but I did not want not to write to the tables at the same time.

[janpio]

Ok. So you wanted to modify the generated migration vs. what migrate dev had created for you, then apply it via migrate dev or migrate deploy? But afterwards you got a message that the migration file was modified?

[kevinsimper]

@janpio Yes correct!

[janpio]

Ok, so to recap:

1. Create migration with migrate dev
2. Realize migration contains index changes
3. Run modified index changes (adding CONCURRENTLY) by hand (as does not work in transaction)
4. Comment out indexes in migration file
5. Run migrate deploy
6. Future migrate commands will complain about a migration being edited

Is that correct?

[kevinsimper]

@janpio Close, it is this steps

1. Create migration with migrate dev
2. Realize migration contains index changes
3. Comment out create indexes, run migration that adds columns.
4. Run modified index changes (adding CONCURRENTLY) by hand (as does not work in transaction)
5. Commit migration as it was initially generated
6. Future migrate commands will complain about a migration being edited

[janpio]

3. Comment out create indexes, run migration that adds columns.

What command are you using to do this?

If you commit a different migration (step 5) than what was run (step 3), I think it is probably expected to get a complaint because the checksum of the migration file is written in step 3, but then on next run compared to what was committed in step 5 - and those won't match. Does that make sense?

(Of course does not help you to figure out a way how to run it the way you expect and want it to run)

[jho-gm]

(For context, I work with Kevin, so I'm not just barging in :-))

@janpio

I think your last message sums up the problem; the checksum in the table is based on the migration run in step 3, but the file on disk after step 5 generates a different checksum, so Prisma complains about those not matching ('The following migrations have been modified since they were applied').

The solution would seem to be to manually updating the checksum in _prisma_migrations (so that it matches the generated file, i.e., step 5). I think this is a reasonable compromise, but that would still require knowing how the checksum is constructed (and ideally in an easily accessible fashion, e.g. some sort of prisma checksum <file> CLI).

[tomhoule]

Candidating this issue for discussion (at least), since we're working on migrate DX, and we may want to just remove these warnings (telling you if a migration has been edited is a job for migrate status).

[aledalgrande]

@janpio is CREATE INDEX CONCURRENTLY in the roadmap for Postgres?

[janpio]

Everything that is not supported is on the roadmap - the question is when exactly. Right now CREATE INDEX CONCURRENTLY is not super high on that list as we have more basic functionality (see feature requests).

[aledalgrande]

Thanks for the update. That feature would really help with databases that have tables with millions of rows with a lot of traffic, as creating an index with locking is not feasible.

[luissquall]

@jho-gm did you find a simple way to generate the checksum (via bash, another command, etc.) of the updated migration? Thanks!