[Evolve Exception] CREATE INDEX CONCURRENTLY cannot be executed within a pipeline Sql query: -- evolve-tx-off

[santosh-kumar-metikoti]

Environment

• Npgsql version: 4.1.3.1
• Evolve: 2.4.0
• Database: PostgreSQL: 11.20
• OS: Windows 11 x86 bit
• .NET version: 5.0

Description

• When executing a migration file without a transaction (by using --evolve-tx-off), creating multiple indexes concurrently in a single migration file is throwing a evolve exception in postgresql 11.20

• It is not throwing any error when

  Exception message:

  Evolve.EvolveException: 'Error executing script: V202307111148__create_index_testresult.sql after 3 ms. 25001: CREATE INDEX CONCURRENTLY cannot be executed within a pipeline Sql query: -- evolve-tx-off  CREATE UNIQUE INDEX CONCURRENTLY test_testresult ON public.test(testresult);  C... 25001: CREATE INDEX CONCURRENTLY cannot be executed within a pipeline'

• This exception didn’t occur in PostgreSQL 10, but this is found in later on some major versions like PostgreSQL 11,12,13

Steps to Reproduce

• Create a table named test with the below syntax in a PostgreSQL 11.02 database

  create table test(
  	id serial not null,
  	testname varchar(32) not null,
  	testresult varchar(32) not null,
  
  	constraint test_pkey primary key(id)
  )

• After creating the table test, create a versioned migration file with contents as shown below

  -- evolve-tx-off
  
  CREATE UNIQUE INDEX CONCURRENTLY test_testname ON public.test(testname);
  
  CREATE UNIQUE INDEX CONCURRENTLY test_testresult ON public.test(testresult);

• Connect to this database from the application and run it.

• You will come across the exception as mentioned above.

Actual Project

• You can check the C# project regarding the same as done by above steps in here: https://github.com/santosh-kumar-metikoti/EvolveProblemWithConcurrentIndexes

Expected/Desired Behavior

• Two indexes should be created without any exception

Actual Behavior

• Getting Evolve exception

  Evolve.EvolveException: 'Error executing script: V202307111148__create_index_testresult.sql after 3 ms. 25001: CREATE INDEX CONCURRENTLY cannot be executed within a pipeline Sql query: -- evolve-tx-off  CREATE UNIQUE INDEX CONCURRENTLY test_testresult ON public.test(testresult);  C... 25001: CREATE INDEX CONCURRENTLY cannot be executed within a pipeline'

Additional Context

• Same problem occurred in Liquibase as mentioned [here](CREATE INDEX CONCURRENTLY cannot be executed within a pipeline liquibase/liquibase#3806)
• Same Problem occurred in DbUp as mentioned in [here](PostgreSQL 14.7, 11.19 incompatibility "CREATE INDEX CONCURRENTLY cannot be executed within a pipeline" DbUp/dbup-postgresql#10)
• See PostgreSQL expert answer [here](https://www.postgresql.org/message-id/4139340.1676050723%40sss.pgh.pa.us) (taken from the Liquibase issue)