Getting pq: syntax error at or near "transaction" when running migration with transaction:false about dbmate

[dossy]

I was able to reproduce this issue with the latest dbmate 2.7.0-main, against PostgreSQL 10.21:

root@c2a4e68d4451:/src# cat testdata/db/migrations/455_test_transaction_concurrently.sql 
-- migrate:up transaction:false

drop index concurrently my_index;

create index concurrently my_index on my_table (
    some_column
);

-- migrate:down

root@c2a4e68d4451:/src# dist/dbmate -u $POSTGRES_TEST_URL -d testdata/db/migrations up
Applying: 455_test_transaction_concurrently.sql
Error: pq: DROP INDEX CONCURRENTLY cannot be executed from a function or multi-command string

Searching for that particular error turns up lib/pq issue #820, and our own #126 that references it, where @amacneil wrote:

It sounds like this is a limitation of the lib/pq driver, and I'm not aware of any other golang drivers for postgresql. Therefore, I will close this with the workaround of putting each concurrent index creation in a separate migration file.

And this issue came up again in #182.

It looks like this is ultimately an issue with how lib/pq and Postgres handle transactions and support for multi-command queries, and isn't something that can be fixed from dbmate's end.

The workaround is to create separate migration files for each CREATE INDEX CONCURRENTLY command, which isn't great but it's simple and it works.

Perhaps the migrations option section of the README could document this, including the various error string responses above so that people searching may be able to find the information?

This is certainly a problem that has occurred repeatedly over the years, so I think there should be value in mentioning it clearly in the documentation.

[docapotamus]

Taking a look at this, I've created similar migration:

-- migrate:up transaction:false
drop index concurrently idx;

create index concurrently idx on test (id);

I get a slightly different error which actually states it can't work within a transaction:

% dbmate up
Applying: 20230831142614_error.sql
Error: pq: DROP INDEX CONCURRENTLY cannot run inside a transaction block

Details:

• Version: 2.6.0
• Database: PostgreSQL 15.3
• System: MacOS 13.4.1

Not got my test enviornment setup back up yet, I'll investigate more soon.

[dossy]

Duplicate of #126.