Problems with transaction:false

[trsh]

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?

[amacneil]

Which version of dbmate are you using?

[trsh]

@amacneil

VERSION:
1.10.0

[trsh]

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

[trsh]

Did upgrade to dbmate version 1.11.0 and same :(

[polymeris]

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 
...

[polymeris]

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.

[amacneil]

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.

[AnatolyRugalev]

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

[atombender]

This is still a problem with 1.12.1.

[Whoaa512]

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;

[vrajanap]

This is likely the same issue as in #126.