dbmate
dbmate copied to clipboard
amacneil
Problems with transaction:false
Hi! I am running dbmate migrations inside docker and for some reason the parameter transaction:false is completely ignored.
-- migrate:up transaction:false
CREATE INDEX CONCURRENTLY IF NOT EXISTS ....
and I still get Error: pq: CREATE INDEX CONCURRENTLY cannot run inside a transaction block
Any advice where can I look, debug, configure?
Info from docker shell
app@742f084ae085:/app$ uname -a
Linux 742f084ae085 4.19.104-microsoft-standard #1 SMP Wed Feb 19 06:37:35 UTC 2020 x86_64 GNU/Linux
Same here. Getting Error: pq: CREATE MATERIALIZED VIEW ... WITH DATA cannot run inside a transaction block on version 1.11.0 with:
-- migrate:up transaction:false
create materialized view
...
Turns out I had more than one statement in the up migration, and apparently it can only be a single one if not in transaction.
That's definitely not intentional. I don't have time to investigate right now, but it should be possible to run multiple statements with transaction:false.
I'm fairly sure this used to work, so if someone has time to test out previous versions and figure out when this broke (transaction:false was added in v1.5.0) that would be helpful.
The thing is that PostgreSQL wraps statements into internal transaction if they are executed outside of transaction. See (hacky) solution of that for go-migrate here
fwiw, I tried running this migration on v1.5 and it still fails with Error: pq: CREATE INDEX CONCURRENTLY cannot run inside a transaction block. However commenting out to only have 1 index created at a time worked, so seems like this has never worked for multiple statements, or at the very least multiple concurrent index creations.
-- migrate:up transaction:false
CREATE INDEX CONCURRENTLY invalidated_at_index ON jobs (invalidated_at);
CREATE INDEX CONCURRENTLY state_index ON jobs (state);
CREATE INDEX CONCURRENTLY status_index ON jobs (status);
-- migrate:down
DROP INDEX invalidated_at_index;
DROP INDEX state_index;
DROP INDEX status_index;
DROP INDEX metadata_expiring_rekick_submitted_index;
This is likely the same issue as in https://github.com/amacneil/dbmate/issues/126.
