transaction:false not working on PostgreSQL

[kashifsoofi]

Hi,
We are using dbmate for migrations, we are running into an issue where we want to run following

-- migrate:up transaction:false

CREATE INDEX CONCURRENTLY index_name ON table(new_id);

We are getting following error
Error: pq: CREATE INDEX CONCURRENTLY cannot run inside a transaction block

I didn't had to chance to run dbmate from source, has anyone else run into similar?

Thanks

[rohitpaulk]

@kashifsoofi could you confirm what version of dbmate you're running?

[rohitpaulk]

The transaction:false option was added in https://github.com/amacneil/dbmate/releases/tag/v1.5.0, I want to make sure that you're running a version that includes that change.

[kashifsoofi]

I have tested with dbmate version 1.11.0 and dbmate version 1.14.0, from code it looks like it should not create a transaction

[rohitpaulk]

Got it, thanks! Will see if I can replicate.

[kashifsoofi]

If I run a single CREATE INDEX CONCURRENTLY statement then there is no error

[rohitpaulk]

@kashifsoofi the example in the description has only one statement - could you paste an example file here that triggers the error?

[kashifsoofi]

Modifying test file in dbmate code

-- migrate:up
create table users (
  id integer,
  name varchar(255)
);
insert into users (id, name) values (1, 'alice');

CREATE INDEX CONCURRENTLY index_users_on_id ON users(id);

-- migrate:down
drop table users;

Like I said, if i create another migration file, with only CREATE INDEX statement it works, work around would be to create lot of files :(

[kashifsoofi]

I think the solution would be to somehow run each statement 1 by 1 incase there are multiple statements in a migration file. Maybe with an option multi-statement:true so that script owner can control it 🤔

[kashifsoofi]

Something like this

func (m migrationOptions) MultiStatement() bool {
	if val, ok := m["multi-statement"]; ok {
		return val != "true"
	}

	return false
}

And then in db.go

...
			err = doTransaction(sqlDB, execMigration)
		} else if up.Options.MultiStatement() {
			var stmtSeparatorRegExp = regexp.MustCompile(`;`)
			stmts := stmtSeparatorRegExp.Split(up.Contents, -1)

			for i, stmt := range stmts {
				err = execMigrationStmt(sqlDB, stmt, i+1 == len(stmts))
				if err != nil {
					break
				}
			}
		} else {
			// run outside of transaction
...

[rohitpaulk]

#285 (comment)

@kashifsoofi if you use transaction:false in the script in that comment, does the error still occur? i.e. something like this:

-- migrate:up transaction:false
create table users (
  id integer,
  name varchar(255)
);
insert into users (id, name) values (1, 'alice');

CREATE INDEX CONCURRENTLY index_users_on_id ON users(id);

-- migrate:down
drop table users;

[kashifsoofi]

@rohitpaulk Yes it does occur, however if I have file with only single step then there is no error

-- migrate:up transaction:false
CREATE INDEX CONCURRENTLY index_users_on_id ON users(id);

-- migrate:down

[x-EricH-x]

@rohitpaulk Yes it does occur, however if I have file with only single step then there is no error

-- migrate:up transaction:false
CREATE INDEX CONCURRENTLY index_users_on_id ON users(id);

-- migrate:down

I can confirm the same issue,
-- migrate:up transaction:false works when there is only one statement,
and fails when there are multiple.

[amacneil]

Duplicate of #182