Comments
3 comments
-
Official commentHi Vlad,
Unfortunately, Flyway itself cannot change how PostgreSQL handles DDL locking.
The ALTER TABLE ADD COLUMN command in Postgres requires an ACCESS EXCLUSIVE lock, which blocks read and writes to the table until the statement completes. This is a PostgreSQL behavior, not something Flyway can override.
To change this, you could change the DDL statements, but that would require manual change.
You're correct in your assumption, that property only affects its own flyway_schema_history table.
-
Hi Dan,
Thank you very much for your answer! Helped a lot!
Have a nice day!
-
Yeah, your understanding is right. Flyway can’t change the lock mode PostgreSQL uses for ALTER TABLE; that part is controlled by PostgreSQL itself. The transactional-lock setting is about Flyway’s own advisory lock for coordinating migrations, not the ACCESS EXCLUSIVE lock taken by the DDL.
For a busy production table, I’d keep the migration as short as possible and consider setting a small lock_timeout so the migration fails instead of sitting there waiting and blocking traffic. For larger schema changes, an expand/contract approach is usually safer.
Add comment
Please sign in to leave a comment.
Hi,
Context: I have a setup as follows: Spring Boot app + flyway 10.2 + Postgres 16.8 in AWS. There are a lot of db operations going on in production.
Problem: I noticed in the last months when I needed to add a new column with flyway(ALTER TABLE mytable ADD COLUMN identifier bigint) in production, a lot of calls to the application started to fail due to the fact that an ACCESS EXCLUSIVE lock was required for the ALTER command so no new transactions where allowed any more until the alter command was executed.
Questions:
1. Is this something flyway can improve? I would assume not because I don't see how flyway can influence how postgres alter table behaves.
2. I assume the property spring.flyway.postgresql.transactional-lock is related exclusively to how flyway interacts with its own table(flyway_schema_history) in postgres and not how the postgres DDL like ALTER TABLE is executed, right?Thank you!